Backup on-premise database and restore to Oracle cloud database

Posted on April 24, 2018April 25, 2018 by debasis maity

My on-premise database hostname:-gnssrv01

Database name:-orcl12c

Cloud database hostname:-myclone

A.Backup of on-premise database to cloud container

Please create the empty container for holding backup of on-premise database using following link

https://clouddba.co/oracle-dbaas-1z0-160-part-24-backup-in-single-instance-oracle-cloud/

1.First I need to install backup module for cloud in on-premise database

You need to download opc.cert and opc_install.jar mentioned in above metalink note (Doc ID 2360941.1)

[oracle@myclone ~]$ java -jar opc_install.jar -host https://sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun/oracle-data-storage6-1 
-opcId 'sadhuarun1980@gmail.com' -opcPass 'xxx' -walletDir /home/oracle/my_wallet -libDir $ORACLE_HOME/lib -libPlatform linux64 -debug -trustedCerts /home/oracle/opc.cert

Oracle Database Cloud Backup Module Install Tool, build 12.2.0.1.0DBBKPCSBP_2017-11-28
Debug: os.name = Linux
Debug: os.arch = amd64
Debug: os.version = 4.1.12-112.14.10.el6uek.x86_64
Debug: file.separator = /
Debug: Platform = PLATFORM_LINUX64
Debug: OPC Account Verification: <?xml version="1.0" encoding="UTF-8"?><container name="oracle-data-storage6-1"><object><name>file_chunk/804514966/ORCL12C/backuppiece/2018-04-23/18t11tsh_1_1/KTVs8w9V0wwD/0000000001</name><hash>5752a8f0131e0f3d0c728f15a7ea71d6</hash><bytes>75235328</bytes><content_type>Application/Octet-Stream</content_type><last_modified>2018-04-23T03:09:28.687920</last_modified></object></container>
 Error: The specified OPC host name should not include container or object in the URL path.

In that case we will try not to provide the storage container name and try.Oracle will create a default container.

[oracle@myclone ~]$ java -jar opc_install.jar -host https://sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun 
-opcId 'sadhuarun1980@gmail.com' -opcPass 'Start123456$' -walletDir /home/oracle/my_wallet 
-libDir $ORACLE_HOME/lib -libPlatform linux64 -debug -trustedCerts /home/oracle/opc.cert

Oracle Database Cloud Backup Module Install Tool, build 12.2.0.1.0DBBKPCSBP_2017-11-28
Debug: os.name = Linux
Debug: os.arch = amd64
Debug: os.version = 4.1.12-112.14.10.el6uek.x86_64
Debug: file.separator = /
Debug: Platform = PLATFORM_LINUX64
Debug: OPC Account Verification: <?xml version="1.0" encoding="UTF-8" standalone="yes"?><account name="Storage-62b84aec8de8478ba17a6e1f84c3475c"><container><name>oracle-data-storage6-1</name><count>1</count><bytes>20640</bytes><accountId><id>31714</id></accountId><deleteTimestamp>0.0</deleteTimestamp><containerId><id>7665965</id></containerId></container></account>
Oracle Database Cloud Backup Module credentials are valid.
Debug: Certificate Success: file = /home/oracle/opc.cert
Debug: Certificate Success:
 Subject : CN=DigiCert Global Root CA, OU=www.digicert.com, O=DigiCert Inc, C=US
 Validity : Fri Nov 10 00:00:00 UTC 2006 - Mon Nov 10 00:00:00 UTC 2031
 Issuer : CN=DigiCert Global Root CA, OU=www.digicert.com, O=DigiCert Inc, C=US
Oracle Database Cloud Backup Module wallet created in directory /home/oracle/my_wallet.
Oracle Database Cloud Backup Module initialization file /u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora created.
Downloading Oracle Database Cloud Backup Module Software Library from file opc_linux64.zip.
Debug: Temp zip file = /tmp/opc_linux646549103754721599993.zip
Debug: Downloaded 27314069 bytes in 14 seconds.
Debug: Transfer rate was 1951004 bytes/second.
Download complete.
Debug: Delete RC = true

Please note your default container is oracle-data-storage6-1.

2.Please encrypt all tablespace (Including PDB tablespaces) using below note.

http://clouddba.co/tde-encryption-setup-for-migrating-on-premise-backup-to-oracle-cloud-using-rman/

3. Taking backup from on-premises to oracle cloud now.Please note that backup configuration file was created as a part of step 1.

[oracle@gnssrv01 dbs]$ cat $ORACLE_HOME/dbs/opcorcl12c.ora
OPC_HOST=https://sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun
OPC_WALLET=’LOCATION=file:/home/oracle/bkp_wallet CREDENTIAL_ALIAS=alias_opc’

RMAN script to execute:-

run{
CONFIGURE CHANNEL DEVICE TYPE sbt PARMS='SBT_LIBRARY=/u01/app/oracle/product/12.1.0/dbhome_1/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)';
CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
configure default device type to sbt;
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 5 BACKUP TYPE TO COMPRESSED BACKUPSET;
backup database ;
backup archivelog all;
backup current controlfile;
}

[oracle@gnssrv01 dbs]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Mon Apr 23 08:25:49 2018

connected to target database: ORCL12C (DBID=804514966)

RMAN> set encryption on;

executing command: SET encryption
using target database control file instead of recovery catalog

RMAN> run{
2> CONFIGURE CHANNEL DEVICE TYPE sbt PARMS=’SBT_LIBRARY=/u01/app/oracle/product/12.1.0/dbhome_1/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)’;
CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
configure default device type to sbt;
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 5 BACKUP TYPE TO COMPRESSED BACKUPSET;
backup database plus archivelog;
backup current controlfile;
}3> 4> 5> 6> 7> 8> 9>

old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ PARMS ‘SBT_LIBRARY=/u01/app/oracle/product/12.1.0/dbhome_1/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)’;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ PARMS ‘SBT_LIBRARY=/u01/app/oracle/product/12.1.0/dbhome_1/lib/libopc.so,SBT_PARMS=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)’;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE ‘SBT_TAPE’ PARALLELISM 5 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE ‘SBT_TAPE’ BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 5;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE ‘SBT_TAPE’ BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 5;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE ‘SBT_TAPE’ PARALLELISM 5 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored

Starting backup at 23-APR-18
current log archived
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=276 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Database Backup Service Library VER=12.2.0.1
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=250 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Database Backup Service Library VER=12.2.0.1
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: SID=41 device type=SBT_TAPE
channel ORA_SBT_TAPE_3: Oracle Database Backup Service Library VER=12.2.0.1
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: SID=270 device type=SBT_TAPE
channel ORA_SBT_TAPE_4: Oracle Database Backup Service Library VER=12.2.0.1
allocated channel: ORA_SBT_TAPE_5
channel ORA_SBT_TAPE_5: SID=30 device type=SBT_TAPE
channel ORA_SBT_TAPE_5: Oracle Database Backup Service Library VER=12.2.0.1
channel ORA_SBT_TAPE_1: starting compressed archived log backup set
channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=16 RECID=15 STAMP=974188502
channel ORA_SBT_TAPE_1: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_2: starting compressed archived log backup set
channel ORA_SBT_TAPE_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=18 RECID=17 STAMP=974190408
channel ORA_SBT_TAPE_2: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_3: starting compressed archived log backup set
channel ORA_SBT_TAPE_3: specifying archived log(s) in backup set
input archived log thread=1 sequence=17 RECID=16 STAMP=974188571
channel ORA_SBT_TAPE_3: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_3: finished piece 1 at 23-APR-18
piece handle=1at11tsh_1_1 tag=TAG20180423T082801 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_3: backup set complete, elapsed time: 00:00:25
channel ORA_SBT_TAPE_2: finished piece 1 at 23-APR-18
piece handle=19t11tsh_1_1 tag=TAG20180423T082801 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:35
channel ORA_SBT_TAPE_1: finished piece 1 at 23-APR-18
piece handle=18t11tsh_1_1 tag=TAG20180423T082801 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:05:05
Finished backup at 23-APR-18

Starting backup at 23-APR-18
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4
using channel ORA_SBT_TAPE_5
channel ORA_SBT_TAPE_1: starting compressed full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl12c/system01_encrypt.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_2: starting compressed full datafile backup set
channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl12c/sysaux01_encrypt.dbf
channel ORA_SBT_TAPE_2: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_3: starting compressed full datafile backup set
channel ORA_SBT_TAPE_3: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/orcl12c/orclpdb/sysaux01_encrypt.dbf
channel ORA_SBT_TAPE_3: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_4: starting compressed full datafile backup set
channel ORA_SBT_TAPE_4: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
channel ORA_SBT_TAPE_4: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_5: starting compressed full datafile backup set
channel ORA_SBT_TAPE_5: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/orcl12c/orclpdb/system01_encrypt.dbf
channel ORA_SBT_TAPE_5: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_5: finished piece 1 at 23-APR-18
piece handle=1ft11u65_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_5: backup set complete, elapsed time: 00:05:15
channel ORA_SBT_TAPE_5: starting compressed full datafile backup set
channel ORA_SBT_TAPE_5: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
channel ORA_SBT_TAPE_5: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_4: finished piece 1 at 23-APR-18
piece handle=1et11u63_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_4: backup set complete, elapsed time: 00:09:40
channel ORA_SBT_TAPE_4: starting compressed full datafile backup set
channel ORA_SBT_TAPE_4: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl12c/undotbs01.dbf_encrypt.dbf
channel ORA_SBT_TAPE_4: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_5: finished piece 1 at 23-APR-18
piece handle=1gt11ug0_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_5: backup set complete, elapsed time: 00:04:26
channel ORA_SBT_TAPE_5: starting compressed full datafile backup set
channel ORA_SBT_TAPE_5: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl12c/users01_encrypt.dbf
channel ORA_SBT_TAPE_5: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_5: finished piece 1 at 23-APR-18
piece handle=1it11uoa_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_5: backup set complete, elapsed time: 00:01:25
channel ORA_SBT_TAPE_5: starting compressed full datafile backup set
channel ORA_SBT_TAPE_5: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/orcl12c/orclpdb/users01_encrypt.dbf
channel ORA_SBT_TAPE_5: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_4: finished piece 1 at 23-APR-18
piece handle=1ht11uo9_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_4: backup set complete, elapsed time: 00:01:26
channel ORA_SBT_TAPE_5: finished piece 1 at 23-APR-18
piece handle=1jt11uqv_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_5: backup set complete, elapsed time: 00:00:25
channel ORA_SBT_TAPE_2: finished piece 1 at 23-APR-18
piece handle=1ct11u63_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:12:13
channel ORA_SBT_TAPE_3: finished piece 1 at 23-APR-18
piece handle=1dt11u63_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_3: backup set complete, elapsed time: 00:14:33
channel ORA_SBT_TAPE_1: finished piece 1 at 23-APR-18
piece handle=1bt11u63_1_1 tag=TAG20180423T083306 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:38:04
Finished backup at 23-APR-18

Starting backup at 23-APR-18
current log archived
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4
using channel ORA_SBT_TAPE_5
channel ORA_SBT_TAPE_1: starting compressed archived log backup set
channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=19 RECID=18 STAMP=974193071
channel ORA_SBT_TAPE_1: starting piece 1 at 23-APR-18
channel ORA_SBT_TAPE_1: finished piece 1 at 23-APR-18
piece handle=1kt120dg_1_1 tag=TAG20180423T091112 comment=API Version 2.0,MMS Version 12.2.0.1
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:35
Finished backup at 23-APR-18

Starting Control File and SPFILE Autobackup at 23-APR-18
piece handle=c-804514966-20180423-00 comment=API Version 2.0,MMS Version 12.2.0.1
Finished Control File and SPFILE Autobackup at 23-APR-18

4.Please note control file backup taken now which will be required to restore in cloud database.

RMAN> list backup of controlfile;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
45 Full 512.00K SBT_TAPE 00:00:08 23-APR-18
BP Key: 45 Status: AVAILABLE Compressed: YES Tag: TAG20180423T091147
Handle: 1lt120ej_1_1 Media: sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun/oracle
Control File Included: Ckp SCN: 2008170 Ckp time: 23-APR-18

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
46 Full 18.00M SBT_TAPE 00:00:09 23-APR-18
BP Key: 46 Status: AVAILABLE Compressed: NO Tag: TAG20180423T091214
Handle: c-804514966-20180423-00 Media: sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun/oracle
Control File Included: Ckp SCN: 2008184 Ckp time: 23-APR-18

5.Please note list of archive logs in backup.These archive logs needs to be restored later .

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL12C
=====================================================================

Key Thrd Seq S Low Time
——- —- ——- – ———
15 1 16 A 23-APR-18
Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_23/o1_mf_1_16_fftjzrkr_.arc

16 1 17 A 23-APR-18
Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_23/o1_mf_1_17_fftk238x_.arc

17 1 18 A 23-APR-18
Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_23/o1_mf_1_18_fftlvjqy_.arc

18 1 19 A 23-APR-18
Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_23/o1_mf_1_19_fftogq8q_.arc

19 1 20 A 23-APR-18
Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_24/o1_mf_1_20_ffxojbfp_.arc

20 1 21 A 24-APR-18
Name: /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2018_04_24/o1_mf_1_21_ffxpg2fn_.arc

B.Restoration of on-premise database backup to cloud Machine

1.Please copy the TDE wallet generated in on-premise in previous step to oracle cloud TDE wallet (/u01/app/oracle/admin/orcl12c/tde_wallet).This database will be cloned from existing on-premise backup stored in cloud container.

[oracle@myclone admin]$ cat sqlnet.ora
SQLNET.ENCRYPTION_SERVER = required

SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA1)

SQLNET.CRYPTO_CHECKSUM_SERVER = required

ENCRYPTION_WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/orcl12c/tde_wallet)))

SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)

NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

SQLNET.WALLET_OVERRIDE = FALSE

SQLNET.EXPIRE_TIME = 10

SSL_VERSION = 1.2

WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/orcl12c/db_wallet)))

Please restart the database and check wallet status

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/u01/app/oracle/admin/orcl12c/tde_wallet/
OPEN AUTOLOGIN SINGLE NO
1

2.Please copy opc wallet from on-premise database which we created using step 1 procedure (earlier part of this note) of this note.You can also create a new OPC file as well using step 1 procedure.

3.Please make a note or modify the opc configuration file.Please note I have explicitly mentioned OPC_CONTAINER if it is not able to identify from previous process.

[oracle@myclone dbs]$ cat opcorcl12c.ora
OPC_HOST=https://sadhuarun.eu.storage.oraclecloud.com/v1/Storage-sadhuarun
OPC_WALLET=’LOCATION=file:/home/oracle/my_wallet CREDENTIAL_ALIAS=alias_opc’ OPC_CONTAINER=’oracle-data-storage6-1′

4.Please restore the controlfile now.Please use the control file tag we identified during backup.

[oracle@myclone orcl12c]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 24 06:48:01 2018

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.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2768240640 bytes
Fixed Size 8796624 bytes
Variable Size 704644656 bytes
Database Buffers 1979711488 bytes
Redo Buffers 75087872 bytes
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

[oracle@myclone orcl12c]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Apr 24 06:49:43 2018

connected to target database: ORCL12C (not mounted)

RMAN>set decryption identified by ‘manager123’; <—(If you do not use autologin TDE wallet,you need to execute this command for password based TDE wallet)

RMAN> set DBID=804514966;<---(This DBID is on-premise database DBID)
run{

executing command: SET DBID
allocate CHANNEL t1 TYPE SBT_TAPE PARMS 'SBT_LIBRARY=/u01/app/oracle/product/12.2.0/dbhome_1/lib/libopc.so,ENV=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)';
restore controlfile from 'c-804514966-20180423-00';
}

RMAN> 2> 3> 4>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=237 device type=SBT_TAPE
channel t1: Oracle Database Backup Service Library VER=12.2.0.1

