資料庫的一次資料恢復過程

ORA-32700,ORA-00221,ORA-00206等錯誤出現在資料庫恢復過程中[@more@]

Linux ractest 2.4.21-32.ELsmp #1 SMP Fri Apr 15 21:17:59 EDT 2005 i686 i686 i386 GNU/Linux

###################alert_sid.ora中日誌記錄################
2008年3月20號09:04:50 ractest所在伺服器重啟後進行資料庫的重啟

Wed Mar 12 11:51:58 2008
Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/***/**gov/archive/1_463.dbf'Wed Mar 12 11:51:58 2008
Log actively being archived by another process
Wed Mar 12 11:52:00 2008
ARCH: Completed archiving log 4 thread 1 sequence 463
Thu Mar 20 09:04:50 2008
Starting ORACLE instance (normal)
ARC0: Thread not mounted
Thu Mar 20 09:32:03 2008
ARC1: Thread not mounted
Thu Mar 20 09:32:04 2008
ALTER DATABASE MOUNT
Thu Mar 20 09:32:04 2008
ORA-32700 signalled during: ALTER DATABASE MOUNT.
Thu Mar 20 09:32:56 2008
Restarting dead background process

#######################ORA-32700處理############################
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 319923400 bytes
Fixed Size 451784 bytes
Variable Size 201326592 bytes
Database Buffers 117440512 bytes
Redo Buffers 704512 bytes
ORA-32700: error occurred in DIAG Group Service
-------------------------ORA-32700 explain-----------------------
ORA-32700: error occurred in DIAG Group Service
Cause: An unexpected error occurred while performing a DIAG Group Service operation.
Action: Verify that the DIAG process is still active.Also, check the Oracle DIAG trace files for errors.
---------------------------------------------------------------------------------------
考慮到$ORACLE_BASE/admin/***/network/admin/listener.ora中有RAC監聽的配置；
估計本機以前做測試機用時，沒有重新裝過系統而拿來直接做資料庫伺服器存在雙機的執行環境

以ROOT使用者啟動 $ORACLE_HOME/oracm/bin/ocmstart.sh 即可
-bash-2.05b$ ps -ef |grep oracm
root 3847 1 0 Mar20 ? 00:01:34 oracm

再次startup 時沒有出現ora-32700的錯誤

#######################重建控制檔案########################
SQL> startup
ORACLE instance started.
Total System Global Area 1286332064 bytes
Fixed Size 451744 bytes
Variable Size 150994944 bytes
Database Buffers 134217728 bytes
Redo Buffers 667648 bytes
ORA-00221: error on write to controlfile
ORA-00206: error in writing (block 1, # blocks 1) of controlfile
ORA-00202: controlfile: '/***/localdata/control01.ctl'
ORA-27041: unable to open file
Linux Error: 13: Permission denied
Additional information: 3

-------------------ORA-00221-ORA-00206------------------------------------------

ORA-00206: error in writing (block string, # blocks string) ofcontrolfile
Cause: A disk I/O failure was detected on writing the controlfile.
Action: Check if the disk is online, if it is not, bring it online and try a warm start again.
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
重新設定初始化引數加入_allow_resetlogs_corruption=TRUE
/opt/***/product/9.2.0.4/dbs/initsdgov.ora
--------------------------------------------------------------------------------
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 286332064 bytes
Fixed Size 451744 bytes
Variable Size 150994944 bytes
Database Buffers 134217728 bytes
Redo Buffers 667648 bytes

CREATE CONTROLFILE REUSE DATABASE "sdgov" NORESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 226
LOGFILE
GROUP 4 '/opt/oracle/localdata/redo011.log' size 100M,
GROUP 5 '/opt/oracle/localdata/redo021.log' size 100M,
GROUP 6 '/opt/oracle/localdata/redo031.log' size 100M
-- STANDBY LOGFILE
DATAFILE
'/opt/oracle/localdata/system01.dbf',
'/opt/oracle/localdata/undotbs01.dbf',
'/opt/oracle/localdata/indx01.dbf',
'/opt/oracle/localdata/tools01.dbf',
'/opt/oracle/localdata/users01.dbf',
'/opt/oracle/localdata/sdgov.dbf',
'/opt/oracle/localdata/sd.dbf',
'/opt/oracle/localdata/wang.dbf'
CHARACTER SET ZHS16GBK
;

CREATE CONTROLFILE REUSE DATABASE "*****" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01163: SIZE clause indicates 40960 (blocks), but should match header 204800
ORA-01517: log member: '/***/localdata/redo03.log

換了N次的目錄位置也不行，開始引數必須設定正確

MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 226

後來檢查是當前使用者在/opt/oracle/localdata 目錄沒有可執行許可權，經授權

[root@ractest sh]#chgrp dba 777 /opt/oracle/localdata
[root@ractest sh]#chown -R 200:dba /opt/oracle/localdata
[root@ractest sh]#chmod 777 /opt/oracle/localdata

SQL> CREATE CONTROLFILE REUSE DATABASE "sdgov" RESETLOGS ARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 6
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXINSTANCES 8
7 MAXLOGHISTORY 226
8 LOGFILE
9 GROUP 1 '/opt/oracle/redo011.log' size 100M,
10 GROUP 2 '/opt/oracle/redo021.log' size 100M,
11 GROUP 3 '/opt/oracle/redo031.log' size 100M
12 -- STANDBY LOGFILE
13 DATAFILE
14 '/opt/oracle/localdata/system01.dbf',
15 '/opt/oracle/localdata/undotbs01.dbf',
16 '/opt/oracle/localdata/indx01.dbf',
17 '/opt/oracle/localdata/tools01.dbf',
18 '/opt/oracle/localdata/users01.dbf',
19 '/opt/oracle/localdata/sdgov.dbf',
20 '/opt/oracle/localdata/sd.dbf',
21 '/opt/oracle/localdata/wang.dbf'
22 CHARACTER SET ZHS16GBK
23 ;

Control file created.

SQL> RECOVER DATABASE UNTIL CANCEL;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-00344: unable to re-create online log '/opt/oracle/redo011.log'
ORA-27041: unable to open file
Linux Error: 13: Permission denied
Additional information: 4
rm -f /opt/oracle/redo011.log

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-00344: unable to re-create online log '/opt/oracle/redo021.log'
ORA-27041: unable to open file
Linux Error: 13: Permission denied
Additional information: 4
rm -f /opt/oracle/redo021.log

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01190: controlfile or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/opt/oracle/localdata/system01.dbf'

======================================================

[root@ractest sh]# more restore_db.rcv
connect target rman/rman
connect rcvcat
run {
allocate channel t1 type 'SBT_TAPE'
parms 'ENV=(NB_ORA_POLICY=fang_ora,NB_ORA_SERV=server4.sd.gov.cn,NB_ORA_CLIENT=ractest,NB_ORA_SCHED=Default-Application-Backup)';
restore database;
release channel t1;
}

[root@ractest sh]# more restore_arch.rcv
connect target rman/rman
connect rcvcat
run {
allocate channel t1 type 'SBT_TAPE'
parms 'ENV=(NB_ORA_POLICY=fang_arch,NB_ORA_SERV=server4.sd.gov.cn,NB_ORA_CLIENT=ractest,NB_ORA_SCHED=Default-Application-Backup)';
restore archivelog sequence 463;
release channel t1;
}

SQL>alter database open;