Posted in Database, ORACLE

RMAN Backup and Restore Scenario for Oracle 12C Multitenancy System – Part 1

1)Performing Level 0 Backup of Pluggable Database:

Below mentioned command should be run by connecting to container database i.e in our case it is IMADB

We have to connect to RMAN prompt–

rman target sys/imadb@imadb catalog rcat01/rcat01@masterdb

NOTE:- In above example IMADB is container database and MASTERDB is holding Catalog for our pluggable database confdb.

Below mentioned command will be used to take incremental level backup of pluggable database confdb.

RMAN> BACKUP INCREMENTAL LEVEL 0 MAXSETSIZE  2G FILESPERSET 2 TAG 'Level0_32122' PLUGGABLE DATABASE confdb SKIP READONLY plus archivelog;

Starting backup at 03-APR-18

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=138 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=263 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=218 RECID=218 STAMP=972436655

input archived log thread=1 sequence=219 RECID=219 STAMP=972436681

channel ORA_DISK_1: starting piece 1 at 03-APR-18

channel ORA_DISK_2: starting archived log backup set

channel ORA_DISK_2: specifying archived log(s) in backup set

input archived log thread=1 sequence=216 RECID=217 STAMP=972436651

input archived log thread=1 sequence=217 RECID=216 STAMP=972436651

channel ORA_DISK_2: starting piece 1 at 03-APR-18

channel ORA_DISK_1: finished piece 1 at 03-APR-18

piece handle=+REDO2/IMADB/BACKUPSET/2018_04_03/annnf0_level0_3222_0.394.972440729 tag=LEVEL0_3222 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=220 RECID=220 STAMP=972440727

channel ORA_DISK_1: starting piece 1 at 03-APR-18

channel ORA_DISK_2: finished piece 1 at 03-APR-18

piece handle=+REDO2/IMADB/BACKUPSET/2018_04_03/annnf0_level0_3222_0.393.972440729 tag=LEVEL0_3222 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:03

channel ORA_DISK_1: finished piece 1 at 03-APR-18

piece handle=+REDO2/IMADB/BACKUPSET/2018_04_03/annnf0_level0_3222_0.392.972440733 tag=LEVEL0_3222 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 03-APR-18

Starting backup at 03-APR-18

using channel ORA_DISK_1

using channel ORA_DISK_2

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00009 name=+ORACONF/oradata/confdb/system01confdb.dbf

input datafile file number=00037 name=+ORACONF/oradata/confdb/ncrcattbsp01.dbf

channel ORA_DISK_1: starting piece 1 at 03-APR-18

channel ORA_DISK_2: starting incremental level 0 datafile backup set

channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00010 name=+ORACONF/oradata/confdb/sysaux01confdb.dbf

channel ORA_DISK_2: starting piece 1 at 03-APR-18

channel ORA_DISK_1: finished piece 1 at 03-APR-18

piece handle=+REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn0_level0_3222_0.391.972440733 tag=LEVEL0_3222 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00011 name=+ORACONF/oradata/confdb/undo01confdb.dbf

channel ORA_DISK_1: starting piece 1 at 03-APR-18

channel ORA_DISK_2: finished piece 1 at 03-APR-18

piece handle=+REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn0_level0_3222_0.390.972440733 tag=LEVEL0_3222 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:15

channel ORA_DISK_2: starting incremental level 0 datafile backup set

channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00012 name=+ORACONF/oradata/confdb/configdata01.dbf

channel ORA_DISK_2: starting piece 1 at 03-APR-18

channel ORA_DISK_2: finished piece 1 at 03-APR-18

piece handle=+REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn0_level0_3222_0.388.972440749 tag=LEVEL0_3222 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: finished piece 1 at 03-APR-18

piece handle=+REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn0_level0_3222_0.389.972440749 tag=LEVEL0_3222 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 03-APR-18

Starting backup at 03-APR-18

current log archived

using channel ORA_DISK_1

using channel ORA_DISK_2

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=221 RECID=221 STAMP=972440752

channel ORA_DISK_1: starting piece 1 at 03-APR-18

channel ORA_DISK_1: finished piece 1 at 03-APR-18

piece handle=+REDO2/IMADB/BACKUPSET/2018_04_03/annnf0_level0_3222_0.386.972440753 tag=LEVEL0_3222 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 03-APR-18

Starting Control File Autobackup at 03-APR-18