Starting restore at 24-APR-18

channel t1: restoring control file
channel t1: restore complete, elapsed time: 00:00:03
output file name=/u02/app/oracle/oradata/orcl12c/control01.ctl
output file name=/u03/app/oracle/fast_recovery_area/orcl12c/control02.ctl
Finished restore at 24-APR-18
released channel: t1

RMAN>alter database mount;

5.Now we will restore the database.

run{
allocate CHANNEL t1 TYPE SBT_TAPE PARMS 'SBT_LIBRARY=/u01/app/oracle/product/12.2.0/dbhome_1/lib/libopc.so,ENV=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)';
restore database;
}

RMAN> 2> 3> 4>

allocated channel: t1
channel t1: SID=22 device type=SBT_TAPE
channel t1: Oracle Database Backup Service Library VER=12.2.0.1

Starting restore at 24-APR-18
Starting implicit crosscheck backup at 24-APR-18
Finished implicit crosscheck backup at 24-APR-18

Starting implicit crosscheck copy at 24-APR-18
Finished implicit crosscheck copy at 24-APR-18

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_8_ffxnqmvs_.arc
File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_7_ffxlzxkz_.arc
File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_22/o1_mf_1_6_ffrz86dj_.arc
File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_21/o1_mf_1_1_ffpn8cd7_.arc
File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_21/o1_mf_1_3_ffpn9jpr_.arc
File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_21/o1_mf_1_2_ffpn969s_.arc
File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_21/o1_mf_1_4_ffpnbm66_.arc
File Name: /u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_21/o1_mf_1_5_ffpnc7g3_.arc

channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00009 to /u01/app/oracle/oradata/orcl12c/orclpdb/system01_encrypt.dbf
channel t1: reading from backup piece 1ft11u65_1_1
channel t1: piece handle=1ft11u65_1_1 tag=TAG20180423T083306
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:15
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf

…….

channel t1: restore complete, elapsed time: 00:00:15
Finished restore at 24-APR-18
released channel: t1

6.Please restore the archive logs from backup

[oracle@myclone orcl12c]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Apr 24 07:32:18 2018

connected to target database: ORCL12C (DBID=804514966, not open)

RMAN> run{
allocate CHANNEL t1 TYPE SBT_TAPE PARMS 'SBT_LIBRARY=/u01/app/oracle/product/12.2.0/dbhome_1/lib/libopc.so,ENV=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcorcl12c.ora)';
restore archivelog from sequence 16 until sequence 21;
}

7.Recover the database now

[oracle@myclone orcl12c]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 24 07:33:55 2018

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

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 2007164 generated at 04/23/2018 08:33:07 needed for thread 1
ORA-00289: suggestion :
/u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_19_ffxq
gvp1_.arc
ORA-00280: change 2007164 for thread 1 is in sequence #19

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 2008148 generated at 04/23/2018 09:11:11 needed for thread 1
ORA-00289: suggestion :
/u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_20_%u_.
arc
ORA-00280: change 2008148 for thread 1 is in sequence #20
ORA-00278: log file
‘/u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_19_ffx
qgvp1_.arc’ no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
‘/u03/app/oracle/fast_recovery_area/ORCL12C/archivelog/2018_04_24/o1_mf_1_20_%u_
.arc’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

7. Please open the database in resetlogs

SQL> alter database open resetlogs;

Database altered.

Active RMAN duplicate clone 12c using section size and compress backupset

Posted on December 10, 2017April 11, 2018 by debasis maity

Overview of New PULL method

The original “push” process is based on image copies.With Oracle Database 12c, a “pull” (or restore) process is based on backup sets. A connection is first established with the source database. The auxiliary instance then retrieves the required database files from the source database as backup sets. A restore operation is performed from the auxiliary instance instance. Therefore, fewer resources are used on the source database.

Both TNS connections are required on target and auxiliary instances.Based on the DUPLICATE clauses, RMAN dynamically determines which process to use (push or pull’. This ensures that existing customized scripts continue to function.

When you specify USING BACKUPSET, RMAN uses the pull method.
When you specify SET ENCRYPTION before the DUPLICATE command, RMAN

automatically uses the pull method and creates backup sets. The backups sent to the destination are encrypted.

The SECTION SIZE clause divides data files into subsections that are restored in parallel across multiple channels on the auxiliary database. For an effective use of parallelization, allocate more AUXILIARY channels.

With the USING COMPRESSED BACKUPSET clause, the files are transferred as compressed backup sets. RMAN uses unused block compression while creating backups,thus reducing the size of backups that are transported over the network.

NOOPEN

You might duplicate a database with RMAN for various reasons. In earlier versions a recovered duplicated database was automatically opened. By default, this functionality continues with the Oracle Database 12c.
What is new is that you have an option to finish the duplication process with the database in a mounted, but not opened state. This is useful when the attempt to open the database would produce errors and in all cases when you want to modify initialization settings, which are otherwise quite difficult to modify.
For example, you may want to move the location of the database to ASM. Also when you are performing an upgrade, where the database must not be open with resetlogs, prior to running upgrade scripts.
The NOOPEN option allows the duplication to create a new database as part of an upgrade procedure and leaves the database in a state ready for opening in upgrade mode and subsequent execution of upgrade scripts.

Multi-section now is available on image copy.

Active duplication step

Create init parameter file from source and change relevant parameters like control_file,db_name etc.I have provided below sample of init.ora.

[oracle@rac1 dbs]$ cat initrcat.ora
*._catalog_foreign_restore=FALSE
*.audit_file_dest=’/u01/app/product/admin/rcat/adump’
*.audit_trail=’db’
*.compatible=’12.1.0.2.0′
*.control_files=’+DATA/controlrcat.clt’
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_domain=”
*.db_name=’rcat’
*.db_recovery_file_dest=’+DATA’
*.db_recovery_file_dest_size=4785m
*.diagnostic_dest=’/u01/app/product’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rcatXDB)’
*.enable_pluggable_database=true
*.open_cursors=300
*.optimizer_adaptive_features=FALSE
*.parallel_max_servers=8
*.parallel_min_servers=0
*.pga_aggregate_target=570m
*.processes=300
*.remote_login_passwordfile=’exclusive’
*.session_cached_cursors=1000
*.sga_target=1710m
*.shared_pool_size=629145600
*.undo_tablespace=’UNDOTBS1′

Create required audit directory in target

mkdir -p /u01/app/product/admin/rcat/adump

Add entry as static listener.ora under $GRID_HOME/network/admin

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = rcat)
(ORACLE_HOME = /u01/app/product/ora12c/12.1.0/dbhome_1)
(GLOBAL_DBNAME = rcat)
)
)

Add corresponding entry in tnsnames.ora

[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/product/ora12c/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

############Source###################

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

#######Target#################
RCAT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rcat)
(UR=A)
)
)

