edba: RAC to RAC/One Instance refresh

RAC to one Instance ASM refresh

1) Turn on the CONTROLFILE AUTOBACKUP and take production backup of RAC or include backup current C.F as last command in rman run.

RMAN> run{
2> allocate channel c1 type disk format '/oracle/10g/backup/%U';
3> backup database;
4> backup archivelog all;
5> }

RMAN> backup database format '/tmp/%U';
RMAN> backup archivelog all format '/tmp/%U';
RMAN> backup current controlfile format '/tmp/control.bks';
RMAN> backup spfile format '/tmp/spfile.bks';

Starting backup at

Finished backup at

Starting Control File and SPFILE Autobackup at 12-FEB-07
piece handle=/oracle/10g/backup/c-610677177-20070212-00 comment=NONE
Finished Control File and SPFILE Autobackup at 12-FEB-07

2) Create a PFILE for the single instance database using the production RAC parameter file

Modifying

audit_file_dest,

background_dump_dest,

control_files, (ex. =/u01/oracle/oradata/ractest/control01.ctl)

core_dump_dest,

log_archive_dest_1,

user_dump_dest etc

Removing

cluster_database_instances,

cluster_database etc

Mentioning

any one undo tablespace name for undo_tablespace

3) a. Nomount and Restore C.F,

b. Mount and check archivelogs,

c. Restore/recover DB, rename logfiles and open resetlogs

oracle@test-br ractest]$ sqlplus "/ as sysdba"

SQL> startup nomount;

[oracle@test-br ractest]$ rman target / nocatalog

RMAN> restore controlfile from '/u01/oracle/oradata/ractest/c-610677177-20070212-00';
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/oracle/oradata/ractest/control01.ctl
Finished restore at 16-FEB-07

NOTE: From Doc ID 1338233.1 , if different backup location on UAT

+ RMAN> catalog start with '<new location>' noprompt;

   (add all backup pieces in metadata and now onward we can see 2 copies of backup in metadata )
+ RMAN> crosscheck backup tag '<backup tag from backup log>' ;
   (By default oracle picks latest backup tag and the first copy if multiple copies exist.So that original location backup pieces ie first copy will be marked as expired )
