Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Archive for the ‘Clone11g DB or Duplicate’ Category

Clone Oracle11gR2 Database on Same Windows Server (without using RMAN)

Posted by Hendry chinnapparaj on February 14, 2012

Source Database : oracle11gr2
Clone New Database: clone1
Mode:- No Archive Mode
Login to Source Database – oracle11gr2
—————————-
SQL> create pfile from spfile;
File created.

SQL> alter database backup controlfile to trace;
Database altered.

mkdir G:\app\oracle\admin\clone1
mkdir G:\app\oracle\admin\clone1\adump
mkdir G:\app\oracle\oradata\clone1 — copy all the datafiles, redo log files here from the source.

Location of spfile and pfile
G:\app\oracle\product\11.2.0\dbhome_1\database
Copy INIToracle11gr2.ora as INITclone1.ora
INITclone1.ora
SPFILECLONE1.ORA
Edit INTclone1.ora and make the following changes and more if required.
*.audit_file_dest=’G:\app\oracle\admin\clone1\adump’
*.control_files=’G:\app\oracle\oradata\clone1\control01.ctl’,’G:\app\oracle\recovery_area\clone1\control02.ctl’
*.db_name=’clone1′
*.db_unique_name=’clone1′

create the script for Controlfile creation from the trace file generated in trace location
G:\app\oracle\diag\rdbms\oracle11gr2\oracle11gr2\trace\oracle11gr2_dbrm_4028.trc

vi createcon.sql
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE “CLONE1” RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘G:\APP\ORACLE\ORADATA\CLONE1\REDO01.LOG’ SIZE 50M BLOCKSIZE 512,
GROUP 2 ‘G:\APP\ORACLE\ORADATA\CLONE1\REDO02.LOG’ SIZE 50M BLOCKSIZE 512,
GROUP 3 ‘G:\APP\ORACLE\ORADATA\CLONE1\REDO03.LOG’ SIZE 50M BLOCKSIZE 512
DATAFILE
‘G:\APP\ORACLE\ORADATA\CLONE1\SYSTEM01.DBF’,
‘G:\APP\ORACLE\ORADATA\CLONE1\SYSAUX01.DBF’,
‘G:\APP\ORACLE\ORADATA\CLONE1\UNDOTBS01.DBF’,
‘G:\APP\ORACLE\ORADATA\CLONE1\USERS01.DBF’,
‘G:\APP\ORACLE\ORADATA\CLONE1\EXAMPLE01.DBF’
CHARACTER SET WE8MSWIN1252
;

RECOVER DATABASE USING BACKUP CONTROLFILE;

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE ‘G:\APP\ORACLE\ORADATA\CLONE1\TEMP01.DBF’
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

😡
Create the Listener Database Service, Tns entry and also the NEW Service

G:\app\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN
vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = clone1)
(ORACLE_HOME = G:\app\oracle\product\11.2.0\dbhome_1)
(SID_NAME = clone1)
)
)

😡
G:\app\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN
vi tnsnames.ora
CLONE1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.174.131)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = clone1)
)
)

😡

G:\>oradim -NEW -SID CLONE1 -SRVC OracleServiceCLONE1 -STARTMODE manual -PFILE G:\app\oracle\product\11.2.0\dbhome_1\database\initclone1.ora
Instance created.

Go to the services and check if the New Oracle Service is running.
OracleServiceCLONE1
OracleJobSchedulerCLONE1
Oracle CLONE1 VSS Writer Service
Shutdown and Copy the Database files from the Source Oracle11gr2 database.

Create the Controlfile and startup the database

Open command prompt in Windows
set oracle_sid=clone1
sqlplus / as sysdba
SQL> @createcon.sql

ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1375032 bytes
Variable Size 318768328 bytes
Database Buffers 96468992 bytes
Redo Buffers 6057984 bytes

Control file created.

Then open the database.

If you have copied the database files when the source database was running, then you will get error like

ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘G:\APP\ORACLE\ORADATA\CLONE1\SYSTEM01.DBF’
Try the recovery steps below

copy also the online redo logfiles from the source database to the new clone1 database directory and specify the full path during recovery.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1060604 generated at 02/14/2012 11:02:25 needed for thread 1
ORA-00289: suggestion :
G:\APP\ORACLE\RECOVERY_AREA\CLONE1\ARCHIVELOG\2012_02_14\O1_MF_1_7_%U_.ARC
ORA-00280: change 1060604 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
G:\app\oracle\oradata\clone1\REDO01.LOG
Log applied.
Media recovery complete.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL>

Create the Temporary tablespace

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘G:\APP\ORACLE\ORADATA\CLONE1\TEMP01.DBF

2 SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.
SQL> select name,created, open_mode from v$database;

NAME CREATED OPEN_MODE
——— ——— ——————–
CLONE1 14-FEB-12 READ WRITE
==================================

SQL to generate the copy script

Select ‘COPY DATAFILE ‘ ||FILE_ID||’ TO “G:\app\oracle\oradata\clone1\’||SUBSTR(FILE_NAME,38,50)|| ‘”;’ from dba_data_files;
‘COPYDATAFILE’||FILE_ID||’TO”G:\APP\ORACLE\ORADATA\CLONE1\’||SUBSTR(FILE_NAME,38
——————————————————————————–
COPY DATAFILE 4 TO “G:\app\oracle\oradata\clone1\RS01.DBF”;
COPY DATAFILE 3 TO “G:\app\oracle\oradata\clone1\OTBS01.DBF”;
COPY DATAFILE 2 TO “G:\app\oracle\oradata\clone1\AUX01.DBF”;
COPY DATAFILE 1 TO “G:\app\oracle\oradata\clone1\TEM01.DBF”;
COPY DATAFILE 5 TO “G:\app\oracle\oradata\clone1\MPLE01.DBF”;

5 rows selected.

Shalom

H

Advertisements

Posted in Clone11g DB or Duplicate | Leave a Comment »