trace中的控制文件备份执行恢复,这种通过查看trace文件的记录,查看数据库打开过程中
执行控制文件的情况。
---以sysdba身份登录sqlplus,将控制文件转储到trace文件中:
sys@PROD>alter database backup controlfile to trace;
Database altered.
--找到转储trace文件:
[oracle@enmo trace]$ ls -lrt
... ...
-rw-r----- 1 oracle oinstall 8293 Nov 7 15:33 PROD_ora_15666.trc
-rw-r----- 1 oracle oinstall 109 Nov 7 15:53 PROD_mmon_15614.trm
-rw-r----- 1 oracle oinstall 1425 Nov 7 15:53 PROD_mmon_15614.trc
-rw-r----- 1 oracle oinstall 119 Nov 7 15:53 PROD_ora_15629.trm
-rw-r----- 1 oracle oinstall 8167 Nov 7 15:53 PROD_ora_15629.trc
-rw-r----- 1 oracle oinstall 176417 Nov 7 15:53 alert_PROD.log
[oracle@enmo trace]$
#按时间排序,找到位置最后的trace文件。
---查看最新生成的trace文件,可以清晰的看到创建控制文件的脚本:
[oracle@enmo trace]$ more PROD_ora_15629.trc
Trace file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_15629.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
... ...
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/PROD/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/PROD/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/PROD/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/PROD/system01.dbf',
'/u01/app/oracle/oradata/PROD/sysaux01.dbf',
'/u01/app/oracle/oradata/PROD/undotbs01.dbf',
'/u01/app/oracle/oradata/PROD/users01.dbf',
'/u01/app/oracle/oradata/PROD/example01.dbf',
'/u01/app/oracle/oradata/PROD/ts_xxf_01.dbf',
'/u01/app/oracle/oradata/PROD/ts_ctl01.dbf'
CHARACTER SET AL32UTF8
;
#这部分就是打开数据库过程中启用控制文件的过程。
---删除控制文件并关闭数据库:
sys@PROD>!rm /u01/app/oracle/oradata/PROD/control01.ctl
sys@PROD>!rm /u01/app/oracle/fast_recovery_area/PROD/control02.ctl
sys@PROD>!ls /u01/app/oracle/oradata/PROD/control01.ctl
ls: /u01/app/oracle/oradata/PROD/control01.ctl: No such file or directory
sys@PROD>!ls /u01/app/oracle/fast_recovery_area/PROD/control02.ctl
ls: /u01/app/oracle/fast_recovery_area/PROD/control02.ctl: No such file or directory
---尝试关闭数据库:
sys@PROD>shutdown immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
#由于没有控制文件,正常关库关不了。
#需要强行关库。
---强制性关库:
sys@PROD>shutdown abort;
ORACLE instance shut down.
sys@PROD>
---按照转储trace文件中的步骤,恢复控制文件,第一步:启动到nomount状态:
sys@PROD>startup nomount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 507513936 bytes
Database Buffers 322961408 bytes
Redo Buffers 2371584 bytes
sys@PROD>
---创建控制文件:
sys@PROD>CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/PROD/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/app/oracle/oradata/PROD/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/app/oracle/oradata/PROD/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/oradata/PROD/system01.dbf',
14 '/u01/app/oracle/oradata/PROD/sysaux01.dbf',
15 '/u01/app/oracle/oradata/PROD/undotbs01.dbf',
16 '/u01/app/oracle/oradata/PROD/users01.dbf',
17 '/u01/app/oracle/oradata/PROD/example01.dbf',
18 '/u01/app/oracle/oradata/PROD/ts_xxf_01.dbf',
19 '/u01/app/oracle/oradata/PROD/ts_ctl01.dbf'
20 CHARACTER SET AL32UTF8
21 ;
Control file created.
sys@PROD>
#控制文件已经创建成功。
---查看数据库实例的状态:
sys@PROD>select status from v$instance;
STATUS
------------
MOUNTED
#创建控制文件之后,自动变换到mount状态。
--系统查看控制文件的路径:
sys@PROD>!ls /u01/app/oracle/oradata/PROD/control01.ctl
/u01/app/oracle/oradata/PROD/control01.ctl
sys@PROD>!ls /u01/app/oracle/fast_recovery_area/PROD/control02.ctl
/u01/app/oracle/fast_recovery_area/PROD/control02.ctl
#已经生产新的控制文件。
---尝试恢复数据库并打开数据库:
sys@PROD>RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
sys@PROD>ALTER SYSTEM ARCHIVE LOG ALL;
ALTER SYSTEM ARCHIVE LOG ALL
*
ERROR at line 1:
ORA-01649: operation not allowed with a backup control file
sys@PROD>ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
sys@PROD>ALTER DATABASE OPEN RESETLOGS;
Database altered.
#数据库是以RESETLOGS方式打开。
--添加临时数据文件:
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/PROD/temp01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10 MAXSIZE 200M;
sys@PROD>ALTER TABLESPACE TEMP ADD TEMPFILE
2 '/u01/app/oracle/oradata/PROD/temp01.dbf'
3 SIZE 100M REUSE AUTOEXTEND ON NEXT 10 MAXSIZE 200M;
Tablespace altered.
sys@PROD>
sys@PROD>select FILE_NAME from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/temp01.dbf
sys@PROD>
#控制文件恢复完成。