clone database oracle

===============================================================
source
===============================================================
1. Konfigurasi RMAN pada database harus dalam mode archive log.
[oracle@iscdd ~]$ ORACLE_SID=iscdd; export ORACLE_SID
[oracle@iscdd ~]$ sqlplus / as sysdba
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

2. Konfigurasi RMAN agar archive log disimpan pada folder tertentu sesuai dengan DB_RECOVERY_FILE_DEST.
SQL> host;
[oracle@iscdd ~]$ mkdir /u02/archive/iscdd
[oracle@iscdd ~]$ exit
SQL> alter system set log_archive_dest_1=’location=/u02/archive/iscdd/archive01′;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/archive/iscdd/archive01
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL> exit;

3. Setting RMAN
[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman target / NOCATALOG
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Sep 12 21:51:36 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISCDD (DBID=2058697900)
using target database control file instead of recovery catalog
RMAN> configure controlfile autobackup on;
RMAN> configure backup optimization on;
RMAN> host;
[oracle@iscdd bin]$ mkdir /home/oracle/backup/iscdd/ -p
[oracle@iscdd bin]$ exit
RMAN> configure channel device type disk format ‘/home/oracle/backup/iscdd/%d-id-%I-%s.bkp’;
RMAN> exit;

4. Test
a. tambahan tablespace
[oracle@iscdd ~]$ ORACLE_SID=iscdd; export ORACLE_SID
[oracle@iscdd ~]$ sqlplus / as sysdba
SQL> create tablespace senin_ts
datafile ‘/u02/oradata/iscdd/iscdd/senin_ts01.dbf’
size 2M
segment space management auto;

b. create pfile
SQL> create pfile=’$ORACLE_HOME/dbs/initclone.ora’ from spfile;

c. tambahkan record.
SQL> conn scott/tiger;
SQL> insert into dept values(61,’IT’,’surabaya’);
SQL> insert into dept values(62,’IT’,'madiun’);
SQL> commit;
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
61 IT surabaya
62 IT madiun
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
6 rows selected.
SQL> exit;

5. Pastikan RMAN
[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman target / NOCATALOG
RMAN> delete noprompt obsolete;
RMAN> backup database plus archivelog delete input TAG=clone_backup;
RMAN> exit;

===============================================================
target
===============================================================
6. Menyiapkan directory admin oracle, datafile oracle dan oratab.
Misalkan diinginkan SID bernama clone
[oracle@iscdd ~]$ id
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba),502(oper),503(asmadmin)

a. menyiapkan folder admin oracle clone
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/adump
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/bdump
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/cdump
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/dpdump
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/pfile
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/scripts
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/udump
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/flash_recovery_area

b. menyiapkan folder datafile dan archive oracle clone
[oracle@iscdd ~]$ mkdir -p /u02/oradata/clone/iscdd
[oracle@iscdd ~]$ mkdir -p /u02/archive/clone

c. menyiapkan oratab
[oracle@iscdd ~]$ echo “clone:/u01/app/oracle/product/10.2.0/db_1:N” >> /etc/oratab
[oracle@iscdd ~]$ ORACLE_SID=clone; export ORACLE_SID
[oracle@iscdd ~]$ oraenv
ORACLE_SID = [clone] ?

7. Menyiapkan Oracle Password File oracle clone
[oracle@iscdd ~]$ orapwd file=$ORACLE_HOME/dbs/orapwclone password=ora

8. Menyiapkan file init untuk clone
[oracle@iscdd ~]$ vi $ORACLE_HOME/dbs/initclone.ora

tambahkan dan edit, jika folder berbeda dengan perintah :
*.db_file_name_convert=’/u02/oradata/iscdd’,'/u02/oradata/clone’
*.log_file_name_convert=’/u02/oradata/iscdd’,'/u02/oradata/clone’

======================
selengkapnya :
====================

clone.__db_cache_size=717225984
clone.__java_pool_size=4194304
clone.__large_pool_size=4194304
clone.__shared_pool_size=268435456
clone.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/clone/adump’
*.background_dump_dest=’/u01/app/oracle/admin/clone/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u02/oradata/clone/clone/control01.ctl’,'/u02/oradata/clone/clone/control02.ctl’,'/u02/oradata/clone/clone/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/clone/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_file_name_convert=’/u02/oradata/iscdd’,'/u02/oradata/clone’
*.log_file_name_convert=’/u02/oradata/iscdd’,'/u02/oradata/clone’
*.db_name=’clone’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=cloneXDB)’
*.job_queue_processes=10
*.log_archive_dest_1=’location=/u02/archive/clone/archive01′
*.log_archive_format=’%t_%s_%r.dbf’
*.open_cursors=300
*.pga_aggregate_target=333447168
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=1002438656
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/oracle/admin/clone/udump’

9. Create SPFILE clone
[oracle@iscdd ~]$ ORACLE_SID=clone; export ORACLE_SID
[oracle@iscdd ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 12 14:21:20 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile=’$ORACLE_HOME/dbs/initclone.ora’;

10. Startup nomount clone
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 150667264 bytes
Fixed Size 1298472 bytes
Variable Size 92278744 bytes
Database Buffers 50331648 bytes
Redo Buffers 6758400 bytes
SQL> exit

11. RMAN Clone
[oracle@iscdd ~]$ ORACLE_SID=clone; export ORACLE_SID
[oracle@iscdd ~]$ rman TARGET sys/ora@iscdd AUXILIARY /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Sep 15 22:30:11 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISCDD (DBID=2058697900)
connected to auxiliary database: CLONE (not mounted)

RMAN> DUPLICATE TARGET DATABASE TO “CLONE”;
RMAN> exit;

12. test apakah data terakhir sudah dipindahkan ?
[oracle@iscdd ~]$ ORACLE_SID=clone; export ORACLE_SID
[oracle@iscdd ~]$ sqlplus / as sysdba
SQL> conn scott/tiger
Connected.

SQL> select * from dept;
DEPTNO DNAME LOC
—— ————– ————-
61 IT surabaya
62 IT madiun
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

6 rows selected.
SQL> exit

13. Configure tnsnames.ora and listner.ora instance clone
[oracle@iscdd ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
clone =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = iscdd)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = clone)
)
)

[oracle@iscdd ~]$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = iscdd)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = clone)(PORT = 1521))
)
)

[oracle@iscdd iscdd]$ lsnrctl reload

14. test apakah listener ok ?
[oracle@iscdd iscdd]$ sqlplus scott/tiger@clone
SQL> select * from dept;
DEPTNO DNAME LOC
—— ————– ————-
61 IT surabaya
62 IT madiun
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

6 rows selected.
SQL> exit