恢复10G RAC+ASM 到单机

因掉电，RAC 两NODE CRASH  起不来，从备份中恢复到单机
Release 10.2.0.3.0 RAC 2 NODE
日常备份是备NODE2


1.
restore pfile:

oracle@e4node1:~> 
export ORACLE_SID=XHL

 rman target / catalogrman10g/rman10g@rman
 
Recovery Manager: Release 10.2.0.3.0 - Production on Tue Feb 19 22:40:49 2008
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
connected to target database (not started)
connected to recovery catalog database
 
RMAN>set DBID=63961092  --- [可以从CATALOG 中DB这个表中找到DBID]
 
executing command: SET DBID
database name is "XHL" and DBID is 63961092

 
RMAN> startup nomount;
 
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/data/u01/oracle/dbs/initXHL.ora'
 
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
 
Total System Global Area  838860800 bytes
Fixed Size                  1264524 bytes
Variable Size             201327732 bytes
Database Buffers          629145600 bytes
Redo Buffers                7122944 bytes
 
RMAN> 
run{
2> allocate channel ch1 device type 'SBT_TAPE' PARMS='ENV=(NB_ORA_CLIENT=XHLnode2),SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so';
/* ---[如果报找不到LIBRARY ERROR:
RMAN-03009: failure of allocate command on ch1 channel at 10/11/2007 07:57:22

ORA-19554: error allocating device, device type: SBT_TAPE, device name:

ORA-27211: Failed to load Media Management Library
可以重新在ORACLE用户下运行/usr/openv/netback/bin/oracle_link文件,再重启DB,或是手动直接这样指定]
*/
3> restore spfile to pfile '/data/u01/oracle/dbs/initXHL.ora';
4> SHUTDOWN ABORT;
5> }
 
allocated channel: ch1
channel ch1: sid=36 devtype=SBT_TAPE
channel ch1: VERITAS NetBackup for Oracle - Release 5.1 (2004043014)
 
Starting restore at 19-FEB-08
 
channel ch1: starting datafile backupset restore
channel ch1: restoring SPFILE to PFILE
output filename=/data/u01/oracle/dbs/initXHL.ora
channel ch1: reading from backup piece bk_3675_1_647066069
channel ch1: restored backup piece 1
piece handle=bk_3675_1_647066069 tag=HOT_DB_BK_LEVEL0
channel ch1: restore complete, elapsed time: 00:01:56
Finished restore at 19-FEB-08
 
Oracle instance shut down

Next, edit the restored PFILE . Change any location-specific parameters, for example, those ending in _DEST and _PATH, to reflect the new directory structure. For example, edit the following parameters:

- IFILE
  - *_DUMP_DEST
  - LOG_ARCHIVE_DEST*
  - CONTROL_FILES
 

更改恢复回来的initXHL.ora,将所有关于CLUSTER的参数都去掉,关于INSTANCE 1的参数都去掉 
####################################################################
*.db_cache_size=170425344
*.java_pool_size=16777216
*.large_pool_size=16777216
*.shared_pool_size=136870912
*.streams_pool_size=16777216
*.audit_file_dest='/data/u01/XHL/admin/adump'
*.background_dump_dest='/data/u01/XHL/admin/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/data/u01/XHL/controlfile1.ctl'
*.core_dump_dest='/data/u01/XHL/admin/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='XHL'
*.db_writer_processes=1
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XHLXDB)'
*.job_queue_processes=0
*.log_archive_dest='/oradata/XHL/arch'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.optimizer_mode='FIRST_ROWS'
*.pga_aggregate_target=287973120
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sessions=335
*.sga_target=838860800
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/data/u01/XHL/admin/udump'
#################################################################### 

Then restart the instance, using the edited PFILE:

startup force nomount pfile='/data/u01/oracle/dbs/initXHL.ora';

2.
restore controlfile

RMAN>
run {
2> allocate channel ch1 device type 'SBT_TAPE' PARMS='ENV=(NB_ORA_CLIENT=XHLnode2),SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so';
3> restore controlfile ;  
4> alter database mount;
5> }
 
