oracle 控制文件的重建
发布时间:2020-12-12 13:30:57 所属栏目:百科 来源:网络整理
导读:目录 oracle 控制文件的重建 NORESETLOGS RESETLOGS oracle 控制文件的重建 不到最后时刻,如三个控制文件都已损坏,又没有控制文件的备份。还是不要重建控制文件,处理不好就会有数据丢失。 NORESETLOGS Set #1. NORESETLOGS case---- The following comman
目录
oracle 控制文件的重建
NORESETLOGSSet #1. NORESETLOGS case -- -- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- Additional logs may be required for media recovery of offline -- Use this only if the current versions of all online logs are -- available. -- After mounting the created controlfile,the following SQL -- statement will place the database in the appropriate -- protection mode: -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 4096 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ‘+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167‘ SIZE 200M BLOCKSIZE 512,GROUP 2 ‘+DGSYSTEM/kyeupdb/onlinelog/group_2.267.978829169‘ SIZE 200M BLOCKSIZE 512,GROUP 3 ‘+DGSYSTEM/kyeupdb/onlinelog/group_3.268.978829173‘ SIZE 200M BLOCKSIZE 512,GROUP 4 ‘+DGSYSTEM/kyeupdb/onlinelog/group_4.269.978829183‘ SIZE 200M BLOCKSIZE 512,GROUP 5 ‘+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193‘ SIZE 200M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE ‘+DGSYSTEM/kyeupdb/datafile/system.271.978829205‘,‘+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265‘,‘+DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323‘,‘+DGSYSTEM/kyeupdb/datafile/users.275.978829391‘,‘+DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421‘,‘+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf‘ CHARACTER SET ZHS16GBK ; -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE ‘/oracle/app/oracle/product/11.2.0/dbs/arch1_1_978829161.dbf‘; -- Recovery is required if any of the datafiles are restored backups,-- or if the last shutdown was not normal or immediate. RECOVER DATABASE -- Database can now be opened normally. ALTER DATABASE OPEN; -- Files in normal offline tablespaces are now named. ALTER DATABASE RENAME FILE ‘MISSING00006‘ TO ‘+DGSYSTEM/kyeupdb/datafile/kye_tbs01.dbf‘; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE ‘+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379‘ SIZE 1024M REUSE AUTOEXTEND OFF; -- End of tempfile additions. 操作: [BEGIN] 2018/6/22 21:28:55 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup nomount; ORACLE instance started. Total System Global Area 3858223104 bytes Fixed Size 2259080 bytes Variable Size 788531064 bytes Database Buffers 3053453312 bytes Redo Buffers 13979648 bytes CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 4096 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ‘+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167‘ SIZE 200M BLOCKSIZE 512,GROUP 5 ‘+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193‘ SIZE 200M BLOCKSIZE 512 DATAFILE ‘+DGSYSTEM/kyeupdb/datafile/system.271.978829205‘,‘+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf‘ CHARACTER SET ZHS16GBK 21 ; Control file created. SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-00264: no recovery required SQL> alter database open; Database altered. SQL> select * from dba_temp_files; no rows selected ALTER TABLESPACE TEMP ADD TEMPFILE ‘+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379‘ 2 SIZE 1024M REUSE AUTOEXTEND OFF; Tablespace altered. SQL> select * from dba_temp_files; FILE_NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ---------- ------------------------------ ---------- ---------- ------- ------------ --- ---------- ---------- ------------ ---------- ----------- +DGSYSTEM/kyeupdb/tempfile/temp.274.978829379 1 TEMP 1073741824 131072 ONLINE 1 NO 0 0 1072693248 130944 [END] 2018/6/22 21:49:09 RESETLOGS-- Set #2. RESETLOGS case -- -- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- The contents of online logs will be lost and all backups will -- be invalidated. Use this only if online logs are damaged. -- After mounting the created controlfile,the following SQL -- statement will place the database in the appropriate -- protection mode: -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 4096 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ‘+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167‘ SIZE 200M BLOCKSIZE 512,-- or if the last shutdown was not normal or immediate. RECOVER DATABASE USING BACKUP CONTROLFILE -- Database can now be opened zeroing the online logs. ALTER DATABASE OPEN RESETLOGS; -- Files in normal offline tablespaces are now named. ALTER DATABASE RENAME FILE ‘MISSING00006‘ TO ‘+DGSYSTEM/kyeupdb/datafile/kye_tbs01.dbf‘; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE ‘+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379‘ SIZE 1024M REUSE AUTOEXTEND OFF; -- End of tempfile additions. 操作如下: [BEGIN] 2018/6/22 21:58:29 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning,Automatic Storage Management,OLAP,Data Mining and Real Application Testing options [[email?protected] ~]$ sqlplus ‘/as sysdba‘ SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 22 21:58:41 2018 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,Data Mining and Real Application Testing options SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /oracle/app/oracle/product/11.2.0/dbs/arch Oldest online log sequence 10 Current log sequence 14 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 4096 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ‘+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167‘ SIZE 200M BLOCKSIZE 512,‘+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf‘ CHARACTER SET ZHS16GBK 21 ; CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" RESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-01034: ORACLE not available Process ID: 75347 Session ID: 386 Serial number: 5 SQL> CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" RESETLOGS NOARCHIVELOG SIZE 512,MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 4096 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ‘+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167‘ SIZE 200M BLOCKSIZE 512,‘+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf‘ CHARACTER SET ZHS16GBK 21 ; CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" RESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-01034: ORACLE not available Process ID: 75347 Session ID: 386 Serial number: 5 SQL> startup nomount; ORACLE instance started. Total System Global Area 3858223104 bytes Fixed Size 2259080 bytes Variable Size 788531064 bytes Database Buffers 3053453312 bytes Redo Buffers 13979648 bytes CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 0M BLOCKSIZE 512,‘+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf‘ CHARACTER SET ZHS16GBK MAXLOGMEMBERS 3 MAXDATAFILES 4096 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ‘+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167‘ SIZE 200M BLOCKSIZE 512,‘+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf‘ CHARACTER SET ZHS16GBK 21 ; Control file created. SQL> ALTER DATABASE OPEN RESETLOGS; Database altered. ALTER TABLESPACE TEMP ADD TEMPFILE ‘+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379‘ 2 SIZE 1024M REUSE AUTOEXTEND OFF; Tablespace altered. SQL> select * from dba_temp_files; FILE_NAME -------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS ---------- ------------------------------ ---------- ---------- ------- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ------------ --- ---------- ---------- ------------ ---------- ----------- +DGSYSTEM/kyeupdb/tempfile/temp.274.978829379 1 TEMP 1073741824 131072 ONLINE 1 NO 0 0 0 1072693248 130944 SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /oracle/app/oracle/product/11.2.0/dbs/arch Oldest online log sequence 1 Current log sequence 1 SQL> [END] 2018/6/22 22:10:06
(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |