This posts illustrates steps for cloning a 11gR2 RAC database to a single Instance database. There are many ways to perform a RAC database to NON-RAC database clone, but the below illustrated seems to be the easiest approach for me compare to all other methods.
In this demonstration oelrac1 & oelrac2 are OEL 5.6 Linux hosts running 11.2.0.1 GI and 11.2.0.1 database.
- erpnode3 running OEL 5.6 configured with all database OS pre-requisites, but does not contain any oracle product installed on it.
Steps:
1) perform full database backup from any of the RAC Instance
In this demonstration oelrac1 & oelrac2 are OEL 5.6 Linux hosts running 11.2.0.1 GI and 11.2.0.1 database.
- erpnode3 running OEL 5.6 configured with all database OS pre-requisites, but does not contain any oracle product installed on it.
Steps:
1) perform full database backup from any of the RAC Instance
RMAN> connect target
connected to target database: PROD (DBID=208065887)
RMAN> RUN
2> {
3> allocate channel ch1 type
4> disk format '/u01/11g_db/rman_db_backups/%d_DB_%u_%s_%p';
5> backup database plus archivelog;
6> release channel ch1;
7> }
released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=67 instance=prod1 device type=DISK
Starting backup at 14-OCT-12
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=1 STAMP=796227711
input archived log thread=2 sequence=1 RECID=2 STAMP=796227756
input archived log thread=1 sequence=3 RECID=5 STAMP=796229217
input archived log thread=2 sequence=2 RECID=3 STAMP=796227764
input archived log thread=2 sequence=3 RECID=4 STAMP=796229213
input archived log thread=2 sequence=4 RECID=6 STAMP=796232466
input archived log thread=1 sequence=4 RECID=7 STAMP=796648119
input archived log thread=2 sequence=5 RECID=9 STAMP=796649431
input archived log thread=1 sequence=5 RECID=8 STAMP=796649430
input archived log thread=1 sequence=6 RECID=10 STAMP=796649607
input archived log thread=2 sequence=6 RECID=11 STAMP=796649608
input archived log thread=2 sequence=7 RECID=13 STAMP=796649932
input archived log thread=1 sequence=7 RECID=12 STAMP=796649929
channel ch1: starting piece 1 at 14-OCT-12
channel ch1: finished piece 1 at 14-OCT-12
piece handle=/u01/11g_db/rman_db_backups/PROD_DB_05nnnqef_5_1 tag=TAG20121014T113855 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:25
Finished backup at 14-OCT-12
Starting backup at 14-OCT-12
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=+RACDATA/prod/system01.dbf
input datafile file number=00002 name=+RACDATA/prod/sysaux01.dbf
input datafile file number=00003 name=+RACDATA/prod/undotbs01.dbf
input datafile file number=00005 name=+RACDATA/prod/undotbs02.dbf
input datafile file number=00004 name=+RACDATA/prod/users01.dbf
channel ch1: starting piece 1 at 14-OCT-12
channel ch1: finished piece 1 at 14-OCT-12
piece handle=/u01/11g_db/rman_db_backups/PROD_DB_06nnnqf9_6_1 tag=TAG20121014T113921 comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:16
Finished backup at 14-OCT-12
Starting backup at 14-OCT-12
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=15 STAMP=796650041
input archived log thread=2 sequence=8 RECID=14 STAMP=796650041
channel ch1: starting piece 1 at 14-OCT-12
channel ch1: finished piece 1 at 14-OCT-12
piece handle=/u01/11g_db/rman_db_backups/PROD_DB_07nnnqhr_7_1 tag=TAG20121014T114042 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-OCT-12
Starting Control File and SPFILE Autobackup at 14-OCT-12
piece handle=/u01/11g_db/rman_db_backups/prod_c-208065887-20121014-01 comment=NONE
Finished Control File and SPFILE Autobackup at 14-OCT-12
released channel: ch1
RMAN>
2) create pfile and edit it as per the requirement
- remove all cluster related parameters
ora11g@oelrac1 db_scripts]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Oct 14 12:46:17 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> create pfile='/home/ora11g/db_scripts/initTEST.ora' from spfile;
File created.
- sample initTEST.ora
[ora11g@erpnode3 dbs]$ more initTEST.ora
[ora11g@erpnode3 dbs]$ more initTEST.ora
TEST.__db_cache_size=230686720
TEST.__java_pool_size=4194304
TEST.__large_pool_size=4194304
TEST.__pga_aggregate_target=348127232
TEST.__sga_target=515899392
TEST.__shared_io_pool_size=0
TEST.__shared_pool_size=268435456
TEST.__streams_pool_size=0
*.audit_file_dest='/u01/11g_db/ora11g/admin/TEST/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/11g_db/oradata/TEST/control01.ctl','/u01/11g_db/oradata/TEST/control02.ctl'
*.db_create_file_dest='/u01/11g_db/oradata/TEST'
*.db_block_size=8192
*.db_domain='orasol.com'
*.db_name='prod'
*.diagnostic_dest='/u01/11g_db/ora11g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTXDB)'
*.memory_target=862978048
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
[ora11g@erpnode3 dbs]$
- Here i am using db_name= prod, as this name is stored in controlfile. We can change it after completion of clone 3) Clone RDBMS $ORACLE_HOME - create tar file for ORACLE_HOME from any of the RAC node
- copy to remote server and untar it
- Disable RAC configuration from oracle home & relink binaries
- create password file
- copy backup pieces to the remote host. I have copied it on the same directory location (/u01/11g_db/rman_db_backups)
ora11g@oelrac1 11.2.0]$ ls
11gr2.tar dbhome_1
[ora11g@oelrac1 11.2.0]$ scp 11gr2.tar root@erpnode3:/u01/11g_db/ora11g/product/11.2.0
[ora11g@erpnode3 dbs]$ orapwd file=orapwTEST entries=6 password=oracle
[ora11g@erpnode3 dbs]$ ls -lrt orapwTEST
-rw-r----- 1 ora11g dba 2048 Oct 14 14:33 orapwTEST
[ora11g@erpnode3 dbs]$
rman backups on erpnode3:
[ora11g@erpnode3 11g_db]$ cd rman_db_backups/
[ora11g@erpnode3 rman_db_backups]$ ls
prod_c-208065887-20121014-00 prod_c-208065887-20121014-02 prod_c-208065887-20121015-00 PROD_DB_06nnnqf9_6_1
prod_c-208065887-20121014-01 prod_c-208065887-20121014-03 PROD_DB_05nnnqef_5_1 PROD_DB_07nnnqhr_7_1
[ora11g@erpnode3 rman_db_backups]$ pwd
/u01/11g_db/rman_db_backups
[ora11g@erpnode3 rman_db_backups]$
5) Restore controlfile and startup database in nomount state
6) check backup of archive logs and execute restore database script accordingly
7) Rename online redologfiles and open database with resetlogs
8) Disable and drop closed online redolog group
8) Create new temp tablespace and drop old temp TS
- Here SID is TEST and database name is PROD, just change the database name using dbnewid utility
-verify the database name
- shutdown database
- startup nomount
- use nid command to change
- edit initilization parameter file and change db_name parameter from "prod" to "TEST"
- startup mount and open database with resetlogs option
[ora11g@erpnode3 ~]$ echo $ORACLE_SID
TEST
Done with RAC to Non-RAc clone :)
If you have any comment's/suggestion/queries in regards with this article then update me.
Thanks for reading.
regards,
X A H E E R
Comments
Request you to kindly specify the path from where we have to remove the cluster related entries.
Please refer sample initialization parameter file listed above for all required parameters on a non-rac host.
thanks,
X A H E E R
- i had the same work but on rac 10.2.0.5 and i have done with rman duplicate to .... works :)
I have a small doubt...
You issued "list backup of archivelog all" in step 6 by connecting to clone node as a target database....my question is ..we have restored & mounted backup copy of control file in cloning node..how come backup control file holds updated metadata about the recent backup sets before restoring and recovering it on the same node ?
Plz clarify my doubt...
Thnz in advance..
Very good explanation. how to read AWR report and take action according to that.
zainul from oracle expert group.
Well drafted and executed.
Xaheer's post are handy tools for all Major DB related tasks.
Few are below things that would add up more value to this post cloning from rac to non-rac:-
Firstly :
to convert binaries from rac to non rac it should be noted that
"make -f ins_rdbms.mk rac_off" &"make -f ins_rdbms.mk ioracle" command should be run from $ORACLE_HOME/rdbms/lib not from $ORACLE_HOME/lib or any other.
Someone above asked in the comment.
Secondly got a new issue with my latest cloning from rac to non-rac for the first time.
v$logfile dispalyed members as "+DATA" instead of providing "+DATA\prod\onlinelog1a.dbf" for all the logfile and when we try to rename it got below error:
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 INVALID ONLINE +DATA NO
1 INVALID ONLINE +DATA NO
2 INVALID ONLINE +DATA NO
2 INVALID ONLINE +DATA NO
3 INVALID ONLINE +DATA NO
4 INVALID ONLINE +DATA NO
11 ONLINE +CCB_DATA/ccbprod/onlinelog/group_11_2_1.dbf NO
WHEN I TRY TO DROP THEM ITS SAYS FILE DOESN'T EXIST.
NOW LET'S STRAIGHT AWAY GO TO SOLUTION:
SOLUTION A:
ALTER DATABASE OPEN RESETLOGS fails with ORA-00392 (NOTE 1352133.1)
SOLUTION B:
Hi Yousuf,
This issue may have happened as you have not set log_file_name_convert or db_create_online_log_dest_n parameters before restore and you are cloning from ASM to non-ASM.
To avoid it set any 1 of the parameters and retry the restore/recovery.
To resolve the issue we did below actions:
>> drop the logfile group:
alter database drop logfile group ;
If it doesn't drop, check the status of the logfile and clear it:
alter database clear unarchived logfile group ;
after this runs drop the logfile group and drop it.
ORA Error Encountered:
ORA-00360, ORA-00392, ORA-00312, ORA-00349
Hopefully this might assist you.
Regards,
Usuf.