0>环境
RAC to 单机
Linux 5.6
Oracle 10g
RMAN backup 备份数据库
1>Backup of the primary database.
[oracle@rac1 dr]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 11 20:05:26 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: RMAN (DBID=1645326530)
RMAN> run {
allocate channel d1 type disk;
backup as copy format '/dr/rmando/df_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup as copy format '/dr/rmando/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}
using target database control file instead of recovery catalog
allocated channel: d1
channel d1: sid=148 instance=rman1 devtype=DISK
Starting backup at 11-AUG-11
channel d1: starting datafile copy
input datafile fno=00001 name=+DB/rman/datafile/system.266.758899235
output filename=/dr/rmando/df_t758923565_s11_p1 tag=TAG20110811T200603 recid=7 stamp=758923765
channel d1: datafile copy complete, elapsed time: 00:03:28
channel d1: starting datafile copy
input datafile fno=00003 name=+DB/rman/datafile/sysaux.262.758899241
output filename=/dr/rmando/df_t758923773_s12_p1 tag=TAG20110811T200603 recid=8 stamp=758923817
channel d1: datafile copy complete, elapsed time: 00:00:56
channel d1: starting datafile copy
input datafile fno=00002 name=+DB/rman/datafile/undotbs1.265.758899247
output filename=/dr/rmando/df_t758923832_s13_p1 tag=TAG20110811T200603 recid=9 stamp=758923844
channel d1: datafile copy complete, elapsed time: 00:00:18
channel d1: starting datafile copy
input datafile fno=00005 name=+DB/rman/datafile/undotbs2.292.758899549
output filename=/dr/rmando/df_t758923851_s14_p1 tag=TAG20110811T200603 recid=10 stamp=758923858
channel d1: datafile copy complete, elapsed time: 00:00:07
channel d1: starting datafile copy
copying current control file
output filename=/dr/rmando/df_t758923859_s15_p1 tag=TAG20110811T200603 recid=11 stamp=758923866
channel d1: datafile copy complete, elapsed time: 00:00:15
channel d1: starting datafile copy
input datafile fno=00004 name=+DB/rman/datafile/users.258.758899249
output filename=/dr/rmando/df_t758923875_s16_p1 tag=TAG20110811T200603 recid=12 stamp=758923878
channel d1: datafile copy complete, elapsed time: 00:00:07
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel d1: starting piece 1 at 11-AUG-11
channel d1: finished piece 1 at 11-AUG-11
piece handle=/dr/rmando/df_t758923883_s17_p1 tag=TAG20110811T200603 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:03
Finished backup at 11-AUG-11
sql statement: alter system archive log current
Starting backup at 11-AUG-11
current log archived
channel d1: starting archive copy
input archive log thread=1 sequence=2 recid=2 stamp=758901717
output filename=/dr/rmando/al_t758924043_s18_p1 recid=11 stamp=758924051
channel d1: archivelog copy complete, elapsed time: 00:00:08
channel d1: starting archive copy
input archive log thread=1 sequence=4 recid=6 stamp=758922756
output filename=/dr/rmando/al_t758924052_s19_p1 recid=12 stamp=758924054
channel d1: archivelog copy complete, elapsed time: 00:00:02
channel d1: starting archive copy
input archive log thread=2 sequence=1 recid=1 stamp=758901692
output filename=/dr/rmando/al_t758924054_s20_p1 recid=13 stamp=758924056
channel d1: archivelog copy complete, elapsed time: 00:00:02
channel d1: starting archive copy
input archive log thread=2 sequence=2 recid=3 stamp=758901748
output filename=/dr/rmando/al_t758924057_s21_p1 recid=14 stamp=758924059
channel d1: archivelog copy complete, elapsed time: 00:00:03
channel d1: starting archive copy
input archive log thread=2 sequence=4 recid=7 stamp=758923954
output filename=/dr/rmando/al_t758924060_s22_p1 recid=15 stamp=758924062
channel d1: archivelog copy complete, elapsed time: 00:00:02
channel d1: starting archive copy
input archive log thread=1 sequence=5 recid=8 stamp=758923958
output filename=/dr/rmando/al_t758924063_s23_p1 recid=16 stamp=758924065
channel d1: archivelog copy complete, elapsed time: 00:00:03
channel d1: starting archive copy
input archive log thread=1 sequence=3 recid=4 stamp=758901749
output filename=/dr/rmando/al_t758924066_s24_p1 recid=17 stamp=758924070
channel d1: archivelog copy complete, elapsed time: 00:00:04
channel d1: starting archive copy
input archive log thread=1 sequence=6 recid=9 stamp=758923993
output filename=/dr/rmando/al_t758924070_s25_p1 recid=18 stamp=758924073
channel d1: archivelog copy complete, elapsed time: 00:00:03
channel d1: starting archive copy
input archive log thread=2 sequence=3 recid=5 stamp=758922512
output filename=/dr/rmando/al_t758924073_s26_p1 recid=19 stamp=758924075
channel d1: archivelog copy complete, elapsed time: 00:00:03
channel d1: starting archive copy
input archive log thread=2 sequence=5 recid=10 stamp=758924020
output filename=/dr/rmando/al_t758924076_s27_p1 recid=20 stamp=758924078
channel d1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 11-AUG-11
released channel: d1
[oracle@rac1 rmando]$ ls -al
total 844732
drwxr-xr-x 2 oracle dba 4096 Aug 11 20:14 .
drwxrwxrwx 10 root root 4096 Aug 11 20:04 ..
-rw-r----- 1 oracle dba 27575296 Aug 11 20:14 al_t758924043_s18_p1
-rw-r----- 1 oracle dba 3530240 Aug 11 20:14 al_t758924052_s19_p1
-rw-r----- 1 oracle dba 1268736 Aug 11 20:14 al_t758924054_s20_p1
-rw-r----- 1 oracle dba 1030656 Aug 11 20:14 al_t758924057_s21_p1
-rw-r----- 1 oracle dba 965632 Aug 11 20:14 al_t758924060_s22_p1
-rw-r----- 1 oracle dba 848384 Aug 11 20:14 al_t758924063_s23_p1
-rw-r----- 1 oracle dba 460800 Aug 11 20:14 al_t758924066_s24_p1
-rw-r----- 1 oracle dba 267264 Aug 11 20:14 al_t758924070_s25_p1
-rw-r----- 1 oracle dba 46080 Aug 11 20:14 al_t758924073_s26_p1
-rw-r----- 1 oracle dba 2560 Aug 11 20:14 al_t758924076_s27_p1
-rw-r----- 1 oracle dba 503324672 Aug 11 20:09 df_t758923565_s11_p1
-rw-r----- 1 oracle dba 251666432 Aug 11 20:10 df_t758923773_s12_p1
-rw-r----- 1 oracle dba 26222592 Aug 11 20:10 df_t758923832_s13_p1
-rw-r----- 1 oracle dba 26222592 Aug 11 20:10 df_t758923851_s14_p1
-rw-r----- 1 oracle dba 15286272 Aug 11 20:11 df_t758923859_s15_p1 spfile
-rw-r----- 1 oracle dba 5251072 Aug 11 20:11 df_t758923875_s16_p1
-rw-r----- 1 oracle dba 98304 Aug 11 20:11 df_t758923883_s17_p1 controlfile
修改名称
修改spfile相关参数，路径等
mount 数据库
2. Determine how much disk space will be required.
SQL> select DF.TOTAL/1048576 "DataFile Size Mb",
2 LOG.TOTAL/1048576 "Redo Log Size Mb",
3 CONTROL.TOTAL/1048576 "Control File Size Mb",
4 (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,
5 (select sum(a.bytes) TOTAL from dba_data_files a
6 where tablespace_name in('SYSTEM','UNDOTBS1', 'SYSAUX', 'USERS')) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;
7 8
DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb
---------------- ---------------- -------------------- -------------
935 200 14.578125 1149.57813
3. Ensuring you have enough space on your target server.
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda1 15480800 4529308 10165112 31% /
none 352660 0 352660 0% /dev/shm
/dev/sdh1 8254240 1510796 6324152 20% /dr
4. list backup
crosscheck copy;
delete expired copy;
list copy;
RMAN> list copy;
specification does not match any archive log in the recovery catalog
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
7 1 A 11-AUG-11 513516 11-AUG-11 /dr/rmando/df_t758923565_s11_p1
9 2 A 11-AUG-11 514243 11-AUG-11 /dr/rmando/df_t758923832_s13_p1
8 3 A 11-AUG-11 514208 11-AUG-11 /dr/rmando/df_t758923773_s12_p1
10 5 A 11-AUG-11 514252 11-AUG-11 /dr/rmando/df_t758923851_s14_p1
SQL> select file#, name from v$datafile
/
FILE# NAME
---------- ----------------------------------------
1 +DB/rman/datafile/system.266.758899235
2 +DB/rman/datafile/undotbs1.265.758899247
3 +DB/rman/datafile/sysaux.262.758899241
4 +DB/rman/datafile/users.258.758899249
5 +DB/rman/datafile/undotbs2.292.758899549
--注册archivelog
catalog archivelog '/dr/rmando/al_t758924043_s18_p1' ;
catalog archivelog '/dr/rmando/al_t758924052_s19_p1' ;
catalog archivelog '/dr/rmando/al_t758924054_s20_p1' ;
catalog archivelog '/dr/rmando/al_t758924057_s21_p1' ;
catalog archivelog '/dr/rmando/al_t758924060_s22_p1' ;
catalog archivelog '/dr/rmando/al_t758924063_s23_p1' ;
catalog archivelog '/dr/rmando/al_t758924066_s24_p1' ;
catalog archivelog '/dr/rmando/al_t758924070_s25_p1' ;
catalog archivelog '/dr/rmando/al_t758924073_s26_p1' ;
catalog archivelog '/dr/rmando/al_t758924076_s27_p1' ;
SQL> select name,deleted,status,applied from v$archived_log
2 /
--修改文件
mv df_t758923565_s11_p1 system.dbf
mv df_t758923773_s12_p1 sysaux.dbf
mv df_t758923832_s13_p1 undo1.dbf
mv df_t758923851_s14_p1 undo2.dbf
mv df_t758923875_s16_p1 user.dbf
alter database rename file '+DB/rman/datafile/system.266.758899235' to '/dr/rmando/system.dbf' ;
alter database rename file '+DB/rman/datafile/undotbs1.265.758899247' to '/dr/rmando/undo1.dbf' ;
alter database rename file '+DB/rman/datafile/sysaux.262.758899241' to '/dr/rmando/sysaux.dbf' ;
alter database rename file '+DB/rman/datafile/undotbs2.292.758899549' to '/dr/rmando/undo2.dbf' ;
alter database rename file '+DB/rman/datafile/users.258.758899249' to '/dr/rmando/user.dbf' ;
RMAN> list copy
2> ;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
7 1 A 11-AUG-11 513516 11-AUG-11 /dr/rmando/df_t758923565_s11_p1
9 2 A 11-AUG-11 514243 11-AUG-11 /dr/rmando/df_t758923832_s13_p1
8 3 A 11-AUG-11 514208 11-AUG-11 /dr/rmando/df_t758923773_s12_p1
10 5 A 11-AUG-11 514252 11-AUG-11 /dr/rmando/df_t758923851_s14_p1
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
7 1 2 A 11-AUG-11 /dr/rmando/al_t758924043_s18_p1
13 1 3 A 11-AUG-11 /dr/rmando/al_t758924066_s24_p1
8 1 4 A 11-AUG-11 /dr/rmando/al_t758924052_s19_p1
12 1 5 A 11-AUG-11 /dr/rmando/al_t758924063_s23_p1
14 1 6 A 11-AUG-11 /dr/rmando/al_t758924070_s25_p1
9 2 1 A 11-AUG-11 /dr/rmando/al_t758924054_s20_p1
10 2 2 A 11-AUG-11 /dr/rmando/al_t758924057_s21_p1
15 2 3 A 11-AUG-11 /dr/rmando/al_t758924073_s26_p1
11 2 4 A 11-AUG-11 /dr/rmando/al_t758924060_s22_p1
16 2 5 A 11-AUG-11 /dr/rmando/al_t758924076_s27_p1
5. 恢复数据库
--恢复归档
RMAN> run {
2> set until sequence 7 thread 1;
3> recover database;
4> }
注意:sequence 为7
executing command: SET until clause
using target database control file instead of recovery catalog
Starting recover at 11-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 instance=rman1 devtype=DISK
starting media recovery
archive log thread 1 sequence 5 is already on disk as file /dr/rmando/al_t758924063_s23_p1
archive log thread 1 sequence 6 is already on disk as file /dr/rmando/al_t758924070_s25_p1
archive log thread 2 sequence 4 is already on disk as file /dr/rmando/al_t758924060_s22_p1
archive log thread 2 sequence 5 is already on disk as file /dr/rmando/al_t758924076_s27_p1
archive log filename=/dr/rmando/al_t758924063_s23_p1 thread=1 sequence=5
archive log filename=/dr/rmando/al_t758924060_s22_p1 thread=2 sequence=4
archive log filename=/dr/rmando/al_t758924070_s25_p1 thread=1 sequence=6
archive log filename=/dr/rmando/al_t758924076_s27_p1 thread=2 sequence=5
media recovery complete, elapsed time: 00:00:04
Finished recover at 11-AUG-11
SQL> alter database open resetlogs
2 /
SQL> select member from v$logfile
2 ;
MEMBER
--------------------------------------------------------------------------------
/dr/rmando/RMAN/onlinelog/o1_mf_2_747ob6d7_.log
/dr/rmando/RMAN/onlinelog/o1_mf_2_747ob99w_.log
/dr/rmando/RMAN/onlinelog/o1_mf_1_747ob0kj_.log
/dr/rmando/RMAN/onlinelog/o1_mf_1_747ob2lj_.log
/dr/rmando/RMAN/onlinelog/o1_mf_3_747obg1d_.log
/dr/rmando/RMAN/onlinelog/o1_mf_3_747objbc_.log
/dr/rmando/RMAN/onlinelog/o1_mf_4_747oblf2_.log
/dr/rmando/RMAN/onlinelog/o1_mf_4_747obnr1_.log
--thread
SQL> select THREAD#, STATUS, ENABLED from v$thread
/
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 OPEN PUBLIC
--disable 2节点的redo thread
SQL> alter database disable thread 2
/
Database altered.
--2号节点的group
SQL> select group# from v$log where THREAD#=2
/
GROUP#
----------
3
4
--delete 2节点的redo
SQL> alter database drop logfile group 4
/
SQL> alter database drop logfile group 4
/
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00350: log 4 of instance rman2 (thread 2) needs to be archived
ORA-00312: online log 4 thread 2:
'/dr/rmando/RMAN/onlinelog/o1_mf_4_747oblf2_.log'
ORA-00312: online log 4 thread 2:
'/dr/rmando/RMAN/onlinelog/o1_mf_4_747obnr1_.log'
SQL> alter database clear unarchived logfile group 4
/
SQL> alter database drop logfile group 4
/
SQL> alter database drop logfile group 3
/
SQL> select THREAD#, STATUS, ENABLED from v$thread
/
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
--删除undo
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
UNDOTBS2
USERS
SQL> drop tablespace UNDOTBS2 including contents and datafiles
/
Tablespace dropped.
--重建temp
SQL> select name from v$tempfile
2 /
NAME
--------------------------------------------------------------------------------
+DB/tt/tempfile/temp.262.758668873
SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY'
/
TABLESPACE_NAME
------------------------------
TEMP
SQL> create temporary tablespace TEMP1
tempfile '/dr/rmando/tmp2.dbf' size 50M;
Tablespace created.
SQL> alter database default temporary tablespace TEMP1
/
Database altered.
SQL> drop tablespace TEMP including contents and datafiles
/
Tablespace dropped.
--spfile
修改pfile中相关RAC参数,重建spfile
#rman2.__db_cache_size=88080384
#rman2.__java_pool_size=4194304
#rman2.__large_pool_size=4194304
#rman2.__shared_pool_size=83886080
#rman2.__streams_pool_size=0
#rman.cluster_database_instances=2
#rman.cluster_database=true
#rman1.instance_number=1
#rman2.instance_number=2
#*.remote_listener='LISTENERS_RMAN'
#rman2.thread=2
#rman2.undo_tablespace='UNDOTBS2'
SQL>startup nomount pfile='/dr/rmando/tmptt.ora'
SQL>create spfile from pfile='/dr/rmando/tmptt.ora'
SQL>shutdown immediate;
SQL>startup
