Rman – Oacle DBWR

Duplicate a Database Using RMAN in Oracle Database 12c Release 1

Cloning Oracle 12c Database Using Active database Duplication :-

To create a Physical Standby database using RMAN DUPLICATE FROM ACTIVE DATABASE feature which is now available in 11g Release 1 onwards. This enables us to create a physical standby database without having to take a backup of the primary database as a prerequisite step.

Steps for creating database using Active database Duplication :-

Change the archive log mode :

If it is noarchivelog mode, switch to archivelog mode.

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT
SQL> alter database archivelog

Database altered.

SQL> alter database open

Database altered.

SQL> archive log list
Database log mode                  Archive Mode
Automatic archival                 Enabled
Archive destination                +DG01
Oldest online log sequence         299300
Next log sequence to archive       299305
Current log sequence               299305

2. Enable force logging mode:

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

3. Initialization Parameters :

Check the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to “prod” on the primary database.

The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value.

SQL> show parameter db_name;

NAME     TYPE   VALUE
-------- -----  ------
db_name  string prod

SQL> show parameter db_unique;

NAME           TYPE   VALUE
-------------- -----  ------
db_unique_name string prod

SQL> alter system set log_archive_config='DG_CONFIG=(prod,clone)' SCOPE=both sid='*';

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/prod/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod' SCOPE=both sid='*';

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=clone LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=clone' SCOPE=both sid='*';
SQL> alter system set fal_server=prod SCOPE=both sid='*';

System altered.

SQL> alter system set fal_client=clone SCOPE=both sid='*';

System altered.

SQL> alter system set standby_file_management=auto SCOPE=both sid='*';

System altered.

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=exclusive scope=spfile;

System altered.

4. Listener Configuration in Target database :

[oracle@devserver ]$ export ORACLE_SID=clone
[oracle@devserver ]$ export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
[oracle@devserver admin]$ cd $ORACLE_HOME/network/admin
[oracle@devserver admin]$ cat listener.ora

# listener.ora Network Configuration File: /oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)

      (SID_NAME = clone )

    )

  )

LISTENER_CLONE =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = devserver.localdomain.com)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

5. TNS Service Name Registration in Target database :

CLONE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = devserver.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = CLONE)
)
)

6. Create respective directories in Target Server:

[oracle@devserver admin]$ mkdir /oracle/app/oracle/oradata/clone/ctrl
[oracle@devserver admin]$ mkdir /oracle/app/oracle/oradata/clone/data
[oracle@devserver admin]$ mkdir /oracle/app/oracle/oradata/clone/logs
[oracle@devserver admin]$ mkdir /oracle/app/oracle/oradata/clone/arch
[oracle@devserver admin]$ mkdir /oracle/app/oracle/admin/clone/adump

7. Start listener in Target side:

[oracle@proddr01 admin] $lsnrctl start LISTENER_CLONE

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JAN-2019 14:05:49

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Starting listener to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=devserver.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER_CLONE
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 03-DEC-2018 14:09:08
Uptime 55 days 23 hr. 56 min. 40 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/devserver/listener_clone/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=devserver.localdomain.com)(PORT=1521)))
Services Summary...
Service "clone" has 1 instance(s).
Instance "clone", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

8. Copying password & parameter file to standby server:

After copying pfile,only keep the parameter entry in PFILE:

db_name

[oracle@devserver ]$ cd $ORACLE_HOME/dbs
[oracle@devserver dbs]$ scp initprod.ora orapwprod
oracle@devserver:/oracle/app/oracle/product/12.1.0/dbhome_1/dbs oracle@devserver's password: 
initprod.ora  100% 1536     1.5KB/s   00:00
orapwprod     100% 1536     1.5KB/s   00:00                                 
[oracle@proddr01 dbs]$ mv orapwprod orapwclone

[oracle@proddr01 dbs]$ cat initprod.ora
db_name='clone'

9. Check connectivity between primary and standby side :

[oracle@devserver ]$ tnsping prod  [In boths the nodes]

[oracle@devserver ]$ tnsping clone [In boths the nodes]

10. Clone Database Creation :-

Startup in nomount stage :

[oracle@proddr01 ]$ export ORACLE_SID=clone
[oracle@proddr01 ]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jan 29 01:12:25 2019

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

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes

11. Connect RMAN to create target database,

