Clone database from Oracle 11g RAC (ASM) to single instance (non ASM)
===============================================================
komputer source
===============================================================
1. Setting RMAN
[oracle@node1 bin]$ cd $ORACLE_HOME/bin
[oracle@node1 bin]$ ./rman target / NOCATALOG
Recovery Manager: Release 11.1.0.6.0 - Production on Tue Sep 23 15:57:07 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: NODE (DBID=711660360)
using target database control file instead of recovery catalog
RMAN> configure controlfile autobackup on;
RMAN> configure backup optimization on;
RMAN> host;
[oracle@node1 bin]$ su - root
Password:
[root@node1 ~]# mkdir /u03/backup/node/ -p
[root@node1 ~]# chown oracle.oinstall /u03 -R
[root@node1 ~]# exit
exit
host command complete
RMAN> configure channel device type disk format ‘/u03/backup/node/%d-id-%I-%s.bkp’;
RMAN> exit;
2. Test dengan menambahkan record
a. tambahan 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;
c. list tablespace
[oracle@node1 ~]$ sqlplus / as sysdba
SQL> column tablespace_name format a10
SQL> column file_name format a55
SQL> select tablespace_name, file_name from dba_data_files;
TABLESPACE FILE_NAME
———- ——————————————————-
USERS +DATA1/node/datafile/users.259.666184225
UNDOTBS1 +DATA1/node/datafile/undotbs1.258.666184225
SYSAUX +DATA1/node/datafile/sysaux.257.666184225
SYSTEM +DATA1/node/datafile/system.256.666184225
EXAMPLE +DATA1/node/datafile/example.267.666184357
UNDOTBS2 +DATA1/node/datafile/undotbs2.268.666184499
6 rows selected.
SQL> exit;
3. Lakukan backup ditambah dengan archive log
a. lakukan full backup
[oracle@node1 bin]$ cd $ORACLE_HOME/bin
[oracle@node1 bin]$ rman target /
Recovery Manager: Release 11.1.0.6.0 - Production on Fri Sep 26 11:12:43 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: NODE (DBID=711660360)
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE plus archivelog delete input TAG=full_backup_260908;
b. delete obsolote backup dan lihat daftar backupset
RMAN> delete noprompt obsolete;
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
11 Full 1.16G DISK 00:00:41 26-SEP-08
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20080926T111320
Piece Name: /home/oracle/clone/0bjri7qg_1_2
List of Datafiles in backup set 11
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 Full 1033174 26-SEP-08 +DATA1/node/datafile/system.256.666184225
2 Full 1033174 26-SEP-08 +DATA1/node/datafile/sysaux.257.666184225
3 Full 1033174 26-SEP-08 +DATA1/node/datafile/undotbs1.258.666184225
4 Full 1033174 26-SEP-08 +DATA1/node/datafile/users.259.666184225
5 Full 1033174 26-SEP-08 +DATA1/node/datafile/example.267.666184357
6 Full 1033174 26-SEP-08 +DATA1/node/datafile/undotbs2.268.666184499
7 Full 1033174 26-SEP-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
15 Full 17.98M DISK 00:00:02 26-SEP-08
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20080926T111741
Piece Name: /home/oracle/clone/controlfile
SPFILE Included: Modification time: 26-SEP-08
SPFILE db_unique_name: NODE
Control File Included: Ckp SCN: 1034682 Ckp time: 26-SEP-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
21 Full 17.98M DISK 00:00:02 26-SEP-08
BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20080926T115138
Piece Name: +DATA1/node/autobackup/2008_09_26/s_666445898.325.666445901
SPFILE Included: Modification time: 26-SEP-08
SPFILE db_unique_name: NODE
Control File Included: Ckp SCN: 1041704 Ckp time: 26-SEP-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
24 Incr 0 1.16G DISK 00:00:41 26-SEP-08
BP Key: 26 Status: AVAILABLE Compressed: NO Tag: TAG20080926T115816
Piece Name: /u03/backup/node/NODE-id-711660360-25.bkp
List of Datafiles in backup set 24
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/system.256.666184225
2 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/sysaux.257.666184225
3 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/undotbs1.258.666184225
4 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/users.259.666184225
5 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/example.267.666184357
6 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/undotbs2.268.666184499
BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
25 32.00K DISK 00:00:00 26-SEP-08
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: FULL_BACKUP_260908
Piece Name: /u03/backup/node/NODE-id-711660360-26.bkp
List of Archived Logs in backup set 25
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——— ———- ———
1 59 1066370 26-SEP-08 1066474 26-SEP-08
2 44 1066373 26-SEP-08 1066471 26-SEP-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
26 Full 17.98M DISK 00:00:02 26-SEP-08
BP Key: 28 Status: AVAILABLE Compressed: NO Tag: TAG20080926T115910
Piece Name: +DATA1/node/autobackup/2008_09_26/s_666446350.334.666446353
SPFILE Included: Modification time: 26-SEP-08
SPFILE db_unique_name: NODE
Control File Included: Ckp SCN: 1066483 Ckp time: 26-SEP-08
c. copykan backupset fullbackup ke local file system, dimana terlihat pada langkah diatas
BS (Backup Set) no 11 adalah BS full backup
[oracle@node1 bin]$ mkdir /home/oracle/clone/ -p
[oracle@node1 bin]$ exit
RMAN> backup backupset 24 format ‘/home/oracle/clone/%U’;
d. juga lakukan backup secara manual spfile dan control file
backup spfile include current controlfile format ‘/home/oracle/clone/controlfile’;
RMAN> backup spfile include current controlfile format ‘/home/oracle/clone/controlfile’;
RMAN> exit
e. periksa hasil backup backupset dan controlfile yang akan dibutuhkan untuk clone
[oracle@node1 bin]$ ls /home/oracle/clone/
0pjriaep_1_2 controlfile
f. pindahkan hasil backup ke komputer target
[oracle@node1 bin]$ cd /home/oracle/clone/
[oracle@node1 clone]$ scp 0pjriaep_1_2 controlfile 172.20.141.153:/home/oracle/clone
oracle@172.20.141.153’s password:
===============================================================
komputer target
Sudah diinstall oracle database software
===============================================================
4. Menyiapkan directory admin oracle, datafile oracle dan oratab.
a. menyiapkan folder admin oracle clone
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/adump
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/bdump
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/cdump
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/dpdump
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/pfile
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/scripts
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/udump
[oracle@test ~]$ mkdir -p /u01/app/oracle/flash_recovery_area
b. menyiapkan folder datafile dan archive oracle clone
[oracle@test ~]$ mkdir -p /u01/oradata/node
[oracle@test ~]$ mkdir -p /u01/oradata/node/controlfile/
[oracle@test ~]$ mkdir -p /u01/archive/node
c. menyiapkan oratab
[oracle@test db_1]$ echo “node:/u01/app/oracle/product/11.1.0/db_1:N” >> /etc/oratab
[oracle@test db_1]$ ORACLE_SID=node; export ORACLE_SID
[oracle@test db_1]$ oraenv
ORACLE_SID = [node] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle
5. Menyiapkan Oracle Password File oracle clone
[oracle@test db_1]$ orapwd file=$ORACLE_HOME/dbs/orapwnode password=ora
6. Restore SPFILE
[oracle@test ~]$ rman
Recovery Manager: Release 11.1.0.6.0 - Production on Sat Sep 26 04:38:49 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> connect target;
connected to target database (not started)
RMAN> restore spfile from ‘/home/oracle/clone/controlfile’;
Starting restore at 26-SEP-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/clone/controlfile
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 26-SEP-08
RMAN> exit
Recovery Manager complete.
[oracle@test ~]$ sqlplus / as sysdba
SQL> create pfile from spfile;
File created.
SQL> exit
7. Edit PFILE
a. asli pfile
[oracle@test ~]$ vi $ORACLE_HOME/dbs/initnode.ora
node2.__db_cache_size=322961408
node1.__db_cache_size=281018368
node2.__java_pool_size=4194304
node1.__java_pool_size=12582912
node2.__large_pool_size=4194304
node1.__large_pool_size=4194304
node1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
node2.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
node2.__pga_aggregate_target=293601280
node1.__pga_aggregate_target=343932928
node2.__sga_target=562036736
node1.__sga_target=511705088
node2.__shared_io_pool_size=0
node1.__shared_io_pool_size=0
node2.__shared_pool_size=222298112
node1.__shared_pool_size=205520896
node2.__streams_pool_size=0
node1.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/node/adump’
*.audit_trail=’db’
*.cluster_database_instances=2
*.cluster_database=true
*.compatible=’11.1.0.0.0′
*.control_files=’+DATA1/node/controlfile/current.261.666184329′,’+DATA1/node/controlfile/current.260.666184329′
*.db_block_size=8192
*.db_create_file_dest=’+DATA1′
*.db_domain=”
*.db_name=’node’
*.db_recovery_file_dest=’+DATA1′
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=nodeXDB)’
node1.instance_number=1
node2.instance_number=2
*.log_archive_dest_1=’location=/u03/archive01′
*.log_archive_format=’%t_%s_%r.dbf’
*.memory_target=852492288
*.open_cursors=300
*.processes=150
*.remote_listener=’LISTENERS_NODE’
*.remote_login_passwordfile=’exclusive’
node2.thread=2
node1.thread=1
node1.undo_tablespace=’UNDOTBS1′
node2.undo_tablespace=’UNDOTBS2′
b. del semua informasi, sisakan 1 node (example del node2.*)
c. del informasi cluster
*.cluster_database_instances=2
*.cluster_database=true
*.remote_listener=’LISTENERS_NODE’
node1.instance_number=1
node1.thread=1
d. add ASM to non-ASM
*.db_file_name_convert=’+DATA1′,’/u01/oradata/node’
*.log_file_name_convert=’+DATA1′,’/u01/oradata/node’
e. menjadi :
*.__db_cache_size=281018368
*.__java_pool_size=12582912
*.__large_pool_size=4194304
*.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/node/adump’
*.compatible=’11.1.0.0.0′
*.control_files=’/u01/oradata/node/controlfile/control01.ctl’,'/u01/oradata/node/controlfile/control02.ctl’
*.db_block_size=8192
*.db_create_file_dest=’/u01/oradata/node’
*.db_domain=”
*.db_file_name_convert=’+DATA1′,’/u01/oradata/node’
*.db_name=’node’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=nodeXDB)’
*.log_file_name_convert=’+DATA1′,’/u01/oradata/node’
*.open_cursors=300
*.__pga_aggregate_target=343932928
*.processes=150
*.remote_login_passwordfile=’exclusive’
*.__sga_target=511705088
*.__shared_io_pool_size=0
*.__shared_pool_size=205520896
*.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
*.audit_trail=’db’
*.diagnostic_dest=’/u01/app/oracle’
*.log_archive_dest_1=’location=/u01/archive/node/archive01′
*.log_archive_format=’%t_%s_%r.dbf’
*.memory_target=852492288
*.undo_tablespace=’UNDOTBS1′
8. create SPFILE dari PFILE yang sudah diedit
[oracle@test ~]$ ORACLE_SID=node; export ORACLE_SID
[oracle@test ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Sat Sep 26 07:19:04 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 853716992 bytes
Fixed Size 1303244 bytes
Variable Size 566234420 bytes
Database Buffers 281018368 bytes
Redo Buffers 5160960 bytes
SQL> exit
9. Restore Controlfile
[oracle@test ~]$ su - root
Password:
[root@test ~]# chown oracle.oinstall /u01 -R
[oracle@test ~]$ rman target /
RMAN> restore controlfile from ‘/home/oracle/clone/controlfile’;
Starting restore at 26-SEP-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:28
output file name=/u01/oradata/node/controlfile/control01.ctl
output file name=/u01/oradata/node/controlfile/control02.ctl
Finished restore at 26-SEP-08
10. Restore Database
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 26-SEP-08
Starting implicit crosscheck backup at 26-SEP-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 26-SEP-08
Starting implicit crosscheck copy at 26-SEP-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 26-SEP-08
searching for all files in the recovery area
cataloging files…
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA1/node/datafile/system.256.666184225
channel ORA_DISK_1: restoring datafile 00002 to +DATA1/node/datafile/sysaux.257.666184225
channel ORA_DISK_1: restoring datafile 00003 to +DATA1/node/datafile/undotbs1.258.666184225
channel ORA_DISK_1: restoring datafile 00004 to +DATA1/node/datafile/users.259.666184225
channel ORA_DISK_1: restoring datafile 00005 to +DATA1/node/datafile/example.267.666184357
channel ORA_DISK_1: restoring datafile 00006 to +DATA1/node/datafile/undotbs2.268.666184499
channel ORA_DISK_1: reading from backup piece /home/oracle/clone/0pjriaep_1_2
channel ORA_DISK_1: piece handle=/home/oracle/clone/0pjriaep_1_2 tag=TAG20080926T115816
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:17:56
Finished restore at 26-SEP-08
RMAN> exit
Recovery Manager complete.
11. Recover Database
[oracle@test ~]$ sqlplus / as sysdba
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1066400 generated at 09/26/2008 11:58:17 needed for thread 1
ORA-00289: suggestion : /u01/archive/node/archive011_59_666184331.dbf
ORA-00280: change 1066400 for thread 1 is in sequence #59
Specify log: {
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
12. test apakah data terakhir sudah dipindahkan ?
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. membuat listener.ora
[oracle@test ~]$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER_NODE =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.141.153)(PORT = 1521)(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
14. menghidupkan listener.ora
[oracle@test ~]$ lsnrctl start LISTENER_NODE
15. membuat tnsnames.ora
[oracle@test ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
NODE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.141.153)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = node)
(INSTANCE_NAME = node)
)
)
16. test connection
[oracle@test ~]$ sqlplus scott/tiger@node
17. bersih - bersih data rman di target komputer
RMAN> delete noprompt force backup;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=126 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
——- ——- — — ———– ———– ———-
15 11 1 2 EXPIRED DISK /home/oracle/clone/0bjri7qg_1_2
17 15 1 1 EXPIRED DISK /home/oracle/clone/controlfile
23 21 1 1 EXPIRED DISK +DATA1/node/autobackup/2008_09_26/s_666445898.325.666445901
26 24 1 1 EXPIRED DISK /u03/backup/node/NODE-id-711660360-25.bkp
29 24 1 2 AVAILABLE DISK /home/oracle/clone/0pjriaep_1_2
27 25 1 1 EXPIRED DISK /u03/backup/node/NODE-id-711660360-26.bkp
28 26 1 1 EXPIRED DISK +DATA1/node/autobackup/2008_09_26/s_666446350.334.666446353
30 27 1 1 EXPIRED DISK +DATA1/node/autobackup/2008_09_26/s_666446563.339.666446565
31 28 1 1 AVAILABLE DISK /u01/app/oracle/flash_recovery_area/NODE/autobackup/2008_09_29/o1_mf_s_666680379_4fzzlpmm_.bkp
deleted backup piece
backup piece handle=/home/oracle/clone/0bjri7qg_1_2 RECID=15 STAMP=666443807
deleted backup piece
backup piece handle=/home/oracle/clone/controlfile RECID=17 STAMP=666443862
deleted backup piece
backup piece handle=+DATA1/node/autobackup/2008_09_26/s_666445898.325.666445901 RECID=23 STAMP=666445900
deleted backup piece
backup piece handle=/u03/backup/node/NODE-id-711660360-25.bkp RECID=26 STAMP=666446299
deleted backup piece
backup piece handle=/home/oracle/clone/0pjriaep_1_2 RECID=29 STAMP=666446518
deleted backup piece
backup piece handle=/u03/backup/node/NODE-id-711660360-26.bkp RECID=27 STAMP=666446349
deleted backup piece
backup piece handle=+DATA1/node/autobackup/2008_09_26/s_666446350.334.666446353 RECID=28 STAMP=666446352
deleted backup piece
backup piece handle=+DATA1/node/autobackup/2008_09_26/s_666446563.339.666446565 RECID=30 STAMP=666446565
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/NODE/autobackup/2008_09_29/o1_mf_s_666680379_4fzzlpmm_.bkp RECID=31 STAMP=666680390
Deleted 9 objects
RMAN> delete noprompt force copy;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=126 device type=DISK
List of Archived Log Copies for database with db_unique_name NODE
=====================================================================
Key Thrd Seq S Low Time
——- —- ——- - ———
102 2 1 A 29-SEP-08
Name: /u01/archive/node/archive012_1_666680175.dbf
deleted archived log
archived log file name=/u01/archive/node/archive012_1_666680175.dbf RECID=102 STAMP=666680349
Deleted 1 objects
RMAN> exit
sumber :
http://www.idevelopment.info/data/Oracle/DBA_tips/Automatic_Storage_Management/ASM_30.shtml
Tidak ada komentar:
Posting Komentar