Tuesday, August 02, 2005

Database Cloning

How to create database base on existing database.
You can able to clone database in same machine using Oracle Database Configuration Assistant but what about to copy database from Production to Test Server.


So I had done this through below way.
I have 2 Oracle Database EE 9.2 on Windows 2000 Server.
I had done below task on my A server.

CREATE PFILE=’d:/oracle/ora92/inittest.ora’ FROM SPFILE;
shutdown the database instance.
make a copy of oracle database files from oracle/ora92/oradata//*.* to temporary location including your PFILE so your database will not be down for more time.

I had done below taks on my B server.
1) create directory in oracle/ora92/oradata/ ie. Test
2) copy all files from temporary location where u had copy in A server. To ur directory in above steps.
3) go to command prompt.
set ORACLE_SID=TEST
create windows service through below command
ORADIM -new -sid babul92 -intpwd oracle -startmode manual
or
ORADIM -new -sid TEST -intpwd password -startmode auto -pfile
%ORACLE_HOME%\DATABASE\INITTEST.ORA
4) Start the above service. (if you mention with –startmode auto no need to start it manually It will start automatically after that command)
5) lsnrctl stop
6) add below entry into listener.ora (which u find in D:\oracle\ora92\network\admin)
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = test)
)
7) lsnrctl start
8) Create test directory into D:\oracle\admin (where test is your instance name)
9) open D:\oracle\admin\test directory & create below directory in it.
bdump
cdump
create
pfile
udump
10) go to command prompt and enter sqlplus /nolog
CONNECT SYS/password AS SYSDBA
-- create the server parameter file
CREATE SPFILE='d:/oracle/ora92/database/spfilemynewdb.ora' FROM
PFILE='d:/oracle/ora92/database/initmynewdb.ora';
startup

Note : if you get error at time of startup you can create password file through below command
orapwd file=d:\oracle\ora92\database\pwdtest.ora password=oracle
Now if you wish to set oracle database should start automaticaly when ur service start (if u choose mannual in steps 3) then follow below steps.
1) Open Administration Assistant for Windows NT which u find in Oracle - OraHome92 -> Configuration and Migration Tools
2) Double click on Oracle Managed Objects then Computers then nikunj then Databases single click on your SID (TEST)
3) Right click on it and select Startup/Shutdown options.
4) check mark Start up instance when service is started.
orapwd file=d:\oracle\ora92\database\pwdtryguard.ora password=oracle
CONNECT SYS/password AS SYSDBA
-- create the server parameter file
CREATE SPFILE='d:/oracle/ora92/database/spfilemynewdb.ora' FROM
PFILE='d:/oracle/ora92/database/initmynewdb.ora';
SHUTDOWN

-- this time you will start up using the server parameter file
CONNECT SYS/password AS SYSDBA
STARTUP
EXIT
orapwd file=d:\oracle\ora92\database\pwdtryguard.ora password=oracle


IF database in different directory then
I have 2 Oracle Database EE 9.2 on Windows 2000 Server.
I had done below task on my A server.
CREATE PFILE=’d:/oracle/ora92/inittest.ora’ FROM SPFILE;
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
This will create control file statement in your server trace file (at last) i.e.
D:\oracle\admin\nikunj\udump
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "NIKUNJ" RESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'D:\ORACLE\ORADATA\NIKUNJ\REDO01.LOG' SIZE 100M,
GROUP 2 'D:\ORACLE\ORADATA\NIKUNJ\REDO02.LOG' SIZE 100M,
GROUP 3 'D:\ORACLE\ORADATA\NIKUNJ\REDO03.LOG' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'D:\ORACLE\ORADATA\NIKUNJ\SYSTEM01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\UNDOTBS01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\CWMLITE01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\DRSYS01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\EXAMPLE01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\INDX01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\ODM01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\TOOLS01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\XDB01.DBF'
CHARACTER SET WE8MSWIN1252
;
# Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
# Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''D:\oracle\orabackup\%F''');
# Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT ''d:\oracle\orabackup\backup_db_%d_S_%s_P_%p_T_%t'' MAXPIECESIZE 250 M');
# Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('MAXSETSIZE TO','UNLIMITED');
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\ORADATA\NIKUNJ\TEMP01.DBF'
SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
# End of tempfile additions.
#
shutdown the database instance.
make a copy of oracle database files from oracle/ora92/oradata//*.* to temporary location including your PFILE so your database will not be down for more time.
I had done below taks on my B server.
1) create directory in oracle/ora92/oradata/ ie. Test
2) copy all files from temporary location where u had copy in A server. To ur directory in above steps.
3) go to command prompt.
set ORACLE_SID=TEST
create windows service through below command
ORADIM -new -sid babul92 -intpwd oracle -startmode manual
or
ORADIM -new -sid TEST -intpwd password -startmode auto -pfile
%ORACLE_HOME%\DATABASE\INITTEST.ORA
4) Start the above service. (if you mention with –startmode auto no need to start it manually It will start automatically after that command)
5) lsnrctl stop
6) add below entry into listener.ora (which u find in D:\oracle\ora92\network\admin)
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = test)
)
7) lsnrctl start
8) Create test directory into D:\oracle\admin (where test is your instance name)
9) open D:\oracle\admin\test directory & create below directory in it.
bdump
cdump
create
pfile
udump
10) go to command prompt and enter sqlplus /nolog
1. CONNECT SYS/password AS SYSDBA
-- create the server parameter file
2. CREATE SPFILE='d:/oracle/ora92/database/spfilemynewdb.ora' FROM
PFILE='d:/oracle/ora92/database/initmynewdb.ora';
3. paste your edited control file create script.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "NIKUNJ" RESETLOGS
ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'C:\ORACLE\ORADATA\NIKUNJ\REDO01.LOG' SIZE 100M,
GROUP 2 'C:\ORACLE\ORADATA\NIKUNJ\REDO02.LOG' SIZE 100M,
GROUP 3 'C:\ORACLE\ORADATA\NIKUNJ\REDO03.LOG' SIZE 100M
DATAFILE
'C:\ORACLE\ORADATA\NIKUNJ\SYSTEM01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\UNDOTBS01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\CWMLITE01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\DRSYS01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\EXAMPLE01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\INDX01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\ODM01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\TOOLS01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\XDB01.DBF'
CHARACTER SET WE8MSWIN1252
;
ALTER DATABASE OPEN RESETLOGS;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\NIKUNJ\TEMP01.DBF'
SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Shutdown immediate;
Startup;

No comments: