RMAN database restore from lower version (11.2.0.1) to 11.2.0.4 by using upgrade in Oracle database 11gR2
Posted by Mir Sayeed Hassan on February 25th, 2018
RMAN database restore from lower version (11.2.0.1) to 11.2.0.4 by using upgrade in Oracle database 11gR2
Copy the datafiles, archivelog files and controlfile from real database to restore backup location by using the NC or Winscp or any suitable method
First verify the real database version:
[oracle@realdb backup]$ !sq sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sun Feb 25 14:37:06 2018 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select INSTANCE_NAME,VERSION from v$instance; INSTANCE_NAME VERSION ---------------- ----------------- realdb 11.2.0.1.0
In my case, I have copied all the datafile,archivelog files, controlfile from real database to restore database by using nc command as shown below:
Source database
[root@realdb backup]# nc 192.168.110.109 1234 < df_REALDB_20180225_3336.bck [root@realdb backup]# nc 192.168.110.109 1234 < arch_REALDB_20180225_3337.bck [root@realdb backup]# nc 192.168.110.109 1234 < ctlfileL0_REALDB_20180225.bck
Verify Size of files transfer from real db:
[root@realdb fulldb_daily_bck]# du -h df_REALDB_20180225_3336.bck arch_REALDB_20180225_3337.bck ctlfileL0_REALDB_20180225.bck 2.2G df_REALDB_20180225_3336.bck 177M arch_REALDB_20180225_3337.bck 13M ctlfileL0_REALDB_20180225.bck
Destination database (Restore database)
[root@DB backup]# nc -l 1234 < df_REALDB_20180225_3336.bck [root@DB backup]# nc -l 1234 > arch_REALDB_20180225_3337.bck [root@DB backup]# nc -l 1234 > ctlfileL0_REALDB_20180225.bck
Verify Size of files transfer in restore db
[root@DB backup]# du -h df_REALDB_20180225_3336.bck arch_REALDB_20180225_3337.bck ctlfileL0_REALDB_20180225.bck
2.2G df_REALDB_20180225_3336.bck
177M arch_REALDB_20180225_3337.bck
13M ctlfileL0_REALDB_20180225.bck
Step by step procedure to restore database:
Set the ORACLE_SID, ORACLE_HOME etc in . bash_profile as shown below
[oracle@DB ~]$ pwd /home/oracle
[oracle@DB ~]$ cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH # Oracle Settings TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME ORACLE_SID=realdb; export ORACLE_SID ORACLE_HOME_LISTNER=$ORACLE_HOME; export ORACLE_HOME_LISTNER ORACLE_TERM=xterm; export ORACLE_TERM TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11 PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib; export CLASSPATH if [ $USER = "oracle" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi fi umask 022
Run the bash profile
[oracle@DB ~]$ . .bash_profile
[oracle@DB ~]$ echo $ORACLE_SID realdb
Startup the database in nomount to restore the controlfile
[oracle@DB backup]$ !sq sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 25 14:53:29 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance.
SQL> startup nomount ORACLE instance started. Total System Global Area 350777344 bytes Fixed Size 2253104 bytes Variable Size 293605072 bytes Database Buffers 50331648 bytes Redo Buffers 4587520 bytes
Assign the permission to the backup of files
[root@DB backup]# ls arch_REALDB_20180225_3337.bck ctlfileL0_REALDB_20180225.bck df_REALDB_20180225_3336.bck
[root@DB backup]# chmod -R 775 *
[root@DB backup]# ll total 2441860 -rwxrwxr-x. 1 root root 184598528 Feb 25 14:48 arch_REALDB_20180225_3337.bck -rwxrwxr-x. 1 root root 12746752 Feb 25 14:49 ctlfileL0_REALDB_20180225.bck -rwxrwxr-x. 1 root root 2303115264 Feb 25 14:47 df_REALDB_20180225_3336.bck
Switch to Oracle user & start the RMAN Process
[root@DB backup]# su - oracle
Restore the controlfile from backup location:
[oracle@DB backup]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Sun Feb 25 14:58:09 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: REALDB (not mounted) RMAN> restore controlfile from '/oradata/backup/ctlfileL0_REALDB_20180225.bck'; Starting restore at 25-FEB-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/oradata/realdb/control01.ctl Finished restore at 25-FEB-18
Mount the database:
RMAN> alter database mount; database mounted released channel: ORA_DISK_1
Start the restore database:
RMAN> run {
>set newname for database to '/oradata/datafiles/%b';
>restore database;
>switch datafile all;
>}
executing command: SET NEWNAME
Starting restore at 25-FEB-18
Starting implicit crosscheck backup at 25-FEB-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 55 objects
Finished implicit crosscheck backup at 25-FEB-18
Starting implicit crosscheck copy at 25-FEB-18
using channel ORA_DISK_1
Finished implicit crosscheck copy at 25-FEB-18
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/25/2018 14:59:21
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore ----Error
In the above error, its due to the uncatalog the backup files, therefore catalog the backup files
RMAN> catalog start with '/oradata/backup/'; searching for all files that match the pattern /oradata/backup/ List of Files Unknown to the Database ===================================== File Name: /oradata/backup/arch_REALDB_20180225_3337.bck File Name: /oradata/backup/df_REALDB_20180225_3336.bck File Name: /oradata/backup/ctlfileL0_REALDB_20180225.bck Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done List of Cataloged Files ======================= File Name: /oradata/backup/arch_REALDB_20180225_3337.bck File Name: /oradata/backup/df_REALDB_20180225_3336.bck File Name: /oradata/backup/ctlfileL0_REALDB_20180225.bck
Crosscheck the current backup in restore database
RMAN> delete expired backup; ----------- 3336 3336 1 1 EXPIRED DISK /u01/backup/rman/fulldb_daily_bck/arch_REALDB_20180225_3337.bck 3337 3337 1 1 EXPIRED DISK /u01/backup/rman/fulldb_daily_bck/spf_REALDB_20180225_3338.bck Do you really want to delete the above objects (enter YES or NO)? yes
RMAN> crosscheck backup; using channel ORA_DISK_1 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/oradata/backup/df_REALDB_20180225_3336.bck RECID=3339 STAMP=969029995 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/oradata/backup/arch_REALDB_20180225_3337.bck RECID=3338 STAMP=969029994 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/oradata/backup/ctlfileL0_REALDB_20180225.bck RECID=3340 STAMP=969029995 Crosschecked 3 objects
RMAN> run {
>set newname for database to '/oradata/datafiles/%b';
>restore database;
>switch datafile all;
>}
executing command: SET NEWNAME
Starting restore at 25-FEB-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 /oradata/datafiles/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oradata/datafiles/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata/datafiles/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oradata/datafiles/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oradata/datafiles/charge_01.dbf
channel ORA_DISK_1: reading from backup piece /oradata/backup/df_REALDB_20180225_3336.bck
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:08:05
Finished restore at 25-FEB-18
datafile 1 switched to datafile copy
input datafile copy RECID=16 STAMP=969030667 file name=/oradata/datafiles/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=17 STAMP=969030667 file name=/oradata/datafiles/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=18 STAMP=969030667 file name=/oradata/datafiles/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=19 STAMP=969030667 file name=/oradata/datafiles/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=21 STAMP=969030668 file name=/oradata/datafiles/charge_01.dbf
datafile 6 switched to datafile copy
Check does the flashback is set in this database or not, If the flashback is enable, disable the flashback to proceed further for restore
[oracle@DB backup]$ !sq sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 25 15:11:43 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES
SQL> alter database flashback off; Database altered.
Recover the database:
[oracle@DB backup]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Feb 25 15:14:00 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: REALDB (DBID=2011359186, not open)
RMAN> run {
>recover database;
>}
Starting recover at 25-FEB-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 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=1 sequence=13069
channel ORA_DISK_1: reading from backup piece /oradata/backup/arch_REALDB_20180225_3337.bck
channel ORA_DISK_1: piece handle=/oradata/backup/arch_REALDB_20180225_3337.bck tag=TAG20180225T003451
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
archived log file name=/oradata/flash_recovery_area/REALDB/archivelog/2018_02_25/o1_mf_1_13069_f958dx5t_.arc thread=1 sequence=13069
channel default: deleting archived log(s)
archived log file name=/oradata/flash_recovery_area/REALDB/archivelog/2018_02_25/o1_mf_1_13069_f958dx5t_.arc RECID=19966 STAMP=969030874
unable to find archived log
archived log thread=1 sequence=13070
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/25/2018 15:14:41
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 13070 and starting SCN of 282864595
Try to open the database with reset logs
RMAN> alter database open resetlogs; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 02/25/2018 15:15:01 ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-39700: database must be opened with UPGRADE option Process ID: 12069 Session ID: 20 Serial number: 39 RMAN-00571: -----
As per the above error the database has to open with an upgrade option because our real database was 11.2.0.1 version & restore database is 11.2.0.4
[oracle@DB backup]$ !sq sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 25 15:15:56 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount ORACLE instance started. Total System Global Area 350777344 bytes Fixed Size 2253104 bytes Variable Size 293605072 bytes Database Buffers 50331648 bytes Redo Buffers 4587520 bytes Database mounted. SQL> alter database set standby database to maximize performance; Database altered.
SQL> alter database open upgrade; Database altered.
SQL> select status from v$instance; STATUS ------------ OPEN MIGRATE
Now run the upgrade package & then open the database:
Sql> @$ORACLE_HOME/rdbms/admin/catupgrd.sql;
(To recompile the invalid object, upgrade package procedure, it takes 30 – 45 minutes or more to complete) and once the upgrades finishes. It will shut down the database automatically, Login again as sysdba and startup in normal mode.
Now startup the database:
SQL> startup upgrade; ORACLE instance started. Total System Global Area 350777344 bytes Fixed Size 2253104 bytes Variable Size 293605072 bytes Database Buffers 50331648 bytes Redo Buffers 4587520 bytes Database mounted. Database opened.
Sql> @$ORACLE_HOME/rdbms/admin/utlmmig.sql;
After completion of above package, it will shutdown the database again, need to start the database with normal mode:
SQL> startup ORACLE instance started. Total System Global Area 350777344 bytes Fixed Size 2253104 bytes Variable Size 293605072 bytes Database Buffers 50331648 bytes Redo Buffers 4587520 bytes Database mounted. Database opened.
Oracle recommend to run the below packages after successfully restore
Sql> @$ORACLE_HOME/rdbms/admin/catproc.sql; (Create in built PL/SQL Procedures, Packages etc)
Sql> @$ORACLE_HOME/rdbms/admin/utlrp.sql; (The utlrp.sql script can be called to recompile all objects within the database)
Therefore verify the database:
SQL> select max(C_OCCURRENCEDATE) from CHARGE.T_charge; MAX(C_OCCURRENCEDATE) --------------------------------------------------------------------------- 25-FEB-18 12.40.10.438000 AM