Ramakrishna Vejandla: RMAN RESTORE

RMAN COLD BACKUP AND RESTORE

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

DATABASE :

Copy source database ORACLE_HOME

cd /<dbname>/oracle/product/

tar -cvf - 12.1.0.2 |gzip -c > <dbname>_12.1.0.2.tar.gz

scp <dbname>_12.1.0.2.tar.gz lg204757@<dbname>.ae.ge.com:/tmp

TAKE COLD BACKUP AT SOURCE AND COPY IT TO TARGET

$ cat cold_backup.sh

export ORACLE_HOME=/<dbname>/oracle/product/12.1.0.2

export ORACLE_SID=<dbname>

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

rman target / log=/<dbname>/wcapp/BACKUP/<dbname>/<dbname>`date +%d%m%Y`.log <<EOF

sql 'alter system checkpoint';

shutdown immediate;

startup mount;

sql "create pfile="/<dbname>/wcapp/BACKUP/<dbname>/pfile`date +%d%m%Y`.ora" from spfile";

RUN {

ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/<dbname>/wcapp/BACKUP/<dbname>/%U';

ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/<dbname>/wcapp/BACKUP/<dbname>/%U';

ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT '/<dbname>/wcapp/BACKUP/<dbname>/%U';

BACKUP AS COMPRESSED BACKUPSET DATABASE;

BACKUP CURRENT CONTROLFILE FORMAT '/<dbname>/wcapp/BACKUP/<dbname>/cntrl_%s_%p_%t';

RELEASE CHANNEL disk1;

RELEASE CHANNEL disk2;

RELEASE CHANNEL disk3;

}

Zip the backup directory in source database using below command

tar -cvf - <dbname> |gzip -c > <dbname>_bkp_30AUG19.tar.gz

scp <dbname>_bkp_30AUG19.tar.gz lg204757@<dbname>.ae.ge.com:/tmp

TARGET DATABSE

UPDATE THE INVENTORY

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

[oracle@<dbname> 12.1.0.2]$ vi /etc/oraInst.loc

[oracle@<dbname> 12.1.0.2]$ cat /etc/oraInst.loc

inventory_loc=/a01/<dbname>/oracle/oraInventory

inst_group=oinstall

[oracle@<dbname> 12.1.0.2]$ vi oraInst.loc

EXECUTE RUN INSTALLER

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

[oracle@<dbname> 12.1.0.2]$ cd /a01/<dbname>/oracle/12.1.0.2/oui/bin

[oracle@<dbname> bin]$ ./runInstaller -clone -silent -ignorePreReq ORACLE_HOME="/a01/<dbname>/oracle/12.1.0.2" ORACLE_HOME_NAME="dbhome_12cR2" ORACLE_BASE="/a01/<dbname>/oracle"

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 16383 MB Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2019-08-27_10-39-24AM. Please wait ...[oracle@<dbname> bin]$ Oracle Universal Installer, Version 12.1.0.2.0 Production

You can find the log of this install session at:

/a01/<dbname>/oracle/oraInventory/logs/cloneActions2019-08-27_10-39-24AM.log

.................................................................................................... 100% Done.

Installation in progress (Tuesday, August 27, 2019 10:41:46 AM EDT)

................................................................................ 80% Done.

Install successful

Linking in progress (Tuesday, August 27, 2019 10:45:48 AM EDT)

. 81% Done.

Link successful

Setup in progress (Tuesday, August 27, 2019 11:03:15 AM EDT)

.......... 100% Done.

Setup successful

Saving inventory (Tuesday, August 27, 2019 11:03:54 AM EDT)

Saving inventory complete

Configuration complete

End of install phases.(Tuesday, August 27, 2019 11:13:25 AM EDT)

WARNING:

The following configuration scripts need to be executed as the "root" user.

/a01/<dbname>/oracle/12.1.0.2/root.sh

To execute the configuration scripts:

1. Open a terminal window

2. Log in as "root"

3. Run the scripts

The cloning of dbhome_12cR2 was successful.

Please check '/a01/<dbname>/oracle/oraInventory/logs/cloneActions2019-08-27_10-39-24AM.log' for more details.

RECREATE PASSWORD FILE

