26 | 06 | 2017
Latest Articles
Popular Articles

RMAN

Move and upgrade at the same time

User Rating:  / 3
PoorBest 

Move and upgrade at the same time

 

1. We have to move the database

2. We have to upgrade the database from 10.2.0.3 to 10.2.0.4

3. We have to change the wordsize from 32 bit to 64 bit

4. Both database servers have access to the shared network drive on which the backupsets are located

5. The filesystem layout is different. The database data files are to be restored elsewhere.


Read Metalink Note:430278.1 "Can you restore RMAN backups taken on 32-bit Oracle with 64-bit Oracle ?"

Read Metalink Note:558408.1 "Restoring a database to a higher patchset"

REMARK

Note that you can also use this procedure to move and upgrade from 10G R2 to 11G R2 BUT prior to the backup and clone operation the script utlu112i.sql needs to be launched as SYS againts the database whilst it is still in 10G R2.  utlu112i.sql is packaged with the 11G R2 binaries and available in the $ORACLE_HOME/rdbms/admin directory of your 11G R2 Oracle Home after installation of the new Oracle Home. You can copy over that script after installation of the 11G R2 binaries towards the 10G platform. The script utlu112i.sql create some objects in the 10G database which needs to be available before you open the database with alter database open resetlogs upgrade.

 

On the source database server we shutdown and open read only the database. ( read also the remark at the bottom of this page )


SQL> startup mount;
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1373956 bytes
Variable Size             304744700 bytes
database Buffers          301989888 bytes
Redo Buffers                4259840 bytes
database mounted.


SQL> alter database open read only;
database altered.





On the destination database server we startup nomount the instance, It is very important to start the database instance with the right NLS_LANG environment for RMAN recovery

 


SET NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15

sqlplus / as sysdba


SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                  2163712 bytes
Variable Size             446495744 bytes
database Buffers          805306368 bytes
Redo Buffers                4325376 bytes

 

We set the DBID and restore the controlfile. The first advice is to keep the dbid' s of all your databases at a separate location (database).


RMAN > SET DBID 3388899642

connected to target database: PLATINUM (DBID=3388899642)

RMAN > restore controlfile from '\\BACKUPSERVER\EXPORT00$\MYHOST\PLATINUM\CT_PLATINUM_A6KK1CUD_1_1';


The controlfile is restored, thus we can mount the instance.


RMAN> SQL "alter database mount";


We run the script below, note

1. the set newname command is used to have restored data files and temp files at another location than so far known in the restored control file. The restored control file "only" knows the original location and does not have an idea yet where the data files are to be restored.

2. the switch clone datafile all is used to update the control file information if we are connected with an auxiliary channel.

3. the switch datafile all is used to update the control file information if we are not connected with an auxiliary channel.

 

RMAN> run {

2> allocate channel ch1 device type DISK;

3> allocate channel ch2 device type DISK;

4> allocate channel ch3 device type DISK;

5> allocate channel ch4 device type DISK;

6> set newname for datafile 1 to 'E:\oradata\PLATINUM\SYSTEM01.DBF';

7> set newname for datafile 2 to 'E:\oradata\PLATINUM\UNDOTBS01.DBF';

8> set newname for datafile 3 to 'E:\oradata\PLATINUM\SYSAUX01.DBF';

9> set newname for datafile 4 to 'E:\oradata\PLATINUM\USERS01.DBF';

10> set newname for datafile 5 to 'E:\oradata\PLATINUM\TOOLS01.DBF';

11> set newname for datafile 6 to 'E:\oradata\PLATINUM\INDX01.DBF';

12> set newname for datafile 7 to 'E:\oradata\PLATINUM\PLATINUM_DATA01.DBF';

13> set newname for datafile 8 to 'E:\oradata\PLATINUM\SILVER_DATA01.DBF';

14> set newname for datafile 9 to 'E:\oradata\PLATINUM\PLATINUM_DATA_NOT_LOGGED01.DBF';

15> set newname for datafile 10 to 'E:\oradata\PLATINUM\PLATINUM_INDX_NOT_LOGGED01.DBF';

16> set newname for datafile 11 to 'E:\oradata\PLATINUM\PLATINUM_DATA02.DBF';

17> set newname for tempfile 1 to 'E:\oradata\PLATINUM\TEMP01.DBF';

18> restore database;

19> switch datafile all;

20> switch tempfile all;

21> recover database;

22>}

 