piece handle=/ora00/app/oracle/bkp/sep02pvvm392/IMADB/LEVEL0/imadbcfc-194714882-20180403-03.RW99 comment=NONE

Finished Control File Autobackup at 03-APR-18

 

2)Performing Level 1 Backup of Pluggable Database:

We have to connect to RMAN prompt–

rman target sys/imadb@imadb catalog rcat01/rcat01@masterdb

Here we will be taking incremental level 1 backup of pluggable database confdb .Above mentioned command will be run while connecting to Container database:

RMAN> BACKUP INCREMENTAL LEVEL 1 MAXSETSIZE  2G FILESPERSET 2 TAG 'Level0_3222' PLUGGABLE DATABASE CONFDB SKIP READONLY plus archivelog;

Starting backup at 03-APR-18

current log archived

using channel ORA_DISK_1

using channel ORA_DISK_2

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=218 RECID=218 STAMP=972436655

input archived log thread=1 sequence=219 RECID=219 STAMP=972436681

channel ORA_DISK_1: starting piece 1 at 03-APR-18

channel ORA_DISK_2: starting archived log backup set

channel ORA_DISK_2: specifying archived log(s) in backup set

input archived log thread=1 sequence=216 RECID=217 STAMP=972436651

input archived log thread=1 sequence=217 RECID=216 STAMP=972436651

channel ORA_DISK_2: starting piece 1 at 03-APR-18

channel ORA_DISK_1: finished piece 1 at 03-APR-18

piece handle=+REDO2/IMADB/BACKUPSET/2018_04_03/annnf0_level0_3222_0.384.972440905 tag=LEVEL0_3222 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=220 RECID=220 STAMP=972440727

input archived log thread=1 sequence=221 RECID=221 STAMP=972440752

channel ORA_DISK_1: starting piece 1 at 03-APR-18

channel ORA_DISK_2: finished piece 1 at 03-APR-18

piece handle=+REDO2/IMADB/BACKUPSET/2018_04_03/annnf0_level0_3222_0.383.972440905 tag=LEVEL0_3222 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:03

channel ORA_DISK_2: starting archived log backup set

channel ORA_DISK_2: specifying archived log(s) in backup set

input archived log thread=1 sequence=222 RECID=222 STAMP=972440904

channel ORA_DISK_2: starting piece 1 at 03-APR-18

channel ORA_DISK_1: finished piece 1 at 03-APR-18

piece handle=+REDO2/IMADB/BACKUPSET/2018_04_03/annnf0_level0_3222_0.382.972440907 tag=LEVEL0_3222 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_2: finished piece 1 at 03-APR-18

piece handle=+REDO2/IMADB/BACKUPSET/2018_04_03/annnf0_level0_3222_0.381.972440907 tag=LEVEL0_3222 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01

Finished backup at 03-APR-18

Starting backup at 03-APR-18

using channel ORA_DISK_1

using channel ORA_DISK_2

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00009 name=+ORACONF/oradata/confdb/system01confdb.dbf

input datafile file number=00037 name=+ORACONF/oradata/confdb/ncrcattbsp01.dbf

channel ORA_DISK_1: starting piece 1 at 03-APR-18

channel ORA_DISK_2: starting incremental level 1 datafile backup set

channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00010 name=+ORACONF/oradata/confdb/sysaux01confdb.dbf

channel ORA_DISK_2: starting piece 1 at 03-APR-18

channel ORA_DISK_1: finished piece 1 at 03-APR-18

piece handle=+REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn1_level0_3222_0.380.972440909 tag=LEVEL0_3222 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00011 name=+ORACONF/oradata/confdb/undo01confdb.dbf

channel ORA_DISK_1: starting piece 1 at 03-APR-18

channel ORA_DISK_2: finished piece 1 at 03-APR-18

piece handle=+REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn1_level0_3222_0.379.972440909 tag=LEVEL0_3222 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07

channel ORA_DISK_2: starting incremental level 1 datafile backup set

channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00012 name=+ORACONF/oradata/confdb/configdata01.dbf

channel ORA_DISK_2: starting piece 1 at 03-APR-18

channel ORA_DISK_2: finished piece 1 at 03-APR-18

piecehandle=+REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn1_level0_3222_0.377.972440917 tag=LEVEL0_3222 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: finished piece 1 at 03-APR-18

piecehandle=+REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn1_level0_3222_0.378.972440917 tag=LEVEL0_3222 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 03-APR-18

Starting backup at 03-APR-18

current log archived

