無redo情況下11.2.0.2資料檔案遷移到11.2.0.4後ORA-39700的異常處理
11.2.0.2的資料檔案遷移到另一臺11.2.0.4的測試環境,無redo log
--os:centos 6.5 64位
--db: 11.2.0.4
----情況一:正確的處理流程,要同時resetlogs upgrade
[oracle@cent6501 ~]$ cd $ORACLE_HOME/dbs
[oracle@cent6501 dbs]$ ls
hc_ct6501.dat init.ora lkCT6501 orapwct6501 spfilect6501.ora
hc_orclbi.dat initorclbi.ora lkORCLBI orapworclbi spfileorclbi.ora
[oracle@cent6501 dbs]$ ORACLE_SID=orclbi
[oracle@cent6501 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 6 16:59:30 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 427820072 bytes
Database Buffers 188743680 bytes
Redo Buffers 7507968 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE orclbi RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/oradata/orclbi/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u02/oradata/orclbi/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u02/oradata/orclbi/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u02/oradata/orclbi/system01.dbf',
'/u02/oradata/orclbi/sysaux01.dbf',
2 3 4 5 6 7 8 9 10 11 12 13 14 15 '/u02/oradata/orclbi/undotbs01.dbf',
16 '/u02/oradata/orclbi/users01.dbf',
'/u02/oradata/orclbi/GOLDENGATE_TABLESPACE.dbf',
17 18 '/u02/oradata/orclbi/FUZECHARGING_TABLESPACE.dbf',
19 '/u02/oradata/orclbi/FUZEPASSPORT_TABLESPACE.dbf',
20 '/u02/oradata/orclbi/FUZEAPP_TABLESPACE.dbf',
21 '/u02/oradata/orclbi/FUZEFRD_TABLESPACE.dbf'
CHARACTER SET AL32UTF8
22 23 ;
Control file created.
SQL> alter database open resetlogs upgrade;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/orclbi/temp01.dbf' SIZE 73400320 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
--之後執行@$ORACLE_HOME/rdbms/admin/catupgrd.sql,@$ORACLE_HOME/rdbms/admin/utlrp即可.
----情況二:執行alter database open resetlogs沒有加upgrade,報ORA-00704,ORA-39700
[oracle@cent6502 ~]$ ORACLE_SID=orclbi
[oracle@cent6502 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 7 09:28:30 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@cent6502 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 7 09:33:10 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 440402984 bytes
Database Buffers 180355072 bytes
Redo Buffers 3313664 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE orclbi RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u02/oradata/orclbi/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u02/oradata/orclbi/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u02/oradata/orclbi/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u02/oradata/orclbi/system01.dbf',
'/u02/oradata/orclbi/sysaux01.dbf',
'/u02/oradata/orclbi/undotbs01.dbf',
14 15 16 '/u02/oradata/orclbi/users01.dbf',
17 '/u02/oradata/orclbi/GOLDENGATE_TABLESPACE.dbf',
18 '/u02/oradata/orclbi/FUZECHARGING_TABLESPACE.dbf',
19 '/u02/oradata/orclbi/FUZEPASSPORT_TABLESPACE.dbf',
20 '/u02/oradata/orclbi/FUZEAPP_TABLESPACE.dbf',
'/u02/oradata/orclbi/FUZEFRD_TABLESPACE.dbf'
21 22 CHARACTER SET AL32UTF8
23 ;
Control file created.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 2530
Session ID: 1 Serial number: 3
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@cent6502 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 7 09:34:30 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 440402984 bytes
Database Buffers 180355072 bytes
Redo Buffers 3313664 bytes
Database mounted.
SQL> alter database open resetlogs upgrade;
alter database open resetlogs upgrade
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.
SQL> alter databse open upgrade;
alter databse open upgrade
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> alter database open upgrade;
alter database open upgrade
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u02/oradata/orclbi/system01.dbf'
SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 440402984 bytes
Database Buffers 180355072 bytes
Redo Buffers 3313664 bytes
--此處的CREATE CONTROLFILE用noRESETLOGS,此時資料檔案在recover狀態,需要第一次resetlogs後產生的redo log
SQL> CREATE CONTROLFILE REUSE DATABASE orclbi noRESETLOGS ARCHIVELOG
MAXLOGFILES 16
2 3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u02/oradata/orclbi/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u02/oradata/orclbi/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u02/oradata/orclbi/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u02/oradata/orclbi/system01.dbf',
14 '/u02/oradata/orclbi/sysaux01.dbf',
'/u02/oradata/orclbi/undotbs01.dbf',
15 16 '/u02/oradata/orclbi/users01.dbf',
17 '/u02/oradata/orclbi/GOLDENGATE_TABLESPACE.dbf',
18 '/u02/oradata/orclbi/FUZECHARGING_TABLESPACE.dbf',
19 '/u02/oradata/orclbi/FUZEPASSPORT_TABLESPACE.dbf',
20 '/u02/oradata/orclbi/FUZEAPP_TABLESPACE.dbf',
21 '/u02/oradata/orclbi/FUZEFRD_TABLESPACE.dbf'
CHARACTER SET AL32UTF8
22 23 ;
Control file created.
SQL> alter database open noresetlogs upgrade;
alter database open noresetlogs upgrade
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> recover database;
Media recovery complete.
SQL> alter database open upgrade;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/orclbi/temp01.dbf' SIZE 73400320 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
--之後執行@$ORACLE_HOME/rdbms/admin/catupgrd.sql,@$ORACLE_HOME/rdbms/admin/utlrp即可.
----情況三:執行alter database open resetlogs沒有加upgrade,報ORA-00704,ORA-39700後,刪除了控制檔案和redo log
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 440402984 bytes
Database Buffers 180355072 bytes
Redo Buffers 3313664 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE orclbi RESETLOGS ARCHIVELOG
MAXLOGFILES 16
2 3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u02/oradata/orclbi/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u02/oradata/orclbi/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u02/oradata/orclbi/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u02/oradata/orclbi/system01.dbf',
14 '/u02/oradata/orclbi/sysaux01.dbf',
15 '/u02/oradata/orclbi/undotbs01.dbf',
16 '/u02/oradata/orclbi/users01.dbf',
17 '/u02/oradata/orclbi/GOLDENGATE_TABLESPACE.dbf',
'/u02/oradata/orclbi/FUZECHARGING_TABLESPACE.dbf',
18 19 '/u02/oradata/orclbi/FUZEPASSPORT_TABLESPACE.dbf',
20 '/u02/oradata/orclbi/FUZEAPP_TABLESPACE.dbf',
21 '/u02/oradata/orclbi/FUZEFRD_TABLESPACE.dbf'
22 CHARACTER SET AL32UTF8
23 ;
Control file created.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 11473
Session ID: 1 Serial number: 3
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@cent6502 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 7 10:23:43 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 440402984 bytes
Database Buffers 180355072 bytes
Redo Buffers 3313664 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE orclbi RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
2 3 4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u02/oradata/orclbi/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u02/oradata/orclbi/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u02/oradata/orclbi/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u02/oradata/orclbi/system01.dbf',
14 '/u02/oradata/orclbi/sysaux01.dbf',
15 '/u02/oradata/orclbi/undotbs01.dbf',
16 '/u02/oradata/orclbi/users01.dbf',
17 '/u02/oradata/orclbi/GOLDENGATE_TABLESPACE.dbf',
18 '/u02/oradata/orclbi/FUZECHARGING_TABLESPACE.dbf',
19 '/u02/oradata/orclbi/FUZEPASSPORT_TABLESPACE.dbf',
20 '/u02/oradata/orclbi/FUZEAPP_TABLESPACE.dbf',
21 '/u02/oradata/orclbi/FUZEFRD_TABLESPACE.dbf'
CHARACTER SET AL32UTF8
22 23 ;
Control file created.
SQL> alter database open resetlogs upgrade;
alter database open resetlogs upgrade
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u02/oradata/orclbi/system01.dbf'
SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 0 52428800 512 1 YES
UNUSED 0 0
3 1 0 52428800 512 1 YES
CURRENT 0 0
2 1 0 52428800 512 1 YES
UNUSED 0 0
SQL> recover database using backup controlfile;
ORA-00279: change 1312571 generated at 07/07/2015 10:23:18 needed for thread 1
ORA-00289: suggestion : /u02/archivelog/orclbi/1_1_884427794.dbf
ORA-00280: change 1312571 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u02/archivelog/orclbi/1_1_884427794.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/u02/archivelog/orclbi/1_1_884427794.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> alter database open resetlogs upgrade;
alter database open resetlogs upgrade
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u02/oradata/orclbi/system01.dbf'
--此時資料檔案在recover狀態,需要第一次resetlogs後產生的redo log,就要做當前日誌檔案被刪除的處理.
如果只是clear unarchived logfile是不行的,資料檔案已經在recover狀態了.
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> startup force mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 440402984 bytes
Database Buffers 180355072 bytes
Redo Buffers 3313664 bytes
Database mounted.
SQL> alter database open resetlogs upgrade;
Database altered.
SQL> alter system reset "_allow_resetlogs_corruption" scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 440402984 bytes
Database Buffers 180355072 bytes
Redo Buffers 3313664 bytes
Database mounted.
SQL> alter database open upgrade;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/orclbi/temp01.dbf' SIZE 73400320 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
--之後執行@$ORACLE_HOME/rdbms/admin/catupgrd.sql,@$ORACLE_HOME/rdbms/admin/utlrp即可.
--os:centos 6.5 64位
--db: 11.2.0.4
----情況一:正確的處理流程,要同時resetlogs upgrade
[oracle@cent6501 ~]$ cd $ORACLE_HOME/dbs
[oracle@cent6501 dbs]$ ls
hc_ct6501.dat init.ora lkCT6501 orapwct6501 spfilect6501.ora
hc_orclbi.dat initorclbi.ora lkORCLBI orapworclbi spfileorclbi.ora
[oracle@cent6501 dbs]$ ORACLE_SID=orclbi
[oracle@cent6501 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 6 16:59:30 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 427820072 bytes
Database Buffers 188743680 bytes
Redo Buffers 7507968 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE orclbi RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/oradata/orclbi/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u02/oradata/orclbi/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u02/oradata/orclbi/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u02/oradata/orclbi/system01.dbf',
'/u02/oradata/orclbi/sysaux01.dbf',
2 3 4 5 6 7 8 9 10 11 12 13 14 15 '/u02/oradata/orclbi/undotbs01.dbf',
16 '/u02/oradata/orclbi/users01.dbf',
'/u02/oradata/orclbi/GOLDENGATE_TABLESPACE.dbf',
17 18 '/u02/oradata/orclbi/FUZECHARGING_TABLESPACE.dbf',
19 '/u02/oradata/orclbi/FUZEPASSPORT_TABLESPACE.dbf',
20 '/u02/oradata/orclbi/FUZEAPP_TABLESPACE.dbf',
21 '/u02/oradata/orclbi/FUZEFRD_TABLESPACE.dbf'
CHARACTER SET AL32UTF8
22 23 ;
Control file created.
SQL> alter database open resetlogs upgrade;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/orclbi/temp01.dbf' SIZE 73400320 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
--之後執行@$ORACLE_HOME/rdbms/admin/catupgrd.sql,@$ORACLE_HOME/rdbms/admin/utlrp即可.
----情況二:執行alter database open resetlogs沒有加upgrade,報ORA-00704,ORA-39700
[oracle@cent6502 ~]$ ORACLE_SID=orclbi
[oracle@cent6502 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 7 09:28:30 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@cent6502 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 7 09:33:10 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 440402984 bytes
Database Buffers 180355072 bytes
Redo Buffers 3313664 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE orclbi RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u02/oradata/orclbi/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u02/oradata/orclbi/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u02/oradata/orclbi/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u02/oradata/orclbi/system01.dbf',
'/u02/oradata/orclbi/sysaux01.dbf',
'/u02/oradata/orclbi/undotbs01.dbf',
14 15 16 '/u02/oradata/orclbi/users01.dbf',
17 '/u02/oradata/orclbi/GOLDENGATE_TABLESPACE.dbf',
18 '/u02/oradata/orclbi/FUZECHARGING_TABLESPACE.dbf',
19 '/u02/oradata/orclbi/FUZEPASSPORT_TABLESPACE.dbf',
20 '/u02/oradata/orclbi/FUZEAPP_TABLESPACE.dbf',
'/u02/oradata/orclbi/FUZEFRD_TABLESPACE.dbf'
21 22 CHARACTER SET AL32UTF8
23 ;
Control file created.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 2530
Session ID: 1 Serial number: 3
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@cent6502 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 7 09:34:30 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 440402984 bytes
Database Buffers 180355072 bytes
Redo Buffers 3313664 bytes
Database mounted.
SQL> alter database open resetlogs upgrade;
alter database open resetlogs upgrade
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.
SQL> alter databse open upgrade;
alter databse open upgrade
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> alter database open upgrade;
alter database open upgrade
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u02/oradata/orclbi/system01.dbf'
SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 440402984 bytes
Database Buffers 180355072 bytes
Redo Buffers 3313664 bytes
--此處的CREATE CONTROLFILE用noRESETLOGS,此時資料檔案在recover狀態,需要第一次resetlogs後產生的redo log
SQL> CREATE CONTROLFILE REUSE DATABASE orclbi noRESETLOGS ARCHIVELOG
MAXLOGFILES 16
2 3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u02/oradata/orclbi/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u02/oradata/orclbi/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u02/oradata/orclbi/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u02/oradata/orclbi/system01.dbf',
14 '/u02/oradata/orclbi/sysaux01.dbf',
'/u02/oradata/orclbi/undotbs01.dbf',
15 16 '/u02/oradata/orclbi/users01.dbf',
17 '/u02/oradata/orclbi/GOLDENGATE_TABLESPACE.dbf',
18 '/u02/oradata/orclbi/FUZECHARGING_TABLESPACE.dbf',
19 '/u02/oradata/orclbi/FUZEPASSPORT_TABLESPACE.dbf',
20 '/u02/oradata/orclbi/FUZEAPP_TABLESPACE.dbf',
21 '/u02/oradata/orclbi/FUZEFRD_TABLESPACE.dbf'
CHARACTER SET AL32UTF8
22 23 ;
Control file created.
SQL> alter database open noresetlogs upgrade;
alter database open noresetlogs upgrade
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> recover database;
Media recovery complete.
SQL> alter database open upgrade;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/orclbi/temp01.dbf' SIZE 73400320 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
--之後執行@$ORACLE_HOME/rdbms/admin/catupgrd.sql,@$ORACLE_HOME/rdbms/admin/utlrp即可.
----情況三:執行alter database open resetlogs沒有加upgrade,報ORA-00704,ORA-39700後,刪除了控制檔案和redo log
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 440402984 bytes
Database Buffers 180355072 bytes
Redo Buffers 3313664 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE orclbi RESETLOGS ARCHIVELOG
MAXLOGFILES 16
2 3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u02/oradata/orclbi/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u02/oradata/orclbi/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u02/oradata/orclbi/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u02/oradata/orclbi/system01.dbf',
14 '/u02/oradata/orclbi/sysaux01.dbf',
15 '/u02/oradata/orclbi/undotbs01.dbf',
16 '/u02/oradata/orclbi/users01.dbf',
17 '/u02/oradata/orclbi/GOLDENGATE_TABLESPACE.dbf',
'/u02/oradata/orclbi/FUZECHARGING_TABLESPACE.dbf',
18 19 '/u02/oradata/orclbi/FUZEPASSPORT_TABLESPACE.dbf',
20 '/u02/oradata/orclbi/FUZEAPP_TABLESPACE.dbf',
21 '/u02/oradata/orclbi/FUZEFRD_TABLESPACE.dbf'
22 CHARACTER SET AL32UTF8
23 ;
Control file created.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 11473
Session ID: 1 Serial number: 3
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@cent6502 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 7 10:23:43 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 440402984 bytes
Database Buffers 180355072 bytes
Redo Buffers 3313664 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE orclbi RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
2 3 4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u02/oradata/orclbi/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u02/oradata/orclbi/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u02/oradata/orclbi/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u02/oradata/orclbi/system01.dbf',
14 '/u02/oradata/orclbi/sysaux01.dbf',
15 '/u02/oradata/orclbi/undotbs01.dbf',
16 '/u02/oradata/orclbi/users01.dbf',
17 '/u02/oradata/orclbi/GOLDENGATE_TABLESPACE.dbf',
18 '/u02/oradata/orclbi/FUZECHARGING_TABLESPACE.dbf',
19 '/u02/oradata/orclbi/FUZEPASSPORT_TABLESPACE.dbf',
20 '/u02/oradata/orclbi/FUZEAPP_TABLESPACE.dbf',
21 '/u02/oradata/orclbi/FUZEFRD_TABLESPACE.dbf'
CHARACTER SET AL32UTF8
22 23 ;
Control file created.
SQL> alter database open resetlogs upgrade;
alter database open resetlogs upgrade
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u02/oradata/orclbi/system01.dbf'
SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 0 52428800 512 1 YES
UNUSED 0 0
3 1 0 52428800 512 1 YES
CURRENT 0 0
2 1 0 52428800 512 1 YES
UNUSED 0 0
SQL> recover database using backup controlfile;
ORA-00279: change 1312571 generated at 07/07/2015 10:23:18 needed for thread 1
ORA-00289: suggestion : /u02/archivelog/orclbi/1_1_884427794.dbf
ORA-00280: change 1312571 for thread 1 is in sequence #1
Specify log: {
auto
ORA-00308: cannot open archived log '/u02/archivelog/orclbi/1_1_884427794.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/u02/archivelog/orclbi/1_1_884427794.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> alter database open resetlogs upgrade;
alter database open resetlogs upgrade
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u02/oradata/orclbi/system01.dbf'
--此時資料檔案在recover狀態,需要第一次resetlogs後產生的redo log,就要做當前日誌檔案被刪除的處理.
如果只是clear unarchived logfile是不行的,資料檔案已經在recover狀態了.
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> startup force mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 440402984 bytes
Database Buffers 180355072 bytes
Redo Buffers 3313664 bytes
Database mounted.
SQL> alter database open resetlogs upgrade;
Database altered.
SQL> alter system reset "_allow_resetlogs_corruption" scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 440402984 bytes
Database Buffers 180355072 bytes
Redo Buffers 3313664 bytes
Database mounted.
SQL> alter database open upgrade;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/orclbi/temp01.dbf' SIZE 73400320 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
--之後執行@$ORACLE_HOME/rdbms/admin/catupgrd.sql,@$ORACLE_HOME/rdbms/admin/utlrp即可.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28539951/viewspace-1725453/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Java程式異常處理的特殊情況Java
- Oracle 無備份情況下undo檔案損壞處理Oracle
- 異常處理：IDEA Git 修改後的檔案無法CommitIdeaGitMIT
- jmeter問題處理隨筆1 - CSV取值資料異常處理（包含"號,","號的情況)JMeter
- MySQL異常恢復之無主鍵情況下innodb資料恢復的方法MySql資料恢復
- Redo 丟失的4種情況的處理方法
- 【故障處理】DG環境主庫丟失歸檔情況下資料檔案的恢復
- Redo丟失的4種情況及處理方法
- undo表空間檔案丟失恢復（3）--無備份無redo的情況下恢復
- STREAMS筆記(3) REDO清理 & 異常處理筆記
- 把檔案系統的資料檔案遷移到ASM儲存ASM
- undo表空間檔案丟失恢復（2）--無備份有redo的情況下恢復
- session儲存資料庫中以及禁用cookie情況下的處理Session資料庫Cookie
- 資料庫從檔案系統遷移到ASM資料庫ASM
- 利用rman將本地資料檔案遷移到asmASM
- 資料庫異常關閉後無法啟動問題處理一例資料庫
- oracle 將表空間下的資料檔案從檔案系統遷移到ASM磁碟組OracleASM
- 資料泵實現資料遷移到異地庫
- windows重灌/資料庫物理檔案完好的情況下恢復資料庫Windows資料庫
- xcodebuild -workspace 情況下，部分坑的處理XCodeUI
- 將資料庫從ASM遷移到檔案系統資料庫ASM
- 將資料庫從檔案系統遷移到ASM資料庫ASM
- 異常的處理
- Python之錯誤異常和檔案處理Python
- 3.python檔案操作及異常處理Python
- 異常篇——異常處理
- 異常-throws的方式處理異常
- redo log檔案丟失處理措施
- Sqoop匯入資料異常處理OOP
- 資料庫連線異常處理思路資料庫
- springboot下新增全域性異常處理和自定義異常處理Spring Boot
- docker容器無法啟動的情況下，如果修改配置檔案Docker
- 將ORACLE資料庫資料檔案遷移到其他目錄(ZT)Oracle資料庫
- springboot統一異常處理及返回資料的處理Spring Boot
- 資料檔案OFFLINE的3種情況
- 【SpringMVC】檔案上傳與下載、攔截器、異常處理器SpringMVC
- 異常處理
- REDO檔案丟失的恢復__沒有任何備份的情況