Oracle Fundamentals: Restoring Database Using RMAN Backup Based Duplication

Steps:

Note:

If ORACLE_HOME is not present at the TARGET host need to take tar backup of ORACLE_HOME at the target server.

Also edit the listener.ora and tnsnames.ora.

We have not done the necessary changes in the above files as we have cloned on the same host.

Refer to the links doc "rman_duplicate_links.txt" for more details on editing the above files.

===============================================================

Step 1.

RMAN Backup of PRODUCTION database in our case "ORCL".

The Script used was "rman_script_01oct2017.sh"

Taking Backup Of PRODUCTION database "ORCL":

The Crontab entry is as below:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

[oracle@localhost u01]$ crontab -l

####################RMAN_BKP#######################################

#07 14 * * * /u01/RMAN_full_bkp.sh >> /u01/RMAN.log

###################################################################

######################LOG_SWITCH####################################

#*/60 * * * * sh /home/oracle/switch_logfile.sh >> /home/oracle/switch_logfile.log

##############################################################

################## TAR_BKP_ORCL ##########################################

#41 17 * * * /home/oracle/tar.sh > /home/oracle/tar_oracle.log

############## RMAN_BKP ################################

30 10 * * * /u01/rman_script_01oct2017/rman_script_01oct2017.sh >> /u01/orcl_db_bkp/crontab_rman_log/crontab_rman_bkp.log

++++++++++++++++++++++++++++++++++++++++++++++++++++++++

The script used to take backup is as below:

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

PATH=$PATH:$HOME/bin

export PATH

ORACLE_SID=orcl export ORACLE_SID

ORACLE_BASE=/u01/app/oracle/ export ORACLE_BASE

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/ export ORACLE_HOME

PATH=/usr/sbin:$PATH export PATH

PATH=$ORACLE_HOME/bin:$PATH export PATH

rman target / log=/u01/orcl_db_bkp/orcl_db_bkp.log <<EOF

RUN {

allocate channel c1 type DISK;

allocate channel c2 type DISK;

crosscheck backup;

crosscheck archivelog all;

delete noprompt expired backup;

sql 'alter system archive log current';

backup as compressed backupset database format '/u01/orcl_db_bkp/%d_DB_%T_%s' ;

sql 'alter system archive log current';

backup as compressed backupset archivelog all format '/u01/orcl_db_bkp/%d_ARCH_%T_%s' ;

copy current controlfile to '/u01/orcl_db_bkp/%d_control_file';

DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;

release channel c1;

release channel c2;

}

exit

EOF

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

Note:

This "/u01/orcl_db_bkp/" is the directory where our RMAN backup of PRODUCTION (ORCL) is stored.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

============================================================

Step 2.

Make a directory to store the spool and the trace files:

$ cd /u01

$ mkdir rman_cloning_02_oct_2017

TAKING SPOOL IN ORCL (PROD instance)

sqlplus / as sysdba

spool /u01/rman_cloning_02_oct_2017/ORCL_spool_02oct17_.txt

select name from v$datafile order by file#;

select name from v$controlfile;

select member from v$logfile;

select file_name from dba_temp_files;

archive log list

show parameter dump

column NAME format a25

column VALUE format a50

select NAME,VALUE from v$diag_info;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

1. Tablespace and Datafiles:

column TABLESPACE_NAME format a10

column AUTOEXTENSIBLE format a15

column FILE_NAME format a50

select AUTOEXTENSIBLE,TABLESPACE_NAME,FILE_NAME from dba_data_files;

2. Physical size of the database:

select (select sum(bytes) /1024/1024/1024 data_size from dba_data_files) + (select nvl (sum(bytes),0) /1024/1024/1024 temp_size from dba_temp_files) + (select sum(bytes) /1024/1024/1024 redo_size from sys.v_$log) + (select sum (block_size * file_size_blks) /1024/1024/1024 controlfile_size from v$controlfile) "size in GB" from dual;

3. Logical size of the database:

select sum(bytes) /1024/1024/1024 "size in GB" from dba_segments;

4. Determine used space of tablespaces:

select tablespace_name ,sum(bytes) /1024/1024 from dba_segments group by tablespace_name;

5. Determine free space of tablespaces:

select tablespace_name ,sum(bytes) /1024/1024 from dba_free_space group by tablespace_name;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

======================================================

Step 3.

Take controlfile trace:

alter database backup controlfile to trace as '/u01/rman_cloning_02_oct_2017/cntrl_ORCL.txt'

show parameter spfile