The (UR=A) clause for TNS connect strings was created in response to an enhancement request.
This clause can be inserted into the “(CONNECT_DATA=” section of a TNS connect string and allow a privileged or administrative user to connect via the listener even when the service handler is blocking connections for non-privileged users. This feature is introduced since Oracle 10g

If UR=A is not added,you will get following error during connection to target in nomount state in next steps:-

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Create password file

cd $ORACLE_HOME/dbs

orapwd file=orapwrcat password=oracle

Now start target database in nomount

export ORACLE_SID=rcat

[oracle@rac1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 9 23:20:41 2017

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> shutdow abort;
  ORACLE instance shut down.
  SQL> startup nomount pfile='initrcat.ora';
  ORACLE instance started.

Total System Global Area 1795162112 bytes
Fixed Size 2925456 bytes
Variable Size 805309552 bytes
Database Buffers 973078528 bytes
Redo Buffers 13848576 bytes

Please verify password file is working as expected in target

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
—————————— —– —– —– —– —– —– ———-
SYS TRUE TRUE FALSE FALSE FALSE FALSE 1

Register in listener

SQL>alter system register;

Now connect to target and auxiliary and start duplicate using new 12c parameters

[oracle@rac1 dbs]$ rman target sys/oracle@orcl auxiliary sys/oracle@rcat

Recovery Manager: Release 12.1.0.2.0 – Production on Sat Dec 9 23:24:40 2017

connected to target database: ORCL (DBID=1489144156)
connected to auxiliary database: RCAT (not mounted)

RMAN> CONFIGURE DEVICE TYPE disk PARALLELISM 4;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> duplicate target database to rcat from active database section size 500M using compressed backupset;

Starting Duplicate Db at 09-DEC-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=34 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=35 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=36 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=37 device type=DISK
current log archived

contents of Memory Script:
{
sql clone “create spfile from memory”;
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1795162112 bytes

Fixed Size 2925456 bytes
Variable Size 822086768 bytes
Database Buffers 956301312 bytes
Redo Buffers 13848576 bytes

contents of Memory Script:
{
sql clone “alter system set db_name =
”ORCL” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”RCAT” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone from service ‘orcl’ using compressed backupset
primary controlfile;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ”ORCL” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set db_unique_name = ”RCAT” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 1795162112 bytes

Fixed Size 2925456 bytes
Variable Size 822086768 bytes
Database Buffers 956301312 bytes
Redo Buffers 13848576 bytes

Starting restore at 09-DEC-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=33 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=35 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=36 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=37 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using compressed network backup set from service orcl
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DATA/controlrcat.clt
Finished restore at 09-DEC-17

database mounted

contents of Memory Script:
{
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 7 to new;
set newname for clone datafile 8 to new;
set newname for clone datafile 15 to new;
set newname for clone datafile 16 to new;
set newname for clone datafile 17 to new;
restore
from service ‘orcl’ section size
500 m using compressed backupset
clone database
;
sql ‘alter system archive log current’;
}
executing Memory Script

executing command: SET NEWNAME

Starting restore at 09-DEC-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using compressed network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA/RCAT/DATAFILE/system.313.962321229
channel ORA_AUX_DISK_1: restoring section 1 of 2
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: using compressed network backup set from service orcl
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00003 to +DATA/RCAT/DATAFILE/sysaux.325.962321231
channel ORA_AUX_DISK_2: restoring section 1 of 2
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: using compressed network backup set from service orcl
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00004 to +DATA/RCAT/DATAFILE/undotbs1.326.962321233
channel ORA_AUX_DISK_3: restoring section 1 of 1
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: using compressed network backup set from service orcl
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00005 to +DATA/RCAT/DATAFILE/system.327.962321241
channel ORA_AUX_DISK_4: restoring section 1 of 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:27
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: using compressed network backup set from service orcl
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00001 to +DATA/RCAT/DATAFILE/system.313.962321229
channel ORA_AUX_DISK_3: restoring section 2 of 2
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:01:25
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: using compressed network backup set from service orcl
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00003 to +DATA/RCAT/DATAFILE/sysaux.325.962321231
channel ORA_AUX_DISK_4: restoring section 2 of 2
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:02:03
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: using compressed network backup set from service orcl
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00006 to +DATA/RCAT/DATAFILE/users.328.962321353
channel ORA_AUX_DISK_2: restoring section 1 of 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:05
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: using compressed network backup set from service orcl
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00007 to +DATA/RCAT/DATAFILE/sysaux.329.962321359
channel ORA_AUX_DISK_2: restoring section 1 of 2
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:01:46
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: using compressed network backup set from service orcl
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00007 to +DATA/RCAT/DATAFILE/sysaux.329.962321359
channel ORA_AUX_DISK_3: restoring section 2 of 2
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using compressed network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to +DATA/RCAT/DATAFILE/undotbs2.330.962321375
channel ORA_AUX_DISK_1: restoring section 1 of 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:13
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: using compressed network backup set from service orcl
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00015 to +DATA/RCAT/DATAFILE/system.331.962321379
channel ORA_AUX_DISK_3: restoring section 1 of 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:55
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: using compressed network backup set from service orcl
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00016 to +DATA/RCAT/DATAFILE/sysaux.332.962321381
channel ORA_AUX_DISK_4: restoring section 1 of 2
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using compressed network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00016 to +DATA/RCAT/DATAFILE/sysaux.332.962321381
channel ORA_AUX_DISK_1: restoring section 2 of 2
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:17
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using compressed network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00017 to +DATA/RCAT/DATAFILE/my_tbs.333.962321401
channel ORA_AUX_DISK_1: restoring section 1 of 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:01:08
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:01:50
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:01:37
Finished restore at 09-DEC-17

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
restore clone force from service ‘orcl’ using compressed backupset
archivelog from scn 3152882;
switch clone datafile all;
}
executing Memory Script

Starting restore at 09-DEC-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using compressed network backup set from service orcl
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=47
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: using compressed network backup set from service orcl
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=48
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:02
Finished restore at 09-DEC-17

datafile 1 switched to datafile copy
input datafile copy RECID=27 STAMP=962321483 file name=+DATA/RCAT/DATAFILE/system.313.962321229
datafile 3 switched to datafile copy
input datafile copy RECID=28 STAMP=962321483 file name=+DATA/RCAT/DATAFILE/sysaux.325.962321231
datafile 4 switched to datafile copy
input datafile copy RECID=29 STAMP=962321484 file name=+DATA/RCAT/DATAFILE/undotbs1.326.962321233
datafile 5 switched to datafile copy
input datafile copy RECID=30 STAMP=962321484 file name=+DATA/RCAT/DATAFILE/system.327.962321241
datafile 6 switched to datafile copy
input datafile copy RECID=31 STAMP=962321484 file name=+DATA/RCAT/DATAFILE/users.328.962321353
datafile 7 switched to datafile copy
input datafile copy RECID=32 STAMP=962321485 file name=+DATA/RCAT/DATAFILE/sysaux.329.962321359
datafile 8 switched to datafile copy
input datafile copy RECID=33 STAMP=962321485 file name=+DATA/RCAT/DATAFILE/undotbs2.330.962321375
datafile 15 switched to datafile copy
input datafile copy RECID=34 STAMP=962321485 file name=+DATA/RCAT/DATAFILE/system.331.962321379
datafile 16 switched to datafile copy
input datafile copy RECID=35 STAMP=962321485 file name=+DATA/RCAT/DATAFILE/sysaux.332.962321381
datafile 17 switched to datafile copy
input datafile copy RECID=36 STAMP=962321486 file name=+DATA/RCAT/DATAFILE/my_tbs.333.962321401

contents of Memory Script:
{
set until scn 3153106;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 09-DEC-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

starting media recovery

archived log for thread 1 with sequence 47 is already on disk as file +DATA/RCAT/ARCHIVELOG/2017_12_09/thread_1_seq_47.336.962321481
archived log for thread 1 with sequence 48 is already on disk as file +DATA/RCAT/ARCHIVELOG/2017_12_09/thread_1_seq_48.337.962321483
archived log file name=+DATA/RCAT/ARCHIVELOG/2017_12_09/thread_1_seq_47.336.962321481 thread=1 sequence=47
archived log file name=+DATA/RCAT/ARCHIVELOG/2017_12_09/thread_1_seq_48.337.962321483 thread=1 sequence=48
media recovery complete, elapsed time: 00:00:01
Finished recover at 09-DEC-17
Oracle instance started

Total System Global Area 1795162112 bytes

Fixed Size 2925456 bytes
Variable Size 822086768 bytes
Database Buffers 956301312 bytes
Redo Buffers 13848576 bytes

contents of Memory Script:
{
sql clone “alter system set db_name =
”RCAT” comment=
”Reset to original value by RMAN” scope=spfile”;
sql clone “alter system reset db_unique_name scope=spfile”;
}
executing Memory Script

sql statement: alter system set db_name = ”RCAT” comment= ”Reset to original value by RMAN” scope=spfile

sql statement: alter system reset db_unique_name scope=spfile
Oracle instance started

Total System Global Area 1795162112 bytes

Fixed Size 2925456 bytes
Variable Size 822086768 bytes
Database Buffers 956301312 bytes
Redo Buffers 13848576 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “RCAT” RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M
DATAFILE
‘+DATA/RCAT/DATAFILE/system.313.962321229’,
‘+DATA/RCAT/DATAFILE/system.327.962321241’,
‘+DATA/RCAT/DATAFILE/system.331.962321379’
CHARACTER SET AL32UTF8

sql statement: ALTER DATABASE ADD LOGFILE

INSTANCE ‘i2’
GROUP 3 SIZE 50 M ,
GROUP 4 SIZE 50 M

contents of Memory Script:
{
set newname for clone tempfile 1 to new;
set newname for clone tempfile 2 to new;
set newname for clone tempfile 3 to new;
switch clone tempfile all;
catalog clone datafilecopy “+DATA/RCAT/DATAFILE/sysaux.325.962321231”,
“+DATA/RCAT/DATAFILE/undotbs1.326.962321233”,
“+DATA/RCAT/DATAFILE/users.328.962321353”,
“+DATA/RCAT/DATAFILE/sysaux.329.962321359”,
“+DATA/RCAT/DATAFILE/undotbs2.330.962321375”,
“+DATA/RCAT/DATAFILE/sysaux.332.962321381”,
“+DATA/RCAT/DATAFILE/my_tbs.333.962321401”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file
renamed tempfile 2 to +DATA in control file
renamed tempfile 3 to +DATA in control file

cataloged datafile copy
datafile copy file name=+DATA/RCAT/DATAFILE/sysaux.325.962321231 RECID=1 STAMP=962321529
cataloged datafile copy
datafile copy file name=+DATA/RCAT/DATAFILE/undotbs1.326.962321233 RECID=2 STAMP=962321529
cataloged datafile copy
datafile copy file name=+DATA/RCAT/DATAFILE/users.328.962321353 RECID=3 STAMP=962321530
cataloged datafile copy
datafile copy file name=+DATA/RCAT/DATAFILE/sysaux.329.962321359 RECID=4 STAMP=962321530
cataloged datafile copy
datafile copy file name=+DATA/RCAT/DATAFILE/undotbs2.330.962321375 RECID=5 STAMP=962321530
cataloged datafile copy
datafile copy file name=+DATA/RCAT/DATAFILE/sysaux.332.962321381 RECID=6 STAMP=962321530
cataloged datafile copy
datafile copy file name=+DATA/RCAT/DATAFILE/my_tbs.333.962321401 RECID=7 STAMP=962321530

datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=962321529 file name=+DATA/RCAT/DATAFILE/sysaux.325.962321231
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=962321529 file name=+DATA/RCAT/DATAFILE/undotbs1.326.962321233
datafile 6 switched to datafile copy
input datafile copy RECID=3 STAMP=962321530 file name=+DATA/RCAT/DATAFILE/users.328.962321353
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=962321530 file name=+DATA/RCAT/DATAFILE/sysaux.329.962321359
datafile 8 switched to datafile copy
input datafile copy RECID=5 STAMP=962321530 file name=+DATA/RCAT/DATAFILE/undotbs2.330.962321375
datafile 16 switched to datafile copy
input datafile copy RECID=6 STAMP=962321530 file name=+DATA/RCAT/DATAFILE/sysaux.332.962321381
datafile 17 switched to datafile copy
input datafile copy RECID=7 STAMP=962321530 file name=+DATA/RCAT/DATAFILE/my_tbs.333.962321401

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened

contents of Memory Script:
{
sql clone “alter pluggable database all open”;
}
executing Memory Script

sql statement: alter pluggable database all open
Cannot remove created server parameter file
Finished Duplicate Db at 09-DEC-17

Create password file in ASM now

Create spfile.

Now add database in srvctl

srvctl add database -db rcat -oraclehome $ORACLE_HOME
srvctl modify database -db rcat -pwfile +DATA/pwdrcat.ora
srvctl modify database -db rcat -spfile +DATA/spfilercat.ora

Issue faced:-

RMAN-11003: failure during parse/execution of SQL statement: alter system set db_unique_name = ‘RCAT’ comment= ‘Modified by RMAN duplicate’ scope=spfile
ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

Please remove the database from srvctl if already added before duplicate

[oracle@rac1 dbs]$ srvctl remove database -d rcat

rman command reference

Posted on December 8, 2017April 30, 2018 by debasis maity

##Connecting RMAN##############

rman TARGET SYS/target_pwd@target_str # connects in NOCATALOG mode
rman TARGET / CATALOG rman/rman@rcat
rman TARGET / CATALOG rman/rman@rcat AUXILIARY sys/aux_pwd@aux_str

##Create user and catalog in RMAN database ########

CREATE USER rman_dba IDENTIFIED BY rman_dba TEMPORARY TABLESPACE temp DEFAULT TABLESPACE rman_dba QUOTA UNLIMITED ON rman_dba;
GRANT RECOVERY_CATALOG_OWNER TO rman_dba;
CREATE CATALOG;

## Register Database######

Rman target / catalog rman_dba/rman_dba@<catalog>

register database;
select * from rc_database;

### Catalog copy in RMAN Catalog###

CATALOG BACKUPPIECE ‘/disk2/09dtq55d_1_2’, ‘/disk2/0bdtqdou_1_1’;
CATALOG DATAFILECOPY ‘/tmp/users01.dbf’;
CATALOG RECOVERY AREA;
CHANGE CONTROLFILECOPY ‘/tmp/control01.ctl’ UNCATALOG;
CHANGE DATAFILECOPY ‘/tmp/system01.dbf’ UNCATALOG;
CHANGE DATAFILECOPY ‘/tmp/control01.ctl’ UNAVAILABLE;
CHANGE COPY OF ARCHIVELOG SEQUENCE BETWEEN 1000 AND 1012 UNAVAILABLE;
CHANGE BACKUPSET 12 UNAVAILABLE;
CHANGE BACKUP OF SPFILE TAG “TAG20020208T154556” UNAVAILABLE;
CHANGE DATAFILECOPY ‘/tmp/system01.dbf’ AVAILABLE;
CHANGE BACKUPSET 12 AVAILABLE;
CHANGE BACKUP OF SPFILE TAG “TAG20020208T154556” AVAILABLE;
CATALOG START WITH ‘/backup/MYSID/arch’;

### Configure RMAN ######

CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR;
CONFIGURE RETENTION POLICY CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

CONFIGURE DEFAULT DEVICE TYPE TO DISK/SBT;
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DEVICE TYPE sbt PARALLELISM 2;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘d:\oracle\orclbackup\ora_df%t_s%s_s%p’;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘d:\oracle\orclbackup\ora_cf%F’;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT /tmp/%U;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘+dgroup1/%F’;
CONFIGURE DEVICE TYPE <DISK | SBT> BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/disk1/%U’, ‘/disk2/%U’;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE sbt TO 2;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE sbt TO 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;

###RMAN backup check view######

SELECT SID,
SERIAL#,
CONTEXT,
SOFAR,
TOTALWORK,
ROUND(SOFAR / TOTALWORK * 100, 2) “% COMPLETE”
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE ‘RMAN%’ AND OPNAME NOT LIKE ‘%aggregate%’ AND
TOTALWORK != 0 AND SOFAR <> TOTALWORK ;

COL in_sec FORMAT a10
COL out_sec FORMAT a10
COL TIME_TAKEN_DISPLAY FORMAT a10
SELECT SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY in_sec,
OUTPUT_BYTES_PER_SEC_DISPLAY out_sec,
TIME_TAKEN_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;

SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER
WHERE RECOVER = ‘YES’
OR (RECOVER IS NULL AND ERROR IS NOT NULL);

SELECT FILE#, INCREMENTAL_LEVEL, COMPLETION_TIME,
BLOCKS, DATAFILE_BLOCKS
FROM V$BACKUP_DATAFILE
WHERE INCREMENTAL_LEVEL > 0
AND BLOCKS / DATAFILE_BLOCKS > .2
ORDER BY COMPLETION_TIME;

SELECT * FROM V$RECOVERY_FILE_DEST;
SELECT * FROM V$RECOVERY_AREA_USAGE;
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

### List and Report of backup ####

LIST BACKUP OF DATABASE;
LIST COPY OF DATAFILE 1, 2;
LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 10;
LIST BACKUPSET OF DATAFILE 1;
LIST BACKUP;
LIST cOPY;
LIST ARCHIVELOG;
LIST RESTORE POINT;
LIST EXPIRED;
LIST BACKUP SUMMARY;
LIST FAILURE;
LIST BACKUPSET TAG ‘weekly_full_db_backup’;
LIST BACKUPSET 213;
LIST COPY OF DATAFILE 2 COMPLETED BETWEEN ’10-DEC-2002′ AND ’17-DEC-2002′
LIST BACKUP OF DATAFILE 1;

REPORT OBSOLETE;
REPORT SCHEMA;
REPORT NEED BACKUP
REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE DEVICE TYPE sbt;
REPORT NEED BACKUP DEVICE TYPE DISK;
REPORT NEED BACKUP TABLESPACE TBS_3 DEVICE TYPE sbt;
REPORT OBSOLETE RECOVERY WINDOW OF 3 DAYS;
REPORT OBSOLETE REDUNDANCY 1;

## Crosscheck backup #####

CROSSCHECK BACKUP DEVICE TYPE DISK;
CROSSCHECK BACKUP DEVICE TYPE sbt;
CROSSCHECK BACKUP; # checks backup sets, proxy copies, and image copies
CROSSCHECK COPY OF DATABASE;
CROSSCHECK BACKUPSET 1338, 1339, 1340;
CROSSCHECK BACKUPPIECE TAG ‘nightly_backup’;
CROSSCHECK BACKUP OF ARCHIVELOG ALL SPFILE;
CROSSCHECK BACKUP OF DATAFILE “?/oradata/trgt/system01.dbf” COMPLETED AFTER ‘SYSDATE-14’;
CROSSCHECK CONTROLFILECOPY ‘/tmp/control01.ctl’;
CROSSCHECK DATAFILECOPY 113, 114, 115;
CROSSCHECK PROXY 789;

### Delete backup ########

DELETE BACKUPPIECE 101;
DELETE CONTROLFILECOPY ‘/tmp/control01.ctl’;
DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE 300;
DELETE BACKUP TAG ‘before_upgrade’;
DELETE ARCHIVELOG ALL BACKED UP 3 TIMES TO sbt;
DELETE EXPIRED BACKUP;
DELETE OBSOLETE;

###Simple unix script #############

#!/bin/tcsh
# name: runbackup.sh
# usage: use the tag name and number of copies as arguments
set media_family = $argv[1]
set format = $argv[2]
set restore_point = $argv[3]
rman @’/disk1/scripts/whole_db.cmd’ USING $media_family $format $restore_point

% runbackup.sh archival_backup bck0906 FY06Q3

##Backup Database command #####

BACKUP DEVICE TYPE sbt BACKUPSET COMPLETED BEFORE ‘SYSDATE-7’ DELETE INPUT;
BACKUP DEVICE TYPE DISK COPIES 3 DATAFILE 7 FORMAT ‘/disk1/%U’,’?/oradata/%U’,’?/%U’;
BACKUP DEVICE TYPE sbt BACKUPSET COMPLETED BEFORE ‘SYSDATE-7’ DELETE INPUT;
BACKUP AS BACKUPSET DATABASE;
BACKUP AS BACKUPSET DEVICE TYPE DISK DATABASE;
BACKUP AS BACKUPSET DEVICE TYPE SBT DATABASE;
BACKUP AS COPY DEVICE TYPE DISK DATABASE;
BACKUP AS BACKUPSET COPIES 1 DATAFILE 7 TAG mondaybkp;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
BACKUP DEVICE TYPE sbt DATAFILE 1,2,3,4 DATAFILECOPY ‘/tmp/system01.dbf’;
BACKUP AS COPY DB_FILE_NAME_CONVERT (‘/maindisk/oradata/users’,’/backups/users_ts’) TABLESPACE users;
BACKUP INCREMENTAL LEVEL 0 DATABASE;
BACKUP AS BACKUPSET DATABASE FORMAT ‘/disk1/%U’,’/disk2/%U’;
BACKUP AS BACKUPSET DEVICE TYPE DISK COPIES 3 INCREMENTAL LEVEL 0 DATABASE;
BACKUP DURATION 4:00 TABLESPACE users;
BACKUP DURATION 4:00 PARTIAL TABLESPACE users FILESPERSET 1;
BACKUP DURATION 4:00 PARTIAL MINIMIZE TIME DATABASE FILESPERSET 1;
BACKUP DURATION 4:00 PARTIAL MINIMIZE LOAD DATABASE FILESPERSET 1;
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
backup as compressed backupset incremental level 0 database plus archivelog;
BACKUP DEVICE TYPE sbt ARCHIVELOG ALL DELETE ALL INPUT;
BACKUP INCREMENTAL LEVEL 1 TABLESPACE SYSTEM, tools;
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE TABLESPACE users;
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
BACKUP ARCHIVELOG FROM TIME ‘SYSDATE-1’;
BACKUP ARCHIVELOG FROM TIME ‘SYSDATE-5’ UNTIL TIME ‘SYSDATE-1’

RUN
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT ‘/disk1/%d_backups/%U’;
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT ‘/disk2/%d_backups/%U’;
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT ‘/disk3/%d_backups/%U’;
BACKUP AS COPY DATABASE;
}

RUN
{
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT ‘/disk1/%d_backups/%U’;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT ‘/disk2/%d_backups/%U’;
CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT ‘/disk3/%d_backups/%U’;
BACKUP AS COPY DATABASE;
}

## Incremental updated backup #######

recover copy of database with tag ‘tcstest3’;
backup incremental level 1 tag ‘tcstest3’ for recover of copy with tag ‘tcstest3’ database ;

## Block change tracking ########

ALTER SYSTEM SET
DB_CREATE_FILE_DEST = ‘/disk1/bct/’
SCOPE=BOTH SID=’*’;

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE ‘/mydir/rman_change_track.f’ REUSE;

COL STATUS FORMAT A8
COL FILENAME FORMAT A60
SELECT STATUS, FILENAME
FROM V$BLOCK_CHANGE_TRACKING;

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘new_location’;

### Restore Preview #########

RESTORE DATABASE VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;
RESTORE DATABASE PREVIEW;
RESTORE ARCHIVELOG FROM TIME ‘SYSDATE-7’ PREVIEW;
RESTORE DATABASE PREVIEW SUMMARY;
RESTORE ARCHIVELOG ALL PREVIEW RECALL;
REPAIR FAILURE PREVIEW;
VALIDATE DATABASE;
RUN{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
VALIDATE DATAFILE 1 SECTION SIZE 1200M;
}
VALIDATE DATAFILE 4 BLOCK 10 TO 13;
VALIDATE BACKUPSET 3;
RECOVER CORRUPTION LIST;

### Recover database flashback technology ####

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

SELECT CURRENT_SCN FROM V$DATABASE;
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE=’YES’;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

FLASHBACK DATABASE TO SCN 46963;
FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;
FLASHBACK DATABASE TO TIME “TO_DATE(’09/20/05′,’MM/DD/YY’)”;
ALTER DATABASE OPEN READ ONLY;

SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;

SET UNTIL TIME ‘Nov 15 2004 09:00:00’;
SET UNTIL SEQUENCE 9923;
SET UNTIL RESTORE POINT before_update;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;

### Recover database to copy location and again recover back to original location #######

RUN{
SWITCH DATABASE TO COPY;
RECOVER DATABASE;
ALTER DATABASE OPEN;
}

run{
SET NEWNAME FOR DATAFILE 1 TO ‘/oracle/oradata/tcstest/system01.dbf’;
SET NEWNAME FOR DATAFILE 2 TO ‘/oracle/oradata/tcstest/undotbs01.dbf’;
SET NEWNAME FOR DATAFILE 3 TO ‘/oracle/oradata/tcstest/sysaux01.dbf’;
SET NEWNAME FOR DATAFILE 4 TO ‘/oracle/oradata/tcstest/users01.dbf’;
SET NEWNAME FOR DATAFILE 5 TO ‘/oracle/oradata/tcstest/example01.dbf’;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}

## Recover individual tablespace and datafile###

sql “alter tablespace working_data offline”;
sql “alter database datafile 13 offline”;
restore tablespace working_data;
restore datafile 13;
recover tablespace working_data;
recover datafile 13;
sql “alter tablespace working_data online”;
sql “alter database datafile 13 online”;

SWITCH DATAFILE 4 TO COPY;
RECOVER DATAFILE 4;

backup as copy datafile 4;

run{
SET NEWNAME FOR DATAFILE 4 TO ‘/oracle/oradata/tcstest/users01.dbf’;
restore datafile 4;
switch datafile 4;
recover datafile 4;
}

SET NEWNAME FOR DATAFILE ‘/disk1/oradata/prod/users01.dbf’
TO ‘/disk2/users01.dbf’;
RESTORE TABLESPACE users;
SWITCH DATAFILE ALL; # update control file with new filenames
RECOVER TABLESPACE users;

## Recover individual block #######

RECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 199 FROM TAG mondayam;
RECOVER CORRUPTION LIST;

## Recover No-archivelog mode with catalog########

rman target sys/password catalog rcat_user/rcat_password@catalogdb
startup force nomount;
restore spfile from autobackup;
shutdown immediate;
startup nomount;
restore controlfile from autobackup;
alter database mount;
configure default device type to sbt;
configure channel 1 device type sbt parms = “env=(nb_ora_serv=mgtserv, nb_ora_client=cervantes)”;
restore database;
recover database noredo;
alter database open resetlogs;

## Recover No-archivelog mode with no catalog####

rman target sys/password
startup nomount
set dbid=2540040039;
restore controlfile from autobackup;
sql ‘alter database mount’;
restore database;
recover database noredo;
sql “alter database open resetlogs”;

### Recover complete database loss–No catalog ###

rman target /
set dbid=204062491;
startup force nomount;
run {
allocate channel tape_1 type sbt
parms=’env=(nb_ora_serv=rmsrv, nb_ora_client=cervantes)’;
restore spfile from autobackup;

}

shutdown immediate;

startup nomount;

alter system set control_files= ‘/u02/oradata/prod/control01.dbf’,
‘/u03/oradata/prod/control02.dbf’ scope=spfile;

alter system set db_file_name_convert= (‘/u04’ , ‘/u02’ ,
‘/u05’ , ‘/u02’ ,
‘u06’ , ‘ u03’ ,
‘u07’ , ‘u03’) scope=spfile;

alter system set log_file_name_convert= (‘/u04’ , ‘/u02’ ,
‘/u05’ , ‘/u02’ ,
‘u06’ , ‘ u03’ ,
‘u07’ , ‘u03’) scope=spfile;

alter system set log_archive_dest_1=
‘location=/u02/oradata/prod/arch’ scope=spfile;

alter system set db_cache_size=300m scope=spfile;

alter system set shared_pool_size=200m scope=spfile;

shutdown immediate;

startup nomount;

run {
allocate channel tape_1 type sbt
parms=’env=(nb_ora_serv=rmsrv, nb_ora_client=Cervantes)’;
restore controlfile from autobackup;

}

alter database mount;

configure default device type to sbt;

configure device type sbt parallelism 2;

configure auxiliary channel 1 device type sbt parms
= “env=(nb_ora_serv=mgtserv, nb_ora_client=cervantes)”;

configure auxiliary channel 2 device type sbt parms
= “env=(nb_ora_serv=mgtserv, nb_ora_cient=cervantes)”;

list backup of archivelog from time = ‘sysdate-7’;

restore database;

recover database until sequence=<number>;

alter database open resetlogs;

## Recovery of database if inactive redo log is deleted####

sql>startup mount;
sql>alter database clear logfile group 2;
sql>alter database open;

##Recovery after loss of controlfile #######

rman target /
startup nomount;
restore controlfile from autobackup;
alter database mount;
recover database;
alter database open (resetlogs);

## Tablespace point in time recovery ########

Database should be in opened state

recover tablespace “APP_DATA” until time
“to_date(‘2009-08-04 12:15:00’,’YYYY-MM-DD HH24:MI:SS’)”
auxiliary destination ’/opt/oracle/temp’;

### Recovery after loss of current redolog file ###

RUN
{
# SET UNTIL TIME ‘Nov 15 2002 09:00:00’;
# SET UNTIL SCN 1000; # alternatively, specify SCN
SET UNTIL SEQUENCE 1; # alternatively, specify log sequence number
RESTORE DATABASE;
RECOVER DATABASE;
}

###Backup of CDB and PDB in 12c#####

—CDB backup–

export ORACLE_SID=ORCL1

[oracle@rac1 ~]$ rman target /

RMAN> backup database plus archivelog;

—CDB root backup—

RMAN> backup pluggable database “CDB$ROOT”;

–Backup pluggable database—

backup pluggable database oem;

backup pluggable database oem plus archivelog;

Database backup and restore after manual catalog RMAN backup

Posted on December 8, 2017December 8, 2017 by debasis maity

Database backup and restore after manual catalog RMAN backup

1.Please take backup of primary database ORCL

rman target / nocatalog log=/tmp/rman_bkp.log << EOF1
run
{
backup as compressed backupset database format ‘/opt/app/oratest1/bkp/ORCL_%U’;
backup as compressed backupset archivelog all format ‘/opt/app/oratest1/bkp/ORCL_ARCH_%U’;
}
exit;
EOF1

Please copy it in backup location to /opt/app/oratest1/bkp

2.Please start nomount RCATT database.

3.Please create controlfile from backup.The DB_CREATE_FILE_DEST should be set to diskgroup. Control file location should be in Diskgroup location
as control.ctl

CREATE CONTROLFILE SET DATABASE “RCATT” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘+DATA1/rcatt/onlinelog/group_1.261.825435443’ SIZE 50M BLOCKSIZE 512,
GROUP 2 ‘+DATA1/rcatt/onlinelog/group_2.262.825435443’ SIZE 50M BLOCKSIZE 512,
GROUP 3 ‘+DATA1/rcatt/onlinelog/group_3.263.825435445’ SIZE 50M BLOCKSIZE 512
— STANDBY LOGFILE
DATAFILE
‘+DATA1/rcatt/datafile/system.256.825435379’,
‘+DATA1/rcatt/datafile/sysaux.257.825435379’,
‘+DATA1/rcatt/datafile/undotbs1.258.825435381’,
‘+DATA1/rcatt/datafile/users.259.825696133’,
‘+DATA1/rcatt/datafile/orcl.266.825438401’
CHARACTER SET WE8MSWIN1252
;

4.rman target /
RMAN> catalog start with ‘/opt/app/oratest1/bkp’;
RMAN>restore database;
RMAN>list backup; (Please identify highest SCN of archivelog)
RMAN>recover database until sequence 26;(Please specify highest SCN)

Please ignore the following error

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/11/2013 17:02:13
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 16 and starting SCN of 1545450

5.Please open database with resetlogs option.

SQL>alter database open resetlogs

Duplicate database until Point in Time recover, using backup location from RAC to single instance

Posted on December 5, 2017December 5, 2017 by debasis maity

Prepare by: Nurullah Sharif

Scope: Point in Time Recovery

Duplicate database until Point in Time recover, using backup location.

#PointInTimeRecovery #DuplicateDatabase #RestoreDatabaseUsingBackupLocation

We are using full backup of 28-11-2017 and archivelog backup of 29-11-2017

Step 1: Take full backup and archive log backup.

On target database :

Rman target /

run
{
backup as compressed BACKUPSET incremental level 0 tag RMAN_BKP_SSPRODDB FORMAT ‘/nfs_bkpvol1/BACKUP/RMAN/SSPRODDB/full_db_%t_set%s_piece%p_dbid%I.rman’ database;
backup current controlfile tag RMAN_BKP_SSPRODDB FORMAT ‘/nfs_bkpvol1/BACKUP/RMAN/SSPRODDB/ctl_%t_dbid%I.rman’;
backup as compressed BACKUPSET archivelog all tag RMAN_BKP_SSPRODDB FORMAT ‘/nfs_bkpvol1/BACKUP/RMAN/SSPRODDB/arc_%t_set%s_piece%p_dbid%I.rman’ ;
}

Transfer the backup file to target Environment. To create duplicate database.

Step 2: create pfile –

Source database is running in RAC, you must remove all the parameter which are related to Cluster which require to your configuration of single instance database.

SOURCE’s PFILE FILE-

SSPRODDB1.__db_cache_size=1006632960
SSPRODDB2.__db_cache_size=1174405120
SSPRODDB1.__java_pool_size=16777216
SSPRODDB2.__java_pool_size=16777216
SSPRODDB1.__large_pool_size=16777216
SSPRODDB2.__large_pool_size=16777216
SSPRODDB1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
SSPRODDB1.__pga_aggregate_target=1342177280
SSPRODDB2.__pga_aggregate_target=1342177280
SSPRODDB1.__sga_target=4026531840
SSPRODDB2.__sga_target=4026531840
SSPRODDB1.__shared_io_pool_size=536870912
SSPRODDB2.__shared_io_pool_size=536870912
SSPRODDB1.__shared_pool_size=2382364672
SSPRODDB2.__shared_pool_size=2214592512
SSPRODDB1.__streams_pool_size=33554432
SSPRODDB2.__streams_pool_size=33554432
*.audit_file_dest=’/u01/app/oracle/admin/SSPRODDB/adump’
*.audit_trail=’db’
*.cluster_database=true
*.compatible=’11.2.0.0.0′
*.control_files=’+DATA/ssproddb/controlfile/current.285.916959529′,’+DATA/ssproddb/controlfile/current.284.916959529′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_domain=”
*.db_name=’SSPRODDB’
*.db_recovery_file_dest_size=1099511627776
*.db_recovery_file_dest=’+FRA’
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=SSPRODDBXDB)’
SSPRODDB2.instance_number=2
SSPRODDB1.instance_number=1
*.log_archive_dest_1=’location=+FRA’
*.log_archive_format=’ARCH_%t_%s_%r.arc’
*.open_cursors=300
*.pga_aggregate_target=1342177280
*.processes=150
*.remote_listener=’scan:1521′
*.remote_login_passwordfile=’exclusive’
*.sga_target=4026531840
SSPRODDB2.thread=2
SSPRODDB1.thread=1
SSPRODDB2.undo_tablespace=’UNDOTBS2′
SSPRODDB1.undo_tablespace=’UNDOTBS1′

TARGET’s PFILE –

SSP.__db_cache_size=201326592
SSP.__java_pool_size=16777216
SSP.__large_pool_size=33554432
SSP.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
SSP.__pga_aggregate_target=738197504
SSP.__sga_target=1409286144
SSP.__shared_io_pool_size=0
SSP.__shared_pool_size=1107296256
SSP.__streams_pool_size=33554432
*.audit_file_dest=’/oradata1/SSP/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/oradata1/SSP/control01.ctl’,’/oradata1/SSP/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’SSP’
*.diagnostic_dest=’/oradata1/SSP’
*.memory_target=2048M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’exclusive’
*.undo_tablespace=’UNDOTBS2′
*.undo_tablespace=’UNDOTBS1′

Target Database name will SSP

Step 3: start database in nomount state and create spfile.

TARGET’s PFILE –

SQL> startup nomount pfile=’/oradata1/SSP/ssppfile.ora’;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1929382088 bytes
Database Buffers 201326592 bytes
Redo Buffers 4947968 bytes

SQL> create spfile from pfile=’/oradata1/SSP/ssppfile.ora’;
File created.

SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
—————- ————
SSP STARTED

SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1929382088 bytes
Database Buffers 201326592 bytes
Redo Buffers 4947968 bytes

SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
—————- ————
SSP STARTED

SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string /u01/app/oracle/product/dbhome
_1/dbs/spfileSSP.ora

Database is start with newly created spfile

Note: we need to recover database until 29-11-2017 11am, to make sure, we need some specific information from Source database,

We have to find the nearest time of 11am , on which archive been switched.

To find out, run below command in Source database.

select sequence#,to_char(completion_time, ‘dd-mm-yyyy hh24:mi:ss’), to_char(next_time,’dd-mm-yyyy hh24:mi:ss’) from v$archived_log order by 2;

We found sequence 2281 were been recorded in time which is near to our required time.

Step 4: Connect RMAN auxiliary and run duplicate command with point in time recovery using until time argument.

rman auxiliary /

RMAN> run {
set newname for database to ‘/oradata1/SSP/%b’;
duplicate target database to SSP nofilenamecheck backup location ‘/oradata1/RMAN/SSPROD’
UNTIL TIME “TO_DATE(‘2017-11-29 11:26:28’, ‘YYYY-MM-DD HH24:MI:SS’)”
logfile
group 1 (‘/oradata1/SSP/redo01.log’) size 50M,
group 2 (‘/oradata1/SSP/redo02.log’) size 50M,
group 3 (‘/oradata1/SSP/redo03.log’) size 50M;
} 3> 4> 5> 6> 7> 8> 9>

executing command: SET NEWNAME
Starting Duplicate Db at 04-DEC-17
contents of Memory Script:
{
sql clone “alter system set db_name =
”SSPRODDB” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”SSP” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from ‘/oradata1/RMAN/SSPROD/ctl_961288234_dbid196682280.rman’;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ”SSPRODDB” comment= ”Modified by RMAN duplicate” scope=spfile
sql statement: alter system set db_unique_name = ”SSP” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down
Oracle instance started
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1929382088 bytes
Database Buffers 201326592 bytes
Redo Buffers 4947968 bytes

Starting restore at 04-DEC-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=96 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata1/SSP/control01.ctl
output file name=/oradata1/SSP/control02.ctl
Finished restore at 04-DEC-17

database mounted

released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=96 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=127 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=156 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=189 device type=DISK
allocated channel: ORA_AUX_DISK_5
channel ORA_AUX_DISK_5: SID=221 device type=DISK
allocated channel: ORA_AUX_DISK_6
channel ORA_AUX_DISK_6: SID=3 device type=DISK
allocated channel: ORA_AUX_DISK_7
channel ORA_AUX_DISK_7: SID=34 device type=DISK
allocated channel: ORA_AUX_DISK_8
channel ORA_AUX_DISK_8: SID=66 device type=DISK
allocated channel: ORA_AUX_DISK_9
channel ORA_AUX_DISK_9: SID=97 device type=DISK
allocated channel: ORA_AUX_DISK_10
channel ORA_AUX_DISK_10: SID=128 device type=DISK
allocated channel: ORA_AUX_DISK_11
channel ORA_AUX_DISK_11: SID=159 device type=DISK
allocated channel: ORA_AUX_DISK_12
channel ORA_AUX_DISK_12: SID=190 device type=DISK
contents of Memory Script:
{
set until scn 126463226;
set newname for datafile 1 to
“/oradata1/SSP/system.280.916959463”;
set newname for datafile 2 to
“/oradata1/SSP/sysaux.281.916959463”;
set newname for datafile 3 to
“/oradata1/SSP/undotbs1.282.916959463”;
set newname for datafile 4 to
“/oradata1/SSP/users.283.916959463”;
set newname for datafile 5 to
“/oradata1/SSP/undotbs2.291.916959571”;
set newname for datafile 6 to
“/oradata1/SSP/users.294.940601841”;
set newname for datafile 7 to
“/oradata1/SSP/system.289.942830555”;
set newname for datafile 8 to
“/oradata1/SSP/sysaux.288.944822531”;
restore
clone database
;
}

executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting restore at 04-DEC-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5
using channel ORA_AUX_DISK_6
using channel ORA_AUX_DISK_7
using channel ORA_AUX_DISK_8
using channel ORA_AUX_DISK_9
using channel ORA_AUX_DISK_10
using channel ORA_AUX_DISK_11
using channel ORA_AUX_DISK_12

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /oradata1/SSP/system.280.916959463
channel ORA_AUX_DISK_1: reading from backup piece /oradata1/RMAN/SSPROD/full_db_961288205_set8943_piece1_dbid196682280.rman
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00002 to /oradata1/SSP/sysaux.281.916959463
channel ORA_AUX_DISK_2: reading from backup piece /oradata1/RMAN/SSPROD/full_db_961288205_set8942_piece1_dbid196682280.rman
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00003 to /oradata1/SSP/undotbs1.282.916959463
channel ORA_AUX_DISK_3: reading from backup piece /oradata1/RMAN/SSPROD/full_db_961288205_set8946_piece1_dbid196682280.rman
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00004 to /oradata1/SSP/users.283.916959463
channel ORA_AUX_DISK_4: reading from backup piece /oradata1/RMAN/SSPROD/full_db_961288206_set8947_piece1_dbid196682280.rman
channel ORA_AUX_DISK_5: starting datafile backup set restore
channel ORA_AUX_DISK_5: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_5: restoring datafile 00005 to /oradata1/SSP/undotbs2.291.916959571
channel ORA_AUX_DISK_5: reading from backup piece /oradata1/RMAN/SSPROD/full_db_961288206_set8948_piece1_dbid196682280.rman
channel ORA_AUX_DISK_6: starting datafile backup set restore
channel ORA_AUX_DISK_6: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_6: restoring datafile 00006 to /oradata1/SSP/users.294.940601841
channel ORA_AUX_DISK_6: reading from backup piece /oradata1/RMAN/SSPROD/full_db_961288206_set8949_piece1_dbid196682280.rman
channel ORA_AUX_DISK_7: starting datafile backup set restore
channel ORA_AUX_DISK_7: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_7: restoring datafile 00007 to /oradata1/SSP/system.289.942830555
channel ORA_AUX_DISK_7: reading from backup piece /oradata1/RMAN/SSPROD/full_db_961288205_set8944_piece1_dbid196682280.rman
channel ORA_AUX_DISK_8: starting datafile backup set restore
channel ORA_AUX_DISK_8: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_8: restoring datafile 00008 to /oradata1/SSP/sysaux.288.944822531
channel ORA_AUX_DISK_8: reading from backup piece /oradata1/RMAN/SSPROD/full_db_961288205_set8945_piece1_dbid196682280.rman
channel ORA_AUX_DISK_3: piece handle=/oradata1/RMAN/SSPROD/full_db_961288205_set8946_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_5: piece handle=/oradata1/RMAN/SSPROD/full_db_961288206_set8948_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_5: restored backup piece 1
channel ORA_AUX_DISK_5: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_6: piece handle=/oradata1/RMAN/SSPROD/full_db_961288206_set8949_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_6: restored backup piece 1
channel ORA_AUX_DISK_6: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_7: piece handle=/oradata1/RMAN/SSPROD/full_db_961288205_set8944_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_7: restored backup piece 1
channel ORA_AUX_DISK_7: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_8: piece handle=/oradata1/RMAN/SSPROD/full_db_961288205_set8945_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_8: restored backup piece 1
channel ORA_AUX_DISK_8: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_4: piece handle=/oradata1/RMAN/SSPROD/full_db_961288206_set8947_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: piece handle=/oradata1/RMAN/SSPROD/full_db_961288205_set8943_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_2: piece handle=/oradata1/RMAN/SSPROD/full_db_961288205_set8942_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:35

Finished restore at 04-DEC-17

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=961823188 file name=/oradata1/SSP/system.280.916959463
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=961823188 file name=/oradata1/SSP/sysaux.281.916959463
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=961823188 file name=/oradata1/SSP/undotbs1.282.916959463
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=961823188 file name=/oradata1/SSP/users.283.916959463
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=961823188 file name=/oradata1/SSP/undotbs2.291.916959571
datafile 6 switched to datafile copy
input datafile copy RECID=14 STAMP=961823188 file name=/oradata1/SSP/users.294.940601841
datafile 7 switched to datafile copy
input datafile copy RECID=15 STAMP=961823188 file name=/oradata1/SSP/system.289.942830555
datafile 8 switched to datafile copy
input datafile copy RECID=16 STAMP=961823188 file name=/oradata1/SSP/sysaux.288.944822531

contents of Memory Script:
{
set until time “to_date(‘NOV 29 2017 11:26:28’, ‘MON DD YYYY HH24:MI:SS’)”;
recover
clone database
delete archivelog
;
}

executing Memory Script

executing command: SET until clause
Starting recover at 04-DEC-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5
using channel ORA_AUX_DISK_6
using channel ORA_AUX_DISK_7
using channel ORA_AUX_DISK_8
using channel ORA_AUX_DISK_9
using channel ORA_AUX_DISK_10
using channel ORA_AUX_DISK_11
using channel ORA_AUX_DISK_12

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=2278
channel ORA_AUX_DISK_1: reading from backup piece /oradata1/RMAN/SSPROD/arc_961288248_set8959_piece1_dbid196682280.rman
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=2 sequence=1706
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=2279
channel ORA_AUX_DISK_2: reading from backup piece /oradata1/RMAN/SSPROD/arc_961288248_set8960_piece1_dbid196682280.rman
channel ORA_AUX_DISK_3: starting archived log restore to default destination
channel ORA_AUX_DISK_3: restoring archived log
archived log thread=2 sequence=1707
channel ORA_AUX_DISK_3: reading from backup piece /oradata1/RMAN/SSPROD/arc_961288248_set8961_piece1_dbid196682280.rman
channel ORA_AUX_DISK_4: starting archived log restore to default destination
channel ORA_AUX_DISK_4: restoring archived log
archived log thread=1 sequence=2280
channel ORA_AUX_DISK_4: reading from backup piece /oradata1/RMAN/SSPROD/arc_961374649_set8977_piece1_dbid196682280.rman
channel ORA_AUX_DISK_5: starting archived log restore to default destination
channel ORA_AUX_DISK_5: restoring archived log
archived log thread=2 sequence=1708
channel ORA_AUX_DISK_5: restoring archived log
archived log thread=1 sequence=2281
channel ORA_AUX_DISK_5: reading from backup piece /oradata1/RMAN/SSPROD/arc_961374649_set8978_piece1_dbid196682280.rman
channel ORA_AUX_DISK_3: piece handle=/oradata1/RMAN/SSPROD/arc_961288248_set8961_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:00
channel ORA_AUX_DISK_2: piece handle=/oradata1/RMAN/SSPROD/arc_961288248_set8960_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_4: piece handle=/oradata1/RMAN/SSPROD/arc_961374649_set8977_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: piece handle=/oradata1/RMAN/SSPROD/arc_961288248_set8959_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch1_2278_916930730.dbf thread=1 sequence=2278
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch2_1706_916930730.dbf thread=2 sequence=1706
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch1_2278_916930730.dbf RECID=5 STAMP=961823193
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch1_2279_916930730.dbf thread=1 sequence=2279
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch2_1706_916930730.dbf RECID=4 STAMP=961823192
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch2_1707_916930730.dbf thread=2 sequence=1707
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch1_2279_916930730.dbf RECID=2 STAMP=961823189
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch1_2280_916930730.dbf thread=1 sequence=2280
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch2_1707_916930730.dbf RECID=1 STAMP=961823189
channel ORA_AUX_DISK_5: piece handle=/oradata1/RMAN/SSPROD/arc_961374649_set8978_piece1_dbid196682280.rman tag=RMAN_BKP_SSPRODDB
channel ORA_AUX_DISK_5: restored backup piece 1
channel ORA_AUX_DISK_5: restore complete, elapsed time: 00:00:08
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch2_1708_916930730.dbf thread=2 sequence=1708
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch1_2280_916930730.dbf RECID=3 STAMP=961823191
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch1_2281_916930730.dbf thread=1 sequence=2281
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch1_2281_916930730.dbf RECID=6 STAMP=961823196
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/dbhome_1/dbs/arch2_1708_916930730.dbf RECID=7 STAMP=961823196
media recovery complete, elapsed time: 00:00:05
Finished recover at 04-DEC-17
Oracle instance started

Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1929382088 bytes
Database Buffers 201326592 bytes
Redo Buffers 4947968 bytes

contents of Memory Script:
{
sql clone “alter system set db_name =
”SSP” comment=
”Reset to original value by RMAN” scope=spfile”;
sql clone “alter system reset db_unique_name scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}

executing Memory Script

sql statement: alter system set db_name = ”SSP” comment= ”Reset to original value by RMAN” scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1929382088 bytes
Database Buffers 201326592 bytes
Redo Buffers 4947968 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE “SSP” RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/oradata1/SSP/redo01.log’ ) SIZE 50 M ,
GROUP 2 ( ‘/oradata1/SSP/redo02.log’ ) SIZE 50 M ,
GROUP 3 ( ‘/oradata1/SSP/redo03.log’ ) SIZE 50 M
DATAFILE
‘/oradata1/SSP/system.280.916959463’
CHARACTER SET AL32UTF8

contents of Memory Script:

{
set newname for tempfile 1 to
“/oradata1/SSP/temp.290.916959535”;
switch clone tempfile all;
catalog clone datafilecopy “/oradata1/SSP/sysaux.281.916959463”,
“/oradata1/SSP/undotbs1.282.916959463”,
“/oradata1/SSP/users.283.916959463”,
“/oradata1/SSP/undotbs2.291.916959571”,
“/oradata1/SSP/users.294.940601841”,
“/oradata1/SSP/system.289.942830555”,
“/oradata1/SSP/sysaux.288.944822531”;
switch clone datafile all;
}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oradata1/SSP/temp.290.916959535 in control file

cataloged datafile copy
datafile copy file name=/oradata1/SSP/sysaux.281.916959463 RECID=1 STAMP=961823218
cataloged datafile copy
datafile copy file name=/oradata1/SSP/undotbs1.282.916959463 RECID=2 STAMP=961823218
cataloged datafile copy
datafile copy file name=/oradata1/SSP/users.283.916959463 RECID=3 STAMP=961823218
cataloged datafile copy
datafile copy file name=/oradata1/SSP/undotbs2.291.916959571 RECID=4 STAMP=961823218
cataloged datafile copy
datafile copy file name=/oradata1/SSP/users.294.940601841 RECID=5 STAMP=961823218
cataloged datafile copy
datafile copy file name=/oradata1/SSP/system.289.942830555 RECID=6 STAMP=961823218
cataloged datafile copy
datafile copy file name=/oradata1/SSP/sysaux.288.944822531 RECID=7 STAMP=961823218

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=961823218 file name=/oradata1/SSP/sysaux.281.916959463
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=961823218 file name=/oradata1/SSP/undotbs1.282.916959463
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=961823218 file name=/oradata1/SSP/users.283.916959463
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=961823218 file name=/oradata1/SSP/undotbs2.291.916959571
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=961823218 file name=/oradata1/SSP/users.294.940601841
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=961823218 file name=/oradata1/SSP/system.289.942830555
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=961823218 file name=/oradata1/SSP/sysaux.288.944822531

contents of Memory Script:

{
Alter clone database open resetlogs;
}

executing Memory Script

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/04/2017 05:06:59
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

SQL> alter database open resetlogs;
alter database open resetlogs

ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled
Please refer to
RMAN Duplicate from RAC backup fails ORA-38856 (Doc ID 334899.1)

SQL> alter system set “_no_recovery_through_resetlogs”=true scope=spfile;
system altered.

SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1929382088 bytes
Database Buffers 201326592 bytes
Redo Buffers 4947968 bytes
Database mounted.

SQL> alter database open resetlogs;
Database altered.

SQL> select name, open_mode from v$database;
NAME OPEN_MODE
——— ——————–
SSP READ WRITE

FLASHBACK DATABASE AND QUERIES

Posted on November 23, 2017November 23, 2017 by debasis maity

FLASHBACK FEATURES

Purging the Recycle Bin

purge table sales.woodscrews;

purge index sales.woodscrews_pk_idx;

purge tablespace sales;

purge recyclebin;

Undropping Objects in the Recycle Bin

flashback table ws_app.woodscrews to before drop;

select object_name, original_name, droptime, dropscn from user_recyclebin;

flashback table bonus to before drop rename to bonus_deb;

———–Accidentaly Delete——————————————

Accidentally dropped table MHC_PA_INT_PPA_SPN_RL_TBL from MHCPA schema

**********************************************************************

How to recover:

1)Login to schema from where you dropped:

2)SELECT * FROM recyclebin where ORIGINAL_NAME =’MHC_PA_INT_PPA_SPN_RL_TBL’;

3)If you get multiple dates check the drop date.

