How To Enable DDL Logging in the Database

If for whatever reason, you are required to log DDL, for example, I need to know why the LAST_DDL_TIME of a table was getting updated, you can do this from Oracle 11g.

To enable:

SQL> show parameter ENABLE_DDL_LOGGING

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ 
enable_ddl_logging boolean FALSE

SQL> ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE;

System altered.

SQL> show parameter ENABLE_DDL_LOGGING

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ 
enable_ddl_logging boolean TRUE

To disable:

SQL> show parameter ENABLE_DDL_LOGGING

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ 
enable_ddl_logging boolean TRUE

SQL> ALTER SYSTEM SET ENABLE_DDL_LOGGING=FASLE;

System altered. 

SQL> show parameter ENABLE_DDL_LOGGING 

NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 
enable_ddl_logging boolean FALSE 

Create some DDL:

SQL> create view zeddba as select * from dual;

View created.

SQL> select * from zeddba;





SQL> drop view zeddba;

View dropped.

Oracle 12c

Now if you look in the following text file:
$ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/log/ddl_${ORACLE_SID}.log

You will see:

Mon Sep 11 15:52:59 2017
diag_adl:create view zahid as select * from dual
diag_adl:drop view zahid

There is also a XML version:
$ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/log/ddl/log.xml

<msg time='2017-09-11T15:41:35.000+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4424:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='v1ex1dbadm01.v1.com' host_addr='x.x.x.x'
 version='1'>
 <txt>create view zeddba as select * from dual
 </txt>
</msg>
<msg time='2017-09-11T15:41:45.942+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4424:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='v1ex1dbadm01.v1.com' host_addr='x.x.x.x'>
 <txt>drop view zeddba
 </txt>
</msg>

Oracle 11g

DDL statements are written to the alert log in: $ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log

License

Oracle Database Lifecycle Management Pack for Oracle Database

Licensed Parameters

The init.ora parameter ENABLE_DDL_LOGGING is licensed as part of the Database Lifecycle Management Pack when set to TRUE.  When set to TRUE, the database reports schema changes in real time into the database alert log under the message group schema_ddl. The default setting is FALSE.”

More info

Database Reference: ENABLE_DDL_LOGGING

See MOS Note:
How To Enable DDL Logging in Database (Doc ID 2207341.1)

“When ENABLE_DDL_LOGGING is set to true, the following DDL statements are written to the alert log:

ALTER/CREATE/DROP/TRUNCATE CLUSTER
ALTER/CREATE/DROP FUNCTION
ALTER/CREATE/DROP INDEX
ALTER/CREATE/DROP OUTLINE
ALTER/CREATE/DROP PACKAGE
ALTER/CREATE/DROP PACKAGE BODY
ALTER/CREATE/DROP PROCEDURE
ALTER/CREATE/DROP PROFILE
ALTER/CREATE/DROP SEQUENCE
CREATE/DROP SYNONYM
ALTER/CREATE/DROP/RENAME/TRUNCATE TABLE
ALTER/CREATE/DROP TRIGGER
ALTER/CREATE/DROP TYPE
ALTER/CREATE/DROP TYPE BODY
DROP USER
ALTER/CREATE/DROP VIEW

Earlier, RENAME was not logged and a bug was reported for that and the same is fixed in 11.2.0.4.
Document 12938609.8 – ENABLE_DDL_LOGGING does not log RENAME table statements, this is fixed in 11.2.0.4

However, the feature does not log DDLs of some DBMS_STATS operations like:
set_column_stats
set_index_stats
create_extended_stats
drop_extended_stats
set_*_prefs (table/schema/global etc)
delete_pending_stats
publish_pending_stats
export_pending_stats
create_stat_table 

There is an enhancement raised with development to add more operations to this mechanism and would get fixed in 12.2.

Unpublished Bug 22368778 : PERF_DIAG: ENABLE_DDL_LOGGING NEEDS TO LOG MORE DDLS”

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Advertisements