show parameter db_name

show parameter service_name

show parameter db_unique

select INSTANCE_NAME from v$instance;

spool off

========================================================

Step 4.

Create the directories on target HOST or LOCATION for datafiles,logfiles,controlfiles as we want:

$ echo $ORACLE_BASE

/u01/app/oracle/

$ echo $ORACLE_HOME

/u01/app/oracle/product/11.2.0/dbhome_1/

$ cd /u01/app/oracle

$ mkdir orcldup

$ mkdir -p orcldup/datafiles

$ mkdir -p orcldup/logfiles

$ mkdir -p orcldup/controlfiles

Make a staging folder for backups and pfile:

On the SOURCE or PRODUCTION (ORCL) our location of RMAN backup is "/u01/orcl_db_bkp" we will not use the same location instead different location.

On our TARGET or CLONE (ORCLDUP) the location would be:

$ mkdir -p /u01/orcldup/rman_backup

create other required folders:

$ mkdir -p /u01/app/oracle/oradata/orcldup

$ mkdir -p /u01/app/oracle/flash_recovery_area/orcldup

$ mkdir -p /u01/app/oracle/admin/orcldup/adump

$ mkdir -p /u01/app/oracle/admin/orcldup/dpdump

Note:

We are cloning on the same HOST but the directory structure is different.

========================================================

Step 5.

create pfile if existing pfile then take backup first:

$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs

$ ls -lrth initorcl*

$ cp initorcl.ora initorcl_bkp_02oct17.ora

CREATE PFILE='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcldup.ora' FROM SPFILE;

=======================================================

Step 6.

Copy backup files from the SOURCE or PRODUCTION (ORCL) host:

We will do "cp" as we are on the same HOST:

check the backup location on SOURCE or PROD (ORCL) verify backup was successful:

cd /u01/orcl_db_bkp/

view orcl_db_bkp.log --(the log file for backup)

cd /u01/orcldup/rman_backup/

ls -lrth

cp /u01/orcl_db_bkp/ORCL* /u01/orcldup/rman_backup/.

Copy pfile of source database (orcl):

cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcldup.ora /u01/orcldup/rman_backup/.

========================================================

Add a line in the file /etc/oratab to reflect the database instance you are going to copy:

orclt:/u01/app/oracle/product/11.2.0/db1:N

============================================================

Step 7.

Edit the initialization parameter file from the main database:

$ vi /u01/orcldup/rman_backup/initorcldup.ora

1. Change db_name = orcldup

2. Edit it to reflect the new locations that might be appropriate

such as control file locations,audit dump destinations, datafile

locations, etc.

3. Add these lines:

db_file_name_convert = ('/u01/app/oracle/oradata/orcl', '/u01/app/oracle/oradata/orclt')

log_file_name_convert = ('/u01/app/oracle/oradata/orcl', '/u01/app/oracle/oradata/orclt')

Make directory for archive location of CLONE or TARGET (ORCLDUP):

mkdir -p /u01/arcorcldup/

=========================================================

Step 8.

Now set the Oracle SID as the duplicated database SID:

export ORACLE_SID=orcldup

sqlplus / as sysdba

startup nomount pfile='/u01/orcldup/rman_backup/initorcldup.ora';

cd /u01/app/oracle/diag/rdbms/orcldup/orcldup/trace/

ls -lrth alert*

tail -1000f alert_orcldup.log

ls -lrth /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl*

create spfile from pfile='/u01/orcldup/rman_backup/initorcldup.ora';

=========================================================

Step 9.

Using RMAN connect to the duplicate database (orcldup) as auxiliary instance:

export ORACLE_SID=orcldup

rman AUXILIARY /

DUPLICATE DATABASE TO ORCLDUP BACKUP LOCATION '/u01/orcldup/rman_backup/' NOFILENAMECHECK;

=========================================================

Step 10.

export ORACLE_SID=orcldup

echo $ORACLE_SID

echo $ORACLE_BASE

echo $ORACLE_HOME

TAKING SPOOL IN ORCLDUP (CLONE instance)

sqlplus / as sysdba

spool /u01/rman_cloning_02_oct_2017/ORCLDUP_spool_02oct17_.txt

select name,open_mode,log_mode from v$database;

select name from v$datafile order by file#;

select name from v$controlfile;

select member from v$logfile;

select file_name from dba_temp_files;

archive log list

show parameter dump

column NAME format a25

column VALUE format a50

select NAME,VALUE from v$diag_info;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

1. Tablespace and Datafiles:

