Diproteksi: CLONE ORACLE 10GR2 dari WINDOWS ke LINUX [detail] Maret 18, 2009
Posted by setijoagus in Linux, Oracle Backup, oracle.Tags: clone database
Masukkan kata sandi Anda untuk melihat komentar
CLONE ORACLE 10GR2 dari WINDOWS ke LINUX Maret 18, 2009
Posted by setijoagus in Linux, Oracle Backup, oracle.Tags: clone
add a comment
CLONE ORACLE 10GR2 dari WINDOWS ke LINUX
seharusnya cara – cara ini juga dapat digunakan untuk melakukkan :
a. control file rusak
b. redolog rusak
c. dan tentunya clone / migrasi ke beda O/S
server asal / windows
1. jika server tidak rusak.
a. create CONTROLFILE script
sql > ALTER DATABASE backup CONTROLFILE to trace
sql > shutdown immediate;
b. temukan trace file terakhir, extention .trc
c. loncat ke langkah no 3
2. jika control file [control01.ctl, control02.ctl, control03.ctl] dan
redolog [redo01.LOG, redo02.LOG, redo03.LOG] rusak
3. edit / buat file yang mirip, jika sulit copykan dari
server yang lain, dalam contoh saya beri nama clone_modif.sql
4. copykan file – file .dbf [find file / semua data file yang ada ]
[kalau ada yang rusak ya wasalam he.....he.....he......]
contoh saya dan disimpan di /u02/data:
index02.dbf
system01.dbf
users01.dbf
sysaux01.dbf
EXAMPLE01.DBF
UNDOTBS01.DBF
data01.dbf
data02.dbf
index01.dbf
5. membuat init.ora, sama dengan langkah no 3, bisa contoh dari server yang lain
6. menyiapkan folder-folder
[oracle@orcl ~]$ mkdir -p /u02/admin/orcl/adump
[oracle@orcl ~]$ mkdir -p /u02/admin/orcl/bdump
[oracle@orcl ~]$ mkdir -p /u02/admin/orcl/cdump
[oracle@orcl ~]$ mkdir -p /u02/flash_recovery_area
[oracle@orcl ~]$ mkdir -p /u02/admin/orcl/udump
7. membuat spfile
8. menjalankan script clone_modif.sql
SQL> @clone_modif.sql;
Control file created.
Database altered.
9. restart, dan lakukan startup normal
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
Oracle 11g RAC (ASM) to single instance (non ASM) – clone database September 26, 2008
Posted by setijoagus in Oracle Backup, oracle.Tags: ASM, clone database, oracle 11g, RMAN
2 comments
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: {<RET>=suggested | filename | AUTO | CANCEL}
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
clone database, not same instance name September 13, 2008
Posted by setijoagus in Oracle Backup, oracle.Tags: clone, oracle, RMAN
add a comment
===============================================================
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
clone database other machine same instance name September 13, 2008
Posted by setijoagus in Oracle Backup, oracle.Tags: clone, oracle, RMAN
add a comment
===============================================================
komputer source
komputer 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. 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;
5. Lakukan backup ditambah dengan archive log
a. lakukan full backup
[oracle@iscdd ~]$ ORACLE_SID=iscdd; export ORACLE_SID
[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman target /
Recovery Manager: Release 10.2.0.1.0 – Production on Fri Sep 12 22:07:39 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISCDD (DBID=2058697900)
RMAN> backup database plus archivelog delete input TAG=clone_backup;
Starting backup at 12-SEP-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=5 recid=4 stamp=665276689
channel ORA_DISK_1: starting piece 1 at 12-SEP-08
channel ORA_DISK_1: finished piece 1 at 12-SEP-08
piece handle=/home/oracle/backup/iscdd/ISCDD-id-2058697900-6.bkp tag=CLONE_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/archive/iscdd/archive011_5_665265839.dbf recid=4 stamp=665276689
Finished backup at 12-SEP-08
Starting backup at 12-SEP-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/iscdd/iscdd/system01.dbf
input datafile fno=00003 name=/u02/oradata/iscdd/iscdd/sysaux01.dbf
input datafile fno=00005 name=/u02/oradata/iscdd/iscdd/example01.dbf
input datafile fno=00002 name=/u02/oradata/iscdd/iscdd/undotbs01.dbf
input datafile fno=00004 name=/u02/oradata/iscdd/iscdd/users01.dbf
input datafile fno=00006 name=/u02/oradata/iscdd/iscdd/senin_ts01.dbf
channel ORA_DISK_1: starting piece 1 at 12-SEP-08
channel ORA_DISK_1: finished piece 1 at 12-SEP-08
piece handle=/home/oracle/backup/iscdd/ISCDD-id-2058697900-7.bkp tag=TAG20080912T230454 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:38
Finished backup at 12-SEP-08
Starting backup at 12-SEP-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=6 recid=5 stamp=665276975
channel ORA_DISK_1: starting piece 1 at 12-SEP-08
channel ORA_DISK_1: finished piece 1 at 12-SEP-08
piece handle=/home/oracle/backup/iscdd/ISCDD-id-2058697900-8.bkp tag=CLONE_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/archive/iscdd/archive011_6_665265839.dbf recid=5 stamp=665276975
Finished backup at 12-SEP-08
Starting Control File and SPFILE Autobackup at 12-SEP-08
piece handle=/u01/app/oracle/flash_recovery_area/ISCDD/autobackup/2008_09_12/o1_mf_s_665276977_4do524wf_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 12-SEP-08
b. delete obsolote backup dan lihat daftar backupset
RMAN> delete noprompt obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
——————– —— —————— ——————–
Backup Set 1 12-SEP-08
Backup Piece 1 12-SEP-08 /u01/app/oracle/flash_recovery_area/ISCDD/autobackup/2008_09_12/o1_mf_s_665272954_4do14fy3_.bkp
Backup Set 2 12-SEP-08
Backup Piece 2 12-SEP-08 /home/oracle/backup/iscdd/ISCDD-id-2058697900-2.bkp
Backup Set 3 12-SEP-08
Backup Piece 3 12-SEP-08 /home/oracle/backup/iscdd/ISCDD-id-2058697900-3.bkp
Backup Set 4 12-SEP-08
Backup Piece 4 12-SEP-08 /home/oracle/backup/iscdd/ISCDD-id-2058697900-4.bkp
Backup Set 5 12-SEP-08
Backup Piece 5 12-SEP-08 /u01/app/oracle/flash_recovery_area/ISCDD/autobackup/2008_09_12/o1_mf_s_665273811_4do1z6wc_.bkp
Backup Set 6 12-SEP-08
Backup Piece 6 12-SEP-08 /home/oracle/backup/iscdd/ISCDD-id-2058697900-6.bkp
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ISCDD/autobackup/2008_09_12/o1_mf_s_665272954_4do14fy3_.bkp recid=1 stamp=665272957
deleted backup piece
backup piece handle=/home/oracle/backup/iscdd/ISCDD-id-2058697900-2.bkp recid=2 stamp=665273519
deleted backup piece
backup piece handle=/home/oracle/backup/iscdd/ISCDD-id-2058697900-3.bkp recid=3 stamp=665273548
deleted backup piece
backup piece handle=/home/oracle/backup/iscdd/ISCDD-id-2058697900-4.bkp recid=4 stamp=665273810
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ISCDD/autobackup/2008_09_12/o1_mf_s_665273811_4do1z6wc_.bkp recid=5 stamp=665273814
deleted backup piece
backup piece handle=/home/oracle/backup/iscdd/ISCDD-id-2058697900-6.bkp recid=6 stamp=665276690
Deleted 6 objects
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
7 Full 585.95M DISK 00:04:28 12-SEP-08
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20080912T230454
Piece Name: /home/oracle/backup/iscdd/ISCDD-id-2058697900-7.bkp
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 Full 502242 12-SEP-08 /u02/oradata/iscdd/iscdd/system01.dbf
2 Full 502242 12-SEP-08 /u02/oradata/iscdd/iscdd/undotbs01.dbf
3 Full 502242 12-SEP-08 /u02/oradata/iscdd/iscdd/sysaux01.dbf
4 Full 502242 12-SEP-08 /u02/oradata/iscdd/iscdd/users01.dbf
5 Full 502242 12-SEP-08 /u02/oradata/iscdd/iscdd/example01.dbf
6 Full 502242 12-SEP-08 /u02/oradata/iscdd/iscdd/senin_ts01.dbf
BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
8 374.50K DISK 00:00:02 12-SEP-08
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: CLONE_BACKUP
Piece Name: /home/oracle/backup/iscdd/ISCDD-id-2058697900-8.bkp
List of Archived Logs in backup set 8
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——— ———- ———
1 6 502235 12-SEP-08 502814 12-SEP-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
9 Full 6.80M DISK 00:00:06 12-SEP-08
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20080912T230937
Piece Name: /u01/app/oracle/flash_recovery_area/ISCDD/autobackup/2008_09_12/o1_mf_s_665276977_4do524wf_.bkp
Control File Included: Ckp SCN: 502820 Ckp time: 12-SEP-08
SPFILE Included: Modification time: 12-SEP-08
c. copykan backupset fullbackup ke local file system, dimana terlihat pada langkah diatas
BS (Backup Set) no 7 adalah BS full backup
RMAN> host;
[oracle@iscdd bin]$ mkdir /home/oracle/clone/ -p
[oracle@iscdd bin]$ exit
RMAN> backup backupset 7 format ‘/home/oracle/clone/%U’;
Starting backup at 13-SEP-08
using channel ORA_DISK_1
input backupset count=7 stamp=665276694 creation_time=12-SEP-08
channel ORA_DISK_1: starting piece 1 at 13-SEP-08
channel ORA_DISK_1: backup piece /home/oracle/backup/iscdd/ISCDD-id-2058697900-7.bkp
piece handle=/home/oracle/07jqek8m_1_2 comment=NONE
channel ORA_DISK_1: finished piece 1 at 13-SEP-08
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:58
Finished backup at 13-SEP-08
Starting Control File and SPFILE Autobackup at 13-SEP-08
piece handle=/u01/app/oracle/flash_recovery_area/ISCDD/autobackup/2008_09_13/o1_mf_s_665282854_4dobssnd_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-08
d. juga lakukan backup secara manual spfile dan control file
backup spfile include current controlfile format ‘/home/oracle/controlfile’;
RMAN> backup spfile include current controlfile format ‘/home/oracle/clone/controlfile’;
Starting backup at 13-SEP-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 13-SEP-08
channel ORA_DISK_1: finished piece 1 at 13-SEP-08
piece handle=/home/oracle/controlfile tag=TAG20080913T004936 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:11
Finished backup at 13-SEP-08
Starting Control File and SPFILE Autobackup at 13-SEP-08
piece handle=/u01/app/oracle/flash_recovery_area/ISCDD/autobackup/2008_09_13/o1_mf_s_665282987_4dobxyr1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-08
RMAN> exit
e. periksa hasil backup backupset dan controlfile yang akan dibutuhkan untuk clone
[oracle@iscdd ~]$ ls -la /home/oracle/clone/
total 607612
drwxr-xr-x 2 oracle oinstall 4096 Sep 13 00:53 .
drwx—— 24 oracle oinstall 4096 Sep 13 00:53 ..
-rw-r—– 1 oracle oinstall 614424576 Sep 13 00:44 07jqek8m_1_2
-rw-r—– 1 oracle oinstall 7143424 Sep 13 00:49 controlfile
[oracle@iscdd ~]$
f. pindahkan hasil backup ke komputer target
[oracle@iscdd iscdd]$ cd /home/oracle/clone/
[oracle@iscdd clone]$ scp 07jqek8m_1_2 controlfile 172.20.141.151:/home/oracle/
oracle@172.20.141.151’s password:
07jqek8m_1_2 100% 586MB 2.3MB/s 04:17
controlfile 100% 6976KB 2.3MB/s 00:03
===============================================================
komputer target
Sudah diinstall oracle database software
===============================================================
6. Menyiapkan directory admin oracle, datafile oracle dan oratab.
Misalkan diinginkan SID bernama clone
[oracle@iscatm2 ~]$ id
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba),502(oper),503(asmadmin)
a. menyiapkan folder admin oracle clone
[oracle@iscatm2 ~]$ mkdir -p /u01/app/oracle/admin/iscdd/adump
[oracle@iscatm2 ~]$ mkdir -p /u01/app/oracle/admin/iscdd/bdump
[oracle@iscatm2 ~]$ mkdir -p /u01/app/oracle/admin/iscdd/cdump
[oracle@iscatm2 ~]$ mkdir -p /u01/app/oracle/admin/iscdd/dpdump
[oracle@iscatm2 ~]$ mkdir -p /u01/app/oracle/admin/iscdd/pfile
[oracle@iscatm2 ~]$ mkdir -p /u01/app/oracle/admin/iscdd/scripts
[oracle@iscatm2 ~]$ mkdir -p /u01/app/oracle/admin/iscdd/udump
[oracle@iscatm2 ~]$ mkdir -p /u01/app/oracle/flash_recovery_area
b. menyiapkan folder datafile dan archive oracle clone
[oracle@iscatm2 ~]$ mkdir -p /u02/oradata/iscdd/iscdd
[oracle@iscatm2 ~]$ mkdir -p /u02/archive/iscdd
c. menyiapkan oratab
[oracle@iscatm2 ~]$ echo “iscdd:/u01/app/oracle/product/10.2.0/db_1:N” >> /etc/oratab
[oracle@iscatm2 ~]$ ORACLE_SID=iscdd; export ORACLE_SID
[oracle@iscatm2 ~]$ oraenv
ORACLE_SID = [iscdd] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 is /u01/app/oracle
7. Menyiapkan Oracle Password File oracle clone
[oracle@iscatm2 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwiscdd password=ora
8. Restore SPFILE
a. menyiapkan temporary file initialization
[oracle@iscatm2 ~]$ vi $ORACLE_HOME/dbs/initiscdd.ora
*.db_name=’iscdd’
b. menjalankan instance nomount
[oracle@iscatm2 ~]$ ORACLE_SID=iscdd; export ORACLE_SID
[oracle@iscatm2 ~]$ 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> 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
c. restore spfile
[oracle@iscatm2 ~]$ ORACLE_SID=iscdd; export ORACLE_SID
[oracle@iscatm2 ~]$ rman target /
RMAN> restore spfile from ‘/home/oracle/controlfile’;
Starting restore at 12-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/controlfile
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 12-SEP-08
RMAN> exit
d. menghapus temporary file initialization dan membuat pfile
[oracle@iscatm2 ~]$ ORACLE_SID=iscdd; export ORACLE_SID
[oracle@iscatm2 ~]$ sqlplus / as sysdba
SQL> host
[oracle@iscatm2 ~]$ rm $ORACLE_HOME/dbs/initiscdd.ora
[oracle@iscatm2 ~]$ exit
SQL> create pfile from spfile;
9. Edit PFILE
SQL> host;
[oracle@iscatm2 ~]$ vi $ORACLE_HOME/dbs/initiscdd.ora
iscdd.__db_cache_size=738197504
iscdd.__java_pool_size=4194304
iscdd.__large_pool_size=4194304
iscdd.__shared_pool_size=247463936
iscdd.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/iscdd/adump’
*.background_dump_dest=’/u01/app/oracle/admin/iscdd/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u02/oradata/iscdd/iscdd/control01.ctl’,'/u02/oradata/iscdd/iscdd/control02.ctl’,'/u02/oradata/iscdd/iscdd/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/iscdd/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’iscdd’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=iscddXDB)’
*.job_queue_processes=10
*.log_archive_dest_1=’location=/u02/archive/iscdd/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/iscdd/udump’
tambahkan dan edit, jika folder berbeda dengan perintah :
*.db_file_multiblock_read_count=16
*.db_file_name_convert=’/u02/oradata/iscdd’,'/u02/oradata/iscdd’
*.job_queue_processes=10
*.log_file_name_convert=’/u02/oradata/iscdd’,'/u02/oradata/iscdd’
menjadi :
iscdd.__db_cache_size=738197504
iscdd.__java_pool_size=4194304
iscdd.__large_pool_size=4194304
iscdd.__shared_pool_size=247463936
iscdd.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/iscdd/adump’
*.background_dump_dest=’/u01/app/oracle/admin/iscdd/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u02/oradata/iscdd/iscdd/control01.ctl’,'/u02/oradata/iscdd/iscdd/control02.ctl’,'/u02/oradata/iscdd/iscdd/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/iscdd/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_file_name_convert=’/u02/oradata/iscdd’,'/u02/oradata/iscdd’
*.db_name=’iscdd’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=iscddXDB)’
*.job_queue_processes=10
*.log_file_name_convert=’/u02/oradata/iscdd’,'/u02/oradata/iscdd’
*.log_archive_dest_1=’location=/u02/archive/iscdd/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/iscdd/udump’
10. create SPFILE dari PFILE yang sudah diedit
[oracle@iscatm2 ~]$ exit
SQL> create spfile from pfile;
SQL> shutdown abort;
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1002438656 bytes
Fixed Size 1223080 bytes
Variable Size 255854168 bytes
Database Buffers 738197504 bytes
Redo Buffers 7163904 bytes
SQL> exit
11. Restore Controlfile
[oracle@iscatm2 ~]$ ORACLE_SID=clone; export ORACLE_SID
[oracle@iscatm2 ~]$ rman target /
RMAN> restore controlfile from ‘/home/oracle/controlfile’;
Starting restore at 13-SEP-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u02/oradata/iscdd/iscdd/control01.ctl
output filename=/u02/oradata/iscdd/iscdd/control02.ctl
output filename=/u02/oradata/iscdd/iscdd/control03.ctl
Finished restore at 13-SEP-08
12. Restore Database
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 13-SEP-08
Starting implicit crosscheck backup at 13-SEP-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 13-SEP-08
Starting implicit crosscheck copy at 13-SEP-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 13-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 backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/iscdd/iscdd/system01.dbf
restoring datafile 00002 to /u02/oradata/iscdd/iscdd/undotbs01.dbf
restoring datafile 00003 to /u02/oradata/iscdd/iscdd/sysaux01.dbf
restoring datafile 00004 to /u02/oradata/iscdd/iscdd/users01.dbf
restoring datafile 00005 to /u02/oradata/iscdd/iscdd/example01.dbf
restoring datafile 00006 to /u02/oradata/iscdd/iscdd/senin_ts01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/07jqek8m_1_2
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/07jqek8m_1_2 tag=TAG20080912T230454
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 13-SEP-08
RMAN> exit;
13. Recover Database
[oracle@iscatm2 ~]$ sqlplus / as sysdba
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 502242 generated at 09/12/2008 23:04:54 needed for thread 1
ORA-00289: suggestion : /u02/archive/iscdd/archive011_6_665265839.dbf
ORA-00280: change 502242 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
14. 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
15. Configure tnsnames.ora and listner.ora instance clone
[oracle@iscatm2 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
iscdd =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = iscatm2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = iscdd)
)
)
[oracle@iscatm2 ~]$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = iscdd)(PORT = 1521))
)
)
[oracle@iscdd iscdd]$ lsnrctl reload
16. test apakah listener ok ?
[oracle@iscatm2 ~]$ 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
Move datafile Oracle with sql command with RMAN September 13, 2008
Posted by setijoagus in Oracle Backup, oracle.Tags: DATAFILE, MOVE, oracle, RMAN, TABLESPACE
add a comment
================================================================
Move datafile Oracle with sql command with RMAN
================================================================
1. catat lokasi tablespace dan datafile
[oracle@iscdd iscdd]$ ORACLE_SID=iscdd; export ORACLE_SID
[oracle@iscdd iscdd]$ rman target /
Recovery Manager: Release 10.2.0.1.0 – Production on Sun Sep 14 17:42:23 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISCDD (DBID=2058697900)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 480 SYSTEM *** /u02/oradata/iscdd/iscdd/system01.dbf
2 30 UNDOTBS1 *** /u02/oradata/iscdd/iscdd/undotbs01.dbf
3 240 SYSAUX *** /u02/oradata/iscdd/iscdd/sysaux01.dbf
4 0 USERS *** /u02/oradata/iscdd/iscdd/users01.dbf
5 100 EXAMPLE *** /u02/oradata/iscdd/iscdd/example01.dbf
6 2 SENIN_TS *** /u02/oradata/iscdd/iscdd/senin_ts01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 20 TEMP 32767 /u02/oradata/iscdd/iscdd/temp01.dbf
2. test memindahkan TABLESPACE users, dengan terlebih dulu membuat TABLESPACE users offline
RMAN> SQL ‘ALTER TABLESPACE users OFFLINE NORMAL’;
3. lakukan copy dengan host, jika pakai linux tambahkan cp
RMAN> copy datafile ‘/u02/oradata/iscdd/iscdd/users01.dbf’ to ‘/u02/oradata/users01.dbf’;
4. mecatat di controlfile
RMAN> switch datafile ‘/u02/oradata/iscdd/iscdd/users01.dbf’ to COPY;
datafile 4 switched to datafile copy “/u02/oradata/users01.dbf”
5. Membuat TABLESPACE users online
RMAN> SQL ‘ALTER TABLESPACE users ONLINE’;
6. periksa perubahan yang terjadi
RMAN> report schema;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 480 SYSTEM *** /u02/oradata/iscdd/iscdd/system01.dbf
2 30 UNDOTBS1 *** /u02/oradata/iscdd/iscdd/undotbs01.dbf
3 240 SYSAUX *** /u02/oradata/iscdd/iscdd/sysaux01.dbf
4 5 USERS *** /u02/oradata/users01.dbf
5 100 EXAMPLE *** /u02/oradata/iscdd/iscdd/example01.dbf
6 2 SENIN_TS *** /u02/oradata/iscdd/iscdd/senin_ts01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 20 TEMP 32767 /u02/oradata/iscdd/iscdd/temp01.dbf
7. test untuk dikembalikan seperti semula
RMAN> SQL ‘ALTER TABLESPACE users OFFLINE NORMAL’;
RMAN> switch datafile ‘/u02/oradata/users01.dbf’ to copy;
datafile 4 switched to datafile copy “/u02/oradata/iscdd/iscdd/users01.dbf”
RMAN> SQL ‘ALTER TABLESPACE users ONLINE’;
sql statement: ALTER TABLESPACE users ONLINE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 09/14/2008 18:05:25
RMAN-11003: failure during parse/execution of SQL statement: ALTER TABLESPACE users ONLINE
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: ‘/u02/oradata/iscdd/iscdd/users01.dbf’
(kok jadi error ????)
RMAN> RECOVER DATAFILE ‘/u02/oradata/iscdd/iscdd/users01.dbf’;
Media recovery complete.
RMAN> SQL ‘ALTER TABLESPACE users ONLINE’;
Move datafile Oracle with sql command without RMAN September 13, 2008
Posted by setijoagus in Oracle Backup, oracle.Tags: DATAFILE, MOVE, oracle, RMAN, TABLESPACE
add a comment
================================================================
Move datafile Oracle with sql command without RMAN
================================================================
1. catat lokasi tablespace dan datafile
[oracle@iscdd ~]$ sqlplus / as sysdba
SQL> column TABLESPACE_NAME format a20;
SQL> column FILE_NAME format a40;
SQL> select TABLESPACE_NAME, FILE_NAME from dba_data_files;
TABLESPACE_NAME FILE_NAME
——————– —————————————-
USERS /u02/oradata/iscdd/iscdd/users01.dbf
SYSAUX /u02/oradata/iscdd/iscdd/sysaux01.dbf
UNDOTBS1 /u02/oradata/iscdd/iscdd/undotbs01.dbf
SYSTEM /u02/oradata/iscdd/iscdd/system01.dbf
EXAMPLE /u02/oradata/iscdd/iscdd/example01.dbf
SENIN_TS /u02/oradata/iscdd/iscdd/senin_ts01.dbf
6 rows selected.
2. test memindahkan TABLESPACE users, dengan terlebih dulu membuat TABLESPACE users offline
SQL> ALTER TABLESPACE users OFFLINE NORMAL;
3. lakukan copy dengan host, jika pakai linux tambahkan cp
SQL> HOST cp /u02/oradata/iscdd/iscdd/users01.dbf /u02/oradata/iscdd/users01.dbf;
4. mecatat di controlfile
SQL> ALTER TABLESPACE users
RENAME DATAFILE ‘/u02/oradata/iscdd/iscdd/users01.dbf’
TO ‘/u02/oradata/iscdd/users01.dbf’;
5. Membuat TABLESPACE users online
SQL> ALTER TABLESPACE users ONLINE;
6. periksa perubahan yang terjadi
SQL> select TABLESPACE_NAME, FILE_NAME from dba_data_files;
TABLESPACE_NAME FILE_NAME
——————– —————————————-
USERS /u02/oradata/iscdd/users01.dbf
SYSAUX /u02/oradata/iscdd/iscdd/sysaux01.dbf
UNDOTBS1 /u02/oradata/iscdd/iscdd/undotbs01.dbf
SYSTEM /u02/oradata/iscdd/iscdd/system01.dbf
EXAMPLE /u02/oradata/iscdd/iscdd/example01.dbf
SENIN_TS /u02/oradata/iscdd/iscdd/senin_ts01.dbf
6 rows selected.
7. test untuk dikembalikan seperti semula
SQL> ALTER TABLESPACE users OFFLINE NORMAL;
SQL> ALTER TABLESPACE users
RENAME DATAFILE ‘/u02/oradata/iscdd/users01.dbf’
TO ‘/u02/oradata/iscdd/iscdd/users01.dbf’;
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: ‘/u02/oradata/iscdd/iscdd/users01.dbf’
(kok jadi error ????)
SQL> RECOVER DATAFILE ‘/u02/oradata/iscdd/iscdd/users01.dbf’;
Media recovery complete.
SQL> ALTER TABLESPACE users ONLINE;
RMAN Backup dan Recovery Oracle 10g (3) September 9, 2008
Posted by setijoagus in Oracle Backup, oracle.Tags: Backup, oracle, Recovery, RMAN
add a comment
Beberapa kasus RMAN
24. Kasus hilangnya datafile
[oracle@iscdd ~]$ rm /u02/oradata/iscdd/system01.dbf
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1002438656 bytes
Fixed Size 1223080 bytes
Variable Size 222299736 bytes
Database Buffers 771751936 bytes
Redo Buffers 7163904 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 – see DBWR trace file
ORA-01110: data file 1: ‘/u02/oradata/iscdd/system01.dbf’
SQL> exit;
[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman target / NOCATALOG
RMAN> startup mount force;
Oracle instance started
database mounted
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
[oracle@iscdd bin]$ ./rman target / NOCATALOG
Recovery Manager: Release 10.2.0.1.0 – Production on Tue Aug 19 10:13:18 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup mount force;
Oracle instance started
database mounted
Total System Global Area 1002438656 bytes
Fixed Size 1223080 bytes
Variable Size 222299736 bytes
Database Buffers 771751936 bytes
Redo Buffers 7163904 bytes
RMAN> restore datafile 1;
Starting restore at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/iscdd/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-12.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-12.bkp tag=TAG20080815T111906
channel ORA_DISK_1: restore complete, elapsed time: 00:05:47
Finished restore at 19-AUG-08
RMAN> recover database;
Starting recover at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/iscdd/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-15.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-15.bkp tag=LEVEL_1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/iscdd/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-18.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-18.bkp tag=TAG20080819T093111
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-AUG-08
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
[oracle@iscdd bin]$
[oracle@iscdd ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Aug 19 10:26:21 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1002438656 bytes
Fixed Size 1223080 bytes
Variable Size 222299736 bytes
Database Buffers 771751936 bytes
Redo Buffers 7163904 bytes
Database mounted.
Database opened.
25. Kasus hilangnya datafile pada saat transaksi
a. session I menambah transaksi
SQL> insert into DEPT values(‘50′,’IT’,'SBY’);
SQL> insert into DEPT values(‘51′,’IT’,'SBY’);
SQL> insert into DEPT values(‘52′,’IT’,'SBY’);
SQL> COMMIT;
SQL> SELECT * FROM DEPT;
DEPTNO DNAME LOC
———- ————– ————-
50 IT SBY
51 IT SBY
52 IT SBY
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
7 rows selected.
b. Session II data rusak / terhapus
[oracle@iscdd ~]$ rm /u02/oradata/iscdd/users01.dbf
c. Session III database restart
SQL> shutdown abort;
SQL> startup;
ORACLE instance started.
Total System Global Area 1002438656 bytes
Fixed Size 1223080 bytes
Variable Size 222299736 bytes
Database Buffers 771751936 bytes
Redo Buffers 7163904 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/u02/oradata/iscdd/users01.dbf’
d. penyelamatan
[oracle@iscdd bin]$ ./rman target / NOCATALOG
Recovery Manager: Release 10.2.0.1.0 – Production on Tue Aug 19 10:36:30 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISCDD (DBID=2055978995, not open)
using target database control file instead of recovery catalog
RMAN> startup mount force;
Oracle instance started
database mounted
Total System Global Area 1002438656 bytes
Fixed Size 1223080 bytes
Variable Size 222299736 bytes
Database Buffers 771751936 bytes
Redo Buffers 7163904 bytes
RMAN> restore datafile 4;
Starting restore at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u02/oradata/iscdd/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-12.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-12.bkp tag=TAG20080815T111906
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 19-AUG-08
RMAN> recover database;
Starting recover at 19-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u02/oradata/iscdd/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-15.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-15.bkp tag=LEVEL_1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u02/oradata/iscdd/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-18.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-18.bkp tag=TAG20080819T093111
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-AUG-08
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
[oracle@iscdd bin]$
[oracle@iscdd ~]$ sqlplus scott/tiger@iscdd
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Aug 19 10:38:13 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
50 IT SBY
51 IT SBY
52 IT SBY
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
7 rows selected.
26. Kasus hilangnya controlfile
[oracle@iscdd ~]$ rm /u02/oradata/iscdd/*.ctl
[oracle@iscdd ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Aug 19 10:43:00 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1002438656 bytes
Fixed Size 1223080 bytes
Variable Size 226494040 bytes
Database Buffers 767557632 bytes
Redo Buffers 7163904 bytes
ORA-00205: error in identifying control file, check alert log for more info
[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman target / NOCATALOG
Recovery Manager: Release 10.2.0.1.0 – Production on Tue Aug 19 10:44:39 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: iscdd (not mounted)
using target database control file instead of recovery catalog
RMAN> set dbid 2055978995;
executing command: SET DBID
RMAN> restore controlfile from autobackup;
Starting restore at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
recovery area destination: /u02/oradata/flash_recovery_area/
database name (or database unique name) used for search: ISCDD
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663154943_4bndrlqh_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u02/oradata/iscdd/control01.ctl
output filename=/u02/oradata/iscdd/control02.ctl
output filename=/u02/oradata/iscdd/control03.ctl
Finished restore at 19-AUG-08
RMAN> startup mount force;
Oracle instance started
database mounted
Total System Global Area 1002438656 bytes
Fixed Size 1223080 bytes
Variable Size 226494040 bytes
Database Buffers 767557632 bytes
Redo Buffers 7163904 bytes
RMAN> restore database;
Starting restore at 19-AUG-08
Starting implicit crosscheck backup at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
Crosschecked 17 objects
Finished implicit crosscheck backup at 19-AUG-08
Starting implicit crosscheck copy at 19-AUG-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 19-AUG-08
searching for all files in the recovery area
cataloging files…
cataloging done
List of Cataloged Files
=======================
File Name: /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663154943_4bndrlqh_.bkp
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/iscdd/system01.dbf
restoring datafile 00002 to /u02/oradata/iscdd/undotbs01.dbf
restoring datafile 00003 to /u02/oradata/iscdd/sysaux01.dbf
restoring datafile 00004 to /u02/oradata/iscdd/users01.dbf
restoring datafile 00005 to /u02/oradata/iscdd/example01.dbf
restoring datafile 00006 to /u02/oradata/iscdd/rman_ts01.dbf
restoring datafile 00007 to /u02/oradata/iscdd/rman_ts02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-12.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-12.bkp tag=TAG20080815T111906
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00008 to /u02/oradata/iscdd/senin_ts01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-18.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-18.bkp tag=TAG20080819T093111
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 19-AUG-08
RMAN> recover database;
Starting recover at 19-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/iscdd/system01.dbf
destination for restore of datafile 00002: /u02/oradata/iscdd/undotbs01.dbf
destination for restore of datafile 00003: /u02/oradata/iscdd/sysaux01.dbf
destination for restore of datafile 00004: /u02/oradata/iscdd/users01.dbf
destination for restore of datafile 00005: /u02/oradata/iscdd/example01.dbf
destination for restore of datafile 00006: /u02/oradata/iscdd/rman_ts01.dbf
destination for restore of datafile 00007: /u02/oradata/iscdd/rman_ts02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-15.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-15.bkp tag=LEVEL_1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/iscdd/system01.dbf
destination for restore of datafile 00002: /u02/oradata/iscdd/undotbs01.dbf
destination for restore of datafile 00003: /u02/oradata/iscdd/sysaux01.dbf
destination for restore of datafile 00004: /u02/oradata/iscdd/users01.dbf
destination for restore of datafile 00005: /u02/oradata/iscdd/example01.dbf
destination for restore of datafile 00006: /u02/oradata/iscdd/rman_ts01.dbf
destination for restore of datafile 00007: /u02/oradata/iscdd/rman_ts02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-18.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-18.bkp tag=TAG20080819T093111
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
starting media recovery
archive log thread 1 sequence 9 is already on disk as file /u02/archive/archive011_9_662546934.dbf
archive log thread 1 sequence 10 is already on disk as file /u02/oradata/iscdd/redo03.log
archive log thread 1 sequence 11 is already on disk as file /u02/oradata/iscdd/redo01.log
archive log thread 1 sequence 12 is already on disk as file /u02/oradata/iscdd/redo02.log
archive log filename=/u02/archive/archive011_9_662546934.dbf thread=1 sequence=9
archive log filename=/u02/oradata/iscdd/redo03.log thread=1 sequence=10
archive log filename=/u02/oradata/iscdd/redo01.log thread=1 sequence=11
archive log filename=/u02/oradata/iscdd/redo02.log thread=1 sequence=12
media recovery complete, elapsed time: 00:00:03
Finished recover at 19-AUG-08
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete.
[oracle@iscdd bin]$
27. script untuk melakukan backup
[oracle@iscdd ~]$ mkdir /home/oracle/script -p
[oracle@iscdd ~]$ vi /home/oracle/script/daily_incremental.sh
#! /bin/bash
# Daily incremental backup
#
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
cd $ORACLE_HOME/bin
./rman @/home/oracle/script/daily_incremental.rman LOG /home/oracle/script/daily_incremental.rman.log
[oracle@iscdd ~]$ chmod +x /home/oracle/script/daily_incremental.sh
[oracle@iscdd ~]$ vi /home/oracle/script/daily_incremental.rman
CONNECT TARGET sys/oracle0@orcl6
BACKUP INCREMENTAL LEVEL 1 DATABASE TAG=daily_incremental;
EXIT;
[oracle@iscdd ~]$ vi /home/oracle/script/weekly_full.sh
#! /bin/bash
# Weekly full backup
#
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
cd $ORACLE_HOME/bin
./rman @/home/oracle/script/weekly_full.rman LOG /home/oracle/script/weekly_full.rman.log
[oracle@iscdd ~]$ chmod +x /home/oracle/script/weekly_full.sh
[oracle@iscdd ~]$ vi /home/oracle/script/weekly_full.rman
CONNECT TARGET sys/ora@iscdd
BACKUP INCREMENTAL LEVEL 0 DATABASE TAG=full_backup;
delete noprompt obsolete;
EXIT;
28. crontab untuk melakukan backup
[root@iscdd ~]# crontab -e
0 10 * * 1-6 /home/oracle/script/daily_incremental.sh
0 10 * * 7 /home/oracle/script/weekly_full.sh
29. hasil crontab untuk backup
[root@iscdd ~]# date
Tue Aug 19 11:03:42 WIT 2008
[root@iscdd ~]# ls -la /home/oracle/script/
total 24
drwxr-xr-x 2 oracle oinstall 4096 Aug 19 11:01 .
drwx—— 18 oracle oinstall 4096 Aug 19 11:01 ..
-rw-r–r– 1 oracle oinstall 95 Aug 19 10:59 daily_incremental.rman
-rwxr-xr-x 1 oracle oinstall 220 Aug 19 10:58 daily_incremental.sh
-rw-r–r– 1 oracle oinstall 115 Aug 19 11:01 weekly_full.rman
-rwxr-xr-x 1 oracle oinstall 203 Aug 19 11:00 weekly_full.sh
[root@iscdd ~]# date -s 23:55:00
Tue Aug 19 23:55:00 WIT 2008
RMAN Backup dan Recovery Oracle 10g (1) September 9, 2008
Posted by setijoagus in Oracle Backup, oracle.Tags: Backup, oracle, Recovery, RMAN
7 comments
Sebagai acuan untuk prosedur backup dan recovery dengan RMAN
1. RMAN dapat melakukan back up pada semua file database yang dibutuhkan untuk recovery pada saat terjadi kerusakan. RMAN support back up file-file
a. Datafile, dan image copy dari datafiles
b. Control file, dan image copy dari control file
c. Archived redo logs
d. file parameter server
e. Backup pieces, yang berisi backup lain yang dibuat oleh RMAN
2. Menggunakan RMAN O/S authentication
[oracle@iscdd bin]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman target / NOCATALOG
RMAN> exit
Recovery Manager complete.
3. Menggunakan RMAN Password authentication
[oracle@iscdd bin]$ cd $ORACLE_HOME/bin
[oracle@iscdd ~]$ ./rman target system/ora NOCATALOG
RMAN> exit
Recovery Manager complete.
4. Konfigurasi RMAN agar dapat diakses oleh user tertentu.
SQL> create tablespace rman_ts
datafile ‘/u02/oradata/iscdd/rman_ts01.dbf’
size 5M
segment space management auto;
SQL> alter tablespace rman_ts
Add datafile ‘/u02/oradata/iscdd/rman_ts02.dbf’
size 5M;
SQL> create user rman_user identified by rman123
default tablespace rman_ts
quota unlimited on rman_ts;
SQL> grant connect, resource, recovery_catalog_owner to rman_user;
SQL> exit
5. Konfigurasi RMAN, setting catalog dengan user tertentu.
[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman CATALOG rman_user/rman123@iscdd TARGET sys/ora@iscdd
Recovery Manager: Release 10.2.0.1.0 – Production on Fri Aug 15 10:25:00 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISCDD (DBID=2055978995)
connected to recovery catalog database
RMAN> create catalog tablespace rman_ts;
recovery catalog created
RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> exit
Recovery Manager complete.
6. Untuk mengetahui apakah sudah ter-rigister
[oracle@iscdd bin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Fri Aug 15 13:56:28 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from rman_user.rc_database;
NAME
——–
ISCDD
SQL> exit
7. Melihat konfigurasi RMAN
[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman CATALOG rman_user/rman123@iscdd TARGET sys/ora@iscdd
RMAN> SHOW ALL;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128′; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/10.1.0/db_1/dbs/snapcf_edxx.f’; # default
8. Konfigurasi RMAN untuk menyimpan control file secara otomatis
RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
9. Konfigurasi RMAN untuk set obsolote backup, default 1 hari misal diganti menjadi 2 hari.
RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
RMAN> CONFIGURE RETENTION POLICY to recovery window of 2 days;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
new RMAN configuration parameters are successfully stored
RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
10. Konfigurasi RMAN agar archive log disimpan pada folder tertentu sesuai dengan DB_RECOVERY_FILE_DEST.
[oracle@iscdd bin]$ sqlplus / as sysdba
SQL> host
[oracle@iscdd ~]$ mkdir /u02/archive
[oracle@iscdd ~]$ exit
SQL> alter system set log_archive_dest_1=’location=/u02/archive/archive01′;
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Enabled
Archive destination /u02/archive/archive01
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
11. Konfigurasi RMAN pada database harus dalam mode archive log.
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;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/archive/archive01
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
SQL> exit
12. Lewat RMAN untuk mengetahui tablespace dan datafilenya.
[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman CATALOG rman_user/rman123@iscdd TARGET sys/ora@iscdd
Recovery Manager: Release 10.2.0.1.0 – Production on Fri Aug 15 14:08:58 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISCDD (DBID=2055978995)
connected to recovery catalog database
RMAN> report schema;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 480 SYSTEM YES /u02/oradata/iscdd/system01.dbf
2 25 UNDOTBS1 YES /u02/oradata/iscdd/undotbs01.dbf
3 230 SYSAUX NO /u02/oradata/iscdd/sysaux01.dbf
4 5 USERS NO /u02/oradata/iscdd/users01.dbf
5 100 EXAMPLE NO /u02/oradata/iscdd/example01.dbf
6 5 RMAN_TS NO /u02/oradata/iscdd/rman_ts01.dbf
7 5 RMAN_TS NO /u02/oradata/iscdd/rman_ts02.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 20 TEMP 32767 /u02/oradata/iscdd/temp01.dbf
13. Setup backup optimization, agar file yang tidak mengalami perubahan tidak perlu dibackup lagi
RMAN> configure backup optimization on;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
14. Tempat penyimpanan backup, sesuai dengan parameter CHANNEL, jika tidak diisi secara default disimpan di DEFAULT DEVICE TYPE, Jika menggunakan flash recovery area maka secara default disimpan di DB_RECOVERY_FILE_DEST.
RMAN> configure channel device type disk format ‘/home/oracle/backup/%d-id-%I-%s.bkp’;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/home/oracle/backup/%d-id-%I-%s.bkp’;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
%d : database name
%I : nomer DBID
%s : nomer sequence
15. RMAN backup controlfile
[oracle@iscdd bin]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman
Recovery Manager: Release 10.2.0.1.0 – Production on Mon Aug 13 23:26:16 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> CONNECT TARGET sys/ora@iscdd
connected to target database: ISCDD (DBID=2055978995)
RMAN> backup current controlfile;
Starting backup at 15-AUG-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 15-AUG-08
channel ORA_DISK_1: finished piece 1 at 15-AUG-08
piece handle=/home/oracle/backup/ISCDD-id-2055978995-1.bkp tag=TAG20080815T103342 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 15-AUG-08
Starting Control File and SPFILE Autobackup at 15-AUG-08 piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662812428_4b9y8zdr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-AUG-08
16. RMAN backup controlfile
RMAN> backup current controlfile format
‘/home/oracle/backup/control.bkp’;
Starting backup at 15-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 15-AUG-08
channel ORA_DISK_1: finished piece 1 at 15-AUG-08
piece handle=/home/oracle/backup/control.bkp tag=TAG20080815T103649 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 15-AUG-08
Starting Control File and SPFILE Autobackup at 15-AUG-08 piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662812616_4b9ygv40_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-AUG-08
17. RMAN backup spfile
RMAN> backup spfile format ‘/home/oracle/backup/spfile.bkp’;
Starting backup at 15-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 15-AUG-08
channel ORA_DISK_1: finished piece 1 at 15-AUG-08
piece handle=/home/oracle/backup/spfile.bkp tag=TAG20080815T103938 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 15-AUG-08
Starting Control File and SPFILE Autobackup at 15-AUG-08
piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662812781_4b9ymzz2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-AUG-08
RMAN> exit
Recovery Manager complete.
18. Copy spfile
SPFILE, Server Parameter File, adalah sebuah file binary yang digunakan oracle sebagai parameter initialization. Parameter dapat diubah menggunakan ALTER SYSTEM SET, dimana parameter hanya berubah pada SPFILE.
PFILE adalah traditional text based dari parameter file init.ora. Umumnya disimpan pada directory $ORACLE_BASE/admin/SID/pfile, dan symbolic link berada di directory $ORACLE_HOME/dbs.
[oracle@iscdd bin]$ sqlplus sys/ora@iscdd as sysdba;
SQL> CREATE PFILE = ‘/home/oracle/backup/initorcl.ora’ FROM SPFILE;
SQL> exit
[oracle@iscdd bin]$ cat /home/oracle/backup/initorcl.ora | more
iscdd.__db_cache_size=713031680
iscdd.__java_pool_size=4194304
iscdd.__large_pool_size=4194304
iscdd.__shared_pool_size=264241152
iscdd.__streams_pool_size=8388608
*.audit_file_dest=’/u01/app/oracle/admin/iscdd/adump’
*.background_dump_dest=’/u01/app/oracle/admin/iscdd/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u02/oradata/iscdd/control01.ctl’,'/u02/oradata/iscdd/control02
.ctl’,'/u02/oradata/iscdd/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/iscdd/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’iscdd’
*.db_recovery_file_dest=’/u02/oradata/flash_recovery_area/’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=iscddXDB)’
*.job_queue_processes=10
*.log_archive_dest_1=’location=/u02/archive/archive01′
*.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/iscdd/udump’
19. Melakukan proses backup
a. full backup
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG=full_backup;
Starting backup at 15-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/iscdd/system01.dbf
input datafile fno=00003 name=/u02/oradata/iscdd/sysaux01.dbf
input datafile fno=00005 name=/u02/oradata/iscdd/example01.dbf
input datafile fno=00002 name=/u02/oradata/iscdd/undotbs01.dbf
input datafile fno=00004 name=/u02/oradata/iscdd/users01.dbf
input datafile fno=00006 name=/u02/oradata/iscdd/rman_ts01.dbf
input datafile fno=00007 name=/u02/oradata/iscdd/rman_ts02.dbf
channel ORA_DISK_1: starting piece 1 at 15-AUG-08
channel ORA_DISK_1: finished piece 1 at 15-AUG-08
piece handle=/home/oracle/backup/ISCDD-id-2055978995-9.bkp tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:22
Finished backup at 15-AUG-08
Starting Control File and SPFILE Autobackup at 15-AUG-08
piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662814329_4bb04dpp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-AUG-08
b. backup incremental
RMAN> backup incremental level 1 tag ‘level_1′ database;
Starting backup at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/iscdd/system01.dbf
input datafile fno=00003 name=/u02/oradata/iscdd/sysaux01.dbf
input datafile fno=00005 name=/u02/oradata/iscdd/example01.dbf
input datafile fno=00002 name=/u02/oradata/iscdd/undotbs01.dbf
input datafile fno=00004 name=/u02/oradata/iscdd/users01.dbf
input datafile fno=00006 name=/u02/oradata/iscdd/rman_ts01.dbf
input datafile fno=00007 name=/u02/oradata/iscdd/rman_ts02.dbf
channel ORA_DISK_1: starting piece 1 at 19-AUG-08
channel ORA_DISK_1: finished piece 1 at 19-AUG-08
piece handle=/home/oracle/backup/ISCDD-id-2055978995-15.bkp tag=LEVEL_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46
Finished backup at 19-AUG-08
Starting Control File and SPFILE Autobackup at 19-AUG-08
piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663153295_4bnc521b_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-AUG-08
c. strategi backup
Minggu : full backup ? backup incremental level 0
Senin – Sabtu : block – block yang diperbarui ? backup
incremental level 1
Hari minggu :
RMAN> backup incremental level 0 tag ‘level_0′ database;
Hari senin – sabtu :
RMAN> backup incremental level 1 tag ‘level_1′ database;
Clone Database Oracle 8i Windows ke Linux Agustus 26, 2008
Posted by setijoagus in Oracle Backup, oracle.Tags: Clone Database Oracle 8i
add a comment
1. Tujuan
Untuk melakukan clone database pada Oracle 8i Enterprise Edition pada Operating System Windows NT ke Linux dengan benar.
2. Ringkasan Metoda
Menggunakan Oracle 8i Enterprise Edition Operating System pada Windows NT Server dan Linux
3. Cara Kerja
1. Jalankan Ms.Dos Prompt pada server yang akan diclone / dicopy / dipindah.
2. Jalankan Server Manager dan connect internal atau sys as sysdba
3. Jalankan perintah ALTER DATABASE untuk menciptakan CONTROLFILE script
4. Shut down database
5. Exit Server Manager.
6. Copy database files ke lokasi baru atau server oracle yang baru (server oracle yang baru harus telah di install oracle server dan telah dilakukkan shutdown database). Dimana letak data biasanya di oradataorcl . Copykan semua file yang terletak di folder tsb.
Orclcontrol.ctl
orcluser01.dbf
orcldata01ts01.dbf
orcltemp01.dbf
orclredog01m01.log
orclredog02m01.log
folder tujuan di Linux, misalnya : /u01/app/oracle/oradata/orcl
7. Periksa file initorcl.ora and pwdorcl.ora di directory orantdatabase,
8. Edit file inittest.ora, sesuaikan server parameters
DB_NAME =
CONTROL_FILES [drive_letter]:oradata
9. Di directory server yang diclone/ dicopy, cari file extension TRC yang terakhir., selanjutnya rename menjadi cr_ctlfile.sql dan copykan ke server yang baru
10. Edit file cr_ctlfile.sql :
a. hapus dari baris pertama sampai baris yang diawali #
b. Ubah perintah CREATE CONTROLFILE
CREATE CONTROLFILE REUSE DATABASE “ORACLE” NORESETLOGS NOARCHIVELOG
menjadi
CREATE CONTROLFILE REUSE SET DATABASE “TEST” RESETLOGS NOARCHIVELOG
c. Ubah semua directory oradata lama ke directory oradata baru
d. Hapus baris RECOVER DATABASE
e. Edit baris
ALTER DATABASE OPEN;
Menjadi
ALTER DATABASE OPEN RESETLOGS;
11. Cari file init.ora yang terletak di folder D:ORACLEADMINPTUBSOR2PFILE
12. Edit letak Control_files yang baru didalam contoh di bawah ini d:oracleoradataptubsor2control01.ctl ke posisi directory baru. Ubah db_name, instance_name, services_name jika diperlukan.
13. Run Server Manager dan connect sebagai internal atau sys as sysdba
[oracle@jems]$ cd /u01/app/oracle/product/8.1.7/dbs
[oracle@jems dbs]$ svrmgrl
Oracle Server Manager Release 3.1.7.0.0 – Production
Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.
Oracle8i Enterprise Edition Release 8.1.7.0.1 – Production
With the Partitioning option
JServer Release 8.1.7.0.1 – Production
SVRMGR> connect internal
Connected.
SVRMGR>
14. Run the cr_ctlfile.sql command script yang telah diedit.
SVRMGR> @cr_ctlfile.sql
ORACLE instance started.
Total System Global Area 5112820 bytes
Fixed Size 45584 bytes
Variable Size 4133348 bytes
Database Buffers 409600 bytes
Redo Buffers 524288 bytes
Statement processed.
Statement processed.
SVRMGR>
15. Jalankan startup seperti biasa