4)FLASHBACK TABLE “BIN$pLplsuQ1FXDgRAAhKFcB9g==$0” TO BEFORE DROP;

5)FLASHBACK TABLE XXX TO BEFORE DROP RENAME TO OLD_XXX;

Display recyclebin

show recyclebin;

Performing the Flashback Table from SQL

flashback table sales.woodscrew to timestamp
to_timestamp(‘2003-12-01 13:30:00′,’YYYY-MM-DD HH24:MI:SS’);
flashback table sales.woodscrew to scn 751652;

To recover a table to a previous timestamp

******************************************

The following query retrieves the previous state of a table:

SELECT * FROM AR_RECEIPT_METHODS AS OF TIMESTAMP TO_TIMESTAMP(‘2011-09-09 14:30:00’, ‘YYYY-MM-DD HH24:MI:SS’);

SQL> alter session set nls_date_format=’YYYY/MM/DD HH24:MI:SS’;

Session altered.

SQL> select sysdate from dual;

SYSDATE

——————-

2011/08/30 00:43:01

SQL> flashback table MHCAP.MHC_INT_MASTRO_EBS_TBL to timestamp TO_TIMESTAMP(‘2011/08/30 00:20:01′,’YYYY/MM/DD HH24:MI:SS’);

Flashback complete.

