Wednesday, December 16, 2020

How to fix error SYS.DBMS_BACKUP_RESTORE version 18.11.00.00 in TARGET database is not current

Even if you're not using a recovery catalog, you may encounter this issue if you have recently patched your database. Simply by connecting to the target database as sysdba, you can verify that you need to update your internal packages:
oracle@myserver.mydomain.com:[proddb01]# rman target / nocatalog

Recovery Manager: Release 18.0.0.0.0 - Production on Wed Dec 16 13:51:43 2020
Version 18.12.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

PL/SQL package SYS.DBMS_BACKUP_RESTORE version 18.11.00.00 in TARGET database is not current
PL/SQL package SYS.DBMS_RCVMAN version 18.11.00.00 in TARGET database is not current
connected to target database: PRODDB01 (DBID=1234567890)
using target database control file instead of recovery catalog
Solution: Follow Doc ID 888818.1: "Rman 06190 Connecting to target database after upgrade" In short, connect to the database as sysdba, and run the following scripts:
SQL> @$ORACLE_HOME/rdbms/admin/dbmsrman.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtrmns.plb   
SQL> @$ORACLE_HOME/rdbms/admin/dbmsbkrs.sql 
SQL> @$ORACLE_HOME/rdbms/admin/prvtbkrs.plb

Friday, December 4, 2020

How to view the contens of a zip file without extracting it

Method 1:
zip -sf myzipfile.zip
Method 2:
zipinfo myzipfile.zip

How to mark an existing SQL Plan baseline as fixed

Thanks a lot to Tim Hall for providing this simple example:
SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_7ff180a4583f257d',
    plan_name       => 'SQL_PLAN_7zwc0njc3y9bx2c993bf4',
    attribute_name  => 'fixed',
    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/
The details for the plan baselines can be found like this:
SELECT sql_handle,plan_name,origin,enabled,accepted,fixed
FROM dba_sql_plan_baselines
WHERE plan_name ='SQL_PLAN_7zwc0njc3y9bx2c993bf4';
Result:
SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED FIXED
SQL_7ff180a4583f257d SQL_PLAN_7zwc0njc3y9bx2c993bf4 AUTO-CAPTURE YES YES YES

After fixing the plan, all the plan alternatives for the SQL ID can be seen below:
SELECT TO_CHAR(LAST_ACTIVE_TIME, 'dd.mm.yyyy hh24:mi') "last active",
       SQL_ID,
       PLAN_HASH_VALUE,
       SQL_PLAN_BASELINE "BASELINE", 
       SQL_PROFILE,
       IS_RESOLVED_ADAPTIVE_PLAN,
       CHILD_NUMBER AS "Child Num",
       ELAPSED_TIME,
       EXECUTIONS,
       ROUND(ELAPSED_TIME/1000000) "duration (sec)"
       ,CASE WHEN EXECUTIONS > 0 THEN
        ROUND( (ELAPSED_TIME/EXECUTIONS)/1000000, 1)
        ELSE
            NULL
       END "sec per exe"
FROM V$SQL 
WHERE SQL_ID in('2mympbsn3r4rk')
ORDER BY sql_id,LAST_ACTIVE_TIME DESC;
Result:
last active SQL_ID PLAN_HASH_VALUE BASELINE SQL_PROFILE IS_RESOLVED_ADAPTIVE_PLAN Child Num ELAPSED_TIME EXECUTIONS duration (sec) sec per exe
04.12.2020 09:49 2mympbsn3r4rk
480132689
SQL_PLAN_7zwc0njc3y9bx3bf43977    
1
612838711
116
613
5,3
04.12.2020 09:49 2mympbsn3r4rk
3102497174
SQL_PLAN_7zwc0njc3y9bx2c993bf4   Y
2
203961
191
0
0
04.12.2020 08:49 2mympbsn3r4rk
480132689
SQL_PLAN_7zwc0njc3y9bx3bf43977    
0
2247452482
30
2247
74,9

Tuesday, November 10, 2020

Date formatting in PostgreSQL

select action_type, to_char(action_date,'DD Mon YYYY'), count(*) from actions group by action_type,
action_type     |   to_char   | count
-----------------+-------------+-------
 action1 | 27 Oct 2020 | 47831
 action1 | 22 Oct 2020 |   640
 action1 | 20 Oct 2020 |     1
 action1 | 22 Oct 2020 |  1654
 action1 | 26 Oct 2020 |   290
 action2 | 21 Oct 2020 |     8
 action2 | 27 Oct 2020 |   140
 action2 | 26 Oct 2020 |   900