column TABLESPACE_NAME format a10

column AUTOEXTENSIBLE format a15

column FILE_NAME format a50

select AUTOEXTENSIBLE,TABLESPACE_NAME,FILE_NAME from dba_data_files;

2. Physical size of the database:

3. Logical size of the database:

select sum(bytes) /1024/1024/1024 "size in GB" from dba_segments;

4. Determine used space of tablespaces:

select tablespace_name ,sum(bytes) /1024/1024 from dba_segments group by tablespace_name;

5. Determine free space of tablespaces:

select tablespace_name ,sum(bytes) /1024/1024 from dba_free_space group by tablespace_name;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

==============================================================

Step 11.

Take controlfile trace:

alter database backup controlfile to trace as '/u01/rman_cloning_02_oct_2017/cntrl_ORCLDUP.txt'

show parameter spfile

show parameter db_name

show parameter service_name

show parameter db_unique

select INSTANCE_NAME from v$instance;

spool off

===============================================================

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

ISSUES FACED:

ORA-01565: Unable to open Spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcldup.ora.

Errors in file /u01/app/oracle/diag/rdbms/orcldup/orcldup/trace/orcldup_mmon_8062.trc:

ORA-01565: error in identifying file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcldup.ora'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SOLUTION:

1. Shut cleanly the clone database.

create spfile from pfile='/u01/orcldup/rman_backup/initorcldup.ora';

Note:

The initfile "/u01/orcldup/rman_backup/initorcldup.ora" is the one which we have edited as per our CLONE (ORCLDUP) enviornment.

echo $ORACLE_SID

rlwrap sqlplus / as sysdba

startup mount

select INSTANCE_NAME from v$instance;

select name,open_mode,log_mode from v$database;

alter database open;

select name,open_mode,log_mode from v$database;

Note:

The error ORA-01565: Unable to open Spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcldup.ora.

Errors in file /u01/app/oracle/diag/rdbms/orcldup/orcldup/trace/orcldup_mmon_8062.trc:

ORA-01565: error in identifying file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcldup.ora'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

was not seen again in alert log of ORCLDUP the issue got resolved.

******************************************************************* ALERT LOG ORCLDUP after above changes:

Mon Oct 02 14:19:29 2017

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Autotune of undo retention is turned on.

IMODE=BR

ILAT =27

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options.

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcldup.ora

System parameters with non-default values:

processes = 150

memory_target = 300M

control_files = "/u01/app/oracle/orcldup/controlfiles/control01.ctl"

control_files = "/u01/app/oracle/orcldup/datafiles/control02.ctl"

db_file_name_convert = "/u01/app/oracle/oradata/orcl/"

db_file_name_convert = "/u01/app/oracle/orcldup/datafiles/"

log_file_name_convert = "/u01/app/oracle/oradata/orcl/"

log_file_name_convert = "/u01/app/oracle/orcldup/logfiles/"

db_block_size = 8192

compatible = "11.2.0.0.0"

log_archive_dest_1 = "location=/u01/arcorcldup/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclidup"

archive_lag_target = 0

db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"

db_recovery_file_dest_size= 4032M

standby_file_management = "AUTO"

undo_tablespace = "UNDOTBS1"

remote_login_passwordfile= "EXCLUSIVE"

db_domain = "localdomain"

dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"

audit_file_dest = "/u01/app/oracle/admin/orcldup/adump"

audit_trail = "DB"

db_name = "orcldup"

open_cursors = 300

diagnostic_dest = "/u01/app/oracle"

Mon Oct 02 14:19:30 2017

PMON started with pid=2, OS id=10100

Mon Oct 02 14:19:30 2017

PSP0 started with pid=3, OS id=10104

Mon Oct 02 14:19:31 2017

VKTM started with pid=4, OS id=10108 at elevated priority

VKTM running at (1)millisec precision with DBRM quantum (100)ms

Mon Oct 02 14:19:32 2017

GEN0 started with pid=5, OS id=10114

Mon Oct 02 14:19:32 2017

DIAG started with pid=6, OS id=10118

Mon Oct 02 14:19:32 2017

DBRM started with pid=7, OS id=10122

Mon Oct 02 14:19:32 2017

DIA0 started with pid=8, OS id=10126

Mon Oct 02 14:19:32 2017

MMAN started with pid=9, OS id=10130

Mon Oct 02 14:19:32 2017

DBW0 started with pid=10, OS id=10134

Mon Oct 02 14:19:32 2017

LGWR started with pid=11, OS id=10138