using channel ORA_DISK_1

using channel ORA_DISK_2

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=223 RECID=223 STAMP=972440919

channel ORA_DISK_1: starting piece 1 at 03-APR-18

channel ORA_DISK_1: finished piece 1 at 03-APR-18

piece handle=+REDO2/IMADB/BACKUPSET/2018_04_03/annnf0_level0_3222_0.375.972440919 tag=LEVEL0_3222 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 03-APR-18

Starting Control File Autobackup at 03-APR-18

piece handle=/ora00/app/oracle/bkp/sep02pvvm392/IMADB/LEVEL0/imadbcfc-194714882-20180403-04.RW99 comment=NONE

Finished Control File Autobackup at 03-APR-18

 

3)Restoring pluggable database.

Now we be restoring above mentioned pluggable database with incremental backup taken above:

We have to connect to RMAN prompt–

rman target sys/imadb@imadb catalog rcat01/rcat01@masterdb

We have to close existing database if we need to test restore pluggable database:

RMAN> alter pluggable database confdb close;

Statement processed

Below mentioned command will Restore pluggable database confdb with backup taken above:

RMAN> restore pluggable database confdb;

Starting restore at 04-APR-18

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=27 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=391 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00010 to +ORACONF/oradata/confdb/sysaux01confdb.dbf

channel ORA_DISK_1: reading from backup piece +REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn0_level0_3222_0.390.972440733

channel ORA_DISK_2: starting datafile backup set restore

channel ORA_DISK_2: specifying datafile(s) to restore from backup set

channel ORA_DISK_2: restoring datafile 00009 to +ORACONF/oradata/confdb/system01confdb.dbf

channel ORA_DISK_2: restoring datafile 00037 to +ORACONF/oradata/confdb/ncrcattbsp01.dbf

channel ORA_DISK_2: reading from backup piece +REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn0_level0_3222_0.391.972440733

channel ORA_DISK_2: piece handle=+REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn0_level0_3222_0.391.972440733 tag=LEVEL0_3222

channel ORA_DISK_2: restored backup piece 1

channel ORA_DISK_2: restore complete, elapsed time: 00:00:08

channel ORA_DISK_2: starting datafile backup set restore

channel ORA_DISK_2: specifying datafile(s) to restore from backup set

channel ORA_DISK_2: restoring datafile 00012 to +ORACONF/oradata/confdb/configdata01.dbf

channel ORA_DISK_2: reading from backup piece +REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn0_level0_3222_0.388.972440749

channel ORA_DISK_2: piece handle=+REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn0_level0_3222_0.388.972440749 tag=LEVEL0_3222

channel ORA_DISK_2: restored backup piece 1

channel ORA_DISK_2: restore complete, elapsed time: 00:00:01

channel ORA_DISK_2: starting datafile backup set restore

channel ORA_DISK_2: specifying datafile(s) to restore from backup set

channel ORA_DISK_2: restoring datafile 00011 to +ORACONF/oradata/confdb/undo01confdb.dbf

channel ORA_DISK_2: reading from backup piece +REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn0_level0_3222_0.389.972440749

channel ORA_DISK_1: piece handle=+REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn0_level0_3222_0.390.972440733 tag=LEVEL0_3222

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:12

channel ORA_DISK_2: piece handle=+REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn0_level0_3222_0.389.972440749 tag=LEVEL0_3222

channel ORA_DISK_2: restored backup piece 1

channel ORA_DISK_2: restore complete, elapsed time: 00:00:03

 

Once restore has been done we will be performing recovery of database

RMAN> recover pluggable database confdb;

Starting recover at 04-APR-18

using channel ORA_DISK_1

using channel ORA_DISK_2

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00010: +ORACONF/oradata/confdb/sysaux01confdb.dbf

channel ORA_DISK_1: reading from backup piece +REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn1_level0_3222_0.379.972440909

channel ORA_DISK_2: starting incremental datafile backup set restore

channel ORA_DISK_2: specifying datafile(s) to restore from backup set

destination for restore of datafile 00009: +ORACONF/oradata/confdb/system01confdb.dbf

destination for restore of datafile 00037: +ORACONF/oradata/confdb/ncrcattbsp01.dbf

channel ORA_DISK_2: reading from backup piece +REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn1_level0_3222_0.380.972440909

channel ORA_DISK_1: piece handle=+REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn1_level0_3222_0.379.972440909 tag=LEVEL0_3222

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00012: +ORACONF/oradata/confdb/configdata01.dbf

