Sunday, March 1, 2015

Restore RAC DB Backup as a Single Instance DB

At times it may require a DBA to restore a backup of a RAC DB as a single instance DB. It could be that RAC DB is production system and copy of it is needed for development. This could be achieved with RAC to single instance duplication as well. However in this post steps are shown on how to restore RAC DB on ASM to a single instance DB which use local file system.
1. Create a backup of the RAC DB including the control files. In this case the backups are created in the local file system.
RMAN> backup database format '/home/oracle/backup/bakp%U' plus archivelog format '/home/oracle/backup/arch%U' delete all input;
RMAN> backup current controlfile format '/home/oracle/backup/ctl%U';
2. Create a pfile of the RAC. The output below shows the RAC DB pfile with RAC specific and instance specific parameters.
*.audit_file_dest='/opt/app/oracle/admin/rac11g2/adump'
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/rac11g2/controlfile/current.260.732796395','+FLASH/rac11g2/controlfile/current.256.732796395'#Restore Controlfile
*.db_32k_cache_size=67108864
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='domain.net'
*.db_name='rac11g2'
*.db_recovery_file_dest='+FLASH'
*.db_recovery_file_dest_size=9437184000
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rac11g2XDB)'
rac11g21.instance_number=1
rac11g22.instance_number=2
*.java_jit_enabled=TRUE
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=150
*.remote_listener='rac-scan.domain.net:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=633339904
rac11g21.thread=1
rac11g22.thread=2
rac11g21.undo_tablespace='UNDOTBS1'
rac11g22.undo_tablespace='UNDOTBS2'
3. Edit the pfile by removing the RAC and instance specific parameters, especially the cluster_database=true must be set to false. Output below gives the edited pfile. The new single instance will use OMF and the db_create_file and db_recoery_file_dest have been replaced with file system directories in place of ASM diskgroup used by the RAC DB. Also all instance specific parameters have been removed.
more rac11g2pfile.ora
*.audit_file_dest='/opt/app/oracle/admin/rac11g2/adump'
*.audit_trail='NONE'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.db_32k_cache_size=67108864
*.db_block_size=8192
*.db_create_file_dest='/data/oradata'
*.db_domain='domain.net'
*.db_name='rac11g2'
*.db_recovery_file_dest='/data/flash_recovery'
*.db_recovery_file_dest_size=9437184000
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rac11g2XDB)'
*.java_jit_enabled=TRUE
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=633339904
*.undo_tablespace='UNDOTBS1'
4. Copy the backup and the pfile to the new host where the single instance DB will be created.

5. Create the audit dump directory
 mkdir -p /opt/app/oracle/admin/rac11g2/adump
Set the ORACLE_SID to the RAC DB sid (not the instance SID in this case the RAC DB is called rac11g2) and start the db in nomount mode.
export ORACLE_SID=rac11g2
SQL> startup nomount pfile='rac11g2pfile.ora';
If desired the spfile could also be created in the same step and restart the instance in nomount mode using the spfile instead of the pfile. With the use of a spfile the control files entries will be added to it automatically when they are restored.
SQL> create spfile from pfile='/home/oracle/backups/rac11g2pfile.ora' ;
SQL> startup force nomount;
6. Use rman to restore the control file from the location in the new host.
 rman target /

connected to target database: RAC11G2 (not mounted)

RMAN> restore controlfile from '/home/oracle/backups/ctl5upvj3oh_1_1';

Starting restore at 18-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data/oradata/RAC11G2/controlfile/o1_mf_bg8ykm51_.ctl
output file name=/data/flash_recovery/RAC11G2/controlfile/o1_mf_bg8ykmdt_.ctl
Finished restore at 18-FEB-15
7. Mount the database and catalog the backups copied over earlier.
RMAN> alter database mount;

RMAN> catalog start with '/home/oracle/backups';

Starting implicit crosscheck backup at 18-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
Crosschecked 20 objects
Finished implicit crosscheck backup at 18-FEB-15

Starting implicit crosscheck copy at 18-FEB-15
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 18-FEB-15

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /home/oracle/backups

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backups/arch5spvj3m6_1_1
File Name: /home/oracle/backups/rac11g2pfile.ora
File Name: /home/oracle/backups/ctl5upvj3oh_1_1
File Name: /home/oracle/backups/arch5npvj3f0_1_1
File Name: /home/oracle/backups/arch5opvj3h1_1_1
File Name: /home/oracle/backups/bakp5qpvj3k0_1_1
File Name: /home/oracle/backups/bakp5rpvj3m2_1_1
File Name: /home/oracle/backups/arch5ppvj3ij_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/backups/arch5spvj3m6_1_1
File Name: /home/oracle/backups/ctl5upvj3oh_1_1
File Name: /home/oracle/backups/arch5npvj3f0_1_1
File Name: /home/oracle/backups/arch5opvj3h1_1_1
File Name: /home/oracle/backups/bakp5qpvj3k0_1_1
File Name: /home/oracle/backups/bakp5rpvj3m2_1_1
File Name: /home/oracle/backups/arch5ppvj3ij_1_1


8.Restore the database from the backups, switch datafiles to new file location and recover the database to the last archivelog available on the backups. Since OMF is used the newname for the databse is set as "to new".
run {
set newname for database to new;
restore database;
switch datafile all;
recover database;
}
  
RMAN> run {
2> set newname for database to new;
3>  restore database;
4> switch datafile all;
5> recover database;
6> }

executing command: SET NEWNAME