Documentation here

Thursday, November 5, 2020

How to change the control_files parameter used in the spfile

Mind your syntax when you are changing certain multi-value parameters directly in the spfile. Oracle will tell you that the syntax is accepted, but it will give you an error later. The following example is from an attempt to change the control_files parameter.

Won't work:
SQL> alter system set control_files='/oradata/proddb01/control01.ctl, /fra/proddb01/control02.ctl' scope=spfile;

System altered.
During mount stage, this will give you the following message in the database's alert log:
ORA-205 signalled during: ALTER DATABASE   MOUNT...
ORA-00202: control file: /oradata/proddb01/control01.ctl, /fra/proddb01/control02.ctl
ORA-27037: cannot obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Works:
SQL> alter system set control_files='/oradata/proddb01/control01.ctl','/fra/proddb01/control02.ctl' scope=spfile;

System altered.
You should enclose both values within ' ' apostroph characters.

Wednesday, October 21, 2020

How to transfer SQL Profiles from one database to another

Following up from part 1 on my post on How to transfer SQL Plan Baselines from one database to another, I will now show you the steps to transfer a SQL Profile in the same manner.
For SQL Profiles you depend on the package dbms_sqltune
I am connecting to the databases as a privileged user called dbadmin throughout this post.

1. First, find the name of SQL profile you are about to transfer:
select * from dba_sql_profiles order by created;
The query above will return the SQL Profiles in the order in which they were created. Notice the name of the relevant one, as this will be used as an argument in the procedures later.

2. On the source database, create a staging table for SQL Profiles:
vi 2.cre_SQLProfile_stgtab.sql
Add the following:
connect dbadmin
BEGIN
  DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (
    table_name  => 'MYSTAGETAB_SQLPROF'
,   schema_name => 'DBADMIN'
);
END;
/
exit

Execute the file:
sqlplus /nolog @2.cre_SQLProfile_stgtab.sql
3. Pack your SQL Profile into your staging table

Create the file 3.pack_SQLProfile_stgtab.sql:
vi 3.pack_SQLProfile_stgtab.sql
Add the following:
conn dbadmin
alter session set nls_language='american';

BEGIN
  DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (
    profile_name         => 'SYS_SQLPROF_014fb0ca24980001'
,   staging_table_name   => 'MYSTAGETAB_SQLPROF'
,   staging_schema_owner => 'DBADMIN'
);
END;
/
exit
Run the file:
sqlplus /nolog @3.pack_SQLProfile_stgtab.sql
Let's verify that the staging table now has some rows in it:
connect dbadmin
select count(*)
from  MYSTAGETAB_SQLPROF;
4. Create the export parameter file and export the table:

vi 4.expdp_SQLPROFILE_stgtab.sql

Add the following:

userid=dbaadmin
tables=MYSTAGETAB_SQLPROF
logfile=expdp_MYSTAGETAB_SQLPROF.log
dumpfile=MYSTAGETAB_SQLPROF.dmp
job_name=exp_MYSTAGETAB_SQLPROF
directory=DP


Make sure the directory DP exists in the database. If not, create it with
create directory DP as '/exports/';
Then export the table:
expdp parfile=4.expdp_SQLPROFILE_stgtab.sql
Logon to the destination server. 

5. Import the staging table in the destination database

Create an import parameter file:
vi 5.impdp_SQLProfile.par
Add the following:
userid=dbadmin
tables=MYSTAGETAB_SQLPROF
logfile=impdp_MYSTAGETAB_SQLPROF.log
dumpfile=MYSTAGETAB_SQLPROF.dmp
job_name=imp_MYSTAGETAB_SQLPROF
directory=DP
Run the import:
impdp parfile=5.impdp_SQLProfile.par
6. Unpack the information in the staging table

Create the file for unpacking the SQL Plans to the local SMB:
vi 6.unpack.sql
Add the following:
conn dbadmin
alter session set nls_language='american';
set serveroutput on
BEGIN
   DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
     replace            => true