select versions_startscn,versions_oper,* from t1 versions between scn 1239229 and 1239236;

select dbms_flashback.get_system_change_number from dual;

Flashback Retention Target

alter system set db_flashback_retention_target=720;

Configure flash back database

1.Database must be in mount

select status from v$instance;

archive log list;

2.Set the flashback retention target to your desired value. We will use 12 hours as the window.

alter system set db_flashback_retention_target=720

SCOPE=BOTH SID=’*’;

3.Set the values for db_recovery_file_dest and db_recovery_file_dest_size (flash recovery area parameters).

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2335825920 SCOPE=BOTH SID=’*’;

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ‘/u02/fra/’ SCOPE=BOTH SID=’*’;

4.Turn flashback logging on

alter database flashback on;

5.Turn flashback logging off for any tablespaces that you deem do not require it.

alter tablespace sales_idx flashback off;

Step 6. Open the database.

alter database open;

Flashback Database: Tuning

select estimated_flashback_size from v$flashback _database_log;

select oldest_flashback_scn, oldest_flashback_time

from v$flashback_database_log;

select * from v$flashback_database_stat;

Flashback example

First, get the current SCN from the database. Because we are simply testing, we can prepare for the test by getting the current SCN prior to putting the fault into the database.

SQL> select current_scn from v$database;

CURRENT_SCN

—————-

885524

Introduce the fault.

SQL> truncate table woodscrew;

Table truncated.

Shut down the database, and then remount. The database must be mounted and not open for flashback.

SQL> connect / as sysdba

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

Issue the FLASHBACK command.

SQL> flashback database to scn 885524;

Flashback complete.

Open the database read-only to confirm that the table has been flashed back to the appropriate SCN.