channel ORA_DISK_1: reading from backup piece +REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn1_level0_3222_0.377.972440917

channel ORA_DISK_2: piece handle=+REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn1_level0_3222_0.380.972440909 tag=LEVEL0_3222

channel ORA_DISK_2: restored backup piece 1

channel ORA_DISK_2: restore complete, elapsed time: 00:00:01

channel ORA_DISK_2: starting incremental datafile backup set restore

channel ORA_DISK_2: specifying datafile(s) to restore from backup set

destination for restore of datafile 00011: +ORACONF/oradata/confdb/undo01confdb.dbf

channel ORA_DISK_2: reading from backup piece +REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn1_level0_3222_0.378.972440917

channel ORA_DISK_1: piece handle=+REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn1_level0_3222_0.377.972440917 tag=LEVEL0_3222

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:00

channel ORA_DISK_2: piece handle=+REDO2/IMADB/382313F87C9C26CAE053334F47997B44/BACKUPSET/2018_04_03/nnndn1_level0_3222_0.378.972440917 tag=LEVEL0_3222

channel ORA_DISK_2: restored backup piece 1

channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
starting media recovery
archived log for thread 1 with sequence 223 is already on disk as file +REDO2/IMADB/ARCHIVELOG/2018_04_03/thread_1_seq_223.376.972440919

archived log for thread 1 with sequence 224 is already on disk as file +REDO2/IMADB/ARCHIVELOG/2018_04_03/thread_1_seq_224.374.972479867

archived log for thread 1 with sequence 225 is already on disk as file +REDO2/IMADB/ARCHIVELOG/2018_04_03/thread_1_seq_225.373.972501467

archived log for thread 1 with sequence 226 is already on disk as file +REDO2/IMADB/ARCHIVELOG/2018_04_03/thread_1_seq_226.372.972511235

archived log for thread 1 with sequence 227 is already on disk as file +REDO2/IMADB/ARCHIVELOG/2018_04_03/thread_1_seq_227.371.972516715

archived log for thread 1 with sequence 228 is already on disk as file +REDO2/IMADB/ARCHIVELOG/2018_04_04/thread_1_seq_228.370.972523167

archived log for thread 1 with sequence 229 is already on disk as file +REDO2/IMADB/ARCHIVELOG/2018_04_04/thread_1_seq_229.369.972544671

archived log file name=+REDO2/IMADB/ARCHIVELOG/2018_04_03/thread_1_seq_223.376.972440919 thread=1 sequence=223

archived log file name=+REDO2/IMADB/ARCHIVELOG/2018_04_03/thread_1_seq_224.374.972479867 thread=1 sequence=224

archived log file name=+REDO2/IMADB/ARCHIVELOG/2018_04_03/thread_1_seq_225.373.972501467 thread=1 sequence=225

archived log file name=+REDO2/IMADB/ARCHIVELOG/2018_04_03/thread_1_seq_226.372.972511235 thread=1 sequence=226

archived log file name=+REDO2/IMADB/ARCHIVELOG/2018_04_03/thread_1_seq_227.371.972516715 thread=1 sequence=227

media recovery complete, elapsed time: 00:00:15

Finished recover at 04-APR-18

 

4)Open the database for user operation once database has been recovered:

Once database has been restored and recovered we will open the pluggable database in READ-WRITE MODE

RMAN> alter pluggable database confdb open;

Statement processed
Advertisements
Posted in Database, ORACLE

Converting non-CDB database as PDB in Oracle 12c.

Version of Non-CDB database = 12.2.0.1.0

Version of CDB database = 12.2.0.1.0

Following steps will plug-in non-cdb database into cdb as pdb.

My non-cdb database name is confdb

My cdb database name is imadb

1) Open non-cdb database in read-only mode to create XML file for the PDB.

  • Shutting down non-cdb database (confdb)
TESTING:oracle $ export ORACLE_SID=confdb
TESTING:oracle $ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 15 06:34:47 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
  • Mounting the database and opening in read-only
SQL> startup mount
ORACLE instance started.

Total System Global Area 1006632960 bytes
Fixed Size 8628160 bytes
Variable Size 322963520 bytes
Database Buffers 666894336 bytes
Redo Buffers 8146944 bytes
Database mounted.
SQL> alter database open read only;

Database altered.
  • CREATE XML FILE FOR PDB