Starting restore at 18-FEB-15
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 /data/oradata/RAC11G2/datafile/o1_mf_system_%u_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /data/oradata/RAC11G2/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /data/oradata/RAC11G2/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /data/oradata/RAC11G2/datafile/o1_mf_users_%u_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /data/oradata/RAC11G2/datafile/o1_mf_undotbs2_%u_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /data/oradata/RAC11G2/datafile/o1_mf_test_%u_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /data/oradata/RAC11G2/datafile/o1_mf_test_%u_.dbf
channel ORA_DISK_1: restoring datafile 00009 to /data/oradata/RAC11G2/datafile/o1_mf_gravelso_%u_.dbf
channel ORA_DISK_1: restoring datafile 00010 to /data/oradata/RAC11G2/datafile/o1_mf_sbxindex_%u_.dbf
channel ORA_DISK_1: restoring datafile 00011 to /data/oradata/RAC11G2/datafile/o1_mf_sbxlobs_%u_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backups/bakp5qpvj3k0_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backups/bakp5qpvj3k0_1_1 tag=TAG20150218T121600
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
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 00008 to /data/oradata/RAC11G2/datafile/o1_mf_sbx32ktb_%u_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backups/bakp5rpvj3m2_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backups/bakp5rpvj3m2_1_1 tag=TAG20150218T121600
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 18-FEB-15

datafile 1 switched to datafile copy
input datafile copy RECID=49 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_system_bg8z9rm9_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=50 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_sysaux_bg8z9rlo_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=51 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_undotbs1_bg8z9rnh_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=52 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_users_bg8z9rp1_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=53 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_undotbs2_bg8z9ro9_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=54 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_test_bg8z9rpp_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=55 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_test_bg8z9rq9_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=56 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_sbx32ktb_bg8zbvny_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=57 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_gravelso_bg8z9rr1_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=58 STAMP=871991658 file name=/data/oradata/RAC11G2/datafile/o1_mf_sbxindex_bg8z9s7h_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=59 STAMP=871991659 file name=/data/oradata/RAC11G2/datafile/o1_mf_sbxlobs_bg8z9s9k_.dbf

Starting recover at 18-FEB-15
using channel ORA_DISK_1

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=216
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=180
channel ORA_DISK_1: reading from backup piece /home/oracle/backups/arch5spvj3m6_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backups/arch5spvj3m6_1_1 tag=TAG20150218T121709
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/data/flash_recovery/RAC11G2/archivelog/2015_02_18/o1_mf_1_216_bg8zccx3_.arc thread=1 sequence=216
archived log file name=/data/flash_recovery/RAC11G2/archivelog/2015_02_18/o1_mf_2_180_bg8zccy6_.arc thread=2 sequence=180
channel default: deleting archived log(s)
archived log file name=/data/flash_recovery/RAC11G2/archivelog/2015_02_18/o1_mf_2_180_bg8zccy6_.arc RECID=1606 STAMP=871991660
unable to find archived log
archived log thread=2 sequence=181
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/18/2015 11:54:21
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 181 and starting SCN of 50748909
9. Open the database with resetlogs
RMAN> alter database open resetlogs;
It is normal at this stage to observe following messages in the alert log
Starting background process ASMB
Wed Feb 18 15:56:57 2015
ASMB started with pid=58, OS id=26760
WARNING: failed to start ASMB (connection failed) state=0x1 sid=''
WARNING: ASMB exiting with error
This is due to some old references to files in the ASM but should not affect the functioning of the database.

10. Create a spfile from the memory (if not created at step 6) and restart the database using the spfile. At this stage the ASMB message observed earlier should not occur anymore.

11. Clean up the additional threads that came as part of the RAC. Since the RAC DB was a two instance the new single instance will have information of the two thread.
SQL> select THREAD#, STATUS, ENABLED from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED PUBLIC

SQL> select group# from v$log where THREAD#=2;

    GROUP#
----------
         3
         4

SQL> alter database disable thread 2;
Database altered.

SQL> alter database clear unarchived logfile group 3;
Database altered.

SQL>  alter database clear unarchived logfile group 4;
Database altered.

SQL> alter database drop logfile group 3;
Database altered.

SQL> alter database drop logfile group 4;
Database altered.

SQL>  select group# from v$log where THREAD#=2;
no rows selected
12. Drop undo tablespaces that are not used as part of the single instance. In this case the RAC DB had two undo tablespaces and one was chosen as the undo tablespace for the single instance. The other undo tablespace is dropped.
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

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

TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2

SQL>drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.

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

TABLESPACE_NAME
------------------------------
UNDOTBS1
13. Since OMF was used the temp file for the temporary tablespace was automatically created when the database was restored. If not create the temp file and assigned to the temporary tablespace or create a new default temporary tablespace.
SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
------------------------------
TEMP

SQL>  select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/data/oradata/RAC11G2/datafile/o1_mf_temp_bg8zfgxv_.tmp
14. Since the restored DB is non-RAC the registry shows RAC option as invalid. Run dbms_registry to remove the RAC option from the registry.
Select comp_name,status,version from dba_registry;

Oracle Real Application Clusters                             INVALID                                      11.2.0.3.0

SQL> exec dbms_registry.removed('RAC');

Oracle Real Application Clusters                             REMOVED                                      11.2.0.3.0
This conclude the restoring a RAC DB backup as single instance DB.

Useful metalink notes
RAC Option Invalid After Migration [ID 312071.1]
HowTo Restore RMAN Disk backups of RAC Database to Single Instance On Another Node [ID 415579.1]