SQL> alter database open read only;

Database altered.

SQL> connect sales/sales;

Connected.

SQL> select count(*) from woodscrew;

COUNT(*)

———-

Open the database with resetlogs.

SQL> connect / as sysdba

SQL> shutdown immediate;

SQL> startup mount;

####### Recover database flashback technology #########################

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

SELECT CURRENT_SCN FROM V$DATABASE;

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE=’YES’;

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

FLASHBACK DATABASE TO SCN 46963;

FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;

FLASHBACK DATABASE TO TIME “TO_DATE(’09/20/05′,’MM/DD/YY’)”;

ALTER DATABASE OPEN READ ONLY;

SHUTDOWN IMMEDIATE

STARTUP MOUNT

ALTER DATABASE OPEN RESETLOGS;

SET UNTIL TIME ‘Nov 15 2004 09:00:00’;

SET UNTIL SEQUENCE 9923;

SET UNTIL RESTORE POINT before_update;

RESTORE DATABASE;

RECOVER DATABASE;

ALTER DATABASE OPEN RESETLOGS;

RMAN incremental Backup cumulative and differential

Posted on November 19, 2017November 19, 2017 by debasis maity

RMAN Incremental backup

This backups only datafile blocks changed since specified previous backup.

1.This strategy could be followed to make incrementally updated backup .These incrementally updated backup will be used to make updated image copy will all roll forward image.

2.Reduce amount of time to take backup.

3.Save network bandwidth

To be able to recover changes to objects created with the NOLOGGING option. For example, direct load inserts do not create redo log entries and their changes cannot be reproduced with media recovery. They do, however, change data blocks and so are captured by incremental backups.
To reduce backup sizes for NOARCHIVELOG databases. Instead of making a whole database backup every time, you can make incremental backups.

As with full backups, if you are in ARCHIVELOG mode, you can make incremental backups if the database is open; if the database is in NOARCHIVELOG mode, then you can only make incremental backups after a consistent shutdown.

Level 0 and Level 1 Incremental Backups

Level 0 incremental backup will register all copies of block which has data.This is base for incremental backup .

Difference between full and level 0 backup is:

Level 0 backup will involve in incremental backup strategy.

Why cumulative backup are useful than differential

Cumulative backups are preferable to differential backups when recovery time is more important than disk space, because during recovery each differential backup must be applied in succession. Use cumulative incremental backups instead of differential, if enough disk space is available to store cumulative incremental backups.

Differential Incremental Backups

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Sunday

An incremental level 0 backup backs up all blocks that have ever been in use in this database.

Monday – Saturday

On each day from Monday through Saturday, a differential incremental level 1 backup backs up all blocks that have changed since the most recent incremental backup at level 1 or 0. So, the Monday backup copies blocks changed since Sunday level 0 backup, the Tuesday backup copies blocks changed since the Monday level 1 backup, and so forth.

The cycle is repeated for the next week.

Cumulative Incremental Backups

BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; # blocks changed since level 0

In the example shown in the following occurs:

Sunday

An incremental level 0 backup backs up all blocks that have ever been in use in this database.

Monday – Saturday

A cumulative incremental level 1 backup copies all blocks changed since the most recent level 0 backup. Because the most recent level 0 backup was created on Sunday, the level 1 backup on each day Monday through Saturday backs up all blocks changed since the Sunday backup.

The cycle is repeated for the next week.

Basic incremetal Backup strategy

you can implement a three-level backup scheme so that a full or level 0 backup is taken monthly, a cumulative level 1 is taken weekly, and a differential level 1 is taken daily. In this scheme, you never have to apply more than a day’s worth of redo for complete recovery.

Making Incremental Backups: BACKUP INCREMENTAL

After starting RMAN, run the BACKUP INCREMENTAL command at the RMAN prompt. This example makes a level 0 incremental backup of the database:

BACKUP INCREMENTAL LEVEL 0 DATABASE;

This example makes a differential level 1 backup of the SYSTEM tablespace and datafile tools01.dbf. It will only back up those data blocks changed since the most recent level 1 or level 0 backup:

BACKUP INCREMENTAL LEVEL 1 TABLESPACE SYSTEM DATAFILE ‘ora_home/oradata/trgt/tools01.dbf’;

This example makes a cumulative level 1 backup of the tablespace users, backing up all blocks changed since the most recent level 0 backup.

BACKUP INCREMENTAL LEVEL = 1 CUMULATIVE TABLESPACE users;

Enabling and Disabling Change Tracking to improve performance of incremental backup

To store the change tracking file in the database area, set DB_CREATE_FILE_DEST in the target database. Then issue the following SQL statement to enable change tracking:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

You can also create the change tracking file in a location you choose yourself, using the following SQL statement:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/mydir/rman_change_track.f’ REUSE;

The REUSE option tells Oracle to overwrite any existing file with the specified name.

To disable change tracking, use this SQL statement:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

If the change tracking file was stored in the database area, then it is deleted when you disable change tracking.

Checking Whether Change Tracking is Enabled

From SQL*Plus, you can query V$BLOCK_CHANGE_TRACKING.STATUS to determine whether change tracking is enabled, and if it is, query V$BLOCK_CHANGE_TRACKING.FILENAME to display the filename.

Different restore and recovery scenario for PDB and CDB in Oracle 12c

Posted on October 25, 2017April 11, 2018 by debasis maity

Introduction

The next document will describe the steps to restore scenario for CDB and PDB using Data protector and RMAN

1.New SYSBACKUP privilege in RMAN 12c

1.1 create common user in CDB with sysbackup privilege.

[oracle@rac1 ~]$ export ORACLE_SID=orcl1
 [oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 9 19:56:36 2017

SQL> create user c##dba_backup identified by test123;

User created.

SQL> grant sysbackup to c##dba_backup;

Grant succeeded.

1.2 Backup whole database using sysbackup privilege

[oracle@rac1 ~]$ rman target '"c##dba_backup@orcl as sysbackup"'

Recovery Manager: Release 12.1.0.2.0 – Production on Sat Dec 9 19:58:38 2017

target database Password:
connected to target database: ORCL (DBID=1489144156)

RMAN> backup database;

Starting backup at 09-DEC-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=105 instance=orcl1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/ORCL/DATAFILE/system.278.961363313
input datafile file number=00003 name=+DATA/ORCL/DATAFILE/sysaux.277.961363267
input datafile file number=00004 name=+DATA/ORCL/DATAFILE/undotbs1.280.961363369
input datafile file number=00008 name=+DATA/ORCL/DATAFILE/undotbs2.291.961364193
input datafile file number=00006 name=+DATA/ORCL/DATAFILE/users.279.961363369
channel ORA_DISK_1: starting piece 1 at 09-DEC-17

2.Backup of CDB and PDB

—CDB backup–

export ORACLE_SID=ORCL1

[oracle@rac1 ~]$ rman target /

RMAN> backup database plus archivelog;

—CDB root backup—

RMAN> backup pluggable database "CDB$ROOT";

–Backup pluggable database—

RMAN>backup pluggable database oem;

RMAN>backup pluggable database oem plus archivelog;

3.Active database duplicate database and other new RMAN 12c feature

Active RMAN duplicate clone 12c using section size and compress backupset

4.Lost of temp file scenario for CDB and PDB

When you create a user, you can specify a temporary tablespace to be used by the user. If a temporary tablespace is not specified, the default tablespace for the PDB is used. If a default tablespace is not specified for the PDB, the temporary tablespace for the CDB is used.
If a temp file belonging to the CDB temporary tablespace is lost or damaged, and the user issuing the statement uses it, an error during the execution of SQL statements that require this temporary space occurs.

SQL> select * from dba_source order by 1;

274137 rows selected.

Execution Plan ———————————————————-

ERROR: ORA-00604: error occurred at recursive SQL level 1

ORA-01116: error in opening database file 201 ORA-01110: data file 201: ‘/u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_temp_do2ljro0_.tmp’

ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3

4.1 Create a temp file in CDB

SQL> alter tablespace temp add tempfile;

Tablespace altered.

NB: Temporary datafile will be automatically re-created once DB is started.

4.2 Temporary tablespace lost for PDB

[oracle@XXX ~]$ sqlplus system/oracle@ORA12CPD1

SQL> set autotrace traceonly;
SQL> select * from dba_source order by 1;

285357 rows selected.

Execution Plan
———————————————————-
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 203
ORA-01110: data file 203:
‘/u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf
_temp_do2lvkg7_.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

4.3 Add tempfile to temp tablespace in PDB and then drop corrupted temp file

SQL> alter tablespace temp add tempfile;

Tablespace altered.

SQL> alter tablespace temp drop tempfile ‘/u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_temp_do2lvkg7_.dbf’;

Tablespace altered.

SQL> select * from dba_source order by 1;

285357 rows selected.

NB: The PDB can open with missing temp files. If any of the temporary files do not exist when the PDB is opened , they are not created automatically. They are automatically recreated at CDB startup.

5. Lost of Control file in CDB (PDB does not contain control file)

Failure Scenario

[oracle@xxx ~]$ rm /u01/app/oracle/oradata/ORA12CP/controlfile/o1_mf_do2ljh6z_.ctl
[oracle@xxx ~]$ sqlplus / as sysdba

SQL> select count(1) from dba_objects;

COUNT(1)
———-
72648

SQL> create table t1(c1 char(100));

Table created.

SQL> insert into t1 select 'A' from dual connect by rownum<100000;
 insert into t1 select 'A' from dual connect by rownum<100000
 *
 ERROR at line 1:
 ORA-00210: cannot open the specified control file
 ORA-00202: control file:
 '/u01/app/oracle/oradata/ORA12CP/controlfile/o1_mf_do2ljh6z_.ctl'
 ORA-27041: unable to open file
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3

SQL> select count(1) from dba_objects;

COUNT(1)
———-
72649

5.1 Recover the CONTROLFILE now (The database was previously restored to old incarnation)

[oracle@XXX ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 15:02:13 2017

connected to target database: ORA12CP (DBID=1429060559, not open)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 ORA12CP 1429060559 PARENT 1 26-JAN-17
2 2 ORA12CP 1429060559 PARENT 1408558 26-JUN-17
3 3 ORA12CP 1429060559 CURRENT 1499533 26-JUN-17

RMAN> reset database to incarnation 2;

database reset to incarnation 2

[oracle@XXX ~]$ rman target / catalog rco/rco@DBADB

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 14:58:14 2017

connected to target database: ORA12CP (DBID=1429060559, not open)
connected to recovery catalog database

RMAN> run {
 2> allocate channel 'dev_0' type 'sbt_tape'
 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> recover database;
 5> alter database open resetlogs;
 6> }

allocated channel: dev_0
channel dev_0: SID=34 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting recover at 27-JUN-17

starting media recovery
media recovery failed
released channel: dev_0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/27/2017 15:04:16
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover
if needed start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_system_do2lf6n8_.dbf’

You need to restore and recover as you reset incarnation

RMAN> run {
 2> allocate channel 'dev_0' type 'sbt_tape'
 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> restore database;
 5> recover database;
 6> alter database open resetlogs;
 7> }

allocated channel: dev_0
channel dev_0: SID=42 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

skipping datafile 5; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_system_do2ljv4y_.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_sysaux_do2ljv4q_.dbf
skipping datafile 8; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_undotbs1_do2ljv4z_.dbf
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00001 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_system_do2lf6n8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_2:947689345:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_2:947689345:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_sysaux_do2lgmrx_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_3:947689360:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_3:947689360:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00010 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_sysaux_do2lvkg6_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_4:947689367:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_4:947689367:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00014 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_sysaux_do2lvqd8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_5:947689374:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_5:947689374:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00009 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_8:947689396:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_8:947689396:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00013 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_system_do2lvqd7_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_9:947689403:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_9:947689403:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00011 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_undotbs1_do2lvkg7_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_11:947689413:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_11:947689413:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00015 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_undotbs1_do2lvqd8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_12:947689416:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_12:947689416:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_undotbs1_do2lhdxt_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_13:947689419:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_13:947689419:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00007 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_users_do2lhg0j_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_14:947689422:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_14:947689422:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00012 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_users_do2lvpy8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_15:947689429:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_15:947689429:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00016 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_users_do2lvxsx_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_16:947689433:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_16:947689433:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery
media recovery failed
released channel: dev_0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/27/2017 15:13:38
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover
if needed start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-19912: cannot recover to target incarnation 2

[oracle@xxx ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 15:16:04 2017

connected to target database: ORA12CP (DBID=1429060559, not open)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 ORA12CP 1429060559 PARENT 1 26-JAN-17
2 2 ORA12CP 1429060559 CURRENT 1408558 26-JUN-17
3 3 ORA12CP 1429060559 ORPHAN 1499533 26-JUN-17

RMAN> reset database to incarnation 3;

RMAN> run {
 2> allocate channel 'dev_0' type 'sbt_tape'
 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> recover database;
 5> alter database open resetlogs;
 6> }

allocated channel: dev_0
channel dev_0: SID=34 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do3p7ltn_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/onlinelog/o1_mf_2_do3fzo1m_.log
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cv24b_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cv24b_.arc RECID=26 STAMP=947776675
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5cwg82_.arc thread=1 sequence=3
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5cwg82_.arc RECID=27 STAMP=947776718
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do3p7ltn_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/onlinelog/o1_mf_2_do3fzo1m_.log thread=1 sequence=2
media recovery complete, elapsed time: 00:00:25
Finished recover at 27-JUN-17

Statement processed
released channel: dev_0

5.2 Restore database control file current incarnation

[oracle@cdv1pradmdbv01 ~]$ rm /u01/app/oracle/oradata/ORA12CP/controlfile/o1_mf_do2ljh6z_.ctl
[oracle@cdv1pradmdbv01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 27 15:26:11 2017

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

SQL> shutdown abort;
 ORACLE instance shut down.

SQL> startup nomount;
 ORACLE instance started.

Total System Global Area 1828716544 bytes
Fixed Size 8793928 bytes
Variable Size 553648312 bytes
Database Buffers 1258291200 bytes
Redo Buffers 7983104 bytes

[oracle@cdv1pradmdbv01 ~]$ rman target /

connected to target database: ORA12CP (not mounted)

RMAN> restore controlfile from autobackup;

Starting restore at 27-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

recovery area destination: /u01/app/oracle/fast_recovery_area/ORA12cP
database name (or database unique name) used for search: ORA12CP
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/autobackup/2017_06_27/o1_mf_s_947776764_do5cxx44_.bkp found in the recovery area
AUTOBACKUP search with format “%F” not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/autobackup/2017_06_27/o1_mf_s_947776764_do5cxx44_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/ORA12CP/controlfile/o1_mf_do2ljh6z_.ctl
output file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/controlfile/o1_mf_do2ljh8f_.ctl
Finished restore at 27-JUN-17

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN> run {
 allocate channel 'dev_0' type 'sbt_tape'
 parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 recover database;
 }2> 3> 4> 5>

allocated channel: dev_0
channel dev_0: SID=35 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/onlinelog/o1_mf_1_do3fznnn_.log
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/onlinelog/o1_mf_1_do3fznnn_.log thread=1 sequence=1
media recovery complete, elapsed time: 00:00:00
Finished recover at 27-JUN-17
released channel: dev_0

RMAN> alter database open RESETLOGS;

Statement processed

RMAN> list incarnation;

RMAN> alter pluggable database all open;

Statement processed

6.RESTORE TABLESPACE SCENERIO (CDB)

If the missing or corrupted data file belongs to the root container SYSTEM or UNDO tablespace, then the CDB instance will require shutdown, and a media recovery is required. In a RAC environment, you would shut down all instances of the CDB.
This means that all PDBs will be closed.
The CDB must be mounted before restoring and recovering the missing root data file.
After the root data file is recovered, open the CDB and all PDBs.

6.1 RESTORE SYSTEM AND UNDO TABLESPACE FOR ROOT(CDB)

[oracle@cdv1pradmdbv01 ~]$ sqlplus / as sysdba

SQL> shutdown immediate;

ORACLE instance shut down.
 SQL> startup mount;

ORACLE instance started.

Total System Global Area 1828716544 bytes
Fixed Size 8793928 bytes
Variable Size 553648312 bytes
Database Buffers 1258291200 bytes
Redo Buffers 7983104 bytes
Database mounted.

[oracle@cdv1pradmdbv01 ~]$ rman target / catalog rco/rco@DBADB

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 15:37:49 2017

connected to target database: ORA12CP (DBID=1429060559, not open)
connected to recovery catalog database

RMAN> run {
 allocate channel 'dev_0' type 'sbt_tape'
 2> 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> restore tablespace 'UNDOTBS1';
 5> recover tablespace 'UNDOTBS1';
 6> }

allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_undotbs1_do2lhdxt_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_13:947689419:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_13:947689419:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5f37t5_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5f37t5_.arc RECID=37 STAMP=947777960
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5f4nw9_.arc thread=1 sequence=3
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5f4nw9_.arc RECID=38 STAMP=947778004
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-JUN-17
released channel: dev_0

RMAN> alter database open;

Statement processed
starting full resync of recovery catalog
full resync complete

RMAN> alter pluggable database all open;

Statement processed

NB: If the missing or corrupted file belongs to root container SYSTEM or UNDO tablespace ,then CDB instance needs to shutdown and media recover required. All PDBs will be shutdown also.
After root datafile recovered, CDB and PDB could be opened.

6.2 RESTORE SYSAUX TABLESPACE FOR ROOT IN CDB

There is no need to stop CDB and PDB.This recovery can be done when database is online

[oracle@cdv1pradmdbv01 ~]$ rman target / catalog rco/rco@DBADB

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 16:10:13 2017

connected to target database: ORA12CP (DBID=1429060559)
connected to recovery catalog database

RMAN> run {
 allocate channel 'dev_0' type 'sbt_tape'
 2> 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> restore tablespace 'SYSAUX';
 5> recover tablespace 'SYSAUX';
 6> alter tablespace SYSAUX online;
 7> }

allocated channel: dev_0
channel dev_0: SID=58 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_sysaux_do2lgmrx_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_3:947689360:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_3:947689360:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:46
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5h134y_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5h134y_.arc RECID=42 STAMP=947779940
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5h2hq4_.arc thread=1 sequence=3
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5h2hq4_.arc RECID=43 STAMP=947779983
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-JUN-17
starting full resync of recovery catalog
full resync complete

Statement processed
released channel: dev_0

7.RESTORE TABLESPACE SCENERIO (PDB)

7.1 Restore missing system tablespace in PDB

If the data file missing or corrupted belongs to a PDB and more specifically to the SYSTEM tablespace, the CDB must be closed unless the PDB is already closed.
A pluggable database or tablespace or data file media recovery is required before the PDB can be reopened.
If the PDB was closed at the time issue, the users can still work in other PDBs during the PDB recovery.
If the PDB was still opened at the time issue, users cannot work at all in any other PDB because the CDB needs to be shut down and mounted only.
The recovery must be issued from root.

Option 1:- Recover whole PDB database .CDB needs to be in Mount state.

[oracle@cdv1pradmdbv01 ~]$ sqlplus

Enter user-name: / as sysdba

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.

SQL> startup mount;
 ORACLE instance started.

Total System Global Area 1828716544 bytes
Fixed Size 8793928 bytes
Variable Size 553648312 bytes
Database Buffers 1258291200 bytes
Redo Buffers 7983104 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

[oracle@cdv1pradmdbv01 ~]$ rman target /

connected to target database: ORA12CP (DBID=1429060559, not open)

RMAN> run {
 allocate channel 'dev_0' type 'sbt_tape'
 2> 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> restore pluggable database ORA12CPD1;
 5> recover pluggable database ORA12CPD1;
 6> alter database open;
 7> alter pluggable database ORA12CPD1 open;
 8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00010 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_sysaux_do2lvkg6_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_4:947689367:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_4:947689367:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00009 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_8:947689396:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_8:947689396:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00011 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_undotbs1_do2lvkg7_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_11:947689413:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_11:947689413:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00012 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_users_do2lvpy8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_15:947689429:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_15:947689429:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5l1gjw_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5l1gjw_.arc RECID=44 STAMP=947783023
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5l2t4x_.arc thread=1 sequence=3
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5l2t4x_.arc RECID=45 STAMP=947783066
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:06
Finished recover at 27-JUN-17

RMAN> alter database open;

Statement processed

RMAN> alter pluggable database ORA12CPD1 open;

Option2: Only recover the corrupted system tablespace. CDB needs to be in mount state.

[oracle@cdv1pradmdbv01 ~]$ rm /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf
[oracle@cdv1pradmdbv01 ~]$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 27 17:09:48 2017

Enter user-name: / as sysdba

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

SQL> shutdown immediate;
 ORA-01116: error in opening database file 9
 ORA-01110: data file 9: '/u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf'
 ORA-27041: unable to open file
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1828716544 bytes
Fixed Size 8793928 bytes
Variable Size 553648312 bytes
Database Buffers 1258291200 bytes
Redo Buffers 7983104 bytes
Database mounted.

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 17:11:18 2017

connected to target database: ORA12CP (DBID=1429060559, not open)

RMAN> run {
 2> allocate channel 'dev_0' type 'sbt_tape'
 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> restore tablespace ORA12CPD1:system;
 5> recover tablespace ORA12CPD1:system;
 6> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00009 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_8:947689396:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_8:947689396:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5llvyw_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5llvyw_.arc RECID=46 STAMP=947783580
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5lnb83_.arc thread=1 sequence=3
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5lnb83_.arc RECID=47 STAMP=947783626
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:04
Finished recover at 27-JUN-17
released channel: dev_0

database dismounted
Oracle instance shut down

RMAN> shutdown immediate;

RMAN> startup;

connected to target database (not started)
Oracle instance started
database mounted
database opened

8.Point in time recovery of pluggable database (PDB)

If you need to recover a PDB database to a point in time in the past beyond flashback retention, then in this case, flashback is not possible, therefore a point-in-time recovery is necessary.
Recovering a PDB to a point-in-time does not affect all parts of the CDB: the whole CDB is still opened and therefore all other PDBs are opened. After recovering a PDB to a specified point-in-time, when you open the PDB using the RESETLOGS option, a new incarnation of the PDB is created. The PDB RESETLOGS does not perform a RESETLOGS for the CDB.

• A PDB record in the control file is updated.
• Each redo log record carries PDB id in the redo header. This is how recovery knows

which redo applies to which PDB. Redo logs are shared by all PDBs; redo from each PDB is written to a single set of redo logs.
Conceptually a PDB resetlogs is similar to a database resetlogs.
After recovery, the old backup of the PDB remains valid and can be used if a media failure occurs. After restoring/recovering a PDB to a past point in time, one cannot open the PDB read only. PDB read-write open through resetlogs is required.
A PDB incarnation is a subincarnation of the CDB. For example, if the CDB is incarnation 5,and a PDB is incarnation 3, then the fully specified incarnation number of the PDB is (5, 3). The nitial incarnation of a PDB is 0. To view the incarnation of a PDB, query the $PDB_INCARNATION view.

CDB and other PDB remain un affected.

RMAN> run {
 set until time = "TO_DATE('06/27/2017 17:34:00','MM/DD/YYYY HH24:MI:SS')" ;
 2> 3> allocate channel 'dev_0' type 'sbt_tape'
 4> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 5> restore pluggable database ORA12CPD1;
 6> recover pluggable database ORA12CPD1 auxiliary destination '/u01/app/oracle/oradata';
 7> alter pluggable database ORA12CPD1 open resetlogs;
 8> }

executing command: SET until clause

allocated channel: dev_0
channel dev_0: SID=8 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

skipping datafile 9; already restored to file /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do5lkhfq_.dbf
skipping datafile 10; already restored to file /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_sysaux_do2lvkg6_.dbf
skipping datafile 11; already restored to file /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_undotbs1_do2lvkg7_.dbf
skipping datafile 12; already restored to file /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_users_do2lvpy8_.dbf
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5ndy7b_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5nns56_.arc RECID=50 STAMP=947785690
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5np4dk_.arc RECID=51 STAMP=947785732
media recovery complete, elapsed time: 00:00:06
Finished recover at 27-JUN-17

Statement processed
released channel: dev_0

Restore Database with PDB PITR Oracle 12c

Posted on October 25, 2017April 11, 2018 by debasis maity

1 Introduction

The next document will describe the steps to restore database with CDB and PDB point in time using Data protector and RMAN.

2. Restore Database with PDB PITR using command prompt (Best/Preferred way)

[oracle@orclbkp admin]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Mon Jun 26 21:32:29 2017

connected to target database: ORA12CP (DBID=1429060559, not open)

RMAN> run {
 allocate channel 'dev_0' type 'sbt_tape'
 2> 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=orclbkp.tdeprdcl.internal)';
 4> set until time "to_date('06/26/17 21:07:41','MM/DD/YY HH24:MI:SS')";
 restore database;
 5> 6> recover database;
 alter database open resetlogs;
 7> 8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=40 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 26-JUN-17

skipping datafile 5; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_system_do2ljv4y_.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_sysaux_do2ljv4q_.dbf
skipping datafile 8; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_undotbs1_do2ljv4z_.dbf
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00001 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_system_do2lf6n8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_2:947689345:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_2:947689345:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:46
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_sysaux_do2lgmrx_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_3:947689360:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_3:947689360:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00010 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_sysaux_do2lvkg6_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_4:947689367:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_4:947689367:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00014 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_sysaux_do2lvqd8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_5:947689374:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_5:947689374:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00009 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_8:947689396:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_8:947689396:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00013 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_system_do2lvqd7_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_9:947689403:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_9:947689403:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00011 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_undotbs1_do2lvkg7_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_11:947689413:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_11:947689413:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00015 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_undotbs1_do2lvqd8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_12:947689416:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_12:947689416:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_undotbs1_do2lhdxt_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_13:947689419:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_13:947689419:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00007 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_users_do2lhg0j_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_14:947689422:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_14:947689422:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00012 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_users_do2lvpy8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_15:947689429:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_15:947689429:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00016 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_users_do2lvxsx_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_16:947689433:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_16:947689433:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
Finished restore at 26-JUN-17

Starting recover at 26-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_2_do3fxw63_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_2_do3fxw63_.arc RECID=4 STAMP=947713277
media recovery complete, elapsed time: 00:00:10
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_3_do3fzm9t_.arc RECID=5 STAMP=947713331
Finished recover at 26-JUN-17

Statement processed
released channel: dev_0

Database point in time recovery using Data protector and RMAN

Posted on October 25, 2017November 2, 2017 by debasis maity

1 Introduction

The next document will describe the steps to restore database point in time using Data protector and RMAN

2. Restore using data protector console

2.1. Restore screen shot

3. Restore Database PITR using command prompt (Best/Preferred way)

3.1 Restore database 1st incarnation (No need to reset incarnation)

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Jun 23 17:38:28 2017

connected to target database: BKPTST (not mounted)

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/23/17 16:41:54′,’MM/DD/YY HH24:MI:SS’)”;
5> restore database;
6> recover database;
7> alter database open resetlogs;
8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=1 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17
released channel: dev_0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/23/2017 17:38:32
ORA-01507: database not mounted

RMAN> alter database mount;

Statement processed

allocated channel: dev_0
channel dev_0: SID=36 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17

creating datafile file number=5 name=/u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_dntyvk81_.dbf
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00001 to /u01/app/oracle/oradata/BKPTST/system01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_15:947418586:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_15:947418586:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/BKPTST/sysaux01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_16:947418601:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_16:947418601:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/BKPTST/undotbs01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_17:947418608:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_17:947418608:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00007 to /u01/app/oracle/oradata/BKPTST/users01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_18:947418611:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_18:947418611:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
Finished restore at 23-JUN-17

Starting recover at 23-JUN-17

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 23-JUN-17

Statement processed
released channel: dev_0

3.2 Now we need to restore database older incarnation than above (For example above restoration happened till 16.41.54 PM now this restoration will happen till 16.41.04)

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/23/17 16:41:04′,’MM/DD/YY HH24:MI:SS’)”;
restore database;
5> 6> recover database;
7> alter database open resetlogs;
8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

You will get the error as we have already have new incarnation in database. So we need to reset the incarnation.

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Jun 23 17:48:01 2017

connected to target database: BKPTST (DBID=2013020542, not open)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 BKPTST 2013020542 PARENT 1 26-JAN-17
2 2 BKPTST 2013020542 PARENT 1408558 22-JUN-17
3 3 BKPTST 2013020542 CURRENT 1511521 23-JUN-17

RMAN> reset database to incarnation 2;

database reset to incarnation 2

RMAN> exit

Restoration will now work fine

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Jun 23 17:49:33 2017

connected to target database: BKPTST (DBID=2013020542, not open)

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/23/17 16:41:04′,’MM/DD/YY HH24:MI:SS’)”;
5> restore database;
6> recover database;
7> alter database open resetlogs;
}8>

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=1 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17

Starting recover at 23-JUN-17

starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947344385.dbf
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=6
channel dev_0: reading from backup piece bkptst_new<BKPTST_22:947418639:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_22:947418639:1>.dbf tag=TAG20170623T115024
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947344385.dbf thread=1 sequence=6
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=7
channel dev_0: reading from backup piece bkptst_new<BKPTST_26:947418651:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_26:947418651:1>.dbf tag=TAG20170623T115024
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947344385.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947344385.dbf thread=1 sequence=8
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-JUN-17

Statement processed
released channel: dev_0

3.3 Now we need to restore database again to 3.1 incarnation (For example above restoration happened till 16.41.04 PM now this restoration will happen till 16.41.54 again)

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Jun 23 18:56:47 2017

connected to target database: BKPTST (DBID=2013020542, not open)

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

You will get the error as we have already have new incarnation in database. So we need to reset the incarnation.

RMAN> list incarnation;

RMAN> reset database to incarnation 2;

database reset to incarnation 2

Now restore again

allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17

Starting recover at 23-JUN-17

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947344385.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947344385.dbf
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947344385.dbf
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947344385.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947344385.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947344385.dbf thread=1 sequence=8
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-JUN-17

Statement processed
released channel: dev_0

NB: If you need to restore backup older than (15 days or control file keep record days) ,then you need to connect to recovery catalog as well.

4 . Restore Database with full, incremental and archive log using command prompt (Best/Preferred way)

Restore Database

All full backup will be restored

Recover Database

All incremental backup will be restored

All archive log backup will be restored

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Sun Jun 25 19:21:28 2017

connected to target database: BKPTST (DBID=2013020542, not open)

RMAN> run {
allocate channel ‘dev_0’ type ‘sbt_tape’
2> 3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/25/17 10:41:54′,’MM/DD/YY HH24:MI:SS’)”;
5> restore database;
recover database;
6> 7> alter database open resetlogs;
8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 25-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00001 to /u01/app/oracle/oradata/BKPTST/system01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_15:947418586:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_15:947418586:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/BKPTST/sysaux01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_16:947418601:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_16:947418601:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/BKPTST/undotbs01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_17:947418608:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_17:947418608:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00005 to /u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_dntyvk81_.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_44:947503937:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_44:947503937:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00007 to /u01/app/oracle/oradata/BKPTST/users01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_18:947418611:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_18:947418611:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 25-JUN-17

Starting recover at 25-JUN-17
channel dev_0: starting incremental datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/BKPTST/system01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_42:947503915:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_42:947503915:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting incremental datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /u01/app/oracle/oradata/BKPTST/sysaux01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_43:947503930:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_43:947503930:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:46
channel dev_0: starting incremental datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u01/app/oracle/oradata/BKPTST/undotbs01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_45:947503941:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_45:947503941:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting incremental datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
destination for restore of datafile 00007: /u01/app/oracle/oradata/BKPTST/users01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_46:947503948:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_46:947503948:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:03

starting media recovery

channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=4
channel dev_0: reading from backup piece bkptst_new<BKPTST_49:947503970:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_49:947503970:1>.dbf tag=TAG20170624T113242
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf thread=1 sequence=4
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=5
channel dev_0: reading from backup piece bkptst_new<BKPTST_50:947503977:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_50:947503977:1>.dbf tag=TAG20170624T113242
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf thread=1 sequence=5
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=6
channel dev_0: restoring archived log
archived log thread=1 sequence=7
channel dev_0: reading from backup piece New3<BKPTST_53:947531995:1>.dbf
channel dev_0: piece handle=New3<BKPTST_53:947531995:1>.dbf tag=TAG20170624T191955
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf thread=1 sequence=7
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=8
channel dev_0: reading from backup piece bkptst_new<BKPTST_61:947590300:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_61:947590300:1>.dbf tag=TAG20170625T113140
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf thread=1 sequence=8
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=9
channel dev_0: reading from backup piece bkptst_new<BKPTST_62:947590308:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_62:947590308:1>.dbf tag=TAG20170625T113140
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf thread=1 sequence=9
media recovery complete, elapsed time: 00:00:06
Finished recover at 25-JUN-17

Statement processed
released channel: dev_0

My on-premise database hostname:-gnssrv01

Database name:-orcl12c

Cloud database hostname:-myclone

A.Backup of on-premise database to cloud container

Please create the empty container for holding backup of on-premise database using following link

1.First I need to install backup module for cloud in on-premise database

You need to download opc.cert and opc_install.jar mentioned in above metalink note (Doc ID 2360941.1)

Please note your default container is oracle-data-storage6-1.

2.Please encrypt all tablespace (Including PDB tablespaces) using below note.

3. Taking backup from on-premises to oracle cloud now.Please note that backup configuration file was created as a part of step 1.

RMAN script to execute:-

4.Please note control file backup taken now which will be required to restore in cloud database.

5.Please note list of archive logs in backup.These archive logs needs to be restored later .

B.Restoration of on-premise database backup to cloud Machine

1.Please copy the TDE wallet generated in on-premise in previous step to oracle cloud TDE wallet (/u01/app/oracle/admin/orcl12c/tde_wallet).This database will be cloned from existing on-premise backup stored in cloud container.

Please restart the database and check wallet status

2.Please copy opc wallet from on-premise database which we created using step 1 procedure (earlier part of this note) of this note.You can also create a new OPC file as well using step 1 procedure.

3.Please make a note or modify the opc configuration file.Please note I have explicitly mentioned OPC_CONTAINER if it is not able to identify from previous process.

4.Please restore the controlfile now.Please use the control file tag we identified during backup.

5.Now we will restore the database.

6.Please restore the archive logs from backup

7.Recover the database now

7. Please open the database in resetlogs

Overview of New PULL method

Both TNS connections are required on target and auxiliary instances.Based on the DUPLICATE clauses, RMAN dynamically determines which process to use (push or pull’. This ensures that existing customized scripts continue to function.

When you specify USING BACKUPSET, RMAN uses the pull method.

When you specify SET ENCRYPTION before the DUPLICATE command, RMAN

automatically uses the pull method and creates backup sets. The backups sent to the destination are encrypted.

The SECTION SIZE clause divides data files into subsections that are restored in parallel across multiple channels on the auxiliary database. For an effective use of parallelization, allocate more AUXILIARY channels.

With the USING COMPRESSED BACKUPSET clause, the files are transferred as compressed backup sets. RMAN uses unused block compression while creating backups,thus reducing the size of backups that are transported over the network.

NOOPEN

Multi-section now is available on image copy.

Active duplication step

Create init parameter file from source and change relevant parameters like control_file,db_name etc.I have provided below sample of init.ora.

Create required audit directory in target

Add entry as static listener.ora under $GRID_HOME/network/admin

Add corresponding entry in tnsnames.ora

If UR=A is not added,you will get following error during connection to target in nomount state in next steps:-

Create password file

Now start target database in nomount

Please verify password file is working as expected in target

Register in listener

Now connect to target and auxiliary and start duplicate using new 12c parameters

Create password file in ASM now

Create spfile.

Now add database in srvctl

Issue faced:-

Please remove the database from srvctl if already added before duplicate

##Connecting RMAN##############

rman TARGET SYS/target_pwd@target_str # connects in NOCATALOG mode rman TARGET / CATALOG rman/rman@rcat rman TARGET / CATALOG rman/rman@rcat AUXILIARY sys/aux_pwd@aux_str

##Create user and catalog in RMAN database ########

CREATE USER rman_dba IDENTIFIED BY rman_dba TEMPORARY TABLESPACE temp DEFAULT TABLESPACE rman_dba QUOTA UNLIMITED ON rman_dba; GRANT RECOVERY_CATALOG_OWNER TO rman_dba; CREATE CATALOG;

## Register Database######

Rman target / catalog rman_dba/rman_dba@<catalog>

register database; select * from rc_database;

### Catalog copy in RMAN Catalog###

### Configure RMAN ######

CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR; CONFIGURE RETENTION POLICY CLEAR; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

###RMAN backup check view######

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR / TOTALWORK * 100, 2) “% COMPLETE” FROM V$SESSION_LONGOPS WHERE OPNAME LIKE ‘RMAN%’ AND OPNAME NOT LIKE ‘%aggregate%’ AND TOTALWORK != 0 AND SOFAR <> TOTALWORK ;

COL in_sec FORMAT a10 COL out_sec FORMAT a10 COL TIME_TAKEN_DISPLAY FORMAT a10 SELECT SESSION_KEY, OPTIMIZED, COMPRESSION_RATIO, INPUT_BYTES_PER_SEC_DISPLAY in_sec, OUTPUT_BYTES_PER_SEC_DISPLAY out_sec, TIME_TAKEN_DISPLAY FROM V$RMAN_BACKUP_JOB_DETAILS ORDER BY SESSION_KEY;

SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME FROM V$DATAFILE_HEADER WHERE RECOVER = ‘YES’ OR (RECOVER IS NULL AND ERROR IS NOT NULL);

SELECT FILE#, INCREMENTAL_LEVEL, COMPLETION_TIME, BLOCKS, DATAFILE_BLOCKS FROM V$BACKUP_DATAFILE WHERE INCREMENTAL_LEVEL > 0 AND BLOCKS / DATAFILE_BLOCKS > .2 ORDER BY COMPLETION_TIME;

SELECT * FROM V$RECOVERY_FILE_DEST; SELECT * FROM V$RECOVERY_AREA_USAGE; SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

### List and Report of backup ####

REPORT OBSOLETE; REPORT SCHEMA; REPORT NEED BACKUP REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE DEVICE TYPE sbt; REPORT NEED BACKUP DEVICE TYPE DISK; REPORT NEED BACKUP TABLESPACE TBS_3 DEVICE TYPE sbt; REPORT OBSOLETE RECOVERY WINDOW OF 3 DAYS; REPORT OBSOLETE REDUNDANCY 1;

## Crosscheck backup #####

### Delete backup ########

DELETE BACKUPPIECE 101; DELETE CONTROLFILECOPY ‘/tmp/control01.ctl’; DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE 300; DELETE BACKUP TAG ‘before_upgrade’; DELETE ARCHIVELOG ALL BACKED UP 3 TIMES TO sbt; DELETE EXPIRED BACKUP; DELETE OBSOLETE;

###Simple unix script #############

#!/bin/tcsh # name: runbackup.sh # usage: use the tag name and number of copies as arguments set media_family = $argv[1] set format = $argv[2] set restore_point = $argv[3] rman @’/disk1/scripts/whole_db.cmd’ USING $media_family $format $restore_point

% runbackup.sh archival_backup bck0906 FY06Q3

##Backup Database command #####

RUN { ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT ‘/disk1/%d_backups/%U’; ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT ‘/disk2/%d_backups/%U’; ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT ‘/disk3/%d_backups/%U’; BACKUP AS COPY DATABASE; }

## Incremental updated backup #######

recover copy of database with tag ‘tcstest3’; backup incremental level 1 tag ‘tcstest3’ for recover of copy with tag ‘tcstest3’ database ;

## Block change tracking ########

ALTER SYSTEM SET DB_CREATE_FILE_DEST = ‘/disk1/bct/’ SCOPE=BOTH SID=’*’;

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/mydir/rman_change_track.f’ REUSE;

rman TARGET SYS/target_pwd@target_str # connects in NOCATALOG mode
rman TARGET / CATALOG rman/rman@rcat
rman TARGET / CATALOG rman/rman@rcat AUXILIARY sys/aux_pwd@aux_str

CREATE USER rman_dba IDENTIFIED BY rman_dba TEMPORARY TABLESPACE temp DEFAULT TABLESPACE rman_dba QUOTA UNLIMITED ON rman_dba;
GRANT RECOVERY_CATALOG_OWNER TO rman_dba;
CREATE CATALOG;

register database;
select * from rc_database;

CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR;
CONFIGURE RETENTION POLICY CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

SELECT SID,
SERIAL#,
CONTEXT,
SOFAR,
TOTALWORK,
ROUND(SOFAR / TOTALWORK * 100, 2) “% COMPLETE”
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE ‘RMAN%’ AND OPNAME NOT LIKE ‘%aggregate%’ AND
TOTALWORK != 0 AND SOFAR <> TOTALWORK ;

COL in_sec FORMAT a10
COL out_sec FORMAT a10
COL TIME_TAKEN_DISPLAY FORMAT a10
SELECT SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY in_sec,
OUTPUT_BYTES_PER_SEC_DISPLAY out_sec,
TIME_TAKEN_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;

SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER
WHERE RECOVER = ‘YES’
OR (RECOVER IS NULL AND ERROR IS NOT NULL);

SELECT FILE#, INCREMENTAL_LEVEL, COMPLETION_TIME,
BLOCKS, DATAFILE_BLOCKS
FROM V$BACKUP_DATAFILE
WHERE INCREMENTAL_LEVEL > 0
AND BLOCKS / DATAFILE_BLOCKS > .2
ORDER BY COMPLETION_TIME;

SELECT * FROM V$RECOVERY_FILE_DEST;
SELECT * FROM V$RECOVERY_AREA_USAGE;
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

REPORT OBSOLETE;
REPORT SCHEMA;
REPORT NEED BACKUP
REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE DEVICE TYPE sbt;
REPORT NEED BACKUP DEVICE TYPE DISK;
REPORT NEED BACKUP TABLESPACE TBS_3 DEVICE TYPE sbt;
REPORT OBSOLETE RECOVERY WINDOW OF 3 DAYS;
REPORT OBSOLETE REDUNDANCY 1;

DELETE BACKUPPIECE 101;
DELETE CONTROLFILECOPY ‘/tmp/control01.ctl’;
DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE 300;
DELETE BACKUP TAG ‘before_upgrade’;
DELETE ARCHIVELOG ALL BACKED UP 3 TIMES TO sbt;
DELETE EXPIRED BACKUP;
DELETE OBSOLETE;

#!/bin/tcsh
# name: runbackup.sh
# usage: use the tag name and number of copies as arguments
set media_family = $argv[1]
set format = $argv[2]
set restore_point = $argv[3]
rman @’/disk1/scripts/whole_db.cmd’ USING $media_family $format $restore_point

RUN
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT ‘/disk1/%d_backups/%U’;
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT ‘/disk2/%d_backups/%U’;
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT ‘/disk3/%d_backups/%U’;
BACKUP AS COPY DATABASE;
}

recover copy of database with tag ‘tcstest3’;
backup incremental level 1 tag ‘tcstest3’ for recover of copy with tag ‘tcstest3’ database ;

ALTER SYSTEM SET
DB_CREATE_FILE_DEST = ‘/disk1/bct/’
SCOPE=BOTH SID=’*’;

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE ‘/mydir/rman_change_track.f’ REUSE;

COL STATUS FORMAT A8
COL FILENAME FORMAT A60
SELECT STATUS, FILENAME
FROM V$BLOCK_CHANGE_TRACKING;

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘new_location’;

SELECT CURRENT_SCN FROM V$DATABASE;
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE=’YES’;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

FLASHBACK DATABASE TO SCN 46963;
FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;
FLASHBACK DATABASE TO TIME “TO_DATE(’09/20/05′,’MM/DD/YY’)”;
ALTER DATABASE OPEN READ ONLY;

SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;

SET UNTIL TIME ‘Nov 15 2004 09:00:00’;
SET UNTIL SEQUENCE 9923;
SET UNTIL RESTORE POINT before_update;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;

RUN{
SWITCH DATABASE TO COPY;
RECOVER DATABASE;
ALTER DATABASE OPEN;
}

sql “alter tablespace working_data offline”;
sql “alter database datafile 13 offline”;
restore tablespace working_data;
restore datafile 13;
recover tablespace working_data;
recover datafile 13;
sql “alter tablespace working_data online”;
sql “alter database datafile 13 online”;

SWITCH DATAFILE 4 TO COPY;
RECOVER DATAFILE 4;

run{
SET NEWNAME FOR DATAFILE 4 TO ‘/oracle/oradata/tcstest/users01.dbf’;
restore datafile 4;
switch datafile 4;
recover datafile 4;
}

SET NEWNAME FOR DATAFILE ‘/disk1/oradata/prod/users01.dbf’
TO ‘/disk2/users01.dbf’;
RESTORE TABLESPACE users;
SWITCH DATAFILE ALL; # update control file with new filenames
RECOVER TABLESPACE users;

RECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 199 FROM TAG mondayam;
RECOVER CORRUPTION LIST;

rman target sys/password
startup nomount
set dbid=2540040039;
restore controlfile from autobackup;
sql ‘alter database mount’;
restore database;
recover database noredo;
sql “alter database open resetlogs”;

rman target /
set dbid=204062491;
startup force nomount;
run {
allocate channel tape_1 type sbt
parms=’env=(nb_ora_serv=rmsrv, nb_ora_client=cervantes)’;
restore spfile from autobackup;

alter system set control_files= ‘/u02/oradata/prod/control01.dbf’,
‘/u03/oradata/prod/control02.dbf’ scope=spfile;

alter system set db_file_name_convert= (‘/u04’ , ‘/u02’ ,
‘/u05’ , ‘/u02’ ,
‘u06’ , ‘ u03’ ,
‘u07’ , ‘u03’) scope=spfile;

alter system set log_file_name_convert= (‘/u04’ , ‘/u02’ ,
‘/u05’ , ‘/u02’ ,
‘u06’ , ‘ u03’ ,
‘u07’ , ‘u03’) scope=spfile;

alter system set log_archive_dest_1=
‘location=/u02/oradata/prod/arch’ scope=spfile;

run {
allocate channel tape_1 type sbt
parms=’env=(nb_ora_serv=rmsrv, nb_ora_client=Cervantes)’;
restore controlfile from autobackup;

configure auxiliary channel 1 device type sbt parms
= “env=(nb_ora_serv=mgtserv, nb_ora_client=cervantes)”;

configure auxiliary channel 2 device type sbt parms
= “env=(nb_ora_serv=mgtserv, nb_ora_cient=cervantes)”;

sql>startup mount;
sql>alter database clear logfile group 2;
sql>alter database open;

rman target /
startup nomount;
restore controlfile from autobackup;
alter database mount;
recover database;
alter database open (resetlogs);

recover tablespace “APP_DATA” until time
“to_date(‘2009-08-04 12:15:00’,’YYYY-MM-DD HH24:MI:SS’)”
auxiliary destination ’/opt/oracle/temp’;