noarchivelog模式下冷備時沒有備份聯機重做日誌檔案

--整理以前的學習筆記

先更改資料庫為noarchivelog模式
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/arch
Oldest online log sequence     12
Next log sequence to archive   15
Current log sequence           15
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             373294984 bytes
Database Buffers          117440512 bytes
Redo Buffers                5922816 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/arch
Oldest online log sequence     12
Current log sequence           15

下面shutdown immediate進行冷備
[oracle@mylinux ~]$ cd /u01/oradata/orcl/
[oracle@mylinux orcl]$ ll
total 1918688
-rw-r----- 1 oracle oinstall   9814016 Oct 21 21:37 control01.ctl
-rw-r----- 1 oracle oinstall   9814016 Oct 21 21:37 control02.ctl
-rw-r----- 1 oracle oinstall 104865792 Oct 21 21:37 example01.dbf
drwxr-x--- 4 oracle oinstall      4096 Sep 5 20:32 ORCL
-rw-r----- 1 oracle oinstall 52429312 Oct 21 21:36 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Oct 21 21:37 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Oct 21 21:36 redo03.log
-rw-r----- 1 oracle oinstall   5243392 Oct 21 21:36 redo04b.log
-rw-r----- 1 oracle oinstall   5243392 Oct 21 21:36 redo04.log
-rw-r----- 1 oracle oinstall 629153792 Oct 21 21:37 sysaux01.dbf
-rw-r----- 1 oracle oinstall 754982912 Oct 21 21:37 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Oct 21 21:37 tbsmn01.dbf
-rw-r----- 1 oracle oinstall 55582720 Oct 21 21:37 tbsmn02.dbf
drwxr-xr-x 2 oracle oinstall      4096 Oct 18 21:07 test
-rw-r----- 1 oracle oinstall 204480512 Oct 21 21:37 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Oct 17 20:01 users01.dbf
[oracle@mylinux orcl]$ cp *.ctl /u01/backup/coldbackup/
[oracle@mylinux orcl]$ cp *.dbf /u01/backup/coldbackup/
[oracle@mylinux orcl]$ cp *.log /u01/backup/coldbackup/

然後對資料庫做一些更改
SQL> create table rec(id number,name varchar2(10));

Table created.

SQL> insert into rec values(1,'a');

1 row created.

SQL> insert into rec values(2,'b');

1 row created.

SQL> insert into rec values(3,'c');

1 row created.

SQL> insert into rec values(4,'d');

1 row created.

SQL> insert into rec values(5,'e');

1 row created.

SQL> commit;

SQL> create table rec1(id1 number,name1 varchar2(10));

Table created.

SQL> insert into rec1 values(11,'aa');

1 row created.

SQL> insert into rec1 values(22,'bb');

1 row created.

SQL> commit;

Commit complete.

下面shutdown immediate將資料庫的資料檔案、控制檔案和聯機重做日誌檔案都刪除掉，並將冷備的資料檔案和控制檔案替換進去，但不復制聯機重做日

