RAC TO NONRAC CLONNING

Cloning of Database from RAC to NON- RAC :-

In below example cloning of database is done from RAC server to different standalone server using backup based duplication.

On Source RAC Database:-

Make the backup of the source database using Rman.

$ rman target=/

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> backup incremental level 0 filesperset 3 database plus archivelog not backed up 1 times;

crosscheck archivelog all;

delete noprompt archivelog all backed up 1 times to disk completed before 'sysdate-2';

Once backup is done transfer the backup sets to target server where we want to restore/clone.

On Target Standalone Server :-

1) Create password file on the standalone as below.

orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwDB11G password=password entries=10


2) Create pfile on source and get that file copied to target and edit it and remove the RAC related parameters and edit paths as highlighted in red.

[oracle@hostname ~]$ cat pfileclone.ora

*._gby_hash_aggregation_enabled=FALSE

*._optimizer_cost_based_transformation='OFF'

*._optimizer_cost_model='IO'

*.aq_tm_processes=1

*.audit_file_dest='/oracle/app/oracle/admin/qaclone/adump'

*.audit_trail='NONE'

*.cluster_database=FALSE

*.compatible='11.2.0.0.0'

*.control_file_record_keep_time=14

*.control_files='+DATA/qaclone/control01.ctl','+DATA/qaclone/control02.ctl'

*.cursor_sharing='EXACT'

*.db_16k_cache_size=335544320

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='+PRODDATA/prod','+DATA/qaclone'

*.db_keep_cache_size=201326592

*.db_name='qaclone'

*.db_writer_processes=4

*.diagnostic_dest='/oracle/app/oracle/admin/qaclone'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=qacloneXDB)'

*.dml_locks=5420

*.fast_start_mttr_target=300

*.filesystemio_options='asynch'

*.hash_area_size=61943040

*.java_pool_size=268435456

*.job_queue_processes=5

*.log_archive_dest_1='LOCATION=+ARCH REOPEN=300'

*.log_archive_format='qaclone_t%t_%s_%r.dbf'

*.log_buffer=1048576

*.log_file_name_convert='+PRODDATA/prod','+DATA/qaclone'

*.open_cursors=300

*.optimizer_features_enable='11.2.0.1'

*.optimizer_index_caching=90

*.optimizer_index_cost_adj=20

*.parallel_adaptive_multi_user=TRUE

*.parallel_execution_message_size=2148

*.parallel_max_servers=20

*.parallel_min_servers=4

*.pga_aggregate_target=2505048064

*.processes=800

*.query_rewrite_enabled='TRUE'

*.remote_login_passwordfile='exclusive'

*.session_cached_cursors=20

*.sessions=1232

*.shared_pool_size=2147483648

*.star_transformation_enabled='FALSE'

*.statistics_level='TYPICAL'

*.timed_statistics=TRUE

*.transactions=1355

*.undo_management='AUTO'

*.undo_retention=10800

*.sga_target=10G

*.sga_max_size=10G

Note:-

( If your Datafile paths are different then important parameter are.

DB_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/DB11G/,/u01/app/oracle/oradata/NEWSID/)

LOG_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/DB11G/,/u02/app/oracle/oradata/NEWSID/)

3) Create required directories on Target.

e.g.
$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump


4) Copy  all the  RMAN backup on Target server  
$ cd  /backup/prod_backup/
$ scp  -r  oracle@hostnamesource:/backup/* .



5) Connect to  Target Instance.

$ ORACLE_SID=DB11G; export ORACLE_SID
$ sqlplus / as sysdba
Start the database in NOMOUNT mode using pfile which is created in step2.

SQL> STARTUP NOMOUNT;


6)  For clonning we are going to use Duplicate command and use the full rman backups which we have copied from source to /backup/prod_backup  on Target server. For duplicating the database we need to connect auxiliary instance. Run the below script for restore.

$ cat Duplicate.sh
#set -x

# Declare your ORACLE environment variables
ORACLE_SID=qaclone
export ORACLE_SID=qaclone
COPY_DATE=`date +"%Y-%m-%d:%T"`

#export ORACLE_BASE= /u01/app/oracle
#export ORACLE_HOME=/oracle/app/oracle/product/11.2.0.3/db

# Start the rman commands
rman auxiliary /  LOG /home/oracle/Duplicate_Datbase_$COPY_DATE   << EOF

run {
allocate auxiliary channel t1 type disk;
allocate auxiliary channel t2 type disk;
allocate auxiliary channel t3 type disk;
allocate auxiliary channel t4 type disk;
allocate auxiliary channel t5 type disk;
allocate auxiliary channel t6 type disk;
allocate auxiliary channel t7 type disk;
allocate auxiliary channel t8 type disk;

duplicate database to "QACLONE" backup location '/backup/prod_backup' nofilenamecheck;
}

EXIT;
EOF



7)  After running above script monitor the logfile for any errors if duplication is successfully database will be opened by RMAN.


8) We have to remove the thread 2   logfiles as below.


Once the database is opened successfully, you may remove the redolog groups for redo threads of other instances.
SQL> select THREAD#, STATUS, ENABLED
2 from v$thread;
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED PRIVATE
SQL> select group# from v$log where THREAD#=2;
GROUP#
----------
4
5
6
SQL> alter database disable thread 2;
Database altered.
SQL> alter database clear unarchived logfile group 4; ( repeat for 4 to 6)
Database altered.
SQL> alter database drop logfile group 4; ( repeat for 4 to 6)
Database altered.



9) Delete the UNDO tablespace related to Node 2.

- Now you can remove the undo tablespaces of other instances.
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. 

After completing above steps our clone database will be ready for use.