How to update OPatch

When applying patches, such as PSUs or one-offs, you may need to update OPatch to meet the minimum OPatch version.  It is also recommended to update OPatch when applying any patch.

To see your current OPatch version:

[oracle@v1ex1dbadm01 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
[oracle@v1ex1dbadm01 ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.1.0.1.3

OPatch succeeded.
[oracle@v1ex1dbadm01 ~]$

Backup existing OPatch:

[oracle@v1ex1dbadm01 ~]$ cd $ORACLE_HOME
[oracle@v1ex1dbadm01 dbhome_1]$ tar -cvf OPatch_backup.tar OPatch/*
OPatch/datapatch
OPatch/datapatch.bat
OPatch/docs/
...
OPatch/oplan/README.txt
OPatch/oplan/README.html
OPatch/oplan/oplan
[oracle@v1ex1dbadm01 dbhome_1]$

Check the backup of OPatch:

[oracle@v1ex1dbadm01 dbhome_1]$ ls -lh | grep OPatch_backup.tar
-rw-rw-r--. 1 oracle oracle 6.7M Aug 29 11:51 OPatch_backup.tar
[oracle@v1ex1dbadm01 dbhome_1]$

Remove the existing OPatch:

[oracle@v1ex1dbadm01 dbhome_1]$ rm -rf OPatch

Unzip the latest OPatch:

[oracle@v1ex1dbadm01 dbhome_1]$ unzip -d $ORACLE_HOME ~/sw/p6880880_122010_Linux-x86-64.zip
Archive: /home/oracle/sw/p6880880_122010_Linux-x86-64.zip
creating: /u01/app/oracle/product/12.1.0/dbhome_1/OPatch/
inflating: /u01/app/oracle/product/12.1.0/dbhome_1/OPatch/datapatch
...
inflating: /u01/app/oracle/product/12.1.0/dbhome_1/OPatch/docs/cversion.txt
inflating: /u01/app/oracle/product/12.1.0/dbhome_1/OPatch/docs/FAQ
inflating: /u01/app/oracle/product/12.1.0/dbhome_1/OPatch/opatch.bat
[oracle@v1ex1dbadm01 dbhome_1]$

Which can be found here:
OPatch – Where Can I Find the Latest Version of OPatch(6880880)? [Video] (Doc ID 224346.1)
OPATCH PLACEHOLDER Patch 6880880

To see your newOPatch version:

[oracle@v1ex1dbadm01 dbhome_1]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.9

OPatch succeeded.
[oracle@v1ex1dbadm01 dbhome_1]$

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

How to obtain the Historical Database Total Used and Allocated Size from OEM Repository

From time to time, it’s useful to know the total allocated size of a database at OS level, how much of it has been used and what the maximum total size the database can grow to at OS level, see blog post:
How to obtain the Database Total Used, Allocated and Max Size

However, it’s also good to know the historical size.  The below query will give you this from the Oracle Enterprise Manager (OEM) repository:

SELECT Database,
Month_Date,
round(sum(decode(metric_column, 'spaceUsed', maximum))/1024/1024, 3) Used_Size_Tb,
round(sum(decode(metric_column, 'spaceAllocated', maximum))/1024/1024, 3) Allocated_Size_Tb
FROM
(
SELECT target_name Database, trunc(rollup_timestamp, 'MONTH') Month_Date, key_value TB, metric_column, round(max(maximum),0) maximum
FROM mgmt$metric_daily
WHERE target_type = 'rac_database'
and metric_name = 'tbspAllocation'
and metric_column in ('spaceAllocated', 'spaceUsed')
and target_name in ('VERS')
GROUP BY target_name, key_value, trunc(rollup_timestamp, 'MONTH'), metric_column
)
GROUP BY Database, Month_Date
ORDER BY Database, Month_Date
/

Output:

DATABASE   MONTH_DAT USED_SIZE_TB ALLOCATED_SIZE_TB
---------- --------- ------------ -----------------
VERS       01-SEP-15        1.198             1.554
VERS       01-OCT-15        1.209             1.652
VERS       01-NOV-15          1.3             1.805
...
VERS       01-MAY-17        6.526             7.226
VERS       01-JUN-17        7.085             8.528
VERS       01-JUL-17        7.136             7.569

23 rows selected.

SQL>

The unit is in Tb, which should be suitable for most, however this can be changed by add/removing division of 1024.

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

How to obtain the Database Total Used, Allocated and Max Size

From time to time, it’s useful to know the total allocated size of a database at OS level, how much of it has been used and what the maximum total size the database can grow to at OS level.

The below query will give you this:

SELECT round(sum(used_ts_size)/1024/1024, 2) total_used_db_size_tb,
 round(sum(curr_ts_size)/1024/1024, 2) total_current_db_size_tb,
 round(sum(max_ts_size)/1024/1024, 2) total_max_allocated_db_size_tb
FROM
(SELECT df.tablespace_name, (df.bytes - sum(fs.bytes)) / (1024 * 1024) used_ts_size,
df.bytes / (1024 * 1024) curr_ts_size,
df.maxbytes / (1024 * 1024) max_ts_size
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes
 from dba_data_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes,df.maxbytes);

Output:

SQL> SELECT round(sum(used_ts_size)/1024/1024, 2) total_used_db_size_tb,
 2 round(sum(curr_ts_size)/1024/1024, 2) total_current_db_size_tb,
 3 round(sum(max_ts_size)/1024/1024, 2) total_max_allocated_db_size_tb
 4 FROM
 5 (SELECT df.tablespace_name, (df.bytes - sum(fs.bytes)) / (1024 * 1024) used_ts_size,
 6 df.bytes / (1024 * 1024) curr_ts_size,
 7 df.maxbytes / (1024 * 1024) max_ts_size
 8 FROM dba_free_space fs,
 9 (select tablespace_name,
 10 sum(bytes) bytes,
 11 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes
 12 from dba_data_files
 13 group by tablespace_name) df
 14 WHERE fs.tablespace_name (+) = df.tablespace_name
 15 GROUP BY df.tablespace_name,df.bytes,df.maxbytes);

TOTAL_USED_DB_SIZE_TB TOTAL_CURRENT_DB_SIZE_TB TOTAL_MAX_ALLOCATED_DB_SIZE_TB
--------------------- ------------------------ ------------------------------
                 7.15                     7.36                           9.04

SQL>

The unit is in Tb, which should be suitable for most, however this can be changed by add/removing division of 1024.

Related Post:
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Delete Old SBT_TAPE Backups that are No Longer Available in RMAN

When in the situation of having ‘SBT_TAPE’ backups, that are still known by the catalog or control file, but the device type ‘SBT_TAPE’ is no longer available.  If you try to delete the backups you get the following error message:

RMAN> list backup summary;

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
11208 B 0 A SBT_TAPE 20-JUL-17 1 1 YES TAG20170720T200232
11209 B 0 A SBT_TAPE 20-JUL-17 1 1 YES TAG20170720T200232
11210 B 0 A SBT_TAPE 20-JUL-17 1 1 YES TAG20170720T200232
...
11356 B 0 A SBT_TAPE 28-JUL-17 1 1 YES TAG20170728T200202
11357 B 0 A SBT_TAPE 28-JUL-17 1 1 YES TAG20170728T200202
11358 B A A SBT_TAPE 28-JUL-17 1 1 YES TAG20170728T201725

RMAN> delete backup;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 08/10/2017 12:49:55
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

 

This is because the backups are of device type ‘SBT_TAPE’, which is no longer available.  To remove, you need to allocate channel for maintenance of device type ‘SBT’ but dummy to disk as explained in MOS Note:

Need Help To Remove Old Tape Backups From Catalog (Doc ID 342284.1)

GOAL

How to delete obsolete TAPE backups via RMAN, when the Media Management Layer (MML Software not installed) is NOT available anymore.

So how to delete OLD backups which have been made to tape, while the current backup strategy is to disk and NO Media Manager is available.

SOLUTION

Allocate a maintenance channel with the dummy sbt API and run the DELETE FORCE OBSOLETE.

RMAN> allocate channel for maintenance device type sbt
parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/tmp)';


RMAN> delete force obsolete;

The DUMMY API (oracle.disksbt) is simulating the callout to the Media Management Layer (MML).  This way RMAN thinks an actual MML is available and can perform the maintenance.”

My scenario:

RMAN> allocate channel for maintenance device type sbt
2> parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/tmp)';

allocated channel: ORA_MAINT_SBT_TAPE_1
channel ORA_MAINT_SBT_TAPE_1: SID=808 device type=SBT_TAPE
channel ORA_MAINT_SBT_TAPE_1: WARNING: Oracle Test Disk API

RMAN> delete force obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 8 days
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 11217 20-JUL-17
 Backup Piece 11217 20-JUL-17 vks9rkqo_1_1
Backup Set 11214 20-JUL-17
 Backup Piece 11214 20-JUL-17 vls9rkqo_1_1
Backup Set 11216 20-JUL-17
 Backup Piece 11216 20-JUL-17 vms9rkqo_1_1
...
 Backup Piece 11341 28-JUL-17 3tsagnmq_1_1
Backup Set 11346 28-JUL-17
 Backup Piece 11346 28-JUL-17 3usagnmq_1_1
Backup Set 11340 28-JUL-17
 Backup Piece 11340 28-JUL-17 3vsagnmq_1_1

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=vks9rkqo_1_1 RECID=11217 STAMP=949867353
deleted backup piece
backup piece handle=vls9rkqo_1_1 RECID=11214 STAMP=949867353
deleted backup piece
backup piece handle=vms9rkqo_1_1 RECID=11216 STAMP=949867353
...
deleted backup piece
backup piece handle=3tsagnmq_1_1 RECID=11341 STAMP=950558426
deleted backup piece
backup piece handle=3usagnmq_1_1 RECID=11346 STAMP=950558426
deleted backup piece
backup piece handle=3vsagnmq_1_1 RECID=11340 STAMP=950558426
Deleted 132 objects

RMAN>

 

You may still have some ‘SBT_TAPE’ backups remaining due to your retention policy and hence the backups are not obsolete, therefore you need to crosscheck as follows:

RMAN> list backup summary;

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
11348 B 0 A SBT_TAPE 28-JUL-17 1 1 YES TAG20170728T200202
11349 B 0 A SBT_TAPE 28-JUL-17 1 1 YES TAG20170728T200202
11350 B 0 A SBT_TAPE 28-JUL-17 1 1 YES TAG20170728T200202
11351 B 0 A SBT_TAPE 28-JUL-17 1 1 YES TAG20170728T200202
11352 B 0 A SBT_TAPE 28-JUL-17 1 1 YES TAG20170728T200202
11353 B 0 A SBT_TAPE 28-JUL-17 1 1 YES TAG20170728T200202
11354 B 0 A SBT_TAPE 28-JUL-17 1 1 YES TAG20170728T200202
11355 B 0 A SBT_TAPE 28-JUL-17 1 1 YES TAG20170728T200202
11356 B 0 A SBT_TAPE 28-JUL-17 1 1 YES TAG20170728T200202
11357 B 0 A SBT_TAPE 28-JUL-17 1 1 YES TAG20170728T200202
11358 B A A SBT_TAPE 28-JUL-17 1 1 YES TAG20170728T201725

RMAN> allocate channel for maintenance device type sbt
2> parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/tmp)';

allocated channel: ORA_MAINT_SBT_TAPE_1
channel ORA_MAINT_SBT_TAPE_1: SID=17 device type=SBT_TAPE
channel ORA_MAINT_SBT_TAPE_1: WARNING: Oracle Test Disk API

RMAN> delete backup;

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
11348 11348 1 1 AVAILABLE SBT_TAPE 46sagnpr_1_1
11349 11349 1 1 AVAILABLE SBT_TAPE 47sagnpr_1_1
11350 11350 1 1 AVAILABLE SBT_TAPE 44sagnpr_1_1
11351 11351 1 1 AVAILABLE SBT_TAPE 48sagnqb_1_1
11352 11352 1 1 AVAILABLE SBT_TAPE 49sagnqb_1_1
11353 11353 1 1 AVAILABLE SBT_TAPE 45sagnpr_1_1
11354 11354 1 1 AVAILABLE SBT_TAPE 41sagnpr_1_1
11355 11355 1 1 AVAILABLE SBT_TAPE 43sagnpr_1_1
11356 11356 1 1 AVAILABLE SBT_TAPE 42sagnpr_1_1
11357 11357 1 1 AVAILABLE SBT_TAPE 40sagnpr_1_1
11358 11358 1 1 AVAILABLE SBT_TAPE 4asagoml_1_1

Do you really want to delete the above objects (enter YES or NO)? yes

RMAN-06207: WARNING: 11 objects could not be deleted for SBT_TAPE channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece 46sagnpr_1_1
RMAN-06214: Backup Piece 47sagnpr_1_1
RMAN-06214: Backup Piece 44sagnpr_1_1
RMAN-06214: Backup Piece 48sagnqb_1_1
RMAN-06214: Backup Piece 49sagnqb_1_1
RMAN-06214: Backup Piece 45sagnpr_1_1
RMAN-06214: Backup Piece 41sagnpr_1_1
RMAN-06214: Backup Piece 43sagnpr_1_1
RMAN-06214: Backup Piece 42sagnpr_1_1
RMAN-06214: Backup Piece 40sagnpr_1_1
RMAN-06214: Backup Piece 4asagoml_1_1

RMAN> crosscheck backup;

crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=46sagnpr_1_1 RECID=11348 STAMP=950558523
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=47sagnpr_1_1 RECID=11349 STAMP=950558524
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=44sagnpr_1_1 RECID=11350 STAMP=950558523
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=48sagnqb_1_1 RECID=11351 STAMP=950558539
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=49sagnqb_1_1 RECID=11352 STAMP=950558539
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=45sagnpr_1_1 RECID=11353 STAMP=950558523
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=41sagnpr_1_1 RECID=11354 STAMP=950558523
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=43sagnpr_1_1 RECID=11355 STAMP=950558523
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=42sagnpr_1_1 RECID=11356 STAMP=950558523
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=40sagnpr_1_1 RECID=11357 STAMP=950558523
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=4asagoml_1_1 RECID=11358 STAMP=950559445
Crosschecked 11 objects

RMAN> delete expired backup;

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
11348 11348 1 1 EXPIRED SBT_TAPE 46sagnpr_1_1
11349 11349 1 1 EXPIRED SBT_TAPE 47sagnpr_1_1
11350 11350 1 1 EXPIRED SBT_TAPE 44sagnpr_1_1
11351 11351 1 1 EXPIRED SBT_TAPE 48sagnqb_1_1
11352 11352 1 1 EXPIRED SBT_TAPE 49sagnqb_1_1
11353 11353 1 1 EXPIRED SBT_TAPE 45sagnpr_1_1
11354 11354 1 1 EXPIRED SBT_TAPE 41sagnpr_1_1
11355 11355 1 1 EXPIRED SBT_TAPE 43sagnpr_1_1
11356 11356 1 1 EXPIRED SBT_TAPE 42sagnpr_1_1
11357 11357 1 1 EXPIRED SBT_TAPE 40sagnpr_1_1
11358 11358 1 1 EXPIRED SBT_TAPE 4asagoml_1_1

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=46sagnpr_1_1 RECID=11348 STAMP=950558523
deleted backup piece
backup piece handle=47sagnpr_1_1 RECID=11349 STAMP=950558524
deleted backup piece
backup piece handle=44sagnpr_1_1 RECID=11350 STAMP=950558523
deleted backup piece
backup piece handle=48sagnqb_1_1 RECID=11351 STAMP=950558539
deleted backup piece
backup piece handle=49sagnqb_1_1 RECID=11352 STAMP=950558539
deleted backup piece
backup piece handle=45sagnpr_1_1 RECID=11353 STAMP=950558523
deleted backup piece
backup piece handle=41sagnpr_1_1 RECID=11354 STAMP=950558523
deleted backup piece
backup piece handle=43sagnpr_1_1 RECID=11355 STAMP=950558523
deleted backup piece
backup piece handle=42sagnpr_1_1 RECID=11356 STAMP=950558523
deleted backup piece
backup piece handle=40sagnpr_1_1 RECID=11357 STAMP=950558523
deleted backup piece
backup piece handle=4asagoml_1_1 RECID=11358 STAMP=950559445
Deleted 11 EXPIRED objects

RMAN>

Now you can continue to use device type ‘DISK’ or configure a new ‘SBT_TAPE’ 🙂

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

RMAN-06403: could not obtain a fully authorized session

An automated process that refreshes a Development database from Production by doing a RMAN duplicate failed with ‘RMAN-06403: could not obtain a fully authorized session’:

Starting Duplicate Db at 25-JUL-2017 17:44
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/25/2017 17:44:46
RMAN-05501: aborting duplication of target database
RMAN-06403: could not obtain a fully authorized session
RMAN-04006: error from auxiliary database: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3651
Additional information: 1054940735

 

This was initially puzzling as nothing appeared to have changed.  A ‘My Oracle Support’ (MOS) search, revealed the following MOS note:

Duplicating database using RMAN fails with errors RMAN-06403, RMAN-04006, ORA-01034, ORA-27101 (Doc ID 274233.1)

CAUSE

The errors are occurring because the auxiliary database is not started in nomount state.

SOLUTION

The auxiliary instance must be in a nomount state during duplication of database using RMAN.

Startup the auxiliary instance in nomount state.  Then try to duplicate the database again.

SQL> connect / as sysdba
startup nomount;

Also you have to verify the following:

1. Ensure that the ORACLE_SID and ORACLE_HOME were set correctly when starting the auxiliary instance.  Especially any additional backslash at the end of the ORACLE_HOME can cause mismatches

2. Verify that the ORACLE_HOME directory is not a linked directory.
Make a hard copy of the init.ora in the ORACLE_HOME/dbs directory.

3. Double check the ‘listener.ora’ and ‘tnsnames.ora’ for correctness.”

 

The database was in nomount state started with a basic pfile:

*.db_name=V1DEV
*.db_unique_name=V1DEV

 

But upon checking point 3, I noticed the static listener registration that is required as the database is in nomount and thus doesn’t register with the listener had the wrong home:

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (SID_NAME = V1DEV1)
 (GLOBAL_DBNAME = V1DEV)
 (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1)
 )
 )

 

I recently changed the Oracle Home to use the dbhome_2 Oracle Home, so that the Development databases were not locked in with the DR databases using the dbhome_1 Oracle Home.  This allows for independent patching between the Oracle Homes, which allows soak testing of patches in Development before applying to Production:

Cloning an Oracle Home

 

Once this was changed:

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (SID_NAME = V1DEV1)
 (GLOBAL_DBNAME = V1DEV)
 (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_2)
 )
 )

 

It started working again 🙂 :

Starting Duplicate Db at 26-JUL-2017 11:38
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=515 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=1475 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=643 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=835 device type=DISK
current log archived

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

How to find the Oldest and Newest Interval Partition

I was required to find the oldest and newest interval partition to be able to do some automated maintenance, but found query USER_TAB_PARTITIONS, ALL_TAB_PARTITIONS and DBA_TAB_PARTITIONS was a bit tricky as the HIGH_VALUE is a LONG data type 😦

A quick search on the web and I found a perfect solution from my good friend Tim Hall (Oracle Base), who created a function to convert the LONG into a DATE by executing the HIGH VALUE 🙂

Script: part_hv_to_date.sql

For ease of access, script below (please check Tim’s blog for updates and always ask for permission if you’re going to quote 🙂 ):

CREATE OR REPLACE FUNCTION part_hv_to_date (p_table_owner IN VARCHAR2,
 p_table_name IN VARCHAR2,
 p_partition_name IN VARCHAR2)
 RETURN DATE
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/miscellaneous/part_hv_to_date.sql
-- Author : Tim Hall
-- Description : Create a function to turn partition HIGH_VALUE column to a date.
-- Call Syntax : @part_hv_to_date
-- Last Modified: 19/01/2012
-- Notes : Has to re-select the value from the view as LONG cannot be passed as a parameter.
-- Example call:
--
-- SELECT a.partition_name, 
-- part_hv_to_date(a.table_owner, a.table_name, a.partition_name) as high_value
-- FROM all_tab_partitions a;
--
-- Does no error handling. 
-- -----------------------------------------------------------------------------------
AS
 l_high_value VARCHAR2(32767);
 l_date DATE;
BEGIN
 SELECT high_value
 INTO l_high_value
 FROM all_tab_partitions
 WHERE table_owner = p_table_owner
 AND table_name = p_table_name
 AND partition_name = p_partition_name;
 
 EXECUTE IMMEDIATE 'SELECT ' || l_high_value || ' FROM dual' INTO l_date;
 RETURN l_date;
END;
/

 

With this you can get the oldest interval partition by running the following SQL:

SELECT PARTITION_NAME
FROM DBA_TAB_PARTITIONS P
WHERE TABLE_OWNER = 'ZEDDBA'
AND TABLE_NAME = 'EXAMPLE'
AND PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME) = (
SELECT MIN(PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME))
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER = P.TABLE_OWNER
AND TABLE_NAME = P.TABLE_NAME);

Output:

SQL> SELECT PARTITION_NAME
   2 FROM DBA_TAB_PARTITIONS P
   3 WHERE TABLE_OWNER = 'ZEDDBA'
   4 AND TABLE_NAME = 'EXAMPLE'
   5 AND PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME) = (
   6 SELECT MIN(PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME))
   7 FROM DBA_TAB_PARTITIONS
   8 WHERE TABLE_OWNER = P.TABLE_OWNER
   9 AND TABLE_NAME = P.TABLE_NAME);

PARTITION_NAME
--------------------
SYS_P10590

SQL>

 

And the newest interval partition by running the following SQL:

SELECT PARTITION_NAME
FROM DBA_TAB_PARTITIONS P
WHERE TABLE_OWNER = 'ZEDDBA'
AND TABLE_NAME = 'EXAMPLE'
AND PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME) = (
SELECT MAX(PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME))
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER = P.TABLE_OWNER
AND TABLE_NAME = P.TABLE_NAME);

Output:

SQL> SELECT PARTITION_NAME
   2 FROM DBA_TAB_PARTITIONS P
   3 WHERE TABLE_OWNER = 'ZEDDBA'
   4 AND TABLE_NAME = 'EXAMPLE'
   5 AND PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME) = (
   6 SELECT MAX(PART_HV_TO_DATE(TABLE_OWNER, TABLE_NAME, PARTITION_NAME))
   7 FROM DBA_TAB_PARTITIONS
   8 WHERE TABLE_OWNER = P.TABLE_OWNER
   9 AND TABLE_NAME = P.TABLE_NAME);

PARTITION_NAME
--------------------
SYS_P17718

SQL>

 

Updates

8th July 2017, changed the query so the sub-query join onto the parent query to avoid having to set the predicates in the sub-query.

 

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)