A nice command to verify whether all the file headers registered in the controlfile are really readable as datafiles  belonging to that database

 

 


SQL > select checkpoint_change# from v$datafile_header;



Then rman prompts for the next archived redo log which was not yet backed up, not yet found we are almost ready to open the database with resetlogs. Before doing so we check the path of the online redo logs. When original path does not match with the place were we would like to create them we adjust the controlfile information.

 

SQL> select member from v$logfile;


MEMBER


D:\oradata\PLATINUM\redo01.log
D:\oradata\PLATINUM\redo02.log
D:\oradata\PLATINUM\redo03.log
E:\oradata\PLATINUM\redo01-b.log
E:\oradata\PLATINUM\redo02-b.log
E:\oradata\PLATINUM\redo03-b.log

6 rows selected.


SQL> alter database rename file 'D:\oradata\PLATINUM\redo01.log' to 'E:\oradata\PLATINUM\redo01.log';

Database altered.

SQL> alter database rename file 'D:\oradata\PLATINUM\redo02.log' to 'E:\oradata\PLATINUM\redo02.log';

Database altered.

SQL> alter database rename file 'D:\oradata\PLATINUM\redo03.log' to 'E:\oradata\PLATINUM\redo03.log';

Database altered.

SQL>  alter database rename file 'E:\oradata\PLATINUM\redo01-b.log' to 'F:\oradata\PLATINUM\redo01-b.log';

Database altered.

SQL> alter database rename file 'E:\oradata\PLATINUM\redo02-b.log' to 'F:\oradata\PLATINUM\redo02-b.log';

Database altered.

SQL> alter database rename file 'E:\oradata\PLATINUM\redo03-b.log' to 'F:\oradata\PLATINUM\redo03-b.log';

Database altered.

 

Then open the database with a very particular command



RMAN > SQL "alter database open resetlogs UPGRADE"

 


The resetlogs option needs to be used since it is a cancel based recovery

The upgrade option needs to be used since the backup of the source database is still on 10.2.0.3 and we open the database with 10.2.0.4 binaries.

But as soon as we opended the database with resetlogs option RMAN throws us a weird error. This error is related to the change 32 BIT / 64 BIT. it hasn' t anything to do with the change 10.2.0.3 and 10.2.0.4

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-12005: error during channel cleanup
ORA-06544: PL/SQL: internal error, arguments: [56319], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [56319]

RMAN> exit

 

 

So we have still 2 remaining parts, the first one is the change in wordsize 32 bit / 64 bit.
Moving from 32bit to 64bit is actually a piece of cake ( check version specific information ) . We run utlirp.sql to invalidate the packages and to have them recompiled afterwards using utlrp.sql

cd %ORACLE_HOME%\rdbms\admin


sqlplus / as sysdba
SQL > shutdown immediate;

SQL > startup upgrade;

SQL > start utlirp.sql;



If we don' t change the Oracle version we run

SQL > shutdown immediate
SQL > startup;

SQL > start ultrp.sql


If we change the Oracle version from 10.2.0.3 towards 10.2.0.4, we have to proceed with the steps indicated in the upgrade guide AFTER the change in wordsize ( 32 BIT / 64 BIT ), I did

 


SQL > shutdown immediate
SQL > startup upgrade
SQL > start catupgrd.sql
SQL > shutdown immediate
SQL > start ultrp.sql

 

My friend Priyanka Kohli found out one could use this for downgrading purposes as well, thanks to her I became aware the below listed commands can be used in those rather rare circumstances

 

RMAN > SQL "alter database open resetlogs DOWNGRADE"
SQL > startup downgrade

 

Remark

1. The original source database can stay stay in read/write mode. I opened it in read only mode since I wanted to give the business read only data access during the move operation.

2. The procedure to restore a database at another host but with data files restored elsewhere while word size an Oracle release stays identicial can be derived from this procedure.

3. The procedure to restore a database at another host with data files restored at the same location but with a change in word size and Oracle release at the same time can be derived from this procedure.