Environment detailed
Steps:-2
Shut down PROD server.
SQL> shutdown immediate
SQL> !
Steps:-3
Copy source PFILE to Target instance.
Go to Pfile location on PROD:-
Edit Pfile on Target server:-
Steps:-4
Copy Logfile:-
OS:- Linux7.2 / 64 bits.
Oracle Database:- 12.1.0
Cold clone PROD to UAT environment
What is Database Cloning?
Cloning is a process that can be used to create a copy of the existing Oracle database. Sometimes DBA’s need to clone databases to test backup, development environments and Relocating an database to another machine, and recovery strategies or export a table that was dropped from the production database and import it back into the production database.
Cold backup Database Cloning is simplest method to clone the database . The disadvantage with this approach is that database will be down during cold backup.
The high level steps for How to clone the database using Cold backup Database Cloning
1) Take the cold backup of the database to be cloned
2) Copy all the backup files to the destination server
3) Create the init.ora ,then startup nomount then create the control file on the new location
4) alter database open resetlogs to bring the new clone online
1) Take the cold backup of the database to be cloned
2) Copy all the backup files to the destination server
3) Create the init.ora ,then startup nomount then create the control file on the new location
4) alter database open resetlogs to bring the new clone online
PROD_SID= Airtel (Source) Host_name:-sujeet
UAT_SID= Idea (Target) Host_name:- Sonu
Steps:-1
Login with Source Instance(PROD).
[oracle@sujeet ~]$ sqlplus / as sysdba
SQL> select INSTANCE_NAME,STATUS from v$INSTANCE;
INSTANCE_NAME STATUS
---------------- ------------
Airtel OPEN
SQL> create pfile from spfile;
File created.
SQL> select name, value From v$parameter Where
name='user_dump_dest';
NAME
VALUE
--------------------------------------------------------------------------------
user_dump_dest /Airtel/product/12.1.0/dbhome_1/rdbms/log
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/Airtel/oradata/airtel/Airtel/datafile/o1_mf_system_cmrsn65x_.dbf
/Airtel/oradata/airtel/Airtel/datafile/tfqa01.dbf
/Airtel/oradata/airtel/Airtel/datafile/o1_mf_sysaux_cmrslrt4_.dbf
/Airtel/oradata/airtel/Airtel/datafile/o1_mf_undotbs1_cmrsopm4_.dbf
/Airteloradata/airtel/Airtel/datafile/rcqa01.dbf
/Airtel/oradata/airtel/Airtel/datafile/o1_mf_users_cmrsomgs_.dbf
/Airtel/oradata/airtel/Airtel/datafile/icdata01.dbf
/Airtel/oradata/airtel/Airtel/datafile/icindx01.dbf
8 rows selected.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/Airtel/oradata/airtel/Airtel/onlinelog/o1_mf_3_cmrspswy_.log
/Airtel/fast_recovery_area/Airtel/onlinelog/o1_mf_3_cmrspt1y_.log
/Airtel/oradata/airtel/Airtel/onlinelog/o1_mf_2_cmrsprnj_.log
/Airtel/fast_recovery_area/Airtel/onlinelog/o1_mf_2_cmrsprs3_.log
/Airtel/oradata/acsqa/Airtel/onlinelog/o1_mf_1_cmrspqcg_.log
/Airtel/fast_recovery_area/Airtel/onlinelog/o1_mf_1_cmrspqjb_.log
6 rows selected.
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /Airtel/oradata/airtel/Airtel /controlfile/o1_mf_cmrspnsv.ctl
Create the script that will re-create the control file:
SQL> alter database backup controlfile to trace;
Database altered.
Steps:-2
Shut down PROD server.
SQL> shutdown immediate
SQL> !
Steps:-3
Copy source PFILE to Target instance.
Go to Pfile location on PROD:-
[oracle@sujeet dbs]$ cd /Airtel/product/12.1.0/dbhome_1/dbs
[oracle@sujeet dbs]$ cp initacsqa.ora /Idea/product/12.1.0/dbhome_1/dbs/
Rename PFILE on Target instance
[oracle@sonu dbs]$ mv initAirtel.ora
initIdea.ora
[oracle@sonu dbs]$ ll
-rw-r--r-- 1 oracle oinstall 1002 Dec 29 01:50
[oracle@sonu dbs]$ chmod 777
Edit Pfile on Target server:-
Idea.__data_transfer_cache_size=0
Idea.__db_cache_size=8388608
Idea.__java_pool_size=16777216
Idea.__large_pool_size=33554432
Idea.__oracle_base='Idea'#ORACLE_BASE set from
environment
Idea.__pga_aggregate_target=603979776
Idea.__sga_target=989855744
Idea.__shared_io_pool_size=0
Idea.__shared_pool_size=230686720
Idea.__streams_pool_size=0
*.audit_file_dest='/Idea/admin/idea/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/Idea/oradata/idea/IDEA/controlfile/o1_mf_cmrspnsv_.ctl','/Idea/fast_recovery_area/IDEA/controlfile/o1_mf_cmrspnxf_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/Idea/oradata/idea'
*.db_domain='sonu.net'
*.db_name='Idea'
*.db_recovery_file_dest='/Idea/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/Idea'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=IdeaXDB)'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
Steps:-4
copy new trace file from PROD to UAT server.
[oracle@sujeet ~]$ cd /Airtel/product/12.1.0/dbhome_1/rdbms/log
[oracle@sujeet log]$ cp Airtel_ora_4547.trc /Idea/product/12.1.0/dbhome_1/rdbms/log
Rename trace file with .sql format.
[oracle@sonu ]$ cd /Idea/product/12.1.0/dbhome_1/rdbms/log
[oracle@sonu log]$ mv Airtel_ora_4547.trc Idea_c1.sql
Edit control file created scripts.
[oracle@sonu log]$ vi
CREATE CONTROLFILE SET DATABASE "IDEA" RESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP
1'/Idea/oradata/Idea/IDEA/onlinelog/o1_mf_1_cmrspqcg_.log' SIZE 50M,
GROUP 2'/Idea/oradata/Idea/IDEA/onlinelog/o1_mf_2_cmrsprnj_.log'
SIZE 50M,
GROUP 3'/Idea/oradata/Idea/IDEA/onlinelog/o1_mf_3_cmrspswy_.log'
SIZE 50M,
GROUP 4'/Idea/fast_recovery_area/IDEA/onlinelog/o1_mf_1_cmrspqjb_.log'
SIZE 50M,
GROUP 5'/Idea/fast_recovery_area/IDEA/onlinelog/o1_mf_2_cmrsprs3_.log'
SIZE 50M,
GROUP 6'/Idea/fast_recovery_area/IDEA/onlinelog/o1_mf_3_cmrspt1y_.log'
SIZE 50M
DATAFILE
'/Idea/oradata/Idea/IDEA/datafile/o1_mf_system_cmrsn65x_.dbf',
'/Idea/oradata/Idea/IDEA/datafile/tfqa01.dbf',
'/Idea/oradata/Idea/IDEA/datafile/o1_mf_sysaux_cmrslrt4_.dbf',
'/Idea/oradata/Idea/IDEA/datafile/o1_mf_undotbs1_cmrsopm4_.dbf',
'/Idea/oradata/Idea/IDEA/datafile/rcqa01.dbf',
'/Idea/oradata/Idea/IDEA/datafile/o1_mf_users_cmrsomgs_.dbf',
'/Idea/oradata/Idea/IDEA/datafile/icdata01.dbf',
'/Idea/oradata/Idea/IDEA/datafile/icindx01.dbf'
CHARACTER SET WE8ISO8859P1
;
ALTER DATABASE OPEN RESETLOGS;
:wq <SAVE this file as is it>
Steps:-5
Copy Datafile, Logfile & ORACLE_HOME PROD to UAT Server.
Copy Datafile:-
[oracle@sujeet datafile]$cd /Airtel/oradata/airtel/Airtel/datafile
[oracle@sujeet datafile]$ cp * /Idea/oradata/idea/IDEA/datafile
[oracle@sujeet onlinelog]$ cd
/Airtel/fast_recovery_area/AIRTEL/onlinelog
[oracle@sujeet onlinelog]$ cp * /Idea/fast_recovery_area/IDEA/onlinelog
[oracle@sujeet ~]$ cd
/Airtel/oradata/airtel/AIRTEL/onlinelog
[oracle@sujeet onlinelog]$ cp * /Idea/oradata/idea/IDEA/onlinelog
Copy ORACLE_HOME:-
[oracle@sujeet ~]$ cd $ORACLE_HOME
[oracle@sujeet dbhome_1 ~]$ cp -r * /Idea/product/12.1.0/dbhome_1
Steps:-6
Set .Bash_Profile on UAT Server.
Steps:-7
[oracle@sonu ~]$ . .bash_profile
[oracle@sonu ~]$ sqlplus / as sysdba
SQL> startup nomount
pfile='/Idea/product/12.1.0/dbhome_1/dbs/initIdea.ora';
ORACLE instance started.
Total System Global Area
251658240 bytes
Fixed Size
2923096 bytes
Variable Size 192939432 bytes
Database Buffers
50331648 bytes
Redo Buffers
5464064 bytes
Steps:-8
Run the re-created control file script on IDEA clone database:
SQL> @
Control file created.
Database altered.
Steps:-9
SQL> select INSTANCE_NAME,STATUS from v$INSTANCE;
INSTANCE_NAME
STATUS
---------------- ------------
Idea OPEN
SQL> create spfile from pfile;
File created.
Check New clone UAT server files location.
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> show parameter control_files;
*************************************END******************************************
No comments:
Post a Comment