allocated channel: ch1
channel ch1: sid=321 devtype=SBT_TAPE
channel ch1: VERITAS NetBackup for Oracle - Release 5.1 (2004043014)
 
Starting restore at 19-FEB-08

3.restore database
查出datafile &logfile来创建恢复脚本：
select fina#,name from v$datafile;
select * from v$logfile;

rman target / catalogrman10g/rman10g@rman 
################################################################3
run {
allocate channel ch1 device type 'SBT_TAPE' PARMS='ENV=(NB_ORA_CLIENT=XHLnode2),SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so';
set until time "to_date('02/19/2008 08:00:00','MM/DD/YYYY HH24:MI:SS')";
set newname for datafile    1 to  '/oradata/XHL/oradata/system.dbf'         ;                 
set newname for datafile    2 to  '/oradata/XHL/oradata/undotbs1.dbf'       ;                 
set newname for datafile    3 to  '/oradata/XHL/oradata/sysaux.dbf'         ;                
set newname for datafile    4 to  '/oradata/XHL/oradata/undotbs2.dbf'       ;                 
set newname for datafile    5 to  '/oradata/XHL/oradata/users.dbf'          ;                 
set newname for datafile    6 to  '/oradata/XHL/oradata/d_beoll_fg.dbf'     ;                
set newname for datafile    7 to  '/oradata/XHL/oradata/hmXHL.dbf'          ;                
set newname for datafile    8 to  '/oradata/XHL/oradata/bluXHL.dbf'         ;               
set newname for datafile    9 to  '/oradata/XHL/oradata/bluXHL_idx.dbf'     ;               
set newname for datafile   10 to  '/oradata/XHL/oradata/idx_beoll_fg.dbf'   ;                  
set newname for datafile   11 to  '/oradata/XHL/oradata/modXHL.dbf'         ;                    
set newname for datafile   12 to  '/oradata/XHL/oradata/modXHL_idx.dbf'     ;                
set newname for datafile   13 to  '/oradata/XHL/oradata/pcbaXHL.dbf'        ;                
SQL "ALTER DATABASE RENAME FILE ''+DGDATA/XHL/onlinelog/group_1.257.621448961'' TO ''/oradata/XHL/oradata/redo01.log''";                                                        
SQL "ALTER DATABASE RENAME FILE ''+DGDATA/XHL/onlinelog/group_2.258.621449111'' TO ''/oradata/XHL/oradata/redo02.log''";                                                       
SQL "ALTER DATABASE RENAME FILE ''+DGDATA/XHL/onlinelog/group_3.272.621448999'' TO ''/oradata/XHL/oradata/redo03.log''";                                    
SQL "ALTER DATABASE RENAME FILE ''+DGDATA/XHL/onlinelog/group_4.273.621449031'' TO ''/oradata/XHL/oradata/redo04.log''";                                                        
SQL "ALTER DATABASE RENAME FILE ''+DGDATA/XHL/onlinelog/group_5.296.621449059'' TO ''/oradata/XHL/oradata/redo05.log''";                                                        
SQL "ALTER DATABASE RENAME FILE ''+DGDATA/XHL/onlinelog/group_6.297.621449173'' TO ''/oradata/XHL/oradata/redo06.log''"; 
RESTORE DATABASE;      
  SWITCH DATAFILE ALL; 
                       
  # recover the database
  RECOVER DATABASE;    
} 
####################################################################################################

4.ALTER DATABASE OPEN RESETLOGS;

5.SHUTDOWN ABORT;

6.STARTUP;

7.DROP 原来的TEMP TABLESPACE ,重新建一个;

8.清理工作

DROP TABLESPACE UNDOTBS1 ;
drop tablespace UNDOTBS1 including contents and datafiles;

SQL> select thread#,status,enabled from v$thread;
 
   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED PUBLIC
 
SQL> ALTER DATABASE DISABLE THREAD 2;
 
Database altered.
 
SQL> select thread#,status,enabled from v$thread;
 
   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED DISABLED