Clone 12C database with another server 12C.
==============================================
references:
https://www.youtube.com/watch?v=QqP7a3_ZDrw&list=PLLoxSRrtzDyDy5ExxR8tFXfdYWhU4e4Gl&index=1
https://www.youtube.com/watch?v=FB6YkK8b8mc&list=PLLoxSRrtzDyDy5ExxR8tFXfdYWhU4e4Gl&index=2
Prerequisite.
* Install same oracle version 11g/12C in new server with software only option.
* make sure to user same paths as existing server for easy configuration. If paths are different it will be complex.
---------------------------------------------------------------------------------
C:\Users\Administrator>ORADIM -NEW -SID TEST -SYSPWD manager
DIM-00019: create service error
O/S-Error: (OS 1073) The specified service already exists.
C:\Users\Administrator>oradim -delete -sid test
Unable to stop service, OS Error = 1062
Instance deleted.
-----------------------------------------------------------
C:\Users\Administrator>ORADIM -NEW -SID TEST -SYSPWD manager
Instance created.
-----------------------------------------------------------
Go to existing database and create pfile and control file trace file
====================================================================
C:\Users\administrator.SMCHO>d:
D:\>sqlplus sys@test as sysdba
SQL> create pfile='\pfileTEST.ora' from spfile;
File created.
SQL> alter database backup controlfile to trace as '\CONTROLFILE.ORA';
Database altered.
-------------------------------------------------------------------
* move new control file and init file in to the new server.
* edit init file according to new SID (if changed) and file paths if changed.
========================================================================
test.__data_transfer_cache_size=0
test.__db_cache_size=18387828736
test.__java_pool_size=469762048
test.__large_pool_size=536870912
test.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment
test.__pga_aggregate_target=4294967296
test.__sga_target=21474836480
test.__shared_io_pool_size=536870912
test.__shared_pool_size=1476395008
test.__streams_pool_size=0
*.audit_file_dest='D:\app\Administrator\admin\TEST\adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='D:\ORADATA\TEST\CONTROL01.CTL','D:\ORADATA\TEST\CONTROL02.CTL','D:\ORADATA\TEST\CONTROL03.CTL'#Restore Controlfile
*.db_block_size=8192
*.db_cache_size=800m
*.db_domain=''
*.db_name='TEST'#Reset to original value by RMAN
*.diagnostic_dest='D:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTXDB)'
*.fixed_date='NONE'
*.java_pool_size=250m
*.job_queue_processes=10
*.large_pool_size=100m
*.nls_language='AMERICAN'
*.nls_length_semantics='CHAR'
*.nls_territory='SRI LANKA'
*.open_cursors=2048
*.optimizer_index_cost_adj=10
*.pga_aggregate_limit=0
*.pga_aggregate_target=4294967296
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=21474836480
*.sga_target=21474836480
*.shared_pool_size=800m
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'
==================================================================
copy below line from trace of control file and create a sql file called create.sql
** make sure to change paths and other parameters if changed from existing.
========================================================================
change below lines
original line => CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS NOARCHIVELOG
Edited line => CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG
========================================================================
CREATE CONTROLFILE
SET DATABASE "TEST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 9348
LOGFILE
GROUP 1 (
'D:\ORADATA\TEST\REDO011.LOG',
'D:\ORADATA\TEST\REDO012.LOG'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
'D:\ORADATA\TEST\REDO021.LOG',
'D:\ORADATA\TEST\REDO022.LOG'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
'D:\ORADATA\TEST\REDO031.LOG',
'D:\ORADATA\TEST\REDO032.LOG'
) SIZE 50M BLOCKSIZE 512
DATAFILE
'D:\ORADATA\TEST\SYSTEM01.DBF',
'D:\ORADATA\TEST\SYSAUX01.DBF',
'D:\ORADATA\TEST\UNDOTBS01.DBF',
'D:\ORADATA\TEST\IFSAPP_ARCHIVE_DATA01.DBF',
'D:\ORADATA\TEST\IFSAPP_ARCHIVE_INDEX01.DBF',
'D:\ORADATA\TEST\IFSAPP_DATA01.DBF',
'D:\ORADATA\TEST\IFSAPP_INDEX01.DBF',
'D:\ORADATA\TEST\IFSAPP_LOB01.DBF',
'D:\ORADATA\TEST\IFSAPP_REPORT_DATA01.DBF',
'D:\ORADATA\TEST\IFSAPP_REPORT_INDEX01.DBF',
'D:\ORADATA\TEST\USERS01.DBF',
'D:\ORADATA\TEST\IFSAPP_LOB02.DBF',
'D:\ORADATA\TEST\IFSAPP_DATA02.DBF',
'D:\ORADATA\TEST\IFSAPP_INDEX02.DBF',
'D:\ORADATA\TEST\IFSAPP_DATA03.DBF',
'D:\ORADATA\TEST\IFSAPP_LOB03.DBF',
'D:\ORADATA\TEST\IFSAPP_LOB04.DBF',
'D:\ORADATA\TEST\IFSAPP_DATA04.DBF',
'D:\ORADATA\TEST\IFSAPP_LOB05.DBF',
'D:\ORADATA\TEST\IFSAPP_LOB06.DBF',
'D:\ORADATA\TEST\IFSAPP_DATA05.DBF',
'D:\ORADATA\TEST\IFSAPP_INDEX03.DBF'
CHARACTER SET AL32UTF8
;
========================================================================
-----------------------------------------------------------
SQL> create spfile from pfile='\pfileTEST.ora';
File created.
SQL>
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2.1475E+10 bytes
Fixed Size 3056960 bytes
Variable Size 2483030720 bytes
Database Buffers 1.8925E+10 bytes
Redo Buffers 64049152 bytes
SQL> @\create.sql;
Control file created.
SQL> alter database open resetlogs;
Database altered.
SQL> select name from v$database;
NAME
---------
TEST
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:\ORADATA\TEST\SYSTEM01.DBF
D:\ORADATA\TEST\SYSAUX01.DBF
D:\ORADATA\TEST\UNDOTBS01.DBF
D:\ORADATA\TEST\IFSAPP_ARCHIVE_DATA01.DBF
D:\ORADATA\TEST\IFSAPP_ARCHIVE_INDEX01.DBF
D:\ORADATA\TEST\IFSAPP_DATA01.DBF
D:\ORADATA\TEST\IFSAPP_INDEX01.DBF
D:\ORADATA\TEST\IFSAPP_LOB01.DBF
D:\ORADATA\TEST\IFSAPP_REPORT_DATA01.DBF
D:\ORADATA\TEST\IFSAPP_REPORT_INDEX01.DBF
D:\ORADATA\TEST\USERS01.DBF
NAME
--------------------------------------------------------------------------------
D:\ORADATA\TEST\IFSAPP_LOB02.DBF
D:\ORADATA\TEST\IFSAPP_DATA02.DBF
D:\ORADATA\TEST\IFSAPP_INDEX02.DBF
D:\ORADATA\TEST\IFSAPP_DATA03.DBF
D:\ORADATA\TEST\IFSAPP_LOB03.DBF
D:\ORADATA\TEST\IFSAPP_LOB04.DBF
D:\ORADATA\TEST\IFSAPP_DATA04.DBF
D:\ORADATA\TEST\IFSAPP_LOB05.DBF
D:\ORADATA\TEST\IFSAPP_LOB06.DBF
D:\ORADATA\TEST\IFSAPP_DATA05.DBF
D:\ORADATA\TEST\IFSAPP_INDEX03.DBF
22 rows selected.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
D:\ORADATA\TEST\REDO031.LOG
D:\ORADATA\TEST\REDO032.LOG
D:\ORADATA\TEST\REDO021.LOG
D:\ORADATA\TEST\REDO022.LOG
D:\ORADATA\TEST\REDO011.LOG
D:\ORADATA\TEST\REDO012.LOG
6 rows selected.
Add new temp files
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORADATA\TEST\TEMP01.DBF' SIZE 1G REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
No comments:
Post a Comment