[oracle@proddr01 ]$ rman target sys/****@prod auxiliary sys/****@clone
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jan 30 20:15:10 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: PROD (DBID=1459429229)
connected to auxiliary database: PROD (not mounted)

RMAN> run
{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'prod','proddr'
set db_file_name_convert='+DG01/prod/datafile','/oradata1/clone/data' 
set db_unique_name='clone'
set cluster_database='false'
set log_file_name_convert='+DG01/prod/onlinelog','/oradata1/clone/logs' 
set control_files='/oracle/app/oracle/oradata/clone/ctrl/control.ctl'
set fal_client='clone'
set fal_server='prod'
set audit_file_dest='/oracle/app/oracle/admin/proddr/adump'
set log_archive_config='dg_config=(clone,prod)'
set log_archive_dest_1='location=location=/oradata1/prod/arch'
set log_archive_dest_2='service=prod async valid_for=(online_logfiles,primary_role) db_unique_name=prod'
set sga_target='50GB'
set sga_max_size='50GB'
set undo_tablespace='UNDOTBS1'
nofilenamecheck;
}

using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=42 device type=DISK
 
allocated channel: prmy2
channel prmy2: SID=36 device type=DISK
 
allocated channel: prmy3 
channel prmy3 : SID=45 device type=DISK

allocated channel: prmy4 
channel prmy4 : SID=45 device type=DISK
 
allocated channel: stby
channel stby: SID=20 device type=DISK
 
Starting Duplicate Db at 30-JAN-19
.
.
.
.
.
Finished Duplicate Db at 30-JAN-19
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN>

12. Disable Archivelog mode in Target database :

SQL> shut immediate

SQL> startup mount

SQL> alter database noarchivelog

Database altered.
SQL> alter database open

Database altered.

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba                       FB Group:https://www.facebook.com/groups/894402327369506/              FB Page: https://www.facebook.com/dbahariprasath/?               Twitter: https://twitter.com/hariprasathdba

Cloning Oracle 12c Database Using Rman Backup Based Duplication

PRODUCTION : MACHINE DB31
CLONE : MACHINE DB32

MACHINE DB32 CLONE:
——————

1) Create Directory CLONE
————————-

[oracle@db32 ~]$ cd /u01

[oracle@db32 u01]$ ls

[oracle@db32 u01]$ mkdir clone

2) Create Pfile for Clone:
————————–

[oracle@db32 ~]$ cd $ORACLE_HOME/dbs

[oracle@db32 dbs]$ vi initclone.ora

*.db_name=CLONE
*.control_files='/u01/app/oracle/oradata/CLONE/control01.ctl','/u01/app/oracle/flash_recovery_area/clone/control02.ctl'
*.db_recovery_file_dest_size=4g
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_file_name_convert='/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/CLONE/'
*.log_file_name_convert='/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/CLONE/'

3) Create password file Clone:
——————————

[oracle@db32 dbs]$ orapwd file=orapwclone password=manager force=y

4) create directory which is mentioned in your pfile:
—————————————————–

/u01/app/oracle/flash_recovery_area/clone (create clone directory)

MACHINE DB32 CLONE:
——————
5) create listener and tns by NETCA Clone Side:
———————————————-

i) Listener Name: list_col
ii) Tns Name: toclone

6) register created listener by NETMGR
————————————–

MACHINE DB31 PRODUCTION:
————————
7) create listener and tns by NETCA

i) Listner Name: list
ii) Tns Name: toprod

8) register created listener by NETMGR

MACHINE DB31 PRODUCTION:
————————
9) copy paste listener and tns for each other : prod & clone

/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

[oracle@db31 admin]$ vi listener.ora
[oracle@db31 admin]$ vi tnsnames.ora

MACHINE DB32 CLONE:
——————
10) copy paste listener and tns for each other : prod & clone

cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin

[oracle@db32 admin]$ vi listener.ora
[oracle@db32 admin]$ vi tnsnames.ora

11) Check Connectivity:

ping tns (tnspingtoclone)
(tnspingtoprod)

rman target sys/manager@toclone
rman target sys/manager@toprod

MACHINE DB31 PRODUCTION:
———————–
12) Check connectivity:

ping tns (tnspingtoclone)
(tnspingtoprod)

Rman>rman target sys/manager@toclone
Rman>rman target sys/manager@toprod

13) Enable archive log:
———————–

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15

14) Connect RMAN :
—————–

[oracle@db31 ~]$ rman target/

RMAN> show all;

RMAN> configure controlfile autobackup on;

15) take database backup through RMAN :
—————————————

RMAN> backup database include current controlfile plus archivelog;

Starting backup at 23-JAN-19
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=1 STAMP=952712839
channel ORA_DISK_1: starting piece 1 at 23-JAN-19
channel ORA_DISK_1: finished piece 1 at 23-JAN-19
piece handle=/u01/app/oracle/flash_recovery_area/PROD/backupset/2019_01_21/o1_mf_annnn_TAG20170822T182720_dsrbkjxj_.bkp tag=TAG20170822T182720 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-JAN-19

Starting backup at 23-JAN-19
using channel ORA_DISK_1
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=/u01/app/oracle/oradata/prod/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/prod/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/prod/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/prod/users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-JAN-19
channel ORA_DISK_1: finished piece 1 at 23-JAN-19
piece handle=/u01/app/oracle/flash_recovery_area/PROD/backupset/2019_01_21/o1_mf_nnndf_TAG20170822T182722_dsrbkzmj_.bkp tag=TAG20170822T182722 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:08:28
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 23-JAN-19
channel ORA_DISK_1: finished piece 1 at 23-JAN-19
piece handle=/u01/app/oracle/flash_recovery_area/PROD/backupset/2019_01_21/o1_mf_ncnnf_TAG20170822T182722_dsrc1mfn_.bkp tag=TAG20170822T182722 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-JAN-19

Starting backup at 23-JAN-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=2 STAMP=952713357
channel ORA_DISK_1: starting piece 1 at 23-JAN-19
channel ORA_DISK_1: finished piece 1 at 23-JAN-19
piece handle=/u01/app/oracle/flash_recovery_area/PROD/backupset/2019_01_21/o1_mf_annnn_TAG20170822T183557_dsrc1p1c_.bkp tag=TAG20170822T183557 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-JAN-19

Starting Control File and SPFILE Autobackup at 23-JAN-19
piece handle=/u01/app/oracle/flash_recovery_area/PROD/autobackup/2019_01_21/o1_mf_s_952713359_dsrc1qyr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 23-JAN-19

16) Go flash_recover_area
————————–

[oracle@db31 ~]$ cd /u01/app/oracle/flash_recovery_area/

[oracle@db31 flash_recovery_area]$ scp -r PROD oracle@192.168.139.32:/u01/app/oracle/flash_recovery_area/
oracle@192.168.139.32's password:
o1_mf_annnn_TAG20170822T183557_dsrc1p1c_.bkp 100% 143KB 142.5KB/s 00:00
o1_mf_ncnnf_TAG20170822T182722_dsrc1mfn_.bkp 100% 9568KB 9.3MB/s 00:01
o1_mf_nnndf_TAG20170822T182722_dsrbkzmj_.bkp 100% 1060MB 2.6MB/s 06:42
o1_mf_annnn_TAG20170822T182720_dsrbkjxj_.bkp 100% 4222KB 4.1MB/s 00:01
o1_mf_1_5_dsrc1o50_.arc 100% 141KB 141.0KB/s 00:00
o1_mf_1_4_dsrbkhmp_.arc 100% 4220KB 602.9KB/s 00:07
o1_mf_s_952713359_dsrc1qyr_.bkp 100% 9600KB 4.7MB/s 00:02

MACHINE DB32 CLONE:

[oracle@db32 ~]$ export ORACLE_SID=clone

[oracle@db32 ~]$ sqlplus / as sysdba

SQL> startup nomount

RMAN> rman target sys/manager@toprod auxiliary sys/manager@toclone

connected to target database: PROD (DBID=364534697)
connected to auxiliary database: CLONE (not mounted)

RMAN> duplicate target database to 'CLONE' Nofilenamecheck;

Starting Duplicate Db at 23-JAN-19
using channel ORA_AUX_DISK_1

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''CLONE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''CLONE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''CLONE'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''CLONE'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     631914496 bytes

Fixed Size                     1338364 bytes
Variable Size                377488388 bytes
Database Buffers             247463936 bytes
Redo Buffers                   5623808 bytes

Starting restore at 23-JAN-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/CLONE/autobackup/2013_01_27/o1_mf_s_805812962_8j9onctk_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/CLONE/autobackup/2013_01_27/o1_mf_s_805812962_8j9onctk_.bkp tag=TAG20130127T125602
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/CLONE/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/CLONE/control02.ctl
Finished restore at 23-JAN-19

database mounted

contents of Memory Script:
{
   set until scn  763666;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/CLONE/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/CLONE/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/CLONE/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/CLONE/users01.dbf";
   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

Starting restore at 23-JAN-19
using channel ORA_AUX_DISK_1

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 /u01/app/oracle/oradata/CLONE/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/CLONE/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/CLONE/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/CLONE/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/CLONE/backupset/2013_01_27/o1_mf_nnndf_TAG20130127T125432_8j9okjyl_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/CLONE/backupset/2013_01_27/o1_mf_nnndf_TAG20130127T125432_8j9okjyl_.bkp tag=TAG20130127T125432
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:22
Finished restore at 23-JAN-19

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=805814774 file name=/u01/app/oracle/oradata/CLONE/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=805814774 file name=/u01/app/oracle/oradata/CLONE/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=805814774 file name=/u01/app/oracle/oradata/CLONE/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=805814774 file name=/u01/app/oracle/oradata/CLONE/users01.dbf

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

executing command: SET until clause

Starting recover at 23-JAN-19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/CLONE/archivelog/2013_01_27/o1_mf_1_4_8j9on8j1_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/CLONE/archivelog/2013_01_27/o1_mf_1_4_8j9on8j1_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-JAN-19

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name =
 ''CLONE'' 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

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     631914496 bytes

Fixed Size                     1338364 bytes
Variable Size                377488388 bytes
Database Buffers             247463936 bytes
Redo Buffers                   5623808 bytes

sql statement: alter system set  db_name =  ''CLONE'' 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     631914496 bytes

Fixed Size                     1338364 bytes
Variable Size                377488388 bytes
Database Buffers             247463936 bytes
Redo Buffers                   5623808 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1  SIZE 50 M ,
  GROUP  2  SIZE 50 M ,
  GROUP  3  SIZE 50 M
 DATAFILE
  '/u01/app/oracle/oradata/CLONE/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/CLONE/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/CLONE/sysaux01.dbf",
 "/u01/app/oracle/oradata/CLONE/undotbs01.dbf",
 "/u01/app/oracle/oradata/CLONE/users01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/CLONE/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/CLONE/sysaux01.dbf RECID=1 STAMP=805814812
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/CLONE/undotbs01.dbf RECID=2 STAMP=805814812
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/CLONE/users01.dbf RECID=3 STAMP=805814812

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=805814812 file name=/u01/app/oracle/oradata/CLONE/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=805814812 file name=/u01/app/oracle/oradata/CLONE/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=805814812 file name=/u01/app/oracle/oradata/CLONE/users01.dbf

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

database opened
Finished Duplicate Db at 23-JAN-19

RMAN performed the following steps automatically to duplicate the database :

Allocates automatic auxiliary channel
Creates a controlfile for the clone database
Performs an incomplete recovery of the clone database using incremental backups and archived redo log files up to the last backed up archived redo log file.
Shutdowns the database and opens it using RESETLOGS option.
Generates a new unique DBID for the clone database.

All file locations were the same as in the production database. However, in case we need to change clone database’s directory structure.

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Oracle utility dbverify (DBV) Tips and Tricks

FEW BLOCKS IN DATAFILE ARE CORRUPT

There can be a corrupt block in a large datafile in your database which RMAN can easily report. Now it would take significant effort and time to perform the traditional restore and recover of a large datafile. So instead we will just recover the corrupt block and not the entire datafile.

For Oracle Database 10g or Oracle9i Database, use the blockrecover command to perform block media recovery.

As for Oracle Database 11g or newer, we will use the recover datafile … block command as shown below:
ONLY FOR TESTING FIRST WE CREATED SOME DB BLOCK CORRUPTION ??

SYS@ram > SELECT header_block FROM dba_segments WHERE segment_name=’EMP’;

HEADER_BLOCK
————
146

$ dd of=/u01/oracle/DB11G/oradata/ram/users01.dbf bs=8192 conv=notrunc seek=147 << EOF
> corruption test
> EOF
0+1 records in
0+1 records out
16 bytes (16 B) copied, 0.000159796 s, 100 kB/s

SYS@ram> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.

SYS@ram> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 147)
ORA-01110: data file 4: ‘/u01/oracle/DB11G/oradata/ram/users01.dbf’

A) CHECK CORRUPTION USING RMAN

RMAN will automatically detect corruption in blocks whenever a backup or backup validate command is issued. These blocks are reported as corrupt in the alert.log file and the V$DATABASE_BLOCK_CORRUPTION view.

Using RMAN command:

RMAN> backup validate database archivelog all;

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
4 FAILED 0 18 667 1086086
File Name: /u01/oracle/DB11G/oradata/ram/users01.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 90
Index 0 39
Other 1 493
In Alert Log:

Wed JAN 18  1:53:28 2019
Hex dump of (file 4, block 147) in trace file /u01/oracle/DB11G/diag/rdbms/ram/ram/trace/ram_ora_6734.trc
Corrupt block relative dba: 0x01000093 (file 4, block 147)
Bad header found during validation
Data in bad block:
type: 99 format: 7 rdba: 0x69747075
last change scn: 0x7365.74206e6f seq: 0x74 flg: 0x0a
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x0eef0602
check value in block header: 0xb843
block checksum disabled
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/ram/users01.dbf. found same corrupt data
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/ram/users01.dbf. found same corrupt data
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/ram/users01.dbf. found same corrupt data
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/ram/users01.dbf. found same corrupt data
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/ram/users01.dbf. found same corrupt data

In V$DATABASE_BLOCK_CORRUPTION view:

RMAN backup populates V$DATABASE_BLOCK_CORRUPTION.

SYS@ram > select * from V$DATABASE_BLOCK_CORRUPTION;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———-   ———-   ———-       ——————           ———
4      147      1            0          CORRUPT

Using DBV:

dbv file=/u01/oracle/DB11G/oradata/ram/users01.dbf blocksize=8192

DBVERIFY: Release 12.2.0.2.0 – Production on Wed JAN 18 1:06:26 2019

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

DBVERIFY – Verification starting : FILE = /u01/oracle/DB11G/oradata/ram/users01.dbf
Page 147 is marked corrupt
Corrupt block relative dba: 0x01000093 (file 4, block 147)
Bad header found during dbv:
Data in bad block:
type: 99 format: 7 rdba: 0x69747075
last change scn: 0x7365.74206e6f seq: 0x74 flg: 0x0a
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x0eef0602
check value in block header: 0xb843
block checksum disabled

DBVERIFY – Verification complete

Total Pages Examined : 640
Total Pages Processed (Data) : 90
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 39
Total Pages Failing (Index): 0
Total Pages Processed (Other): 492
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 18
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1086086 (0.1086086)

B) CORRECT DATA BLOCK CORRUPTION

Database can be in mounted or open when you are performing block-level recovery. Also you do not have to take the datafile being recovered offline. Block-level media recovery allows you to keep your database available hence reducing the mean time to recovery since only the corrupt blocks are offline during the recovery.

RMAN CAN NOT perform block-level recovery on block 1 (datafile header) of the datafile.

RMAN> recover datafile 4 block 147;

Starting recover at 18-JAN-2019 1:07:41
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u01/oracle/DB11G/fast_recovery_area/ram/backupset/2018_02_26/o1_mf_nnndf_TAG20190226T134738_9jwr7wj3_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/DB11G/fast_recovery_area/ram/backupset/2018_02_26/o1_mf_nnndf_TAG20190226T134738_9jwr7wj3_.bkp tag=TAG20190226T134738
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

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

Finished recover at 26-JAN-2019 1:07:45

C) VERIFY

SYS@ram > select * from V$DATABASE_BLOCK_CORRUPTION;

no rows selected

SYS@ram > select * from scott.emp;

EMPNO  ENAME  OB   MGR  HIREDATE  SAL  COMM   DEPTNO
———-   ———-   ———  ———-  ———--    ———-  ———-    ———-
7369  SMITH CLERK 7902 17-JUN-80  800    2       0

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Oracle 12c-Multitenant Database Backup Using Rman

With multi tenant feature introduced in oracle 12c, New commands are there for taking rman backup of pluggable database and root container database.

Backup of complete container( ROOT + ALL PDBS )

Taking backup of particular pluggable database:(backup pluggable database PDB5)

Backup of tablespace of pluggable database

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                       FB Group:https://www.facebook.com/groups/894402327369506/                   FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

Oracle-Delete Old Archives Using RMAN

Deletion of archive logs in oracle 11g database by comparing the sysdate

Prepare the shell script.

cat rman_arch_del.sh

#!/bin/bash
export ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2.0
export ORACLE_SID=FRANCE
export PATH=$ORACLE_HOME/bin:$PATH

delBackup () {
rman log=/home/oracle/arch_del.log << EOF
connect target /
DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE ‘sysdate-1’;
CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;
exit
EOF
}
# Main

delBackup

Now configure in crontab:

00 30 * * * /u01/app/oracle/rman_arch_del.sh > /tmp/rmanarch.log

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                       FB Group:https://www.facebook.com/groups/894402327369506/                   FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

Oracle RMAN Backup Using Shell Script

REQUIREMENT:

Configure shell script to take rman Full backup .

# $Header: rman_full_bkp.sh
# #################################################
# Script to be used on the crontab to schedule an RMAN Full Backup
# ################################################

# ###############################################
# VARIABLES To be Modified by the user to match the Environment:
# ###############################################

# INSTANCE Name: [Replace ${ORACLE_SID} with your instance SID]
ORACLE_SID=${ORACLE_SID}

# ORACLE_HOME Location: [Replace ${ORACLE_HOME} with the right ORACLE_HOME path]
ORACLE_HOME=${ORACLE_HOME}

# Backup Location: [Replace /backup/rmanfull with the backup location path]
BACKUPLOC=/backup/rmanfull

# Backup LOG location:
RMANLOG=${BACKUPLOC}/rmanfull.log

# COMPRESSED BACKUP option:[Y|N] [Default ENABLED]
COMPRESSION=Y

# Perform Maintenance based on below Backup & Archivelog Retention: [Y|N] [Default DISABLED]
MAINTENANCEFLAG=N

# Backup Retention "In Days": [Backups older than this retention will be deleted]
BKP_RETENTION=7

# Archives Deletion "In Days": [Archivelogs older than this retention will be deleted]
ARCH_RETENTION=7

# ##################
# GENERIC VARIABLES: [Can be left without modification]
# ##################

# MAX BACKUP Piece Size: [Must be BIGGER than the size of the biggest datafile in the database]
MAX_BKP_PIECE_SIZE=33g

# Show the full DATE and TIME details in the backup log:
NLS_DATE_FORMAT='DD-Mon-YYYY HH24:MI:SS'

export ORACLE_SID
export ORACLE_HOME
export BACKUPLOC
export COMPRESSION
export BKP_RETENTION
export ARCH_RETENTION
export MAX_BKP_PIECE_SIZE
export RMANLOG
export NLS_DATE_FORMAT
export MAINTENANCEFLAG

# Check the selected COMPRESSION option:
case ${COMPRESSION} in
Y|y|YES|Yes|yes|ON|on)
COMPRESSED_BKP="AS COMPRESSED BACKUPSET"
export COMPRESSED_BKP
;;
*)
COMPRESSED_BKP=""
export COMPRESSED_BKP
;;
esac

# Check the selected MAINTENANCE option:
case ${MAINTENANCEFLAG} in
Y|y|YES|Yes|yes|ON|on)
HASH_MAINT=""
export HASH_MAINT
;;
*)
HASH_MAINT="#"
export COMPRESSED_BKP
;;
esac


# Append the date to the backup log for each script execution:
echo "----------------------------" >> ${RMANLOG}
date >> ${RMANLOG}
echo "----------------------------" >> ${RMANLOG}

# ###################
# RMAN SCRIPT Section:
# ###################

${ORACLE_HOME}/bin/rman target / msglog=${RMANLOG} <<EOF
# Configuration Section:
# ---------------------
${HASH_MAINT}CONFIGURE BACKUP OPTIMIZATION ON;
${HASH_MAINT}CONFIGURE CONTROLFILE AUTOBACKUP ON;
${HASH_MAINT}CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${BACKUPLOC}/%F';
${HASH_MAINT}CONFIGURE SNAPSHOT CONTROLFILE NAME TO '${ORACLE_HOME}/dbs/snapcf_${ORACLE_SID}.f';
## Avoid Deleting archivelogs NOT yet applied on the standby: [When FORCE is not used]
#CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

# Maintenance Section:
# -------------------
## Crosscheck backups/copied to check for expired backups which are physically not available on the media:
${HASH_MAINT}crosscheck backup completed before 'sysdate-${BKP_RETENTION}' device type disk;
${HASH_MAINT}crosscheck copy completed before 'sysdate-${BKP_RETENTION}' device type disk;
## Report & Delete Obsolete backups which don't meet the RETENTION POLICY:
${HASH_MAINT}REPORT OBSOLETE RECOVERY WINDOW OF ${BKP_RETENTION} DAYS device type disk;
${HASH_MAINT}DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF ${BKP_RETENTION} DAYS device type disk;
## Delete All EXPIRED backups/copies which are not physically available:
${HASH_MAINT}DELETE NOPROMPT EXPIRED BACKUP COMPLETED BEFORE 'sysdate-${BKP_RETENTION}' device type disk;
${HASH_MAINT}DELETE NOPROMPT EXPIRED COPY COMPLETED BEFORE 'sysdate-${BKP_RETENTION}' device type disk;
## Crosscheck Archivelogs to avoid the backup failure:
${HASH_MAINT}CHANGE ARCHIVELOG ALL CROSSCHECK;
${HASH_MAINT}DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
## Delete Archivelogs older than ARCH_RETENTION days:
${HASH_MAINT}DELETE NOPROMPT archivelog all completed before 'sysdate -${ARCH_RETENTION}';

# Full Backup Script starts here: [Compressed+Controlfile+Archives]
# ------------------------------
run{
allocate channel F1 type disk;
allocate channel F2 type disk;
allocate channel F3 type disk;
allocate channel F4 type disk;
sql 'alter system archive log current';
BACKUP ${COMPRESSED_BKP}
#MAXSETSIZE ${MAX_BKP_PIECE_SIZE}
NOT BACKED UP SINCE TIME 'SYSDATE-2/24'
INCREMENTAL LEVEL=0
FORMAT '${BACKUPLOC}/%d_%t_%s_%p.bkp' 
FILESPERSET 100
TAG='FULLBKP'
DATABASE include current controlfile PLUS ARCHIVELOG NOT BACKED UP SINCE TIME 'SYSDATE-2/24';
## Backup the controlfile separately:
BACKUP ${COMPRESSED_BKP} CURRENT CONTROLFILE FORMAT '${BACKUPLOC}/CONTROLFILE_%d_%I_%t_%s_%p.bkp' TAG='CONTROLFILE_BKP' REUSE ;
## Trace backup of Controlfile & SPFILE:
SQL "ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ''${BACKUPLOC}/controlfile.trc'' REUSE";
SQL "CREATE PFILE=''${BACKUPLOC}/init${ORACLE_SID}.ora'' FROM SPFILE";
}
EOF

Give 755 permission

chmod 755 rman_full_bkp.sh

Configure script in crontab:

00 22 * * * "/u01/app/oracle/rman_full_bkp.sh" > /tmp/rmanarch.log

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

RMAN FULL Backup of a database

# ###############################################
# This script takes a RMAN Backup a database.
# ###############################################

# ###########
# Description:
# ###########
echo
echo “===================================================”
echo “This script Takes a RMAN FULL Backup of a database.”
echo “===================================================”
echo
sleep 1

# ###########################
# CPU count check:
# ###########################

# Count of CPUs:
CPU_NUM=`cat /proc/cpuinfo|grep CPU|wc -l`
export CPU_NUM

# #######################################
# Excluded INSTANCES:
# #######################################
# Here you can mention the instances the script will IGNORE and will NOT run against:
# Use pipe “|” as a separator between each instance name.
# e.g. Excluding: -MGMTDB, ASM instances:

EXL_DB=”\-MGMTDB|ASM” #Excluded INSTANCES [Will not get reported offline].

# ##############################
# SCRIPT ENGINE STARTS FROM HERE ……………………………………..
# ##############################

# ###########################
# Listing Available Databases:
# ###########################

# Count Instance Numbers:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )

# Exit if No DBs are running:
if [ $INS_COUNT -eq 0 ]
then
echo No Database Running !
exit
fi

# If there is ONLY one DB set it as default without prompt for selection:
if [ $INS_COUNT -eq 1 ]
then
export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk ‘{print $NF}’|sed -e ‘s/ora_pmon_//g’|grep -v sed|grep -v “s///g” )

# If there is more than one DB ASK the user to select:
elif [ $INS_COUNT -gt 1 ]
then
echo
echo “Select the ORACLE_SID:[Enter the number]”
echo ———————
select DB_ID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk ‘{print $NF}’|sed -e ‘s/ora_pmon_//g’|grep -v sed|grep -v “s///g” )
do
if [ -z “${REPLY##[0-9]*}” ]
then
export ORACLE_SID=$DB_ID
echo Selected Instance:
echo $DB_ID
break
else
export ORACLE_SID=${REPLY}
break
fi
done

fi
# Exit if the user selected a Non Listed Number:
if [ -z “${ORACLE_SID}” ]
then
echo “You’ve Entered An INVALID ORACLE_SID”
exit
fi

# #########################
# Getting ORACLE_HOME
# #########################
ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|grep -v “\-MGMTDB”|awk ‘{print $1}’|tail -1`
USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ‘:’|tail -1`

# SETTING ORATAB:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
export ORATAB
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
export ORATAB
fi

# ATTEMPT1: Get ORACLE_HOME using pwdx command:
PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk ‘{print $1}’`
export PMON_PID
ORACLE_HOME=`pwdx ${PMON_PID}|awk ‘{print $NF}’|sed -e ‘s/\/dbs//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from PWDX is ${ORACLE_HOME}”

# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
## If OS is Linux:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME

## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v ‘^\#’ $ORATAB | grep -v ‘^$’| grep -i “^${ORACLE_SID}:” | perl -lpe’$_ = reverse’ | cut -f3 | perl -lpe’$_ = reverse’ |cut -f2 -d’:’`
export ORACLE_HOME
fi
#echo “ORACLE_HOME from oratab is ${ORACLE_HOME}”
fi

# ATTEMPT3: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e ‘s/ORACLE_HOME=//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from environment is ${ORACLE_HOME}”
fi

# ATTEMPT4: If ORACLE_HOME is not found in the environment search user’s profile: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`grep -h ‘ORACLE_HOME=\/’ $USR_ORA_HOME/.bash_profile $USR_ORA_HOME/.*profile | perl -lpe’$_ = reverse’ |cut -f1 -d’=’ | perl -lpe’$_ = reverse’|tail -1`
export ORACLE_HOME
#echo “ORACLE_HOME from User Profile is ${ORACLE_HOME}”
fi

# ATTEMPT5: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`locate -i orapipe|head -1|sed -e ‘s/\/bin\/orapipe//g’`
export ORACLE_HOME
#echo “ORACLE_HOME from orapipe search is ${ORACLE_HOME}”
fi

# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
echo “Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly”
echo “e.g.”
echo “export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1”
exit
fi

# ########################################
# Exit if the user is not the Oracle Owner:
# ########################################
CURR_USER=`whoami`
if [ ${ORA_USER} != ${CURR_USER} ]; then
echo “”
echo “You’re Running This Sctipt with User: \”${CURR_USER}\” !!!”
echo “Please Run This Script With The Right OS User: \”${ORA_USER}\””
echo “Script Terminated!”
exit
fi

# ###############################
# RMAN: Script Creation:
# ###############################
# Last RMAN Backup Info:
# #####################
export NLS_DATE_FORMAT=’DD-Mon-YYYY HH24:MI:SS’
${ORACLE_HOME}/bin/sqlplus -s ‘/ as sysdba’ << EOF
set linesize 170 pages 200
PROMPT LAST 14 DAYS RMAN BACKUP DETAILS:
PROMPT ———————————

set linesize 160
set feedback off
col START_TIME for a15
col END_TIME for a15
col TIME_TAKEN_DISPLAY for a10
col INPUT_BYTES_DISPLAY heading “DATA SIZE” for a10
col OUTPUT_BYTES_DISPLAY heading “Backup Size” for a11
col OUTPUT_BYTES_PER_SEC_DISPLAY heading “Speed/s” for a10
col output_device_type heading “Device_TYPE” for a11
SELECT to_char (start_time,’DD-MON-YY HH24:MI’) START_TIME, to_char(end_time,’DD-MON-YY HH24:MI’) END_TIME, time_taken_display, status,
input_type, output_device_type,input_bytes_display, output_bytes_display, output_bytes_per_sec_display,COMPRESSION_RATIO COMPRESS_RATIO
FROM v\$rman_backup_job_details
WHERE end_time > sysdate -14;

EOF

# Variables:
export NLS_DATE_FORMAT=”DD-MON-YY HH24:MI:SS”

# Building the RMAN BACKUP Script:
echo;echo
echo Please enter the Backup Location: [e.g. /backup/DB]
echo “================================”
while read BKPLOC1
do
/bin/mkdir -p ${BKPLOC1}/RMANBKP_${ORACLE_SID}/`date ‘+%F’`
BKPLOC=${BKPLOC1}/RMANBKP_${ORACLE_SID}/`date ‘+%F’`

if [ ! -d “${BKPLOC}” ]; then
echo “Provided Backup Location is NOT Exist/Writable !”
echo
echo “Please Provide a VALID Backup Location:”
echo “————————————–”
else
break
fi
done
echo
echo “Backup Location is: ${BKPLOC1}”
echo
echo “How many CHANNELS do you want to allocate for this backup? [${CPU_NUM} CPUs Available On This Machine]”
echo “=========================================================”
while read CHANNEL_NUM
do
integ=’^[0-9]+$’
if ! [[ ${CHANNEL_NUM} =~ $integ ]] ; then
echo “Error: Not a valid number !”
echo
echo “Please Enter a VALID NUMBER:”
echo “—————————”
else
break
fi
done
echo
echo “Number Of Channels is: ${CHANNEL_NUM}”
echo
echo “———————————————”
echo “COMPRESSED BACKUP will allocate SMALLER space”
echo “but it’s a bit SLOWER than REGULAR BACKUP.”
echo “———————————————”
echo
echo “Do you want a COMPRESSED BACKUP? [Y|N]: [Y]”
echo “================================”
while read COMPRESSED
do
case $COMPRESSED in
“”|y|Y|yes|YES|Yes) COMPRESSED=” AS COMPRESSED BACKUPSET “; echo “COMPRESSED BACKUP ENABLED.”;break ;;
n|N|no|NO|No) COMPRESSED=””;break ;;
*) echo “Please enter a VALID answer [Y|N]” ;;
esac
done

echo
echo “Do you want to ENCRYPT the BACKUP by Password? [Available in Enterprise Edition only] [Y|N]: [N]”
echo “==============================================”
while read ENCR_BY_PASS_ANS
do
case ${ENCR_BY_PASS_ANS} in
y|Y|yes|YES|Yes)
echo
echo “Please Enter the password that will be used to Encrypt the backup:”
echo “—————————————————————–”
read ENCR_PASS
ENCR_BY_PASS=”SET ENCRYPTION ON IDENTIFIED BY ‘${ENCR_PASS}’ ONLY;”
export ENCR_BY_PASS
echo
echo “BACKUP ENCRYPTION ENABLED.”
echo
echo “Later, To RESTORE this backup please use the following command to DECRYPT it, placing it just before the RESTORE Command:”
echo ” e.g.”
echo ” SET DECRYPTION IDENTIFIED BY ‘${ENCR_PASS}’;”
echo ” restore database ….”
echo
break ;;
“”|n|N|no|NO|No) ENCR_BY_PASS=””;break ;;
*) echo “Please enter a VALID answer [Y|N]” ;;
esac
done

RMANSCRIPT=${BKPLOC}/RMAN_FULL_${ORACLE_SID}.rman
RMANSCRIPTRUNNER=${BKPLOC}/RMAN_FULL_nohup.sh
RMANLOG=${BKPLOC}/rmanlog.`date ‘+%a’`

echo “${ENCR_BY_PASS}” > ${RMANSCRIPT}
echo “run {” >> ${RMANSCRIPT}
CN=1
while [[ ${CN} -le ${CHANNEL_NUM} ]]
do
echo “allocate channel C${CN} type disk;” >> ${RMANSCRIPT}
((CN = CN + 1))
done
echo “CHANGE ARCHIVELOG ALL CROSSCHECK;” >> ${RMANSCRIPT}
#echo “DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;” >> ${RMANSCRIPT}
echo “BACKUP ${COMPRESSED} INCREMENTAL LEVEL=0 FORMAT ‘${BKPLOC}/%d_%I_%t_%s_%p’ TAG=’FULLBKP'” >> ${RMANSCRIPT}
echo “FILESPERSET 100 DATABASE include current controlfile PLUS ARCHIVELOG;” >> ${RMANSCRIPT}
#echo “BACKUP FORMAT ‘${BKPLOC}/%d_%t_%s_%p.ctl’ TAG=’CONTROL_BKP’ CURRENT CONTROLFILE;” >> ${RMANSCRIPT}
echo “BACKUP ${COMPRESSED} FORMAT ‘${BKPLOC}/CONTROLFILE_%d_%I_%t_%s_%p.bkp’ REUSE TAG=’CONTROL_BKP’ CURRENT CONTROLFILE;” >> ${RMANSCRIPT}
echo “SQL \”ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ”${BKPLOC}/controlfile.trc” REUSE\”;” >> ${RMANSCRIPT}
echo “SQL \”CREATE PFILE=”${BKPLOC}/init${ORACLE_SID}.ora” FROM SPFILE\”;” >> ${RMANSCRIPT}
echo “}” >> ${RMANSCRIPT}
echo “RMAN BACKUP SCRIPT CREATED.”
echo
sleep 1
echo “Backup Location is: ${BKPLOC}”
echo
sleep 1
echo “Starting Up RMAN Backup Job …”
echo
sleep 1
echo “#!/bin/bash” > ${RMANSCRIPTRUNNER}
echo “nohup ${ORACLE_HOME}/bin/rman target / cmdfile=${RMANSCRIPT} | tee ${RMANLOG} 2>&1 &” >> ${RMANSCRIPTRUNNER}
chmod 740 ${RMANSCRIPTRUNNER}
source ${RMANSCRIPTRUNNER}
echo
echo ” The RMAN backup job is currently running in the background. Disconnecting the current session will NOT interrupt the backup job :-)”
echo ” Now, viewing the backup job log:”
echo
echo “Backup Location is: ${BKPLOC}”
echo “Check the LOGFILE: ${RMANLOG}”
echo

# #############
# END OF SCRIPT
# #############

Backup of a Container Database (CDB) and a Pluggable Database (PDB) in Oracle Database 12c Release 2

RMAN CONNECTIONS:

[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 9 02:56:50 2018

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL> !
[oracle@ram ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 02:57:12 2018

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

connected to target database: DBWR (DBID=1322876759)

RMAN>

Container Database (CDB) Backup:

A full backup of the CDB you are also doing a full backup of all PDBs.

Connect to RMAN using OS authentication and take a full backup using the following command. This means you are connecting to the root container with “AS SYSDBA” privilege.

[oracle@ram ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 02:57:12 2018

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

connected to target database: DBWR (DBID=1322876759)

RMAN>

The datafiles associated with the CBD and all the PDBs (pdb5, pdb6, pdb$seed) are included in the backup.

RMAN> backup database plus archivelog;


Starting backup at 09-OCT-18
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=1 STAMP=989032088
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_annnn_TAG20181009T030809_fvqml1tn_.bkp tag=TAG20181009T030809 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-OCT-18

Starting backup at 09-OCT-18
using channel ORA_DISK_1
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=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgqv1n6_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgqxhq9_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvgqyx60_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvgqyy96_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T030811_fvqml3k6_.bkp tag=TAG20181009T030811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00022 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvhgn44v_.dbf
input datafile file number=00021 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvhgn44z_.dbf
input datafile file number=00023 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvhgn452_.dbf
input datafile file number=00024 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvhgn454_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T030811_fvqmp18w_.bkp tag=TAG20181009T030811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_sysaux_fvgzclb4_.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_system_fvgzcl9q_.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_undotbs1_fvgzclb5_.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_users_fvgzd8cs_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/777D95E41A572D82E053DD971D40E224/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T030811_fvqmqsfz_.bkp tag=TAG20181009T030811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgr1m08_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgr1m0c_.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvgr1m0f_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/777BDA91F244116BE053DD971D400082/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T030811_fvqmrlvp_.bkp tag=TAG20181009T030811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 09-OCT-18

Starting backup at 09-OCT-18
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=2 STAMP=989032324
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_annnn_TAG20181009T031204_fvqmsdc7_.bkp tag=TAG20181009T031204 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-OCT-18

Starting Control File and SPFILE Autobackup at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/autobackup/2018_10_09/o1_mf_s_989032325_fvqmsgbb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-OCT-18

Root Container Backup

A backup of the root container is a backup of the CDB, excluding any of the PDBs.

Connect to RMAN using OS authentication and backup the root container using the following command. This means you are connecting to the root container with “AS SYSDBA” privilege.

RMAN>rman target /

RMAN> backup database root;

Starting backup at 09-OCT-18
using channel ORA_DISK_1
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=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgqv1n6_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgqxhq9_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvgqyx60_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvgqyy96_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T031649_fvqn2b13_.bkp tag=TAG20181009T031649 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
Finished backup at 09-OCT-18

Starting Control File and SPFILE Autobackup at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/autobackup/2018_10_09/o1_mf_s_989032666_fvqn42vl_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-OCT-18

RMAN>

Pluggable Database (PDB) Backup

There are two ways to backup pluggable databases. When connected to RMAN as the root container, you can backup one or more PDBs

backup pluggable database pdb5,pdb6;
backup database; (Alternatively, connect to a specific PDB)

RMAN> rman target /

RMAN> backup pluggable database pdb5,pdb6;

Starting backup at 09-OCT-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00022 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvhgn44v_.dbf
input datafile file number=00021 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvhgn44z_.dbf
input datafile file number=00023 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvhgn452_.dbf
input datafile file number=00024 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvhgn454_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032310_fvqng76b_.bkp tag=TAG20181009T032310 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_sysaux_fvgzclb4_.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_system_fvgzcl9q_.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_undotbs1_fvgzclb5_.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_users_fvgzd8cs_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/777D95E41A572D82E053DD971D40E224/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032310_fvqnh1n5_.bkp tag=TAG20181009T032310 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:31
Finished backup at 09-OCT-18

Starting Control File and SPFILE Autobackup at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/autobackup/2018_10_09/o1_mf_s_989033048_fvqnj0n4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-OCT-18

RMAN>

Alternative way,

SQL> alter session set container = pdb5;

Session altered.

SQL> !

[oracle@ram ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 03:26:42 2018

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

connected to target database: DBWR (DBID=1322876759)

RMAN> backup database;

Starting backup at 09-OCT-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 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=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgqv1n6_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgqxhq9_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvgqyx60_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvgqyy96_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032653_fvqno5rt_.bkp tag=TAG20181009T032653 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:50
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00022 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvhgn44v_.dbf
input datafile file number=00021 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvhgn44z_.dbf
input datafile file number=00023 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvhgn452_.dbf
input datafile file number=00024 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvhgn454_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032653_fvqnpr4p_.bkp tag=TAG20181009T032653 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_sysaux_fvgzclb4_.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_system_fvgzcl9q_.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_undotbs1_fvgzclb5_.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_users_fvgzd8cs_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/777D95E41A572D82E053DD971D40E224/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032653_fvqnqk88_.bkp tag=TAG20181009T032653 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgr1m08_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgr1m0c_.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvgr1m0f_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/777BDA91F244116BE053DD971D400082/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032653_fvqnrbgm_.bkp tag=TAG20181009T032653 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:30
Finished backup at 09-OCT-18

Starting Control File and SPFILE Autobackup at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/autobackup/2018_10_09/o1_mf_s_989033344_fvqns92k_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-OCT-18

RMAN>

Tablespace and Datafile Backups

Multiple PDBs in the same CDB can have a tablespace with the same name, for example SYSTEM, SYSAUX and USERS.

we can remove that through directly connect to the appropriate PDB.

Once RMAN is connected to the PDB, the tablespace backup commands is unchanged like previous versions.

[oracle@ram admin]$ rman target=sys/pdb5

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 18:21:25 2018

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

connected to target database: DBWR (DBID=1322876759)

RMAN> BACKUP TABLESPACE system, sysaux, users;

Starting backup at 09-OCT-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 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=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgqv1n6_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgqxhq9_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvgqyy96_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T182505_fvs999th_.bkp tag=TAG20181009T182505 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:52
Finished backup at 09-OCT-18

Starting Control File and SPFILE Autobackup at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/autobackup/2018_10_09/o1_mf_s_989087157_fvs9bxpr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-OCT-18

Either way is we can connect to the CDB and take directly backup of the particular PDB.

[oracle@ram admin]$ rman target=sys/dbwr

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 18:28:37 2018

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

connected to target database: DBWR (DBID=1322876759)

RMAN> backup tablespace pdb5:system,pdb5:sysaux,pdb5:users,pdb6:system;

Starting backup at 09-OCT-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=83 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=00022 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvhgn44v_.dbf
input datafile file number=00021 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvhgn44z_.dbf
input datafile file number=00024 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvhgn454_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183030_fvs9mgjy_.bkp tag=TAG20181009T183030 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:30
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00013 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_system_fvgzcl9q_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/777D95E41A572D82E053DD971D40E224/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183030_fvs9nf4n_.bkp tag=TAG20181009T183030 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 09-OCT-18

Starting Control File and SPFILE Autobackup at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/autobackup/2018_10_09/o1_mf_s_989087476_fvs9nwwy_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-OCT-18

Datafiles have unique file numbers and fully qualified names, so they can be backed up from the root container or the individual PDB.

[oracle@ram admin]$ rman target =sys/pdb5

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 18:36:26 2018

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

connected to target database: DBWR (DBID=1322876759)

Datafiles have unique file numbers so they can be backed up from the root container or the individual PDB.

RMAN> backup datafile 5,6,7;

Starting backup at 09-OCT-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgr1m08_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgr1m0c_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/777BDA91F244116BE053DD971D400082/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183709_fvs9zy5o_.bkp tag=TAG20181009T183709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:28
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvgqyy96_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183709_fvsb0to6_.bkp tag=TAG20181009T183709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-OCT-18

Starting Control File and SPFILE Autobackup at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/autobackup/2018_10_09/o1_mf_s_989087859_fvsb0w26_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-OCT-18

If you are connecting to a PDB, only the files belonging to that PDB can be backed up.

So when connected as PDB5, we get an error if we try to backup the SYSAUX datafile from the root container.

RMAN> backup datafile 2;

Starting backup at 09-OCT-18
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 10/09/2018 18:42:39
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: 2

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Oracle18c-RMAN recovery through RESETLOGS

Description:-

In this article we are going to see RMAN recovery through RESETLOGS.

Advantages of using Recovery Through Resetlogs feature:-

There is no need to perform a full backup after an incomplete recovery.
There is no need to recreate a new standby database after a failover operation.
You can take incremental backups based on full backups of a previous incarnation when you use RMAN.
Block media recovery can restore backups from parent incarnation backups and recover the corrupted blocks through a RESETLOGS operation.

Let’s start the Demo:-

Steps to perform RMAN recovery through RESETLOGS :-

To perform recovery through RESETLOGS you must have all archived logs generated after the most recent backup.

Step 1:- Check the log_archive format as ‘%r’

Oracle 10g introduces a new format specification for archived log files. This new format avoids overwriting archived redo log files with the same sequence number across incarnations.

SQL> show parameter log_archive_format
NAME                TYPE       VALUE
------------------ ----------- ----------------
log_archive_format string %t_%s_%r.dbf

The format specification of the log_archive_format string “%”r represents the resetlogs id. It will ensure that a unique name is constructed for the archived redo log file during RMAN restore and as well as restoring via SQL*plus auto recovery mode.

Note: The database would not start if you remove the %r from the log archive format specification.

INCARNATION:-

A database incarnation is created whenever you open the database with the RESETLOGS option.

The Current incarnation is the one in which the database is running at present.

The incarnation from which the current incarnation branched after a ALTER DATABASE OPEN RESETLOGS was performed is called the Parent incarnation.

If you want to go back to some SCN which is not part of the Current database incarnation, we need to use the RESET DATABASE TO INCARNATION command as shown in the example below

Purpose of incarnations:-

An incarnation helps to identify redo streams which have the same SCN, but occurred at different points in time. This prevents the possibility of applying the wrong archive log file from a previous incarnation which could corrupt the database.

Suppose we are at incarnation 1 and are at SCN 100 in the database. I do a resetlogs and now the incarnation of the database becomes 2. Suppose we do another resetlogs and it so happens that the SCN at the time we did the resetlogs was also 100. Somewhere down the line later we want to do a point in time recovery and want to recover the database until SCN 100. How does Oracle know which is the correct SCN and how far back in time should the database be rolled back to and which archive logs need to be applied?

This is where incarnations come in and we will see how we can set the database to a particular incarnation to enable RMAN to do a point in time recovery.

Step 2:-Let us check what the current incarnation of the database

[oracle@18c ~]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Sat Aug 18 00:46:34 2018
Version 18.3.0.0.0

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

connected to target database: PROD18C (DBID=3984767297)

RMAN> LIST INCARNATION OF DATABASE;

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 PROD18C 3984767297 PARENT 1 07-FEB-18
2 2 PROD18C 3984767297 PARENT 1477662 14-AUG-18
3 3 PROD18C 3984767297 PARENT 1585689 15-AUG-18
4 4 PROD18C 3984767297 PARENT 1694352 17-AUG-18
5 5 PROD18C 3984767297 PARENT 1694353 17-AUG-18
6 6 PROD18C 3984767297 PARENT 1695382 17-AUG-18
7 7 PROD18C 3984767297 PARENT 1697963 17-AUG-18
8 8 PROD18C 3984767297 CURRENT 1755525 17-AUG-18

Step 3:- Note the CURRENT_SCN of the database.

RMAN> select current_scn from v$database;

CURRENT_SCN
-----------
1759315

Step 4:-

Now, let us make a change in the database which we will then try to undo by restoring and recovering the database to a point in time before the media failure or as in this case, a wrong transaction has happened.

Simulate the wrong DML execution,

SQL> conn hari/oracle;
Connected.
SQL> select count(*) from emp;

COUNT(*)
----------
100000

SQL> delete from emp where rownum < 1001;

1000 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from emp;

COUNT(*)
----------
99000

Step 5:- Shutdown and mount the database

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


SQL> startup mount;
ORACLE instance started.                                                Total System Global Area 1660943808 bytes
Fixed Size 8658368 bytes
Variable Size 1056964608 bytes
Database Buffers 587202560 bytes
Redo Buffers 8118272 bytes
Database mounted.

Step 6:- Now we will rollback the database to an SCN before the delete operation was performed.

[oracle@18c ~]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Sat Aug 18 00:52:25 2018
Version 18.3.0.0.0

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

connected to target database: PROD18C (DBID=3984767297, not open)

RMAN> run
2> {
3> set until scn 1759315;
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 18-AUG-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PROD18C/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD18C/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD18C/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PROD18C/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/fra/PROD18C/backupset/2018_08_17/o1_mf_nnndf_TAG20180817T230028_fqg1knnf_.bkp
channel ORA_DISK_1: piece handle=/u01/fra/PROD18C/backupset/2018_08_17/o1_mf_nnndf_TAG20180817T230028_fqg1knnf_.bkp tag=TAG20180817T230028
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 18-AUG-18

Starting recover at 18-AUG-18
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 28 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_28_fqg1m1qo_.arc
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_28_fqg1m1qo_.arc thread=1 sequence=28
media recovery complete, elapsed time: 00:00:02
Finished recover at 18-AUG-18

RMAN> alter database open resetlogs;

Statement processed

Step 7:- After open resetlogs,check the table count.

We can now see that the deleted rows have been recovered and the number of rows in the table is now the same as before the delete operation was performed.

SQL> select count(*) from hari.emp;

COUNT(*)
---------
100000

Step 8:- Now let us check what the incarnation of the database,

[oracle@18c ~]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Sat Aug 18 01:56:59 2018
Version 18.3.0.0.0

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

connected to target database: PROD18C (DBID=3984767297)

RMAN> list incarnation of database;

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 PROD18C 3984767297 PARENT 1 07-FEB-18
2 2 PROD18C 3984767297 PARENT 1477662 14-AUG-18
3 3 PROD18C 3984767297 PARENT 1585689 15-AUG-18
4 4 PROD18C 3984767297 PARENT 1694352 17-AUG-18
5 5 PROD18C 3984767297 PARENT 1694353 17-AUG-18
6 6 PROD18C 3984767297 PARENT 1695382 17-AUG-18
7 7 PROD18C 3984767297 PARENT 1697963 17-AUG-18
8 8 PROD18C 3984767297 PARENT 1755525 17-AUG-18
9 9 PROD18C 3984767297 CURRENT 1759316 18-AUG-18

The current incarnation of the database is 9 and since we have recovered the database until SCN 1759315, the RESET SCN has been set to the SCN 1759316.

The alert log file has lines below:

Incomplete Recovery applied until change 1759315 time 08/18/2018 00:47:43
2018-08-18T00:54:25.879829+05:30
Media Recovery Complete (prod18c)
Completed: alter database recover logfile '/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_28_fqg1m1qo_.arc'
2018-08-18T00:54:54.747977+05:30
alter database open resetlogs
2018-08-18T00:54:54.777271+05:30
RESETLOGS after incomplete recovery UNTIL CHANGE 1759315 time 08/18/2018 00:47:43
2018-08-18T00:54:55.507838+05:30
NET (PID:36995): Archived Log entry 65 added for T-1.S-1 ID 0xed877f00 LAD:1
Resetting resetlogs activation ID 3985080064 (0xed877f00)

Step 9:- Now let us make another delete from the emp table.

SQL> conn hari/oracle;
Connected.
SQL> select count(*) from emp;

COUNT(*)
----------
100000

SQL> delete from emp where rownum < 10001;

10000 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from emp;

COUNT(*)
---------
90000

Step 10:- Now, let us shutdown the database, startup and mount it and try the same incomplete recovery which we tried earlier until the SCN 1759315

[oracle@18c ]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Sat Aug 18 02:08:25 2018
Version 18.3.0.0.0

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

connected to target database: PROD18C (DBID=3984767297, not open)

RMAN> run
{
set until scn 1759315;
restore database;
recover database;
}2> 3> 4> 5> 6>

executing command: SET until clause

Starting restore at 18-AUG-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/18/2018 02:08:50
RMAN-20208: UNTIL CHANGE is before RESETLOGS change

We get the error RMAN-20208 because since the current incarnation of the database is 9 and we trying to go to an SCN before this incarnation.
So how do we go back to SCN 1759315 ?
For this to happen, we need to change the current incarnation (9) of the database to an older incarnation (8). The SCN 1759315 was present during the incarnation 8.
After resetting the incarnation to 8, we now see that the restore the database.

Step 11:- Reset incarnation of database.

RMAN> reset database to incarnation 8;

database reset to incarnation 8

Step 12:- Set the SCN number of incarnation 8 and try to restore & recover the database

RMAN> run
{
set until scn 1759315;
restore database;
recover database;
}2> 3> 4> 5> 6>

executing command: SET until clause

Starting restore at 18-AUG-18
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PROD18C/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD18C/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD18C/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PROD18C/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/fra/PROD18C/backupset/2018_08_17/o1_mf_nnndf_TAG20180817T230028_fqg1knnf_.bkp
channel ORA_DISK_1: piece handle=/u01/fra/PROD18C/backupset/2018_08_17/o1_mf_nnndf_TAG20180817T230028_fqg1knnf_.bkp tag=TAG20180817T230028
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 18-AUG-18

Starting recover at 18-AUG-18
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 28 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_28_fqg1m1qo_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_18/o1_mf_1_1_fqg886t8_.arc
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_28_fqg1m1qo_.arc thread=1 sequence=28
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_18/o1_mf_1_1_fqg886t8_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:02
Finished recover at 18-AUG-18

Step 13:- Open the database using resetlogs option

Now we open the database with the ALTER DATABASE OPEN RESETOGS command and see that a new incarnation key (10) has been allocated to the database as the CURRENT incarnation and the previous incarnation (8) has become an ORPHAN incarnation.

SQL> alter database open resetlogs;

Database altered.

The incarnation key 10 now is the CURRENT incarnation of the database, and incarnations 9 become ORPHAN.

RMAN> list incarnation of database;

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 PROD18C 3984767297 PARENT 1 07-FEB-18
2 2 PROD18C 3984767297 PARENT 1477662 14-AUG-18
3 3 PROD18C 3984767297 PARENT 1585689 15-AUG-18
4 4 PROD18C 3984767297 PARENT 1694352 17-AUG-18
5 5 PROD18C 3984767297 PARENT 1694353 17-AUG-18
6 6 PROD18C 3984767297 PARENT 1695382 17-AUG-18
7 7 PROD18C 3984767297 PARENT 1697963 17-AUG-18
8 8 PROD18C 3984767297 PARENT 1755525 17-AUG-18
9 9 PROD18C 3984767297 ORPHAN 1759316 18-AUG-18
10 10 PROD18C 3984767297 CURRENT 1759316 18-AUG-18

Step 14:- Check the CURRENT_SCN and recovered table count.

SQL> select current_scn from v$database;

CURRENT_SCN
------------
1760007

SQL> select count(*) from hari.emp;

COUNT(*)
----------
100000

The table recovered to the previous incarnation successfully and a new incarnation has been created.

Catch Me On:- Hariprasath Rajaram

Oracle18c-Restore using Rman To Create Oracle 18c Database on scratch server

Description:-

As part of disaster recovery exercise or to test the validity of a RMAN backup, a full restore and recovery of databases can be performed on scratch or test servers utilizing the production RMAN backups which have been restored from the tape backups on these test or scratch servers.

The following assumptions are made in this note:

The RMAN backups have been restored from disk backups to the same backup location on the new server as the production server where the backup was originally taken suppose the backup taken on the production server location:/u01/fra/PROD18C/autobackup/2018_08_17/
then create the same folder on the new server and copy the backup on the new server on the same location.
Consider the identical directory structure of production has been created on the new server such as the database files (data, control files, redo log files), bdump, cdump, udump and adump locations etc.
Controlfile autobackup must be enabled.

Steps required to restore the backup of a production database (prod18c) on a backup server:-

Restore the spfile from the autobackup
Restore the controlfile from the autobackup
Restore database
Recover the database
Open the database with resetlogs

Step 1:- Restore the spfile from the autobackup

[oracle@18c ~]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Fri Aug 17 23:31:35 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)

RMAN> set DBID=3984767297
executing command: SET DBID

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/18.0.0/dbhome_1/dbs/initprod18c.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 1073740616 bytes
Fixed Size 8665928 bytes
Variable Size 281018368 bytes
Database Buffers 780140544 bytes
Redo Buffers 3915776 bytes

RMAN> restore spfile from autobackup

Starting restore at 17-AUG-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK

recovery area destination: /u01/fra
database name (or database unique name) used for search: PROD18C
channel ORA_DISK_1: AUTOBACKUP /u01/fra/PROD18C/autobackup/2018_08_17/o1_mf_s_984438075_fqg1m3bh_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180817
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/fra/PROD18C/autobackup/2018_08_17/o1_mf_s_984438075_fqg1m3bh_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 17-AUG-18

RMAN> shutdown immediate;

Oracle instance shut down

Step 2:-Startup with new spfile and restore the controlfile from autobackup

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1660943808 bytes
Fixed Size 8658368 bytes
Variable Size 1056964608 bytes
Database Buffers 587202560 bytes
Redo Buffers 8118272 bytes
SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
[oracle@18c ~]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Fri Aug 17 23:35:56 2018
Version 18.3.0.0.0

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

connected to target database: PROD18C (not mounted)

RMAN> set dbid=3984767297

executing command: SET DBID

RMAN> restore controlfile from autobackup;

Starting restore at 17-AUG-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK

recovery area destination: /u01/fra
database name (or database unique name) used for search: PROD18C
channel ORA_DISK_1: AUTOBACKUP /u01/fra/PROD18C/autobackup/2018_08_17/o1_mf_s_984438075_fqg1m3bh_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180817
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/fra/PROD18C/autobackup/2018_08_17/o1_mf_s_984438075_fqg1m3bh_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/PROD18C/control01.ctl
output file name=/u01/app/oracle/oradata/PROD18C/control02.ctl
Finished restore at 17-AUG-18

Step 3:- Mount and restore the database.

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN> restore database;

Starting restore at 17-AUG-18
Starting implicit crosscheck backup at 17-AUG-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 17-AUG-18

Starting implicit crosscheck copy at 17-AUG-18
using channel ORA_DISK_1
Finished implicit crosscheck copy at 17-AUG-18

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

List of Cataloged Files
=======================
File Name: /u01/fra/PROD18C/autobackup/2018_08_17/o1_mf_s_984438075_fqg1m3bh_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PROD18C/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD18C/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD18C/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PROD18C/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/fra/PROD18C/backupset/2018_08_17/o1_mf_nnndf_TAG20180817T230028_fqg1knnf_.bkp
channel ORA_DISK_1: piece handle=/u01/fra/PROD18C/backupset/2018_08_17/o1_mf_nnndf_TAG20180817T230028_fqg1knnf_.bkp tag=TAG20180817T230028
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 17-AUG-18

Step 4:-Recover the database

RMAN> recover database;

Starting recover at 17-AUG-18
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 28 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_28_fqg1m1qo_.arc
archived log file name=/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_28_fqg1m1qo_.arc thread=1 sequence=28
unable to find archived log
archived log thread=1 sequence=29
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/17/2018 23:42:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 29 and starting SCN of 1755524

The recovery will fail at a point where it cannot restore any more archived redo log files.In this case the last archived log file which has been backed up is sequence 28.

This information can be obtained by issuing a LIST BACKUP OF ARCHIVELOG ALL command.So RMAN will fail when it tries to apply sequence 29….

Alternatively you can use Incomplete recovery (Cancel based) from the SQL prompt and open the database using Resetlogs option.

Step 5:- Open the database using resetlogs option

SQL> alter database open resetlogs;

Database altered.

Note:- Take the backup of database again because we have done incomplete recovery and open resetlogs option to open the database.

Catch Me On:- Hariprasath Rajaram

MACHINE DB32 CLONE: ——————

MACHINE DB32 CLONE: —————— 5) create listener and tns by NETCA Clone Side: ———————————————-

MACHINE DB31 PRODUCTION: ———————— 7) create listener and tns by NETCA

MACHINE DB31 PRODUCTION: ———————– 12) Check connectivity:

MACHINE DB32 CLONE:

FEW BLOCKS IN DATAFILE ARE CORRUPT

A) CHECK CORRUPTION USING RMAN

Using DBV:

B) CORRECT DATA BLOCK CORRUPTION

C) VERIFY

Backup of complete container( ROOT + ALL PDBS )

Taking backup of particular pluggable database:(backup pluggable database PDB5)

Backup of tablespace of pluggable database

RMAN CONNECTIONS:

Container Database (CDB) Backup:

Root Container Backup

Pluggable Database (PDB) Backup

Tablespace and Datafile Backups

MACHINE DB32 CLONE:
——————

MACHINE DB32 CLONE:
——————
5) create listener and tns by NETCA Clone Side:
———————————————-

MACHINE DB31 PRODUCTION:
————————
7) create listener and tns by NETCA

MACHINE DB31 PRODUCTION:
———————–
12) Check connectivity: