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/adumpSet 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-157. 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 507489099. Open the database with resetlogsRMAN> 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 errorThis 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 selected12. 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 ------------------------------ UNDOTBS113. 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_.tmp14. 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.0This 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]