Mon Oct 02 14:19:32 2017

CKPT started with pid=12, OS id=10142

Mon Oct 02 14:19:32 2017

SMON started with pid=13, OS id=10146

Mon Oct 02 14:19:32 2017

RECO started with pid=14, OS id=10150

Mon Oct 02 14:19:32 2017

MMON started with pid=15, OS id=10154

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

Mon Oct 02 14:19:32 2017

MMNL started with pid=16, OS id=10158

starting up 1 shared server(s) ...

ORACLE_BASE from environment = /u01/app/oracle/

Spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcldup.ora is in old pre-11 format and compatible >= 11.0.0; converting to new H.A.R.D. compliant format.

Mon Oct 02 14:19:33 2017

ALTER DATABASE MOUNT

Changing di2dbun from to orcldup

Successful mount of redo thread 1, with mount id 181345525

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE MOUNT

Mon Oct 02 14:20:33 2017

alter database open

Mon Oct 02 14:20:33 2017

LGWR: STARTING ARCH PROCESSES

Mon Oct 02 14:20:33 2017

ARC0 started with pid=20, OS id=10207

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Thread 1 opened at log sequence 5

Current log# 2 seq# 5 mem# 0: /u01/app/oracle/orcldup/logfiles/redo02.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Mon Oct 02 14:20:34 2017

SMON: enabling cache recovery

[10199] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:19584504 end:19584644 diff:140 (1 seconds)

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is WE8MSWIN1252

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Mon Oct 02 14:20:36 2017

QMNC started with pid=22, OS id=10215

Mon Oct 02 14:20:36 2017

ARC1 started with pid=21, OS id=10211

Completed: alter database open

ARC1: Archival started

Mon Oct 02 14:20:37 2017

ARC3 started with pid=24, OS id=10223

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

Mon Oct 02 14:20:37 2017

ARC2 started with pid=23, OS id=10219

Mon Oct 02 14:20:37 2017

Starting background process CJQ0

ARC2: Archival started

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

ARC0: Becoming the heartbeat ARCH

Mon Oct 02 14:20:40 2017

db_recovery_file_dest_size of 4032 MB is 0.00% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

Mon Oct 02 14:20:37 2017

CJQ0 started with pid=30, OS id=10251

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

ScreenShots:

Note:

Refer to the txt doc for detailed commands and steps " Steps_Cloning_RMAN.txt ".

Now set the Oracle SID as the duplicated database SID:

export ORACLE_SID=orcldup

sqlplus / as sysdba

startup nomount pfile='/u01/orcldup/rman_backup/initorcldup.ora';

SQL> create spfile from pfile='/u01/orcldup/rman_backup/initorcldup.ora';

Using RMAN connect to the duplicate database (orcldup) as auxiliary instance:

export ORACLE_SID=orcldup

rman AUXILIARY /

DUPLICATE DATABASE TO ORCLDUP BACKUP LOCATION '/u01/orcldup/rman_backup/' NOFILENAMECHECK;

The following Snap Shots are after the duplicate command is fired:

Initorcldup.ora changes:

#orcl.__db_cache_size=171966464

#orcl.__java_pool_size=4194304

#orcl.__large_pool_size=4194304

#orcl.__oracle_base='/u01/app/oracle/'#ORACLE_BASE set from environment

#orcl.__pga_aggregate_target=255852544

#orcl.__sga_target=478150656

#orcl.__shared_io_pool_size=12582912

#orcl.__shared_pool_size=268435456

#orcl.__streams_pool_size=8388608

*.archive_lag_target=0

*.audit_file_dest='/u01/app/oracle/admin/orcldup/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/orcldup/controlfiles/control01.ctl','/u01/app/oracle/orcldup/datafiles/control02.ctl'

*.db_block_size=8192

*.db_domain='localdomain'

*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/orcldup/datafiles/'

*.db_name='orcldup'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4227858432

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

#*.fal_client='ORCL'

#*.fal_server='ORCLDR'

#*.log_archive_config='DG_CONFIG=(orcl,orcldr)'

*.log_archive_dest_1='location=/u01/arcorcldup/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclidup'

#*.log_archive_dest_2='service=orcldr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldr'

#*.log_archive_dest_state_2='defer'

*.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/orcldup/logfiles/'

*.memory_target=300M

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

Oracle Fundamentals

Wednesday, June 2, 2021

Restoring Database Using RMAN Backup Based Duplication

No comments:

Post a Comment

Contact Form

Cancelling Concurrent Request From Backend