+ RMAN> delete expired backup;
     (delete first copy of backup piece

RMAN> alter database mount;

RMAN> list backup of archivelog all;

Check highest seq. among all the threads with the least “next SCN”

RMAN> run {
2> set until sequence 59 thread 1;
3> set newname for datafile 1 to '/u01/oracle/oradata/ractest/data/system01.dbf';
4> set newname for datafile 2 to '/u01/oracle/oradata/ractest/data/undotbs01.dbf';
5> set newname for datafile 3 to '/u01/oracle/oradata/ractest/data/sysaux01.dbf';
6> set newname for datafile 4 to '/u01/oracle/oradata/ractest/data/users01.dbf';
7> set newname for datafile 5 to '/u01/oracle/oradata/ractest/data/undotbs02.dbf';
8> restore database;
9> switch datafile all;
10> recover database;
11> }

SQL> select member from v$logfile;

MEMBER
-------------------------------

/ocfs2/oradata/racdb/redo01.log'
/ocfs2/oradata/racdb/redo02.log'

alter database rename file ‘/ocfs2/oradata/racdb/redo01.log’ to ‘+DATA’;
alter database rename file ‘/ocfs2/oradata/racdb/redo02.log’ to ‘+DATA’;

SQL> alter database open resetlogs;

After Database Open :

1. Remove the redolog groups of other other instances

SQL> select THREAD#, STATUS, ENABLED from v$thread;
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED PRIVAT
SQL> select group# from v$log where THREAD#=2;
SQL> alter database disable thread 2;
SQL> alter database clear unarchived logfile group 4;
SQL> alter database drop logfile group 4; 5 and 6; 5 and 6;
SQL> select THREAD#, STATUS, ENABLED from v$thread;

2.Remove the undo tablespaces of other instances and recreate temporary tablespace

SQL> select tablespace_name from dba_tablespaces where contents='UNDO';
UNDOTBS1
UNDOTBS2

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';

SQL> create temporary tablespace TEMP1
2 tempfile '/u01/oracle/oradata/ractest/data/temp01.dbf'
3 size 50M;

SQL> alter database default temporary tablespace TEMP1;

SQL> drop tablespace TEMP including contents and datafiles;

Renaming Database

SQL> alter database backup controlfile to trace as '<location and name of trace file>';

SQL> shutdown immediate;

Set . oraenv to UAT and change trace file for
CREATE CONTROLFILE set DATABASE "UAT" RESETLOGS ARCHIVELOG

SQL> startup nomount;

==>run the script to re-create controlfile

SQL> alter database open resetlogs;

Conversion to Two node using rconfig

[oracle@uat01 ~]$ cd $ORACLE_HOME/assistants/rconfig/sampleXMLs
[oracle@uat01 ~]$ cp ConvertToRAC_AdminManaged.xml /tmp/UAT_ConvertToRAC.xml
[oracle@uat01 ~]$ vi /tmp/UAT_ConvertToRAC.xml

Change these parameters

<n:Convert verify="YES">
              <n:SourceDBHome>/u01/app/oracle/product/11.2.0/dbhome_1</n:SourceDBHome>

              <n:TargetDBHome>/u01/app/oracle/product/11.2.0/dbhome_1</n:TargetDBHome>

              <n:SourceDBInfo SID="UAT">
                <n:Credentials>
                  <n:User>sys</n:User>
                  <n:Password>oracle</n:Password>
                  <n:Role>sysdba</n:Role>
                </n:Credentials>
              </n:SourceDBInfo>

              <n:NodeList>
                <n:Node name="uat01"/>
                <n:Node name="uat02"/>
              </n:NodeList>

              

              <n:InstancePrefix>UAT</n:InstancePrefix>

cd $ORACLE_HOME/bin

[oracle@uat01 ~]$ rconfig /tmp/UAT_ConvertToRAC.xml

:::::::::::

Operation Succeeded

:::::::::::

or

Change the pfile (dupdb) and add all RAC related parameters.

Please check for the control_files parameter, this was created when duplication done successfully in previous steps.
initdupdb.ora

DB_NAME=dupdb
CONTROL_FILES='+DGDUP/CONTRILFILE/Current.278.780661547'
DB_FILE_NAME_CONVERT='+DBDATA','+DGDUP'
LOG_FILE_NAME_CONVERT='+DBFLASH','+DGDUP'
db_create_file_dest='+DGDUP'
db_recovery_file_dest='+DGDUP'
db_recovery_file_dest_size=4558159872
dupdb1.instance_number=1
dupdb2.instance_number=2
cluster_database=true
cluster_database_instances=2

Create spfile on the shared storage, because all instances must use the same server parameter file.

SQL> create spfile='+DGDUP' from pfile='D:\app\Inam\product\11.2.0.3\dbhome_1\database\initdupdb.ora';

File created.

Shutdown the instance (dupdb)

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Unregister and Register

Unregister/remove instances/db if they already exist.

D:\app\11.2.0.3\grid\BIN>srvctl remove database -d DUPDB
Remove the database ORCL? (y/[n]) y

D:\app\11.2.0.3\grid\BIN>srvctl stop instance -d dupdb -i dupdb2

D:\app\11.2.0.3\grid\BIN>srvctl remove instance -d dupdb -i dupdb2
Remove instance from the database dupdb? (y/[n]) y

NOTE : Mention correct case as mentioned in your init parameter file while registering db/instances name to RAC

Register/add the new DB (dupdb) with srvctl ::

Check if database dupdb already exist as a resource

D:\app\11.2.0.3\grid\BIN>srvctl config database -d dupdb

PRCD-1120 : The resource for database dupdb could not be found.
PRCR-1001 : Resource ora.dupdb.db does not exist

Since no resource dupdb exists, now add it

D:\app\11.2.0.3\grid\BIN>srvctl add database -d dupdb -o D:\app\Inam\product\11.2.0.3\dbhome_1

Again check DB config

D:\app\11.2.0.3\grid\BIN>srvctl config database -d dupdb

Database unique name: DUPDB
Database name:
Oracle home: D:\app\Inam\product\11.2.0.3\dbhome_1
Oracle user: nt authority\system
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: DUPDB
Database instances:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed

Now add both RAC instances (dupdb1,dupdb2) ::

D:\app\11.2.0.3\grid\BIN>srvctl add instance -d dupdb -i dupdb1 -n or1
D:\app\11.2.0.3\grid\BIN>srvctl add instance -d dupdb -i dupdb2 -n or2

Again check DB config again after adding instances (above steps)

D:\app\11.2.0.3\grid\BIN>srvctl config database -d dupdb

Database unique name: dupdb
Database name:
Oracle home: D:\app\Inam\product\11.2.0.3\dbhome_1
Oracle user: nt authority\system
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: DUPDB
Database instances: dupdb1,dupdb2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed

Now start the database and check via sqlplus on both nodes
D:\app\11.2.0.3\grid\BIN>srvctl start database -d dupdb

Create init.ora on both nodes and point them to common spfile
initdupdb1.ora,
SPFILE='+DGDUP/DUPDB/PARAMETERFILE/spfile.272.780662847'
initdupdb2.ora
SPFILE='+DGDUP/DUPDB/PARAMETERFILE/spfile.272.780662847'

Verify RAC conversion

[oracle@uat01 ~]$ srvctl config database -d uat

[oracle@uat01 ~]$ srvctl status database -d uat

SQL> select thread#, status from gv$instance ;

SQL> select thread#, group#, sequence#, status from v$log order by thread#, group#, sequence# ;

RAC to RAC ASM refresh (Doc ID 1913937.1)

http://docs.oracle.com/cd/E11882_01/install.112/e41962/cvrt2rac.htm#RILIN1163