,    staging_table_name => 'MYSTAGETAB_SQLPROF'
,    profile_name => 'SYS_SQLPROF_014fb0ca24980001'
);
END;
/
exit
Execute the file:
sqlplus /nolog @6.unpack.sql

Let's verify that the optimizer has actually created a new execution plan based on the SQL Profile:
SELECT TO_CHAR(LAST_ACTIVE_TIME, 'dd.mm.yyyy hh24:mi') "last active",
       SQL_ID,
       PLAN_HASH_VALUE,
       SQL_PLAN_BASELINE "BASELINE", 
       SQL_PROFILE,
       IS_RESOLVED_ADAPTIVE_PLAN,
       CHILD_NUMBER AS "Child Num",
       ELAPSED_TIME,
       EXECUTIONS,
       ROUND(ELAPSED_TIME/1000000) "duration (sec)"
       ,CASE WHEN EXECUTIONS > 0 THEN
        ROUND( (ELAPSED_TIME/EXECUTIONS)/1000000, 1)
        ELSE
            NULL
       END "sec per exe"
FROM V$SQL 
WHERE SQL_ID in('5cbuqgt5m5961')
ORDER BY LAST_ACTIVE_TIME DESC;
Output:
last active SQL_ID PLAN_HASH_VALUE BASELINE SQL_PROFILE AdaptivePlan Child Num ELAPSED_TIME EXECUTIONS duration (sec) sec per exe
21.10.2020 13:19 5cbuqgt5m5961
1355560190
     
1
20850937263
1488
20851
14
21.10.2020 12:55 5cbuqgt5m5961
1355560190
     
0
112190741129
27134
112191
4,1
21.10.2020 14:47 5cbuqgt5m5961
3389950398
SQL_PLAN_ds58srfg86uf4f5a21fb1 SYS_SQLPROF_014fb0ca24980001  
3
116651
302
0
0
After the SQL Profile was created, the performance was finally stabelized.
The documentation for the dbms_sqltune package can be found here

How to transfer SQL Plan baselines from one database to another

A customer called with a request to transfer a specific SQL plan from production to test. The query was found in the shared pool:
SELECT TO_CHAR(LAST_ACTIVE_TIME, 'dd.mm.yyyy hh24:mi') "last active",
       SQL_ID,
       PLAN_HASH_VALUE,
       SQL_PLAN_BASELINE "BASELINE", 
       SQL_PROFILE,
       IS_RESOLVED_ADAPTIVE_PLAN "AdaptivePlan",
       CHILD_NUMBER AS "Child Num",
       ELAPSED_TIME,
       EXECUTIONS,
       ROUND(ELAPSED_TIME/1000000) "duration (sec)"
       ,CASE WHEN EXECUTIONS > 0 THEN
        ROUND( (ELAPSED_TIME/EXECUTIONS)/1000000, 1)
        ELSE
            NULL
       END "sec per exe"
FROM V$SQL 
WHERE SQL_ID in('5cbuqgt5m5961')
ORDER BY LAST_ACTIVE_TIME DESC;
last active SQL_ID PLAN_HASH_VALUE BASELINE SQL_PROFILE AdaptivePlan Child Num ELAPSED_TIME EXECUTIONS duration (sec) sec per exe
21.10.2020 13:34 5cbuqgt5m5961
3389950398
SQL_PLAN_ds58srfg86uf4f5a21fb1 SYS_SQLPROF_014fb0ca24980001  
1
210182320
1402169
210
0

In the test database, the same query does not have a plan in the SMB, nor does it have a SQL Profile. The same query as above gave me this result in the test database:
last active SQL_ID PLAN_HASH_VALUE BASELINE SQL_PROFILE AdaptivePlan Child Num ELAPSED_TIME EXECUTIONS duration (sec) sec per exe
21.10.2020 10:47 5cbuqgt5m5961
1355560190
     
0
110508458900
27032
110508
4,1
We see that the query needs a SQL Plan baseline and a SQL Profile to perform well.
Oracle has a way to export the metadata needed to stabilize the query plan, and import it in another database.
When exporting SQL Plan baselines you would use the package dbms_spm

Here is what I did to transfer the SQL Plan from one database to another.
I am connecting to the databases as a privileged user called dbadmin throughout this post. In part two of this post, I will show you how I transferred the SQL Profile from one database to another.

