无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即可.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28539951/viewspace-1725453/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/28539951/viewspace-1725453/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值