SQL> exec DBMS_PDB.DESCRIBE('/home/oracle/depconfdb.xml');

PL/SQL procedure successfully completed.

 

2) Shutdown non-cdb database.

TESTING:oracle $ export ORACLE_SID=imadb

TESTING:oracle $ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 15 06:34:47 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

 

3)Plugin non-cdb database as pdb into new cdb

  • Running following command on CDB database – imadb
SQL>CREATE PLUGGABLE DATABASE confdb USING '/home/oracle/depconfdb.xml' NOCOPY TEMPFILE REUSE;

 

4)Convert the dictionary of new pluggable database to PDB type.

  • Change the container to new pluggable database and run the script
SQL>ALTER SESSION set container=confdb;

Session altered.

SQL>@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

 

5)Start New Pluggable database.

SQL> alter pluggable database confdb open;

Pluggable database altered.
  • If there is some errors on above mentioned you can use this view to get information
SQL>select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name = 'CONFDB' and status != 'RESOLVED';

 

Posted in Database, ORACLE

datapatch fails with ORA-04063 : package body “SYS.DBMS_SQLPATCH” has errors

Recently when i was applying Critical patch Update for October 2017 on my Database Server.

Patch applied process with OPatch utility completed successfully  but there was issue with datapatch utility :

TESTING:oracle $ ./datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Thu Nov 16 04:33:57 2017
Copyright (c) 2012, 2017, Oracle. All rights reserved.

Log file for this invocation: /ora00/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_27492_2017_11_16_04_33_57/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done

DBD::Oracle::db selectrow_array failed: ORA-04063: package body "SYS.DBMS_SQLPATCH" has errors (DBD ERROR: OCIStmtExecute) [for Statement "SELECT dbms_sqlpatch.verify_queryable_inventory FROM dual"] at /ora00/app/oracle/product/12.2.0/sqlpatch/sqlpatch.pm line 4524, <LOGFILE> line 21.




Please refer to MOS Note 1609718.1 and/or the invocation log
/ora00/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_27492_2017_11_16_04_33_57/sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Thu Nov 16 04:33:57 2017

 

There was lot of issue mentioned on MOS Note 1609718.1 but above mentioned package issue was not present.

Latter i tried below mentioned steps to recreate those package body :

1)Finding current status of package :

TESTING:oracle $ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 16 05:04:24 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.




Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> SELECT dbms_sqlpatch.verify_queryable_inventory FROM dual;
SELECT dbms_sqlpatch.verify_queryable_inventory FROM dual
 *
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_SQLPATCH" has errors

 

2) As we can see in above mentioned command package body was displaying error so i tried to look-out for script which create that package by running below mentioned command inside $ORACLE_HOME/rdbms/admin directory.

TESTING:oracle $ find . | xargs grep "dbms_sqlpatch"
grep: .: Is a directory
./e1201000.sql:Rem surman 08/05/13 - 17005047: Add dbms_sqlpatch
./catpdbms.sql:Rem surman 08/02/13 - 17005047: Add dbms_sqlpatch
./catdwgrd.sql:Rem dbms_sqlpatch package, as the package may not be valid
./dbmssqlpatch.sql:Rem surman 08/18/14 - Always reload dbms_sqlpatch
./dbmssqlpatch.sql:CREATE OR REPLACE PACKAGE dbms_sqlpatch AS
./dbmssqlpatch.sql:END dbms_sqlpatch;
./dbmssqlpatch.sql:CREATE OR REPLACE PUBLIC SYNONYM dbms_sqlpatch FOR sys.dbms_sqlpatch;
./dbmssqlpatch.sql:GRANT EXECUTE ON dbms_sqlpatch TO execute_catalog_role;
grep: ./cdb_cloud: Is a directory
grep: ./cdb_cloud/sql: Is a directory
grep: ./cdb_cloud/dbt: Is a directory
grep: ./cdb_cloud/dbt/test: Is a directory
grep: ./cdb_cloud/rsp: Is a directory
grep: ./cdb_cloud/apex_install: Is a directory
grep: ./cdb_cloud/apex_install/ords: Is a directory
./prvtsqlpatch.plb: EXECUTE IMMEDIATE 'DROP TABLE dbms_sqlpatch_state';
./prvtsqlpatch.plb:CREATE TABLE dbms_sqlpatch_state (
./prvtsqlpatch.plb:CREATE OR REPLACE PACKAGE BODY dbms_sqlpatch wrapped
./catpprvt.sql:Rem surman 08/03/13 - 17005047: Add dbms_sqlpatch
./catpprvt.sql:-- 20772435: Queryable dbms_sqlpatch package body is now created in catxrd
./catxrd.sql:Rem dbms_sqlpatch package
./xrde121.sql:DROP PACKAGE dbms_sqlpatch;
./xrde121.sql:DROP PUBLIC SYNONYM dbms_sqlpatch;

From above mentioned output  we can see that prvtsqlpatch.plb script is creating this package body.So ran this script to recreate dbms_sqlpatch package.

3)Running  prvtsqlpatch.plb to recreate dbms_sqlpatch package and checking status of this package;

TESTING:oracle $ export ORACLE_SID=confdb

TESTING:oracle $ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 16 05:04:24 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.




Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> @prvtsqlpatch.plb

Session altered.




PL/SQL procedure successfully completed.




Table created.




Package body created.

No errors.

Session altered.

SQL> SELECT dbms_sqlpatch.verify_queryable_inventory FROM dual;

VERIFY_QUERYABLE_INVENTORY
--------------------------------------------------------------------------------

OK

 

4)Once above mentioned package is recreated we can apply patch on database with the help of datapatch utility:

TESTING:oracle $ ./datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Thu Nov 16 05:05:10 2017
Copyright (c) 2012, 2017, Oracle. All rights reserved.

Log file for this invocation: /ora00/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_5382_2017_11_16_05_05_10/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBRU:
 ID 171017 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
 Nothing to roll back
 The following patches will be applied:
 26710464 (DATABASE RELEASE UPDATE 12.2.0.1.171017)

Installing patches...
Patch installation complete. Total patches installed: 1

Validating logfiles...
Patch 26710464 apply: SUCCESS
 logfile: /ora00/app/oracle/cfgtoollogs/sqlpatch/26710464/21632407/26710464_apply_CONFDB_2017Nov16_05_05_18.log (no errors)
SQL Patching tool complete on Thu Nov 16 05:06:11 2017

 

 

 

 

 

Posted in Database, ORACLE

OPatch failed with error code 2

Before applying Critical Patch Update for October 2017, I wanted to confirm Current patch details which are applied but ran into below mentioned issues.

TESTING:oracle $ /ora00/app/oracle/product/12.2.0/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2017, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /ora00/app/oracle/product/12.2.0
Central Inventory : /ora00/app/oraInventory
 from : /ora00/app/oracle/product/12.2.0/oraInst.loc
OPatch version : 12.2.0.1.6
OUI version : 12.2.0.1.4
Log file location : /ora00/app/oracle/product/12.2.0/cfgtoollogs/opatch/opatch2017-11-16_03-29-39AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"
List of Homes on this system:

Prereq "checkConflictAgainstOHWithDetail" is not executed.

The details are:
Exception occured : RawInventory gets null OracleHomeInfo
Summary of Conflict Analysis:

There are no patches that can be applied now.

OPatch failed with error code 2

 

The above error occurs when Opatch was not able to find the database HOME in inventory xml file present inside Oracle Inventory Directory.

Solution is to have the Oracle Home attach to the Central Inventory of the server

We can re-attach the oracle home by using script present at $ORACLE_HOME/oui/bin “attachHome.sh” location.Do crosscheck value of Oracle_Home present inside attachHome.sh script.

TESTING:oracle $ ./attachHome.sh
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 1921 MB Passed
The inventory pointer is located at /etc/oraInst.loc
Please execute the '/ora00/app/oraInventory/orainstRoot.sh' script at the end of the session.
'AttachHome' was successful.

 

Once that is done we can run above mentioned command without any errors:

TESTING:oracle $ /ora00/app/oracle/product/12.2.0/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2017, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /ora00/app/oracle/product/12.2.0
Central Inventory : /ora00/app/oraInventory
 from : /ora00/app/oracle/product/12.2.0/oraInst.loc
OPatch version : 12.2.0.1.6
OUI version : 12.2.0.1.4
Log file location : /ora00/app/oracle/product/12.2.0/cfgtoollogs/opatch/opatch2017-11-16_03-38-25AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

 

Posted in Database, ORACLE

ORA-39181: Only partial table data may be exported due to fine grain access control

Data pump expdp log shows following error for some tables during the export:

EXP ORA-39181: Only partial table data may be exported due to fine grain access control on "ADMIN"."XCF"
EXP . . exported "ADMIN"."XCF" 0 KB 0 rows
EXP ORA-39181: Only partial table data may be exported due to fine grain access control on "ADMIN"."XDF"
EXP . . exported "ADMIN"."XDF" 0 KB 0 rows