cd $ORACLE_HOME/dbs

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=Oracle123 force=Y

ERROR :

[oracle@<dbname> ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 29 15:39:55 2019

ERROR:

ORA-01017: invalid username/password; logon denied

SOLUTION :

Cd $ORACLE_HOME/lib

edit config.c and remove group id which is pointing to source instance

.Ldgd_string: .string "dbawckhd1"

.Lkmt_string: .string "dbawckhd1"

cd $ORACLE_HOME/rdbms/lib
rm config.o
make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/config.o
$ORACLE_HOME/bin/relink all

RESTORE DATABASE;

[oracle@<dbname> dbs]$ rman target /

RMAN> set DBID=1680432204

RMAN> startup nomount pfile='/a01/<dbname>/oracle/12.1.0.2/dbs/init<dbname>.ora';

RMAN> restore controlfile from '/backup/krishna/<dbname>/cntrl_13_1_1017665992';

RMAN>alter database mount;

RMAN> catalog start with '/backup/krishna/<dbname>';

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

Source Database redo log alter commands

SQL> select 'alter database rename file '||''''|| member||''''|| ' to '|| ''''||'/a01/<dbname>/oradata/redo/'||SUBSTR(member, INSTR(member, '/', -1) + 1)||''''||';' from v$logfile;

Generate Set Newname Script using below syntax

select 'set newname for datafile '||file#||' to '||''''||'/a01/<dbname>/oradata/data/'||SUBSTR(name, INSTR(name, '/', -1) + 1)||''''||';' from v$datafile;

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

RMAN> execute redolog alter commands

RMAN>run

{

set newname for datafile 1 to '/a01/<dbname>/oradata/data/<dbname>/system01.dbf';

set newname for datafile 2 to '/a01/<dbname>/oradata/data/<dbname>/DEV_OCS.dbf';

set newname for datafile 3 to '/a01/<dbname>/oradata/data/<dbname>/sysaux01.dbf';

set newname for datafile 4 to '/a01/<dbname>/oradata/data/<dbname>/undotbs01.dbf';

set newname for datafile 5 to '/a01/<dbname>/oradata/data/<dbname>/example01.dbf';

set newname for datafile 6 to '/a01/<dbname>/oradata/data/<dbname>/users01.dbf';

set newname for datafile 7 to '/a01/<dbname>/oradata/data/<dbname>/DEV_OCS_1.dbf';

set newname for datafile 8 to '/a01/<dbname>/oradata/data/<dbname>/DEV_OCS_2.dbf';

set newname for datafile 9 to '/a01/<dbname>/oradata/data/<dbname>/DEV_OCS_3.dbf';

set newname for datafile 10 to '/a01/<dbname>/oradata/data/<dbname>/DEV_OCS_4.dbf';

set newname for datafile 11 to '/a01/<dbname>/oradata/data/<dbname>/DEV_MDS.dbf';

set newname for datafile 12 to '/a01/<dbname>/oradata/data/<dbname>/DEV_ORABAM.dbf';

set newname for datafile 13 to '/a01/<dbname>/oradata/data/<dbname>/DEV_SOAINFRA.dbf';

set newname for datafile 14 to '/a01/<dbname>/oradata/data/<dbname>/DEV_IPM.dbf';

set newname for datafile 15 to '/a01/<dbname>/oradata/data/<dbname>/DEV_IAS_ORASDPM.dbf';

set newname for datafile 16 to '/a01/<dbname>/oradata/data/<dbname>/DEV_URMSERVER.dbf';

set newname for datafile 17 to '/a01/<dbname>/oradata/data/<dbname>/DEV_ORAIRM';

set newname for datafile 18 to '/a01/<dbname>/oradata/data/<dbname>/DEV_OCSSEARCH';

set newname for datafile 19 to '/a01/<dbname>/oradata/data/<dbname>/DEV_CAPTURE.dbf';

set newname for datafile 20 to '/a01/<dbname>/oradata/data/<dbname>/DEV_CAPTURE_1.dbf';

set newname for datafile 21 to '/a01/<dbname>/oradata/data/<dbname>/DEV_CAPTURE_2.dbf';

restore database;

switch datafile all;

}

select name,open_mode from v$database;

alter system switch logfile;

select count (*) from v$datafile;

DROP TEMP TABLES Using below script and recreate them

Set lines 300

select 'ALTER DATABASE TEMPFILE '||''''||name||''''||' DROP;' from v$tempfile;

---------- RMAN DUPLICATE FAILED DUE TO MISSING ARCHIVELOGS ----------------

1. Restore and Apply the archive logs reported in rman duplicate

a. Check the required archive logs for recovery using the below query -- change the date/time with production backup start/end time

select distinct sequence#, thread# from gv$archived_log where completion_time

between to_date('**-**-**** **:00:00','dd-mm-yyyy hh24:mi:ss') and to_date('**-**-**** **:**:00','dd-mm-yyyy hh24:mi:ss')

order by thread#,sequence#;

b. On production server, Restore the archives to disk from tape -- Replace log sequence numbers from the above obtained values

Use multple sessions to speed up the process,for example 1 rman session for each thread etc.

è rman target=system/<password>@<sid>

run {

set archivelog destination to '/<sid>/backup/oracle/<sid>/oracle/<sid>1/arch_bkp';

allocate channel ch1 type 'sbt_tape';

allocate channel ch2 type 'sbt_tape';

allocate channel ch3 type 'sbt_tape';

allocate channel ch4 type 'sbt_tape';

allocate channel ch5 type 'sbt_tape';

allocate channel ch6 type 'sbt_tape';

allocate channel d1 type disk;

allocate channel d2 type disk;

restore archivelog from logseq ***** until logseq ***** thread 1;

restore archivelog from logseq ***** until logseq ***** thread 2;

restore archivelog from logseq ***** until logseq ***** thread 3;

}

c. Copy the files to the dev server -- you can use even more channels to perform the copy faster

cd <path>/arch

scp oracle@nodename:/<sid>/backup/oracle/<sid>/oracle/<sid>1/arch_bkp/arch_*_1_*.dbf . &

scp oracle@nodename:/<sid>/backup/oracle/<sid>/oracle/<sid>1/arch_bkp/arch_*_2_*.dbf . &

scp oracle@nodename:/<sid>/backup/oracle/<sid>/oracle/<sid>1/arch_bkp/arch_*_3_*.dbf . &

d. Apply the archive logs on dev instance

sqlplus '/ as sysdba'

SQL> alter system set log_archive_dest='<path>arch';

SQL> recover database using backup controlfile until cancel;

AUTO --- type auto to avoid entering for each log. it will apply all the logs present in the 'log_archive_dest' path

2. Create the controlfile trace

SQL> alter database backup controlfile to trace as '/home/oracle/temp/cotrol_fie_<sid>.txt';

3. Modify the above control file trace

a. Delete all the lines before "CREATE CONTROL FILE" statement.

b. Delete all the lines after "CHARACTER SET UTF8 ;" statement.

c. Modify "REUSE" TO "SET" database name from "<oldsid>" to "<newsid>" "NORESETLOGS" to "RESETLOGS" "ARCHIVELOG" to "NOARCHIVELOG"

d. delete the skipped datafile (which will be shown with source path)

grep -v "<sid>DB_VG/<sid>/datafile" control_file > control_file.1

e. Delete the lines for LOGFILE GROUP 7 to LOGFILE GROUP 18

f. Modify source path for LOGIFILE GROUP 1 TO LOGFILE GROUP 6 -- <sid>DB_VG/<sid>/onlinelog --> <sid>DB_VG/<sid>/ONLINELOG

:%s+<sid>DB_VG/<sid>/onlinelog+<sid>DB_VG/<sid>/ONLINELOG+g

%s+<sid>DB_VG/<sid>/onlinelog+<sid>DB_VG/<sid>/ONLINELOG+g

<sid>DB_VG/<sid>/ONLINELOG

4. Shutdown and startup in nomount status with rmanrestore init file

SQL> shutdown immediate

SQL> startup nomount pfile='$SID_HOME/pfile/init<sid>_rman_restore.ora';

startup nomount pfile='/data/dba/oracle/<sid>1/pfile/init<sid>_rman_restore.ora';

5. Remove the control files from asm

a. as grid user, run asmcmd

b. cd +<sid>DB_VG/<sid>/control

c. rm control*

6. Create the controlfile

SQL> @control_file.1

7. Open database using resetlogs

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

8. Create the redo logs for thread 2 and thread 3

SQL> alter database add logfile instance '<SID>2'

GROUP 7 ('+<SID>DB_VG/<SID>/ONLINELOG/redo01g7.log','+<SID>DB_VG/<SID>/ONLINELOG/redo02g7.log') SIZE 500M,

GROUP 8 ('+<SID>DB_VG/<SID>/ONLINELOG/redo01g8.log','+<SID>DB_VG/<SID>/ONLINELOG/redo02g8.log') SIZE 500M,

GROUP 9 ('+<SID>DB_VG/<SID>/ONLINELOG/redo01g9.log','+<SID>DB_VG/<SID>/ONLINELOG/redo02g9.log') SIZE 500M,

GROUP 10 ('+<SID>DB_VG/<SID>/ONLINELOG/redo01g10.log','+<SID>DB_VG/<SID>/ONLINELOG/redo02g10.log') SIZE 500M,

GROUP 11 ('+<SID>DB_VG/<SID>/ONLINELOG/redo01g11.log','+<SID>DB_VG/<SID>/ONLINELOG/redo02g11.log') SIZE 500M,

GROUP 12 ('+<SID>DB_VG/<SID>/ONLINELOG/redo01g12.log','+<SID>DB_VG/<SID>/ONLINELOG/redo02g12.log') SIZE 500M

Database altered.

SQL> alter database add logfile instance '<SID>3'

GROUP 13 ('+<SID>DB_VG/<SID>/ONLINELOG/redo01g13.log','+<SID>DB_VG/<SID>/ONLINELOG/redo02g13.log') SIZE 500M,

GROUP 14 ('+<SID>DB_VG/<SID>/ONLINELOG/redo01g14.log','+<SID>DB_VG/<SID>/ONLINELOG/redo02g14.log') SIZE 500M,

GROUP 15 ('+<SID>DB_VG/<SID>/ONLINELOG/redo01g15.log','+<SID>DB_VG/<SID>/ONLINELOG/redo02g15.log') SIZE 500M,

GROUP 16 ('+<SID>DB_VG/<SID>/ONLINELOG/redo01g16.log','+<SID>DB_VG/<SID>/ONLINELOG/redo02g16.log') SIZE 500M,

GROUP 17 ('+<SID>DB_VG/<SID>/ONLINELOG/redo01g17.log','+<SID>DB_VG/<SID>/ONLINELOG/redo02g17.log') SIZE 500M,

GROUP 18 ('+<SID>DB_VG/<SID>/ONLINELOG/redo01g18.log','+<SID>DB_VG/<SID>/ONLINELOG/redo02g18.log') SIZE 500M

Database altered.

SQL>

SQL> select group#,thread# from v$log;

GROUP# THREAD#

---------- ----------

1 1

2 1

3 1

4 1

5 1

6 1

7 2

8 2

9 2

10 2

11 2

GROUP# THREAD#

---------- ----------

12 2

13 3

14 3

15 3

16 3

17 3

18 3

18 rows selected.

SQL>

SQL> select thread#, instance from v$instance_log_group;

THREAD# INSTANCE

---------- --------------------------------------------------------------------------------

1 <SID>1

2 <SID>2

3 <SID>3

SQL>

9. Open the database using resetlogs

SQL> select name,dbid,open_mode from v$database;

NAME DBID OPEN_MODE

--------- ---------- --------------------

<SID> 1535115040 MOUNTED

SQL> alter database open resetlogs;

10. Add the tempfiles

SQL> select tablespace_name,file_name,sum(bytes)/1024/1024 from dba_temp_files group by tablespace_name,file_name;

no rows selected

SQL>

alter TABLESPACE TEMP add TEMPFILE '+<SID>DB_VG/<SID>/tempfile/temp01.dbf' SIZE 2048M AUTOEXTEND OFF;

alter TABLESPACE TEMP add TEMPFILE '+<SID>DB_VG/<SID>/tempfile/temp02.dbf' SIZE 2048M AUTOEXTEND OFF;

alter TABLESPACE TEMP add TEMPFILE '+<SID>DB_VG/<SID>/tempfile/temp03.dbf' SIZE 2048M AUTOEXTEND OFF;

alter TABLESPACE TEMP add TEMPFILE '+<SID>DB_VG/<SID>/tempfile/temp04.dbf' SIZE 2048M AUTOEXTEND OFF;

alter TABLESPACE TEMP add TEMPFILE '+<SID>DB_VG/<SID>/tempfile/temp05.dbf' SIZE 2048M AUTOEXTEND OFF;

alter TABLESPACE TEMP add TEMPFILE '+<SID>DB_VG/<SID>/tempfile/temp06.dbf' SIZE 2048M AUTOEXTEND OFF;

alter TABLESPACE TEMP add TEMPFILE '+<SID>DB_VG/<SID>/tempfile/temp07.dbf' SIZE 2048M AUTOEXTEND OFF;

alter TABLESPACE TEMP add TEMPFILE '+<SID>DB_VG/<SID>/tempfile/temp08.dbf' SIZE 2048M AUTOEXTEND OFF;

alter TABLESPACE TEMP add TEMPFILE '+<SID>DB_VG/<SID>/tempfile/temp09.dbf' SIZE 2048M AUTOEXTEND OFF;

alter TABLESPACE TEMP add TEMPFILE '+<SID>DB_VG/<SID>/tempfile/temp10.dbf' SIZE 2048M AUTOEXTEND OFF;

SQL> set lines 255

SQL> col file_name for a50

SQL> select tablespace_name,file_name,sum(bytes)/1024/1024 from dba_temp_files group by tablespace_name,file_name;

TABLESPACE_NAME FILE_NAME SUM(BYTES)/1024/1024

------------------------------ -------------------------------------------------- --------------------

TEMP +<SID>DB_VG/<SID>/tempfile/temp06.dbf 2048

TEMP +<SID>DB_VG/<SID>/tempfile/temp03.dbf 2048

TEMP +<SID>DB_VG/<SID>/tempfile/temp09.dbf 2048

TEMP +<SID>DB_VG/<SID>/tempfile/temp05.dbf 2048

TEMP +<SID>DB_VG/<SID>/tempfile/temp07.dbf 2048

TEMP +<SID>DB_VG/<SID>/tempfile/temp10.dbf 2048

TEMP +<SID>DB_VG/<SID>/tempfile/temp08.dbf 2048

TEMP +<SID>DB_VG/<SID>/tempfile/temp01.dbf 2048

TEMP +<SID>DB_VG/<SID>/tempfile/temp04.dbf 2048

TEMP +<SID>DB_VG/<SID>/tempfile/temp02.dbf 2048

10 rows selected.

11. Enable thread2 and thread3

SQL> alter database enable thread 2;

SQL> alter database enable thread 3;

12. Delete the spfile create by rman and create the spfile

a. cd $ORACLE_HOME/dbs

b. rm spfile<SID>1.ora

c. SQL> create spfile='+<sid>DB_VG/<sid>/SPFILE/spfile<sid>.ora' from pfile='/data/dba/oracle/<sid>1/pfile/init<sid>_b4_refresh.ora';

13. Enable RAC (Shutdown, add database to cluster and start up with spfile using srvctl)

a. SQL> shut immediate

b. Add database to cluster

srvctl status diskgroup -g <sid>DB_VG

srvctl add database -d <sid> -o /<sid>/oracle/orclbase/<sid>db/11.2.0.3 -a "<sid>DB_VG"

srvctl add instance -d <sid> -i <sid>1 -n nodename

srvctl add instance -d <sid> -i <sid>2 -n nodename

srvctl add instance -d <sid> -i <sid>3 -n nodename

srvctl add listener -l lstn<sid> -p 1532 -o /<sid>/oracle/orclbase/<sid>db/11.2.0.3

srvctl start database -d <sid>

srvctl status database -d <sid> -v

Ramakrishna Vejandla

Pages

Tuesday, December 22, 2015

RMAN RESTORE

No comments:

Post a Comment