1. On the source database, create a staging table for SQL Plan Baselines:
vi 1.cre_stagetab.sql
Add the following:
connect dbadmin
BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE (
    table_name => 'MYSTAGETAB');
END;
/
Execute the file:
sqlplus /nolog @1.cre_stagetab.sql
Still in the source database, check who the owner of the baseline is:
SELECT plan_name,
         sql_handle,
         creator,
         origin,
         TO_CHAR (created, 'dd.mm.yyyy hh24:mi') "created",
         TO_CHAR (last_executed, 'dd.mm.yyyy hh24:mi') "lastexecuted",
         enabled,
         accepted,
         fixed,
         autopurge,
         module,
         ROUND (ELAPSED_TIME / 1000000) "duration in seconds",
         cpu_time,
         buffer_gets,
         disk_reads
    FROM DBA_SQL_PLAN_BASELINES
where plan_name='SQL_PLAN_ds58srfg86uf4f5a21fb1';
The output shows that the owner is SYS:
PLAN_NAME SQL_HANDLE CREATOR ORIGIN created lastexecuted ENABLED ACCEPTED FIXED AUTOPURGE MODULE duration in seconds CPU_TIME BUFFER_GETS DISK_READS
SQL_PLAN_ds58srfg86uf4f5a21fb1 SQL_dc1518bb9e8369c4 SYS MANUAL-LOAD-FROM-STS 30.03.2020 15:13 18.10.2020 03:13 YES YES YES YES JDBC Thin Client
8
3897100
597270
715

2. Create the file 2.pack_stagetab.sql:
vi 2.pack_stagetab.sql
Add the following:
conn dbadmin
alter session set nls_language='american';

DECLARE
  v_plan_cnt NUMBER;
BEGIN
  v_plan_cnt := DBMS_SPM.PACK_STGTAB_BASELINE (
    table_name => 'MYSTAGETAB'
,   enabled    => 'yes'
,   creator    => 'SYS'
,   plan_name  => 'SQL_PLAN_ds58srfg86uf4f5a21fb1'
);
END;
/
exit
Run the file:
sqlplus /nolog @2.pack_stagetab.sql
Let's verify that the staging table has some rows in it:
connect dbadmin
select count(*)
from  MYSTAGETAB;
3. Create an export parameter file:
vi 3.expdp.par
Add the following. Make sure the directory DP exist and is writable:
userid=dbadmin
tables=MYSTAGETAB
logfile=expdp_MYSTAGETAB.log
dumpfile=MYSTAGETAB.dmp
job_name=exp_MYSTAGETAB
directory=DP
Run the export:
expdp parfile=3.expdp.par
When the export is done, transfer the file to the destination server using ssh.
At this point logon to the destination server.

4. Create an import parameter file:
vi 4.impdp.par
Add the following. Make sure the directory DP exist and is writable:
userid=dbadmin
tables=MYSTAGETAB
logfile=impdp_MYSTAGETAB.log
dumpfile=MYSTAGETAB.dmp
job_name=imp_MYSTAGETAB
directory=DP
Run the import:
impdp parfile=4.impdp.par
5. Create the file for unpacking the SQL Plans to the local SMB:
vi 5.unpack.sql
Add the following:
conn dbadmin
alter session set nls_language='american';
set serveroutput on
DECLARE
  v_plan_cnt NUMBER;
BEGIN
  v_plan_cnt := DBMS_SPM.UNPACK_STGTAB_BASELINE (
    table_name => 'MYSTAGETAB'
    ,plan_name  => 'SQL_PLAN_ds58srfg86uf4f5a21fb1'
);
  DBMS_OUTPUT.put_line('Number of plans loaded: ' || v_plan_cnt);
END;
/
exit
Run the file:
sqlplus /nolog @5.unpack.sql
You should now be able to see a different execution plan when executing the query at the top of this article:
last active SQL_ID PLAN_HASH_VALUE BASELINE SQL_PROFILE AdaptivePlan Child Num ELAPSED_TIME EXECUTIONS duration (sec) sec per exe
21.10.2020 13:19 5cbuqgt5m5961
1355560190
     
1
20850937263
1488
20851
14
21.10.2020 12:55 5cbuqgt5m5961
1355560190
     
0
112190741129
27134
112191
4,1
The performance actually got worse! The reason is that we are missing the SQL Profile I will now transfer the SQL Profile to the same server in the next post.