In this article, we will see the steps to Restore, Recover and Rename RAC database using NID utility of Oracle.
Assume, Two node RAC,
Hosts are :
racexample1a
racexample1b
Source database name : hrdev
Target database name : hrprd
HRDEV database disk backup taken and transfered to this target server. Assume the backup location of target server is :
/u03/backups/hrdev
Source and Target database version : 12.1.0.2.0 ( Oracle 12c)
Step-1: Create a temporary parameter file like below in one of node:
$cd $ORACLE_HOME/dbs
$ vi inithrdev.ora
*.audit_file_dest='/u01/app/oracle/admin/hrdev/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='12.1.0.2.0'
#*.control_files='+DATA/HRDEV/CONTROLFILE/Current.555.2008648327','+DATA/HRDEV/CONTROLFILE/Current.556.2008648327'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='hrdev'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=1024g
*.db_unique_name='hrdev'
*.diagnostic_dest='/u01/app/oracle'
hrdev1.instance_number=1
hrdev2.instance_number=2
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=18G
*.open_cursors=1000
*.processes=1000
*.remote_listener='racexample1:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=1000
hrdev1.thread=1
hrdev2.thread=2
hrdev1.undo_tablespace='UNDOTBS1'
hrdev2.undo_tablespace='UNDOTBS2'
Step:2 : You can create alias in your bash_profile to avoid multiple time exports:
export GRID_HOME=/u01/app/12.1.0.2/grid
export DB_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
alias grid='export ORACLE_SID=+ASM1;unset ORACLE_UNQNAME;export ORACLE_HOME=$GRID_HOME;PATH=$ORACLE_HOME/bin:/bin:/usr/bin'
alias hrdev='export ORACLE_SID=hrdev1;export ORACLE_UNQNAME=hrdev;export ORACLE_HOME=$DB_HOME;export PATH=$ORACLE_HOME/bin:/bin:/usr/bin'
$ . .bash_profile
Step-3: Start the instance in nomount using temporary pfile:
$ hrdev
$ cd $ORACLE_HOME/dbs
$ sqlplus / as sysdba
SQL> startup nomount pfile='inithrdev.ora';
Step-4 : List the backup pieces
Go to the backup location and list out all the backup pices:
$ cd /u03/backups/hrdev
$ ls -lrt | awk '{print $9}'
hrdev_19062019_full_al_1vu4hct9_1_1.bkp
hrdev_19062019_full_cf.bkp
hrdev_19062019_full_db_1tu4hbto_1_1.bkp
hrdev_19062019_full_pf.bkp
hrdev_19062019_full_db_1uu4hcss_1_1.bkp
hrdev_19062019_full_tf.bkp
hrdev_19062019_full_sp.bkp
hrdev_19062019_full_sf.bkp
Step-5 : Restore the controlfile
racexample1a ~]$ hrdev
racexample1a ~]$ rman target /
RMAN>
RMAN> set dbid 3665552429
executing command: SET DBID
RMAN> restore controlfile from "/u03/backups/hrdev/hrdev_19062019_full_cf.bkp";
Starting restore at 20-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1338 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output file name=+DATA/HRDEV/CONTROLFILE/current.928.1011408689
output file name=+DATA/HRDEV/CONTROLFILE/current.925.1011408689
Finished restore at 20-JUN-19
RMAN>
Step-6 : Restore the database
SQL> alter database mount;
Database altered.
SQL>
RMAN> allocate channel c1 device type disk;
RMAN> catalog start with '/u03/backups/hrdev';
RMAN>
RUN
{
allocate channel ch1 type disk;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
}
Output:
RMAN> RUN
{
allocate channel ch1 type disk;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
}2> 3> 4> 5> 6>
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=1338 device type=DISK
Starting restore at 20-JUN-19
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to +DATA/HRDEV/DATAFILE/system.846.940328191
channel ch1: restoring datafile 00002 to +DATA/HRDEV/DATAFILE/sysaux.852.940328101
channel ch1: restoring datafile 00003 to +DATA/HRDEV/DATAFILE/undotbs1.884.940326787
channel ch1: restoring datafile 00004 to +DATA/HRDEV/DATAFILE/undotbs2.883.940326787
channel ch1: restoring datafile 00005 to +DATA/HRDEV/DATAFILE/users.848.940328187
....
channel ch1: restoring datafile 00080 to +DATA/HRDEV/DATAFILE/hr_data.839.940328213
channel ch1: restoring datafile 00081 to +DATA/HRDEV/DATAFILE/hr_index.850.940328147
channel ch1: reading from backup piece /u03/backups/hrdev/hrdev_19062019_full_db_1tu4hbto_1_1.bkp
channel ch1: piece handle=/u03/backups/hrdev/hrdev_19062019_full_db_1tu4hbto_1_1.bkp tag=19-JUN-2019_23-28-42_FULL_DB
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:30:55
Finished restore at 20-JUN-19
released channel: ch1
RMAN>
Step-7 : Recover the database
RMAN> RECOVER DATABASE;
Starting recover at 20-JUN-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1338 device type=DISK
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=3398
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6102
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6103
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=3399
channel ORA_DISK_1: reading from backup piece /u03/backups/hrdev/hrdev_19062019_full_al_1vu4hct9_1_1.bkp
channel ORA_DISK_1: piece handle=/u03/backups/hrdev/hrdev_19062019_full_al_1vu4hct9_1_1.bkp tag=19-JUN-2019_23-28-42_FULL_AL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_1_seq_6102.789.1011411463 thread=1 sequence=6102
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_2_seq_3398.545.1011411463 thread=2 sequence=3398
channel default: deleting archived log(s)
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_1_seq_6102.789.1011411463 RECID=34952 STAMP=1011411462
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_1_seq_6103.1563.1011411463 thread=1 sequence=6103
channel default: deleting archived log(s)
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_2_seq_3398.545.1011411463 RECID=34953 STAMP=1011411462
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_2_seq_3399.1580.1011411463 thread=2 sequence=3399
channel default: deleting archived log(s)
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_2_seq_3399.1580.1011411463 RECID=34955 STAMP=1011411463
unable to find archived log
archived log thread=2 sequence=3400
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/20/2019 03:37:47
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 3400 and starting SCN of 11981117434031
RMAN>
Step-8 : Open the database
RMAN> ALTER DATABASE OPEN RESETLOGS;
Statement processed
RMAN>
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
HRPRD READ WRITE
SQL> !hostname
racexample1a.oracle.com
SQL>
SQL> select name ,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
HRPRD READ WRITE
SQL> alter user sys identified by Pa$$w0rd; -- create the passwordfile as well
User altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
SQL> exit
Step-9 : Proceed to rename the database
$nid TARGET=sys/Pa$$w0rd dbname=hrprd
output:
----------
[oracle@racexample1a dbs]$ nid TARGET=sys/Pa$$w0rd dbname=hrprd
DBNEWID: Release 12.1.0.2.0 - Production on Thu Jun 20 04:17:32 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to database HRDEV (DBID=3665552429)
Connected to server version 12.1.0
Control Files in database:
+DATA/HRDEV/CONTROLFILE/current.928.1011408689
+DATA/HRDEV/CONTROLFILE/current.925.1011408689
Change database ID and database name HRDEV to HRPRD? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 3665552429 to 4195710300
Changing database name from HRDEV to HRPRD
Control File +DATA/HRDEV/CONTROLFILE/current.928.1011408689 - modified
Control File +DATA/HRDEV/CONTROLFILE/current.925.1011408689 - modified
Datafile +DATA/HRDEV/DATAFILE/system.1418.101141110 - dbid changed, wrote new name
Datafile +DATA/HRDEV/DATAFILE/sysaux.1339.101141092 - dbid changed, wrote new name
Datafile +DATA/HRDEV/DATAFILE/undotbs1.933.101140936 - dbid changed, wrote new name
Datafile +DATA/HRDEV/DATAFILE/undotbs2.1006.101140936 - dbid changed, wrote new name
Datafile +DATA/HRDEV/DATAFILE/users.1420.101141113 - dbid changed, wrote new name
...
Datafile +DATA/HRDEV/DATAFILE/hr_data.959.101141116 - dbid changed, wrote new name
Datafile +DATA/HRDEV/DATAFILE/hr_index.1411.101141108 - dbid changed, wrote new name
Datafile +DATA/HRDEV/TEMPFILE/temp.778.101141163 - dbid changed, wrote new name
Control File +DATA/HRDEV/CONTROLFILE/current.928.1011408689 - dbid changed, wrote new name
Control File +DATA/HRDEV/CONTROLFILE/current.925.1011408689 - dbid changed, wrote new name
Instance shut down
Database name changed to HRPRD.
Modify parameter file and generate a new password file before restarting.
Database ID for database HRPRD changed to 4195710300.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
[oracle@racexample1a dbs]$
Step-10 : Create the new directories in ASM
[oracle@racexample1a dbs]$ grid
[oracle@racexample1a dbs]$ env | grep ORA
ORACLE_SID=+ASM1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/12.1.0.2/grid
[oracle@racexample1a ]$ sqlplus / as sysasm
SQL>
SQL> alter diskgroup DATA add directory '+DATA/HRPRD';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/DATAFILE';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/TEMPFILE';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/PARAMETERFILE';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/ONLINELOG';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/CONTROLFILE';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/ARCHIVELOG';
-- List all files what are restored recently
$ asmcmd -p
ASMCMD [+DATA/HRDEV/CONTROLFILE] > ls
current.925.1011408689
current.928.1011408689
ASMCMD [+DATA/HRDEV/ONLINELOG] > ls
group_11.1562.1011411631
group_11.542.1011411631
...
group_5.1788.1011411629
group_5.789.1011411629
group_6.1433.1011411631
group_6.773.1011411629
ASMCMD [+DATA/HRDEV/TEMPFILE] > ls
TEMP.778.1011411637
ASMCMD [+DATA/HRDEV/DATAFILE] > ls
HR_DATA.959.101141116
HR_INDEX.1411.101141108
..
SYSAUX.1339.1011410927
SYSTEM.1418.1011411107
UNDOTBS1.933.1011409367
UNDOTBS2.1006.1011409367
USERS.1420.1011411135
ASMCMD [+DATA/HRDEV/DATAFILE] >
Step-11 : Edit the parameter file and change old database name with new one. Create new alias in bash_profile like above
$ vi inithrprd1.ora
– Save and Exit
$ vi .bash_profile
alias hrprd='export ORACLE_SID=hrprd1;export ORACLE_UNQNAME=hrprd;export ORACLE_HOME=$DB_HOME;export PATH=$ORACLE_HOME/bin:/bin:/usr/bin'
$ . .bash_profile
$ hrprd
[oracle@racexample1a dbs]$ env | grep ORA
ORACLE_UNQNAME=hrprd
ORACLE_SID=hrprd1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
[oracle@racexample1a dbs]$ sqlplus / as sysdba
SQL> startup nomount pfile='inithrprd';
ORACLE instance started.
Step-12 : In ASM copy the control file from hrdev directory to /tmp
ASMCMD [+DATA/HRDEV/CONTROLFILE] > cp +DATA/HRDEV/CONTROLFILE/current.925.1011408689 /tmp
Step-13 : Copy the restored control file to target location
[oracle@racexample1a dbs]$ rman target /
RMAN> restore controlfile from '/tmp/current.925.1011408689';
Starting restore at 20-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1339 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/HRPRD/CONTROLFILE/current.1035.1011433041
output file name=+DATA/HRPRD/CONTROLFILE/current.1038.1011433041
Finished restore at 20-JUN-19
RMAN> quit
Recovery Manager complete.
$
Step-14 : Update the control file location in target parameter file ( inithrprd.ora) and start the database in mount state
already your instance in nomout state
$ sqlplus / as sysdba
SQL> alter database mount;
SQL> alter database open RESETLOGS;
Database altered.
SQL>
step-15 : Oracle 12c onwards, you can move datafiles to new ASM location wven datafiles status 'ONLINE'.
So move all datafiles, redo files to new locations.
SQL>
alter database move datafile '+DATA/HRDEV/DATAFILE/system.1418.1011411107';
alter database move datafile '+DATA/HRDEV/DATAFILE/sysaux.1339.1011410927';
alter database move datafile '+DATA/HRDEV/DATAFILE/undotbs1.933.1011409367';
alter database move datafile '+DATA/HRDEV/DATAFILE/undotbs2.1006.1011409367';
alter database move datafile '+DATA/HRDEV/DATAFILE/users.1420.1011411135';
...
alter database move datafile '+DATA/HRDEV/DATAFILE/hr_data.959.1011411169';
alter database move datafile '+DATA/HRDEV/DATAFILE/hr_index.1411.1011411081';
Same way you can move all redo logs also:
e.g.,
alter database move logfile 'group_11.1562.1011411631';
alter database move logfile 'group_11.542.1011411631';
....
Note: As db_create_file_dest='+DATA' set in parameter file, here target location is not necessary. Automatically it will move to respective locations as per the RAC feature.
Ensure all files moved to new locations.
Now shut down the database and startup.
SQL> shut immediate;
SQL> startup;
Step-16 : Check location of all files once the database is opened
SQL> select name from v$datafile;
SQL> select name from v$tempfile;
SQL> select name from v$controlfile;
SQL> select member from v$logfile;
SQL> select * from v$recover_file;
no rows selected
SQL> select distinct status from v$datafile;
STATUS
-------
ONLINE
Step-17 : create new parameter file in ASM location and make enable cluster_database as 'TRUE'
SQL> create spfile='+DATA' from pfile;
SQL> shut immediate;
SQL> startup;
SQL> alter system set cluster_database=TRUE scope=spfile;
SQL> shut immediate;
SQL> startup;
Step-18 : Configure database and instances to cluster
$ srvctl add database -d hrprd -o /u01/app/oracle/product/12.1.0.2/dbhome_1
$ srvctl add instance -d hrprd -i hrprd1 -n racexample1a
$ srvctl add instance -d hrprd -i hrprd2 -n racexample1b
Step-19 : Stop the instance and create init parameter file on both nodes as shown below
$ sqlplus / as sysdba
SQL> shutdown immediate
[Rename exisitng init parameter file and spfile]
$ mv inithrprd1.ora inithrprd1.ora_orig
$ vi inithrprd1.ora
SPFILE='+DATA/HRPRD/PARAMETERFILE/spfile.1411.1011437099'
[Save and Exit]
[SCP the file on node2]
$ scp inithrprd1.ora racexample1b:$ORACLE_HOME/dbs/inithrprd2.ora
Step-20 : Start the database using srvctl utility
$ srvctl start database -d hrprd
$ srvctl status database -d hrprd
Instance hrprd1 is running on node racexample1a
Instance hrprd2 is running on node racexample1b
$ srvctl start database -d hrprd
$ srvctl status database -d hrprd
Instance hrprd1 is running on node racexample1a
Instance hrprd2 is running on node racexample1b
Assume, Two node RAC,
Hosts are :
racexample1a
racexample1b
Source database name : hrdev
Target database name : hrprd
HRDEV database disk backup taken and transfered to this target server. Assume the backup location of target server is :
/u03/backups/hrdev
Source and Target database version : 12.1.0.2.0 ( Oracle 12c)
Step-1: Create a temporary parameter file like below in one of node:
$cd $ORACLE_HOME/dbs
$ vi inithrdev.ora
*.audit_file_dest='/u01/app/oracle/admin/hrdev/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='12.1.0.2.0'
#*.control_files='+DATA/HRDEV/CONTROLFILE/Current.555.2008648327','+DATA/HRDEV/CONTROLFILE/Current.556.2008648327'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='hrdev'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=1024g
*.db_unique_name='hrdev'
*.diagnostic_dest='/u01/app/oracle'
hrdev1.instance_number=1
hrdev2.instance_number=2
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=18G
*.open_cursors=1000
*.processes=1000
*.remote_listener='racexample1:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=1000
hrdev1.thread=1
hrdev2.thread=2
hrdev1.undo_tablespace='UNDOTBS1'
hrdev2.undo_tablespace='UNDOTBS2'
Step:2 : You can create alias in your bash_profile to avoid multiple time exports:
export GRID_HOME=/u01/app/12.1.0.2/grid
export DB_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
alias grid='export ORACLE_SID=+ASM1;unset ORACLE_UNQNAME;export ORACLE_HOME=$GRID_HOME;PATH=$ORACLE_HOME/bin:/bin:/usr/bin'
alias hrdev='export ORACLE_SID=hrdev1;export ORACLE_UNQNAME=hrdev;export ORACLE_HOME=$DB_HOME;export PATH=$ORACLE_HOME/bin:/bin:/usr/bin'
$ . .bash_profile
Step-3: Start the instance in nomount using temporary pfile:
$ hrdev
$ cd $ORACLE_HOME/dbs
$ sqlplus / as sysdba
SQL> startup nomount pfile='inithrdev.ora';
Step-4 : List the backup pieces
Go to the backup location and list out all the backup pices:
$ cd /u03/backups/hrdev
$ ls -lrt | awk '{print $9}'
hrdev_19062019_full_al_1vu4hct9_1_1.bkp
hrdev_19062019_full_cf.bkp
hrdev_19062019_full_db_1tu4hbto_1_1.bkp
hrdev_19062019_full_pf.bkp
hrdev_19062019_full_db_1uu4hcss_1_1.bkp
hrdev_19062019_full_tf.bkp
hrdev_19062019_full_sp.bkp
hrdev_19062019_full_sf.bkp
Step-5 : Restore the controlfile
racexample1a ~]$ hrdev
racexample1a ~]$ rman target /
RMAN>
RMAN> set dbid 3665552429
executing command: SET DBID
RMAN> restore controlfile from "/u03/backups/hrdev/hrdev_19062019_full_cf.bkp";
Starting restore at 20-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1338 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output file name=+DATA/HRDEV/CONTROLFILE/current.928.1011408689
output file name=+DATA/HRDEV/CONTROLFILE/current.925.1011408689
Finished restore at 20-JUN-19
RMAN>
Step-6 : Restore the database
SQL> alter database mount;
Database altered.
SQL>
RMAN> allocate channel c1 device type disk;
RMAN> catalog start with '/u03/backups/hrdev';
RMAN>
RUN
{
allocate channel ch1 type disk;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
}
Output:
RMAN> RUN
{
allocate channel ch1 type disk;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
}2> 3> 4> 5> 6>
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=1338 device type=DISK
Starting restore at 20-JUN-19
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to +DATA/HRDEV/DATAFILE/system.846.940328191
channel ch1: restoring datafile 00002 to +DATA/HRDEV/DATAFILE/sysaux.852.940328101
channel ch1: restoring datafile 00003 to +DATA/HRDEV/DATAFILE/undotbs1.884.940326787
channel ch1: restoring datafile 00004 to +DATA/HRDEV/DATAFILE/undotbs2.883.940326787
channel ch1: restoring datafile 00005 to +DATA/HRDEV/DATAFILE/users.848.940328187
....
channel ch1: restoring datafile 00080 to +DATA/HRDEV/DATAFILE/hr_data.839.940328213
channel ch1: restoring datafile 00081 to +DATA/HRDEV/DATAFILE/hr_index.850.940328147
channel ch1: reading from backup piece /u03/backups/hrdev/hrdev_19062019_full_db_1tu4hbto_1_1.bkp
channel ch1: piece handle=/u03/backups/hrdev/hrdev_19062019_full_db_1tu4hbto_1_1.bkp tag=19-JUN-2019_23-28-42_FULL_DB
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:30:55
Finished restore at 20-JUN-19
released channel: ch1
RMAN>
Step-7 : Recover the database
RMAN> RECOVER DATABASE;
Starting recover at 20-JUN-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1338 device type=DISK
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=3398
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6102
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6103
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=3399
channel ORA_DISK_1: reading from backup piece /u03/backups/hrdev/hrdev_19062019_full_al_1vu4hct9_1_1.bkp
channel ORA_DISK_1: piece handle=/u03/backups/hrdev/hrdev_19062019_full_al_1vu4hct9_1_1.bkp tag=19-JUN-2019_23-28-42_FULL_AL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_1_seq_6102.789.1011411463 thread=1 sequence=6102
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_2_seq_3398.545.1011411463 thread=2 sequence=3398
channel default: deleting archived log(s)
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_1_seq_6102.789.1011411463 RECID=34952 STAMP=1011411462
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_1_seq_6103.1563.1011411463 thread=1 sequence=6103
channel default: deleting archived log(s)
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_2_seq_3398.545.1011411463 RECID=34953 STAMP=1011411462
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_2_seq_3399.1580.1011411463 thread=2 sequence=3399
channel default: deleting archived log(s)
archived log file name=+DATA/HRDEV/ARCHIVELOG/2019_06_20/thread_2_seq_3399.1580.1011411463 RECID=34955 STAMP=1011411463
unable to find archived log
archived log thread=2 sequence=3400
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/20/2019 03:37:47
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 3400 and starting SCN of 11981117434031
RMAN>
Step-8 : Open the database
RMAN> ALTER DATABASE OPEN RESETLOGS;
Statement processed
RMAN>
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
HRPRD READ WRITE
SQL> !hostname
racexample1a.oracle.com
SQL>
SQL> select name ,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
HRPRD READ WRITE
SQL> alter user sys identified by Pa$$w0rd; -- create the passwordfile as well
User altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
SQL> exit
Step-9 : Proceed to rename the database
$nid TARGET=sys/Pa$$w0rd dbname=hrprd
output:
----------
[oracle@racexample1a dbs]$ nid TARGET=sys/Pa$$w0rd dbname=hrprd
DBNEWID: Release 12.1.0.2.0 - Production on Thu Jun 20 04:17:32 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to database HRDEV (DBID=3665552429)
Connected to server version 12.1.0
Control Files in database:
+DATA/HRDEV/CONTROLFILE/current.928.1011408689
+DATA/HRDEV/CONTROLFILE/current.925.1011408689
Change database ID and database name HRDEV to HRPRD? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 3665552429 to 4195710300
Changing database name from HRDEV to HRPRD
Control File +DATA/HRDEV/CONTROLFILE/current.928.1011408689 - modified
Control File +DATA/HRDEV/CONTROLFILE/current.925.1011408689 - modified
Datafile +DATA/HRDEV/DATAFILE/system.1418.101141110 - dbid changed, wrote new name
Datafile +DATA/HRDEV/DATAFILE/sysaux.1339.101141092 - dbid changed, wrote new name
Datafile +DATA/HRDEV/DATAFILE/undotbs1.933.101140936 - dbid changed, wrote new name
Datafile +DATA/HRDEV/DATAFILE/undotbs2.1006.101140936 - dbid changed, wrote new name
Datafile +DATA/HRDEV/DATAFILE/users.1420.101141113 - dbid changed, wrote new name
...
Datafile +DATA/HRDEV/DATAFILE/hr_data.959.101141116 - dbid changed, wrote new name
Datafile +DATA/HRDEV/DATAFILE/hr_index.1411.101141108 - dbid changed, wrote new name
Datafile +DATA/HRDEV/TEMPFILE/temp.778.101141163 - dbid changed, wrote new name
Control File +DATA/HRDEV/CONTROLFILE/current.928.1011408689 - dbid changed, wrote new name
Control File +DATA/HRDEV/CONTROLFILE/current.925.1011408689 - dbid changed, wrote new name
Instance shut down
Database name changed to HRPRD.
Modify parameter file and generate a new password file before restarting.
Database ID for database HRPRD changed to 4195710300.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
[oracle@racexample1a dbs]$
Step-10 : Create the new directories in ASM
[oracle@racexample1a dbs]$ grid
[oracle@racexample1a dbs]$ env | grep ORA
ORACLE_SID=+ASM1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/12.1.0.2/grid
[oracle@racexample1a ]$ sqlplus / as sysasm
SQL>
SQL> alter diskgroup DATA add directory '+DATA/HRPRD';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/DATAFILE';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/TEMPFILE';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/PARAMETERFILE';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/ONLINELOG';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/CONTROLFILE';
SQL> alter diskgroup DATA add directory '+DATA/HRPRD/ARCHIVELOG';
-- List all files what are restored recently
$ asmcmd -p
ASMCMD [+DATA/HRDEV/CONTROLFILE] > ls
current.925.1011408689
current.928.1011408689
ASMCMD [+DATA/HRDEV/ONLINELOG] > ls
group_11.1562.1011411631
group_11.542.1011411631
...
group_5.1788.1011411629
group_5.789.1011411629
group_6.1433.1011411631
group_6.773.1011411629
ASMCMD [+DATA/HRDEV/TEMPFILE] > ls
TEMP.778.1011411637
ASMCMD [+DATA/HRDEV/DATAFILE] > ls
HR_DATA.959.101141116
HR_INDEX.1411.101141108
..
SYSAUX.1339.1011410927
SYSTEM.1418.1011411107
UNDOTBS1.933.1011409367
UNDOTBS2.1006.1011409367
USERS.1420.1011411135
ASMCMD [+DATA/HRDEV/DATAFILE] >
Step-11 : Edit the parameter file and change old database name with new one. Create new alias in bash_profile like above
$ vi inithrprd1.ora
– Save and Exit
$ vi .bash_profile
alias hrprd='export ORACLE_SID=hrprd1;export ORACLE_UNQNAME=hrprd;export ORACLE_HOME=$DB_HOME;export PATH=$ORACLE_HOME/bin:/bin:/usr/bin'
$ . .bash_profile
$ hrprd
[oracle@racexample1a dbs]$ env | grep ORA
ORACLE_UNQNAME=hrprd
ORACLE_SID=hrprd1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
[oracle@racexample1a dbs]$ sqlplus / as sysdba
SQL> startup nomount pfile='inithrprd';
ORACLE instance started.
Step-12 : In ASM copy the control file from hrdev directory to /tmp
ASMCMD [+DATA/HRDEV/CONTROLFILE] > cp +DATA/HRDEV/CONTROLFILE/current.925.1011408689 /tmp
Step-13 : Copy the restored control file to target location
[oracle@racexample1a dbs]$ rman target /
RMAN> restore controlfile from '/tmp/current.925.1011408689';
Starting restore at 20-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1339 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/HRPRD/CONTROLFILE/current.1035.1011433041
output file name=+DATA/HRPRD/CONTROLFILE/current.1038.1011433041
Finished restore at 20-JUN-19
RMAN> quit
Recovery Manager complete.
$
Step-14 : Update the control file location in target parameter file ( inithrprd.ora) and start the database in mount state
already your instance in nomout state
$ sqlplus / as sysdba
SQL> alter database mount;
SQL> alter database open RESETLOGS;
Database altered.
SQL>
step-15 : Oracle 12c onwards, you can move datafiles to new ASM location wven datafiles status 'ONLINE'.
So move all datafiles, redo files to new locations.
SQL>
alter database move datafile '+DATA/HRDEV/DATAFILE/system.1418.1011411107';
alter database move datafile '+DATA/HRDEV/DATAFILE/sysaux.1339.1011410927';
alter database move datafile '+DATA/HRDEV/DATAFILE/undotbs1.933.1011409367';
alter database move datafile '+DATA/HRDEV/DATAFILE/undotbs2.1006.1011409367';
alter database move datafile '+DATA/HRDEV/DATAFILE/users.1420.1011411135';
...
alter database move datafile '+DATA/HRDEV/DATAFILE/hr_data.959.1011411169';
alter database move datafile '+DATA/HRDEV/DATAFILE/hr_index.1411.1011411081';
Same way you can move all redo logs also:
e.g.,
alter database move logfile 'group_11.1562.1011411631';
alter database move logfile 'group_11.542.1011411631';
....
Note: As db_create_file_dest='+DATA' set in parameter file, here target location is not necessary. Automatically it will move to respective locations as per the RAC feature.
Ensure all files moved to new locations.
Now shut down the database and startup.
SQL> shut immediate;
SQL> startup;
Step-16 : Check location of all files once the database is opened
SQL> select name from v$datafile;
SQL> select name from v$tempfile;
SQL> select name from v$controlfile;
SQL> select member from v$logfile;
SQL> select * from v$recover_file;
no rows selected
SQL> select distinct status from v$datafile;
STATUS
-------
ONLINE
Step-17 : create new parameter file in ASM location and make enable cluster_database as 'TRUE'
SQL> create spfile='+DATA' from pfile;
SQL> shut immediate;
SQL> startup;
SQL> alter system set cluster_database=TRUE scope=spfile;
SQL> shut immediate;
SQL> startup;
Step-18 : Configure database and instances to cluster
$ srvctl add database -d hrprd -o /u01/app/oracle/product/12.1.0.2/dbhome_1
$ srvctl add instance -d hrprd -i hrprd1 -n racexample1a
$ srvctl add instance -d hrprd -i hrprd2 -n racexample1b
Step-19 : Stop the instance and create init parameter file on both nodes as shown below
$ sqlplus / as sysdba
SQL> shutdown immediate
[Rename exisitng init parameter file and spfile]
$ mv inithrprd1.ora inithrprd1.ora_orig
$ vi inithrprd1.ora
SPFILE='+DATA/HRPRD/PARAMETERFILE/spfile.1411.1011437099'
[Save and Exit]
[SCP the file on node2]
$ scp inithrprd1.ora racexample1b:$ORACLE_HOME/dbs/inithrprd2.ora
Step-20 : Start the database using srvctl utility
$ srvctl start database -d hrprd
$ srvctl status database -d hrprd
Instance hrprd1 is running on node racexample1a
Instance hrprd2 is running on node racexample1b
$ srvctl start database -d hrprd
$ srvctl status database -d hrprd
Instance hrprd1 is running on node racexample1a
Instance hrprd2 is running on node racexample1b
you missed tempfiles
ReplyDeleteExcellent one. Nice document.
ReplyDelete