0>环境
RAC to 单机
Linux 5.6
Oracle 10g
RMAN backup 备份数据库
1>Backup of the primary database.
RMAN> run {
allocate channel d1 type disk;
backup format '/dr/tt/df_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup format '/dr/tt/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}
[root@rac2 tt]# ls -al
total 832188
drwxr-xr-x 2 oracle dba 4096 Aug 11 07:23 .
drwxrwxrwx 7 root root 4096 Aug 11 07:13 ..
-rw-r----- 1 oracle dba 104620032 Aug 11 07:24 al_t758877800_s17_p1
-rw-r----- 1 oracle dba 731316224 Aug 11 07:20 df_t758877301_s15_p1
-rw-r----- 1 oracle dba 15368192 Aug 11 07:20 df_t758877630_s16_p1
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 backup;
delete expired backup;
list backup
5. 恢复数据库
--恢复参数文件
SQL>create pfile='/tmp/ntt.ora' from spfile
SQL>shutdown immediate
--修改pfile
SQL>startup nomount pfile='/dr/tmptt.ora'
--恢复控制文件
rman target /
RMAN> restore controlfile to '/dr/reco/controlfile1.dbf' from '/dr/tt/df_t758877630_s16_p1';
Starting restore at 11-AUG-11
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 11-AUG-11
RMAN> alter database mount;
RMAN> catalog backuppiece '/dr/tt/al_t758877800_s17_p1';
RMAN> catalog backuppiece '/dr/tt/df_t758877301_s15_p1';
RMAN> catalog backuppiece '/dr/tt/df_t758877630_s16_p1';
RMAN> list backup of archivelog all;
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
12 99.77M DISK 00:00:00 11-AUG-11
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20110811T072258
Piece Name: /dr/tt/al_t758877800_s17_p1
List of Archived Logs in backup set 12
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 28 353703 08-AUG-11 379686 09-AUG-11
1 29 379686 09-AUG-11 421780 09-AUG-11
1 30 421780 09-AUG-11 466309 09-AUG-11
1 31 466309 09-AUG-11 472785 09-AUG-11
1 32 472785 09-AUG-11 477890 09-AUG-11
1 33 477890 09-AUG-11 504563 11-AUG-11
1 34 504563 11-AUG-11 507216 11-AUG-11
1 35 507216 11-AUG-11 507314 11-AUG-11
2 1 358328 08-AUG-11 445897 09-AUG-11
2 2 445897 09-AUG-11 472790 09-AUG-11
2 3 472790 09-AUG-11 477892 09-AUG-11
2 4 477892 09-AUG-11 507244 11-AUG-11
2 5 507244 11-AUG-11 507312 11-AUG-11
run {
set until sequence 59 thread 1;
set newname for datafile 1 to '/dr/tt/system01.dbf';
set newname for datafile 2 to '/dr/tt/undotbs01.dbf';
set newname for datafile 3 to '/dr/tt/sysaux01.dbf';
set newname for datafile 4 to '/dr/tt/undotbs02.dbf';
set newname for datafile 5 to '/dr/tt/user.dbf';
restore database;
switch datafile all;
recover database;
}
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 11-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 instance=tt1 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 /dr/tt/system01.dbf
restoring datafile 00002 to /dr/tt/undotbs01.dbf
restoring datafile 00003 to /dr/tt/sysaux01.dbf
restoring datafile 00004 to /dr/tt/undotbs02.dbf
restoring datafile 00005 to /dr/tt/user.dbf
channel ORA_DISK_1: reading from backup piece /dr/tt/df_t758877301_s15_p1
channel ORA_DISK_1: restored backup piece 1
piece handle=/dr/tt/df_t758877301_s15_p1 tag=TAG20110811T071500
channel ORA_DISK_1: restore complete, elapsed time: 00:03:46
Finished restore at 11-AUG-11
datafile 1 switched to datafile copy
input datafile copy recid=11 stamp=758880381 filename=/dr/tt/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=12 stamp=758880382 filename=/dr/tt/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=13 stamp=758880382 filename=/dr/tt/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=14 stamp=758880382 filename=/dr/tt/undotbs02.dbf
datafile 5 switched to datafile copy
input datafile copy recid=15 stamp=758880382 filename=/dr/tt/user.dbf
Starting recover at 11-AUG-11
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 35 is already on disk as file +DB/tt/onlinelog/group_1.257.758668823
archive log thread 1 sequence 36 is already on disk as file +DB/tt/onlinelog/group_2.258.758668827
archive log thread 2 sequence 5 is already on disk as file +DB/tt/onlinelog/group_3.265.758672735
archive log thread 2 sequence 6 is already on disk as file +DB/tt/onlinelog/group_4.266.758672751
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=34
channel ORA_DISK_1: restoring archive log
archive log thread=2 sequence=4
channel ORA_DISK_1: reading from backup piece /dr/tt/al_t758877800_s17_p1
channel ORA_DISK_1: restored backup piece 1
piece handle=/dr/tt/al_t758877800_s17_p1 tag=TAG20110811T072258
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
archive log filename=/dr/reco/1_34_758668813.dbf thread=1 sequence=34
archive log filename=/dr/reco/2_4_758668813.dbf thread=2 sequence=4
archive log filename=+DB/tt/onlinelog/group_1.257.758668823 thread=1 sequence=35
archive log filename=+DB/tt/onlinelog/group_3.265.758672735 thread=2 sequence=5
archive log filename=+DB/tt/onlinelog/group_4.266.758672751 thread=2 sequence=6
archive log filename=+DB/tt/onlinelog/group_2.258.758668827 thread=1 sequence=36
media recovery complete, elapsed time: 00:00:06
Finished recover at 11-AUG-11
--修改redo
SQL> select member from v$logfile
/
MEMBER
--------------------------------------------------------------------------------
+DB/tt/onlinelog/group_1.257.758668823
+DB/tt/onlinelog/group_2.258.758668827
+DB/tt/onlinelog/group_3.265.758672735
+DB/tt/onlinelog/group_4.266.758672751
alter database rename file '+DB/tt/onlinelog/group_1.257.758668823' to '/dr/reco/redo1.log';
alter database rename file '+DB/tt/onlinelog/group_2.258.758668827' to '/dr/reco/redo2.log';
alter database rename file '+DB/tt/onlinelog/group_3.265.758672735' to '/dr/reco/redo3.log';
alter database rename file '+DB/tt/onlinelog/group_4.266.758672751' to '/dr/reco/redo4.log';
SQL> alter database rename file '+DB/tt/onlinelog/group_1.257.758668823' to '/dr/reco/redo1.log';
Database altered.
SQL> alter database rename file '+DB/tt/onlinelog/group_2.258.758668827' to '/dr/reco/redo2.log';
Database altered.
SQL> alter database rename file '+DB/tt/onlinelog/group_3.265.758672735' to '/dr/reco/redo3.log';
Database altered.
SQL> alter database rename file '+DB/tt/onlinelog/group_4.266.758672751' to '/dr/reco/redo4.log';
Database altered.
--thread
SQL> select THREAD#, STATUS, ENABLED from v$thread
/
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 OPEN PUBLIC
--open
SQL> alter database open resetlogs
/
Database altered.
SQL> select THREAD#, STATUS, ENABLED from v$thread
/
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED PUBLIC
--disable 2节点的redo thread
SQL> alter database disable thread 2
2 /
Database altered.
--2号节点的group
SQL> select group# from v$log where THREAD#=2
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 tt2 (thread 2) needs to be archived
ORA-00312: online log 4 thread 2: '/dr/reco/redo4.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/tt/tmp1.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
#tt2.__db_cache_size=88080384
#tt2.__java_pool_size=4194304
#tt2.__large_pool_size=4194304
#tt2.__shared_pool_size=83886080
#tt2.__streams_pool_size=0
#*.cluster_database_instances=2
#*.cluster_database=true
#tt1.instance_number=1
#tt2.instance_number=2
#*.remote_listener='LISTENERS_TT'
#tt2.thread=2
#tt2.undo_tablespace='UNDOTBS2'
SQL>startup nomount pfile='/dr/tmptt.ora'
SQL>create spfile from pfile='/dr/tmptt.ora'
SQL>shutdown immediate;
SQL>startup
[oracle@rac1 dr]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-AUG-2011 09:15:50
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_RAC1
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 11-AUG-2011 06:52:23
Uptime 0 days 2 hr. 23 min. 28 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/product/10.2.0/db_1/network/log/listener_rac1.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.63.31)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.63.30)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "tt" has 1 instance(s).
Instance "tt1", status READY, has 1 handler(s) for this service...
Service "ttXDB" has 1 instance(s).
Instance "tt1", status READY, has 1 handler(s) for this service...
Service "tt_XPT" has 1 instance(s).
Instance "tt1", status READY, has 1 handler(s) for this service...
The command completed successfully