Also VPD is not enabled to show this error.

Solution:

Provide the below privilege to the schema which you are trying to export:

SQL> GRANT EXEMPT ACCESS POLICY to <SCHEMA_NAME>;
Posted in Database, ORACLE

Applying Oracle Critical Patch Update of April 2017 on Oracle 12.1.0.2.0

 

1) Finding Current Opatch Version.

[grid@TESTING ora00]$ /ora00/app/grid/product/12.1.0/OPatch/opatch version
OPatch Version: 12.1.0.1.3

OPatch succeeded.

2) Download Updated Opatch patch from Oracle Metalink.

TESTING:oracle $ cd patchopatch/
TESTING:oracle $ ls
p21142429_121010_Linux-x86-64.zip

3) Applying Opatch patch on current Oracle Home and Grid Home.

Oracle Home:-

TESTING:oracle $ unzip -o -qq p21142429_121010_Linux-x86-64.zip -d /ora00/app/oracle/product/12.1.0
TESTING:oracle $ /ora00/app/oracle/product/12.1.0/OPatch/opatch version
OPatch Version: 12.1.0.1.10

OPatch succeeded.

Grid Home:–

[grid@TESTING patchopatch]$ unzip -o -qq p21142429_121010_Linux-x86-64.zip -d /ora00/app/grid/product/12.1.0
[grid@TESTING patchopatch]$ /ora00/app/grid/product/12.1.0/OPatch/opatch version
OPatch Version: 12.1.0.1.10

OPatch succeeded.

4) Stop Grid and Database Services.

5) Analyze Current Patch with opatchauto utility.

[root@TESTING ora00]# /ora00/app/grid/product/12.1.0/OPatch/opatchauto apply /ora00/25434003 -analyze -oh /ora00/app/grid/product/12.1.0
OPatch Automation Tool
Copyright (c)2014, Oracle Corporation. All rights reserved.

OPatchauto Version : 12.1.0.1.10
OUI Version : 12.1.0.2.0
Running from : /ora00/app/grid/product/12.1.0

opatchauto log file: /ora00/app/grid/product/12.1.0/cfgtoollogs/opatchauto/25434003/opatch_gi_2017-06-22_05-45-15_analyze.log

NOTE: opatchauto is running in ANALYZE mode. There will be no change to your system.

Parameter Validation: Successful

Configuration Validation: Successful

Patch Location: /ora00/25434003
Grid Infrastructure Patch(es): 21436941 25171037 25363740 25363750
DB Patch(es): 25171037 25363740

Patch Validation: Successful
User specified following Grid Infrastructure home:
/ora00/app/grid/product/12.1.0




Analyzing patch(es) on "/ora00/app/grid/product/12.1.0" ...
Patch "/ora00/25434003/21436941" successfully analyzed on "/ora00/app/grid/product/12.1.0" for apply.
Patch "/ora00/25434003/25171037" successfully analyzed on "/ora00/app/grid/product/12.1.0" for apply.
Patch "/ora00/25434003/25363740" successfully analyzed on "/ora00/app/grid/product/12.1.0" for apply.
Patch "/ora00/25434003/25363750" successfully analyzed on "/ora00/app/grid/product/12.1.0" for apply.

Apply Summary:
Following patch(es) are successfully analyzed:
GI Home: /ora00/app/grid/product/12.1.0: 21436941,25171037,25363740,25363750

opatchauto succeeded.

6) Once above steps is completed successfully we will generate ocm.rsp file , If you have latter Opatch Version this rsp file is not needed

export ORACLE_HOME=/ora00/app/grid/product/12.1.0
$ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /ora00/ocm.rsp

Applying Opatch on Grid Home

[root@TESTING bin]# /ora00/app/grid/product/12.1.0/OPatch/opatchauto apply /ora00/25434003 -oh /ora00/app/grid/product/12.1.0 -ocmrf /ora00/app/oracle/ocm.rsp
OPatch Automation Tool
Copyright (c)2014, Oracle Corporation. All rights reserved.

OPatchauto Version : 12.1.0.1.10
OUI Version : 12.1.0.2.0
Running from : /ora00/app/grid/product/12.1.0

opatchauto log file: /ora00/app/grid/product/12.1.0/cfgtoollogs/opatchauto/25434003/opatch_gi_2017-06-22_06-07-58_deploy.log