志檔案
[oracle@mylinux orcl]$ rm -f *.ctl
[oracle@mylinux orcl]$ rm -f *.dbf
[oracle@mylinux orcl]$ rm -f *.log
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/*.ctl .
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/*.dbf .

然後startup
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             373294984 bytes
Database Buffers          117440512 bytes
Redo Buffers                5922816 bytes
Database mounted.
ORA-01122: database file 4 failed verification check
ORA-01110: data file 4: '/u01/oradata/users01.dbf'
ORA-01207: file is more recent than control file - old control file
--為什麼我提示這個錯誤呢？我把聯機重做日誌拷貝回去或者不拷貝回去，都是報這個錯？為什麼？
此時的資料檔案和控制檔案是我一起冷備的，為什麼會提示控制檔案是舊的？

我真是手賤啊，竟然把剛才備份的東西給刪除了
然後手忙腳亂的
recover database until cancel using backup controlfile;
alter database open resetlogs;就亂來了，後來也不知道為什麼會整出下面這個錯誤

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 4 was not restored from a sufficiently old backup--重點是這句
ORA-01110: data file 4: '/u01/oradata/users01.dbf'

後來想嘛，實在不行，就把資料庫刪了重新建個
但是進去dbca也提示一堆的錯誤，刪除資料庫也要在資料庫開啟的情況下刪除的，Oracle可能要查詢資料字典的，哎，實在沒轍了，網上搜“ORA-01152:

file 4 was not restored from a sufficiently old backup”
還真被我搜到一篇
http://zionw.blog.163.com/blog/static/9985959200911111181377/

startup force;

alter system set "_allow_resetlogs_corruption"=true scope=spfile;

recover database using backup controlfile until cancel;--要輸入cancel

alter databse open resetlogs;

一次不行再試一次，反正死馬當活馬醫，最後終歸是好了
以後要記住：保護現場最重要，方法終歸是有的，哎，我真是手賤啊

下面我再來一次
[oracle@mylinux ~]$ cd /u01/backup/coldbackup/
[oracle@mylinux coldbackup]$ mkdir 20121022
[oracle@mylinux coldbackup]$ cd /u01/oradata/orcl
[oracle@mylinux orcl]$ cp *.ctl /u01/backup/coldbackup/20121022/
[oracle@mylinux orcl]$ cp *.dbf /u01/backup/coldbackup/20121022/
[oracle@mylinux orcl]$ cp *.log /u01/backup/coldbackup/20121022/
然後對資料庫做一些更改
SQL> create table abc(id number);
Table created.
SQL> insert into abc values(1);
1 row created.
SQL> insert into abc values(2);
1 row created.
SQL> insert into abc values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> create table def(id number);
Table created.
SQL> insert into def values(4);
1 row created.
SQL> insert into def values(5);
1 row created.
SQL> insert into def values(6);
1 row created.
SQL> commit;
Commit complete.

關閉資料庫，刪除資料檔案、控制檔案和聯機重做日誌檔案
[oracle@mylinux orcl]$ rm *.ctl
[oracle@mylinux orcl]$ rm *.dbf
[oracle@mylinux orcl]$ rm *.log

現在把舊的資料檔案、控制檔案和聯機重做日誌檔案拷貝回去
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121022/* .

啟動資料庫試試
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             373294984 bytes
Database Buffers          117440512 bytes
Redo Buffers                5922816 bytes
Database mounted.
ORA-01122: database file 4 failed verification check
ORA-01110: data file 4: '/u01/oradata/users01.dbf'
ORA-01207: file is more recent than control file - old control file
為什麼這樣也會提示錯誤呢？資料檔案、控制檔案、聯機重做日誌檔案不是在關閉資料庫時一致的備份嗎？百思不得其解

SQL> col name for a40
SQL> select name,checkpoint_change# from v$datafile;

NAME                                     CHECKPOINT_CHANGE#
---------------------------------------- ------------------
/u01/oradata/orcl/system01.dbf                      2232609
/u01/oradata/orcl/sysaux01.dbf                      2232609
/u01/oradata/orcl/undotbs01.dbf                     2232609
/u01/oradata/users01.dbf                            2232609
/u01/oradata/orcl/example01.dbf                     2232609
/u01/oradata/orcl/tbsmn01.dbf                       2232609
/u01/oradata/orcl/tbsmn02.dbf                       2232609
/u01/oradata/manualsegs01.dbf                       2232609
/u01/oradata/autosegs01.dbf                         2232609
/u01/oradata/rman01.dbf                             2232609
/u01/oradata/hello.dbf                              2232609

11 rows selected.

SQL> select name,checkpoint_change# from v$datafile_header;

NAME                                     CHECKPOINT_CHANGE#
---------------------------------------- ------------------
/u01/oradata/orcl/system01.dbf                      2232609
/u01/oradata/orcl/sysaux01.dbf                      2232609
/u01/oradata/orcl/undotbs01.dbf                     2232609
/u01/oradata/users01.dbf                            2233382
/u01/oradata/orcl/example01.dbf                     2232609
/u01/oradata/orcl/tbsmn01.dbf                       2232609
/u01/oradata/orcl/tbsmn02.dbf                       2232609
/u01/oradata/manualsegs01.dbf                       2233382
/u01/oradata/autosegs01.dbf                         2233382
/u01/oradata/rman01.dbf                             2233382
/u01/oradata/hello.dbf                              2233382

11 rows selected.
--查詢v$datafile_header和v$datafile發現，確實資料檔案頭中記錄的users01.dbf(2233382)比控制檔案(2232609)中記錄的要新

下面解決這個錯誤，使用重新建立控制檔案的方式
alter database backup controlfile to trace as '/u01/oradata/orcl/ctl.sql';
開啟/u01/oradata/orcl/ctl.sql將其中一段複製出來，儲存為create_controlfile.sql

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512,
GROUP 4 (
    '/u01/oradata/orcl/redo04.log',
    '/u01/oradata/orcl/redo04b.log'
) SIZE 5M BLOCKSIZE 512
-- STANDBY LOGFILE

DATAFILE
'/u01/oradata/orcl/system01.dbf',
'/u01/oradata/orcl/sysaux01.dbf',
'/u01/oradata/orcl/undotbs01.dbf',
'/u01/oradata/users01.dbf',
'/u01/oradata/orcl/example01.dbf',
'/u01/oradata/orcl/tbsmn01.dbf',
'/u01/oradata/orcl/tbsmn02.dbf',
'/u01/oradata/manualsegs01.dbf',
'/u01/oradata/autosegs01.dbf',
'/u01/oradata/rman01.dbf',
'/u01/oradata/hello.dbf'
CHARACTER SET AL32UTF8
;

其中-- STANDBY LOGFILE去掉，否則會影響指令碼的執行
先將資料庫關閉
執行這個指令碼
SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> @/u01/oradata/orcl/create_controlfile.sql
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             373294984 bytes
Database Buffers          117440512 bytes
Redo Buffers                5922816 bytes
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01229: data file 11 is inconsistent with logs
ORA-01110: data file 11: '/u01/oradata/hello.dbf'

--現在我終於明白為什麼會提示控制檔案與資料檔案不一致的情況了，因為manualsegs01.dbf、autosegs01.dbf、rman01.dbf、hello.dbf這幾個資料檔案

與其他資料檔案在不同的路徑下，那麼我原先做的冷備只是備份其中的一些資料檔案，還原也只還原了一部分，所以最後我還原的其實部分是新的，部分

是舊的，鬱悶哪，難怪建立控制檔案不成功了，所以把資料檔案放在統一的路徑下很重要，而且在備份前要查詢v$datafile資料檔案的位置，否則就出現

了我現在這樣的狀況

然後現在因為我控制檔案建立不成功，然後資料庫都mount不了了
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01079: ORACLE database was not properly created, operation aborted

現在我把/u01/oradata/orcl下面的資料檔案、控制檔案、聯機重做日誌檔案全部刪除掉，再重新拷貝一份備份試試看
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             373294984 bytes
Database Buffers          117440512 bytes
Redo Buffers                5922816 bytes
Database mounted.
ORA-01122: database file 4 failed verification check
ORA-01110: data file 4: '/u01/oradata/users01.dbf'
ORA-01207: file is more recent than control file - old control file

下面使用終極法寶，當然只是在測試庫上用用，正式庫上哪敢啊，資料很容易損壞
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL>
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2232609 generated at 10/22/2012 20:57:21 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_1_797294724.dbf
ORA-00280: change 2232609 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 4 was not restored from a sufficiently old backup
ORA-01110: data file 4: '/u01/oradata/users01.dbf'

ORA-01112: media recovery not started

SQL> alter databse open resetlogs;
alter databse open resetlogs
*
ERROR at line 1:
ORA-00940: invalid ALTER command

SQL> alter database open resetlogs;

Database altered.
--竟然開啟了

下面要將那些資料檔案都統一放到/u01/oradata/orcl下
shutdown immediate先在作業系統上移動
再startup mount使用alter database rename file '' to '';
select name from v$tempfile;
select name from v$datafile;--確認資料檔案已經移動到我們想要的位置
再開啟資料庫alter database open;

好吧，下次再來一次冷備，真是折騰啊，但也總算長了教訓
shutdown immediate
[oracle@mylinux coldbackup]$ mkdir 20121023
[oracle@mylinux coldbackup]$ cd 20121023
[oracle@mylinux 20121023]$ ll
total 0
[oracle@mylinux 20121023]$ cp /u01/oradata/orcl/*.ctl .
[oracle@mylinux 20121023]$ cp /u01/oradata/orcl/*.dbf .
[oracle@mylinux 20121023]$ cp /u01/oradata/orcl/*.log .

下面對資料庫做一些更改
conn hr/hr
SQL> create table abc(id number);
Table created.
SQL> insert into abc values(1);
1 row created.
SQL> insert into abc values(2);
1 row created.
SQL> insert into abc values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> create table def(id number);
Table created.
SQL> insert into def values(4);
1 row created.
SQL> insert into def values(5);
1 row created.
SQL> insert into def values(6);
1 row created.
SQL> commit;
Commit complete.

關閉資料庫，刪除控制檔案、資料檔案和聯機重做日誌檔案
oracle@mylinux orcl]$ rm *.dbf
[oracle@mylinux orcl]$ rm *.log
[oracle@mylinux orcl]$ rm *.ctl
重新把備份裡面的控制檔案、資料檔案和聯機重做日誌檔案拷貝過來
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121023/* .

SQL> startup--啟動試試
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             364906376 bytes
Database Buffers          125829120 bytes
Redo Buffers                5922816 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 5968
Session ID: 1 Serial number: 5
--小插曲，檢視了日誌，ps -ef發現後臺程式全部關閉了
退出sqlplus重新進下就好了
startup成功
這時候hr使用者下abc和def兩張表是沒有的，因為是過去的冷備份

下面模擬一種情形，假如我當時備份的時候沒有備份聯機重做日誌檔案，會發生什麼情況
shutdown immediate將控制檔案、資料檔案、聯機重做日誌檔案全部刪除掉
再從冷備處拷貝控制檔案和資料檔案，不拷貝聯機重做日誌檔案
[oracle@mylinux orcl]$ rm *.ctl
[oracle@mylinux orcl]$ rm *.dbf
[oracle@mylinux orcl]$ rm *.log
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121023/*.ctl .
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121023/*.dbf .

看下startup會發生什麼情況
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             364906376 bytes
Database Buffers          125829120 bytes
Redo Buffers                5922816 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 7075
Session ID: 1 Serial number: 5

查詢alert.log發現記錄瞭如下日誌
Errors in file /u01/diag/rdbms/orcl/orcl/trace/orcl_lgwr_7030.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/orcl/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/diag/rdbms/orcl/orcl/trace/orcl_lgwr_7030.trc:

下面嘗試下使用alter database clear logfile group 1;重建日誌檔案試試看
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             364906376 bytes
Database Buffers          125829120 bytes
Redo Buffers                5922816 bytes
Database mounted.
SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         4 UNUSED
         3 UNUSED
         2 UNUSED

SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/orcl/redo02.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database clear logfile group 4;

Database altered.

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 7393
Session ID: 1 Serial number: 5

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mylinux ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 23 21:45:21 2012

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             364906376 bytes
Database Buffers          125829120 bytes
Redo Buffers                5922816 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 7647
Session ID: 1 Serial number: 5

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mylinux ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 23 21:46:38 2012

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             364906376 bytes
Database Buffers          125829120 bytes
Redo Buffers                5922816 bytes
Database mounted.
SQL> select group#,sequence#,status from v$log;

    GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
         1          3 CURRENT
         4          0 UNUSED
         3          0 UNUSED
         2          2 INACTIVE

SQL> alter database open;

Database altered.

--好像出現這些個錯之後
ORA-03113: end-of-file on communication channel
Process ID: 7647
Session ID: 1 Serial number: 5
不能使用startup直接開啟資料庫，需要先startup mount,再alter database open才可以

下面使用小布老師說的方法：
關閉資料庫，刪除控制檔案、資料檔案、聯機重做日誌檔案
從冷備處拷貝控制檔案、資料檔案
[oracle@mylinux orcl]$ rm *.ctl
[oracle@mylinux orcl]$ rm *.dbf
[oracle@mylinux orcl]$ rm *.log
[oracle@mylinux orcl]$ ll
total 8
drwxr-x--- 4 oracle oinstall 4096 Sep 5 20:32 ORCL
drwxr-xr-x 2 oracle oinstall 4096 Oct 23 20:57 test
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121023/*.ctl .
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121023/*.dbf .

[oracle@mylinux ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 23 22:00:25 2012

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             364906376 bytes
Database Buffers          125829120 bytes
Redo Buffers                5922816 bytes
Database mounted.
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 2236227 generated at 10/23/2012 20:56:49 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_1_797380909.dbf
ORA-00280: change 2236227 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database resetlogs;
alter database resetlogs
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

SQL> alter database open resetlogs;
Database altered.
--這樣日誌檔案就重新建立好了，通過不完全恢復之後resetlogs方式來重新建立日誌檔案

但是如果不recover using backup controlfile直接alter database open resetlogs就會提示錯誤如下
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

下面我再試一種，在沒有備份聯機重做日誌的情況下，我不用
recover database until cancel using backup controlfile;
而是用recover database until cancel;

SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             364906376 bytes
Database Buffers          125829120 bytes
Redo Buffers                5922816 bytes
Database mounted.
SQL> recover database until cancel;--成功
Media recovery complete.
SQL> alter database open resetlogs;--成功，/u01/oradata/orcl發現聯機重做日誌已經重新建成
Database altered.
SQL> select group#,sequence#,status from v$log;
    GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          0 UNUSED
         3          0 UNUSED
         4          0 UNUSED

參考資料:http://zionw.blog.163.com/blog/static/9985959200911111181377/
ORA-01152錯誤解決方法（轉）

2009-12-11 11:08:01| 分類：資料庫-oracle |字號訂閱
SQL> startup;

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

Total System Global Area 293601280 bytes

Fixed Size 1248600 bytes

Variable Size 96469672 bytes

Database Buffers 188743680 bytes

Redo Buffers 7139328 bytes

Database mounted.

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: 'L:\ORACLE\PRODUCT\10.2.0\ORADATA\ORC2\SYSTEM01.DBF'

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

4532411

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

------------------

4532411

4664735

9 rows selected.

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#

------------------

4711164

4664735

9 rows selected.

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 4697108 generated at 12/24/2007 11:48:57 needed for thread 1

ORA-00289: suggestion :

L:\ORACLE\PRODUCT\10.2.0\ORADATA\ORC2\ARCHIVEARC00007_0641832494.001

ORA-00280: change 4697108 for thread 1 is in sequence #7

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: 'L:\ORACLE\PRODUCT\10.2.0\ORADATA\ORC2\SYSTEM01.DBF'

ORA-01112: media recovery not started

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: 'L:\ORACLE\PRODUCT\10.2.0\ORADATA\ORC2\SYSTEM01.DBF'

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 4697108 generated at 12/24/2007 11:48:57 needed for thread 1

ORA-00289: suggestion :

L:\ORACLE\PRODUCT\10.2.0\ORADATA\ORC2\ARCHIVEARC00007_0641832494.001

ORA-00280: change 4697108 for thread 1 is in sequence #7

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: 'L:\ORACLE\PRODUCT\10.2.0\ORADATA\ORC2\SYSTEM01.DBF'

ORA-01112: media recovery not started

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> startup force;

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

Total System Global Area 293601280 bytes

Fixed Size 1248600 bytes

Variable Size 96469672 bytes

Database Buffers 188743680 bytes

Redo Buffers 7139328 bytes

Database mounted.

Database opened.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

4717118

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

------------------

4717118

4664735

9 rows selected.

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#

------------------

4717118

0

9 rows selected.

SQL> select last_change# from v$datafile;

LAST_CHANGE#

------------

4664735

9 rows selected.

具體步驟如下:

startup force;

alter system set "_allow_resetlogs_corruption"=true scope=spfile;

recover database using backup controlfile until cancel;

alter database open resetlogs;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26524307/viewspace-1061615/，如需轉載，請註明出處，否則將追究法律責任。

noarchivelog模式下冷備時沒有備份聯機重做日誌檔案

相關文章