ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle之控制文件不备份resetlogs恢复

oracle之控制文件不备份resetlogs恢复

原创 Linux操作系统 作者:atx2418 时间:2012-07-12 17:53:19 0 删除 编辑

-------------------------------------oracle之控制文件不备份resetlogs恢复-----------------------------------------------

SQL> alter system switch logfile; ----联机日志切换

System altered.

SQL> select group#,status from v$log;

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

SQL> alter system archive log current; ----归档日志切换
System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3


SQL> alter system checkpoint; ---检查点切换

System altered.

SQL> alter database backup controlfile to trace; ---生成控制文件备份

Database altered.

SQL>SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') d

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/orcl/udump/orcl_ora_8190.trc -----查看生产的二进制的控制文件目录

[oracle@oracle ~]$ cp -rp /u01/app/oracle/admin/orcl/udump/orcl_ora_8190.trc ./
[oracle@oracle ~]$ ls -l
total 204
drwxr-xr-x 2 oracle oinstall 4096 Jul 11 16:47 Desktop
-rw-r----- 1 oracle oinstall 13173 Jul 12 16:08 orcl_ora_8190.trc
-rw-r----- 1 oracle oinstall 6812 Jul 11 21:35 sqlnet.log

[oracle@oracle ~]$ mv orcl_ora_8190.trc strom.sql -----修改二进制控制文件只留resetlogs信息

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_7zwx2ffr_.log',
'/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_1_7zwx2gkh_.log'
) SIZE 50M,
GROUP 2 (
'/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_7zwx2ho1_.log',
'/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_7zwx2jt4_.log'
) SIZE 50M,
GROUP 3 (
'/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_7zwx2l1w_.log',
'/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_7zwx2mbd_.log'
) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_7zwwzx6l_.dbf',
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_7zwwzxd7_.dbf',
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_7zwwzx72_.dbf',
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7zwwzxdw_.dbf',
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_7zwx380j_.dbf'
CHARACTER SET AL32UTF8
;

[oracle@oracle ~]$ cd /u01/app/oracle/flash_recovery_area/ORCL/controlfile/
[oracle@oracle controlfile]$ ls
o1_mf_7zwzspsm_.ctl
[oracle@oracle controlfile]$ rm -rf o1_mf_7zwzspsm_.ctl -----删除闪回区里的控制文件

[oracle@oracle controlfile]$ cd /u01/app/oracle/oradata/ORCL/controlfile/
[oracle@oracle controlfile]$ ls
o1_mf_7zwzspnx_.ctl
[oracle@oracle controlfile]$ rm -rf o1_mf_7zwzspnx_.ctl ----删除控制文件目录的所有文件

SQL> shutdown abort;
ORACLE instance shut down. ----强制关闭oracle数据库

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 92276328 bytes
Database Buffers 184549376 bytes
Redo Buffers 7168000 bytes
ORA-00205: error in identifying control file, check alert log for more info ---提示控制文件没有找到

SQL> @/home/oracle/strom.sql ----通过备份的二进制的控制脚本重建控制文件
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01276: Cannot add file
/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_7zwygr5l_.ctl. File has an
Oracle Managed Files file name.


SQL> alter system reset control_files scope=spfile sid='orcl';

System altered.

----sid里的值代表所有实例,这里的orcl是通过pfile里修改的,在pfile文件中的control_files前的×号改成orcl。

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 88082024 bytes
Database Buffers 188743680 bytes
Redo Buffers 7168000 bytes
SQL> @/home/oracle/strom.sql ---生成控制文件成功

Control file created.


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_7zwwzx6l_.dbf'

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 484883 generated at 07/12/2012 16:04:24 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_12/o1_mf_1_3_%u_.arc
ORA-00280: change 484883 for thread 1 is in sequence #3


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_7zwx2l1w_.log ---手工找到日志文件进行恢复
Log applied.
Media recovery complete.


SQL> alter database open resetlogs; ---打开数据库

Database altered.

SQL> select * from dual; ---查看DUAL的值

D
-
X

-------------------------------------oracle之控制文件不备份resetlogs恢复-----------------------------------------------


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

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2012-07-12

  • 博文量
    9
  • 访问量
    3552