Parameter Validation: Successful

Configuration Validation: Successful

Patch Location: /ora00/25434003
Grid Infrastructure Patch(es): 21436941 25171037 25363740 25363750
DB Patch(es): 25171037 25363740

Patch Validation: Successful
User specified following Grid Infrastructure home:
/ora00/app/grid/product/12.1.0




Performing prepatch operations on SIHA Home... Successful

Applying patch(es) to "/ora00/app/grid/product/12.1.0" ...
Patch "/ora00/25434003/21436941" successfully applied to "/ora00/app/grid/product/12.1.0".
Patch "/ora00/25434003/25171037" successfully applied to "/ora00/app/grid/product/12.1.0".
Patch "/ora00/25434003/25363740" successfully applied to "/ora00/app/grid/product/12.1.0".
Patch "/ora00/25434003/25363750" successfully applied to "/ora00/app/grid/product/12.1.0".

Performing postpatch operations on SIHA Home... Successful

Apply Summary:
Following patch(es) are successfully installed:
GI Home: /ora00/app/grid/product/12.1.0: 21436941,25171037,25363740,25363750

opatchauto succeeded.

7) Repeat the same steps for Oracle Home:

[root@TESTING ~]# /ora00/app/oracle/product/12.1.0/OPatch/opatchauto apply /ora00/25434003 -oh /ora00/app/oracle/product/12.1.0/ -ocmrf /ora00/app/oracle/ocm.rsp

 

Posted in Database, ORACLE

addnode.sh hangs during copying files to other node.

 

Recently i was trying to add one node to cluster and after running addnode.sh it was hanging in below mentioned line

 

$ ./addnode.sh -silent "CLUSTER_NEW_NODES={TESTING}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={TESTING-VIP}"
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 40619 MB Passed
Checking swap space: must be greater than 150 MB. Actual 49143 MB Passed
[WARNING] [INS-13014] Target environment does not meet some optional requirements.
 CAUSE: Some of the optional prerequisites are not met. See logs for details. /ora00/app/oraInventory/logs/addNodeActions2017-06-02_06-50-36AM.log
 ACTION: Identify the list of failed prerequisite checks from the log: /ora00/app/oraInventory/logs/addNodeActions2017-06-02_06-50-36AM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.

Prepare Configuration in progress.

Prepare Configuration successful.
.................................................. 8% Done.
You can find the log of this install session at:
 /ora00/app/oraInventory/logs/addNodeActions2017-06-02_06-50-36AM.log

Instantiate files in progress.

Instantiate files successful.
.................................................. 14% Done.

Copying files to node in progress.

 

First of all GRID_HOME should be owned by grid by default it will be root.

After searching on metalink find out sometime this issue may be because of below mentioned BUG:

Bug 12318325 – Addnode.sh takes longer due to audit files in GRID_HOME/rdbms/audit. (Doc ID 12318325.8)

 

Once i cleaned up those audit files i was able to run addnode.sh successfully.

 

$ ./addnode.sh -silent "CLUSTER_NEW_NODES={TESTING}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={TESTING-VIP}"
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 40543 MB Passed
Checking swap space: must be greater than 150 MB. Actual 49143 MB Passed

Prepare Configuration in progress.

Prepare Configuration successful.
.................................................. 8% Done.
You can find the log of this install session at:
/ora00/app/oraInventory/logs/addNodeActions2017-06-02_09-56-37AM.log

Instantiate files in progress.

Instantiate files successful.
.................................................. 14% Done.

Copying files to node in progress.

Copying files to node successful.
.................................................. 73% Done.

Saving cluster inventory in progress.
.................................................. 80% Done.

Saving cluster inventory successful.
The Cluster Node Addition of /ora00/grid/product/12.1.0 was successful.
Please check '/tmp/silentInstall.log' for more details.

Setup Oracle Base in progress.

Setup Oracle Base successful.
.................................................. 88% Done.

As a root user, execute the following script(s):
 1. /ora00/app/oraInventory/orainstRoot.sh
 2. /ora00/grid/product/12.1.0/root.sh

Execute /ora00/app/oraInventory/orainstRoot.sh on the following nodes:
[TESTING]
Execute /ora00/grid/product/12.1.0/root.sh on the following nodes:
[TESTING]

The scripts can be executed in parallel on all the nodes.

..........
Update Inventory in progress.
.................................................. 100% Done.

Update Inventory successful.
Successfully Setup Software.
$