Oracle 11g- Recovering from Loss of All Control Files on ASM
Recovering from Loss of All Control Files
Recovery: All Control Files Missing
All Control Files Missing
Re-create lost controlfile
I have deleted all control files and re-created using trace file.
I have deleted all control files and re-created using trace file.
SQL> show parameter control NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ control_file_record_keep_time integer 7 control_files string +DATA/vihaan/controlfile/curre nt.262.844101027, +DATA/vihaan /controlfile/current.263.84410 1029 control_management_pack_access string DIAGNOSTIC+TUNING NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ control_file_record_keep_time integer 7 control_files string +DATA/vihaan/controlfile/current.262.844101027, +DATA/vihaan/controlfile/current.263.844101029 control_management_pack_access string DIAGNOSTIC+TUNING Check is database in archive log mode SQL> archive log list ; Database log mode Archive Mode Automatic archival Enabled Archive destination +DATA/vihaan/archivelog Oldest online log sequence 36 Next log sequence to archive 38 Current log sequence 38 SQL> alter system switch logfile ; System altered. SQL> archive log list ; Database log mode Archive Mode Automatic archival Enabled Archive destination +DATA/vihaan/archivelog Oldest online log sequence 37 Next log sequence to archive 39 Current log sequence 39 SQL> alter database backup controlfile to trace ; Database altered. Location of Trace file /u01/app/oracle/diag/rdbms/vihaan/vihaan/trace -rw-r----- 1 oracle asmadmin 7002 Apr 7 10:18 vihaan_ora_5567.trc <<<<<<<---- -rw-r----- 1 oracle asmadmin 269546 Apr 7 10:18 alert_vihaan.log cat vihaan_ora_5567.trc Trace file /u01/app/oracle/diag/rdbms/vihaan/vihaan/trace/vihaan_ora_5567.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 System name: Linux Node name: unknown Release: 2.6.18-348.1.1.el5 Version: #1 SMP Tue Jan 22 16:24:03 EST 2013 Machine: i686 Instance name: vihaan Redo thread mounted by this instance: 1 Oracle process number: 21 Unix process pid: 5567, image: oracle@unknown (TNS V1-V3) *** 2014-04-07 10:13:24.382 *** SESSION ID:(1.5) 2014-04-07 10:13:24.382 *** CLIENT ID:() 2014-04-07 10:13:24.382 *** SERVICE NAME:(SYS$USERS) 2014-04-07 10:13:24.382 *** MODULE NAME:(sqlplus@unknown (TNS V1-V3)) 2014-04-07 10:13:24.382 *** ACTION NAME:() 2014-04-07 10:13:24.382 kwqmnich: current time:: 9: 13: 23: 0 kwqmnich: instance no 0 repartition flag 1 kwqmnich: initialized job cache structure *** 2014-04-07 10:13:25.194 kwqinfy: Call kwqrNondurSubInstTsk *** 2014-04-07 10:18:55.674 -- The following are current System-scope REDO Log Archival related -- parameters and can be included in the database initialization file. -- -- LOG_ARCHIVE_DEST='' -- LOG_ARCHIVE_DUPLEX_DEST='' -- -- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf -- -- DB_UNIQUE_NAME="vihaan" -- -- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG' -- LOG_ARCHIVE_MAX_PROCESSES=4 -- STANDBY_FILE_MANAGEMENT=MANUAL -- STANDBY_ARCHIVE_DEST=?/dbs/arch -- FAL_CLIENT='' -- FAL_SERVER='' -- -- LOG_ARCHIVE_DEST_1='LOCATION=+DATA/vihaan/archivelog' -- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY' -- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC' -- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY' -- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME' -- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)' -- LOG_ARCHIVE_DEST_STATE_1=ENABLE -- -- Below are two sets of SQL statements, each of which creates a new -- control file and uses it to open the database. The first set opens -- the database with the NORESETLOGS option and should be used only if -- the current versions of all online logs are available. The second -- set opens the database with the RESETLOGS option and should be used -- if online logs are unavailable. -- The appropriate set of statements can be copied from the trace into -- a script file, edited as necessary, and executed when there is a -- need to re-create the control file. -- -- Set #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 "VIHAAN" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '+DATA/vihaan/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '+DATA/vihaan/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '+DATA/vihaan/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '+DATA/vihaan/system01.dbf', '+DATA/vihaan/sysaux01.dbf', '+DATA/vihaan/undotbs01.dbf', '+DATA/vihaan/users01.dbf' CHARACTER SET WE8MSWIN1252 ; -- 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 '+DATA/vihaan/archivelog/1_1_694825248.dbf'; -- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_811404903.dbf'; -- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_813933977.dbf'; -- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_813953696.dbf'; -- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_844101203.dbf'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE -- All logs need archiving and a log switch is needed. ALTER SYSTEM ARCHIVE LOG ALL; -- Database can now be opened normally. ALTER DATABASE OPEN; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/vihaan/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; -- End of tempfile additions. -- -- 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 "VIHAAN" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '+DATA/vihaan/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '+DATA/vihaan/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '+DATA/vihaan/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '+DATA/vihaan/system01.dbf', '+DATA/vihaan/sysaux01.dbf', '+DATA/vihaan/undotbs01.dbf', '+DATA/vihaan/users01.dbf' CHARACTER SET WE8MSWIN1252 ; -- 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 '+DATA/vihaan/archivelog/1_1_694825248.dbf'; -- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_811404903.dbf'; -- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_813933977.dbf'; -- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_813953696.dbf'; -- ALTER DATABASE REGISTER LOGFILE '+DATA/vihaan/archivelog/1_1_844101203.dbf'; -- Recovery is required if any of the datafiles are restored backups, -- 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; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/vihaan/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; -- End of tempfile additions. -- ================================ Do some activity for testing .... SQL> create table anuj as select sysdate s_date from dual ; Table created. SQL> alter session set nls_date_format='dd-mm-yyyy hh:mi:ss '; Session altered. SQL> select * from anuj ; S_DATE -------------------- 07-04-2014 10:24:36 SQL> archive log list ; Database log mode Archive Mode Automatic archival Enabled Archive destination +DATA/vihaan/archivelog Oldest online log sequence 37 Next log sequence to archive 39 Current log sequence 39 SQL> alter system switch logfile ; System altered. SQL> archive log list ; Database log mode Archive Mode Automatic archival Enabled Archive destination +DATA/vihaan/archivelog Oldest online log sequence 38 Next log sequence to archive 40 Current log sequence 40 ================================================= I am going to delete the control file as a grid user [grid@unknown ~]$ asmcmd -p ASMCMD [+] > lsdg State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name MOUNTED EXTERN N 512 4096 1048576 10236 8704 0 8704 0 N DATA/ ASMCMD [+] > cd DATA/ ASMCMD [+DATA] > ls -s Block_Size Blocks Bytes Space Name ASM/ VIHAAN/ ASMCMD [+DATA] > cd VIHAAN/ ASMCMD [+DATA/VIHAAN] > ls -s Block_Size Blocks Bytes Space Name CONTROLFILE/ DATAFILE/ ONLINELOG/ TEMPFILE/ archivelog/ redo01.log => +DATA/VIHAAN/ONLINELOG/group_1.261.844089885 redo02.log => +DATA/VIHAAN/ONLINELOG/group_2.284.844089885 redo03.log => +DATA/VIHAAN/ONLINELOG/group_3.256.844089885 sysaux01.dbf => +DATA/VIHAAN/DATAFILE/SYSAUX.266.844089693 system01.dbf => +DATA/VIHAAN/DATAFILE/SYSTEM.265.844089693 temp01.dbf => +DATA/VIHAAN/TEMPFILE/TEMP.260.844089891 undotbs01.dbf => +DATA/VIHAAN/DATAFILE/UNDOTBS1.257.844089693 users01.dbf => +DATA/VIHAAN/DATAFILE/USERS.258.844089693 ASMCMD [+DATA/VIHAAN] > cd CONTROLFILE/ ASMCMD [+DATA/VIHAAN/CONTROLFILE] > ls -s Block_Size Blocks Bytes Space Name 16384 627 10272768 16777216 current.262.844101027 16384 627 10272768 16777216 current.263.844101029 You can not delete the control file while database is running ... ASMCMD [+DATA/VIHAAN/CONTROLFILE] > rm current.262.844101027 current.263.844101029 ORA-15032: not all alterations performed ORA-15028: ASM file '+DATA/VIHAAN/CONTROLFILE/current.262.844101027' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute) ORA-15032: not all alterations performed ORA-15028: ASM file '+DATA/VIHAAN/CONTROLFILE/current.263.844101029' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute) ASMCMD [+DATA/VIHAAN/CONTROLFILE] > rm -rf current.262.844101027 current.263.844101029 ORA-15032: not all alterations performed ORA-15028: ASM file '+DATA/VIHAAN/CONTROLFILE/current.262.844101027' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute) ORA-15032: not all alterations performed ORA-15028: ASM file '+DATA/VIHAAN/CONTROLFILE/current.263.844101029' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute) ASMCMD [+DATA/VIHAAN/CONTROLFILE] > rm -f current.262.844101027 ORA-15032: not all alterations performed ORA-15028: ASM file '+DATA/VIHAAN/CONTROLFILE/current.262.844101027' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute) so i have to shutdown the database .. SQL> shutdown immediate ; Database closed. Database dismounted. ORACLE instance shut down. deleted the control file .. ASMCMD [+DATA/VIHAAN/CONTROLFILE] > rm -rf current.262.844101027 current.263.844101029 ASMCMD [+DATA/VIHAAN/CONTROLFILE] > ls -s ASMCMD-08002: entry 'CONTROLFILE' does not exist in directory '+DATA/VIHAAN/' SQL> startup ; ORACLE instance started. Total System Global Area 640286720 bytes Fixed Size 1338420 bytes Variable Size 448791500 bytes Database Buffers 184549376 bytes Redo Buffers 5607424 bytes ORA-00205: error in identifying control file, check alert log for more info in alter log file error SUCCESS: diskgroup DATA was mounted ORA-00210: cannot open the specified control file ORA-00202: control file: '+DATA/vihaan/controlfile/current.263.844101029' ORA-17503: ksfdopn:2 Failed to open file +DATA/vihaan/controlfile/current.263.844101029 ORA-15012: ASM file '+DATA/vihaan/controlfile/current.263.844101029' does not exist ORA-00210: cannot open the specified control file ORA-00202: control file: '+DATA/vihaan/controlfile/current.262.844101027' ORA-17503: ksfdopn:2 Failed to open file +DATA/vihaan/controlfile/current.262.844101027 ORA-15012: ASM file '+DATA/vihaan/controlfile/current.262.844101027' does not exist ORA-205 signalled during: ALTER DATABASE MOUNT... Mon Apr 07 10:37:40 2014 Checker run found 2 new persistent data failures Start Recovery SQL> shutdown immediate ; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup nomount ; ORACLE instance started. Total System Global Area 640286720 bytes Fixed Size 1338420 bytes Variable Size 448791500 bytes Database Buffers 184549376 bytes Redo Buffers 5607424 bytes SQL> select name from v$controlfile; no rows selected Now Create Control file CREATE CONTROLFILE REUSE DATABASE "VIHAAN" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '+DATA/vihaan/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '+DATA/vihaan/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '+DATA/vihaan/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '+DATA/vihaan/system01.dbf', '+DATA/vihaan/sysaux01.dbf', '+DATA/vihaan/undotbs01.dbf', '+DATA/vihaan/users01.dbf' CHARACTER SET WE8MSWIN1252 ; SQL> select name from v$controlfile; no rows selected SQL> CREATE CONTROLFILE REUSE DATABASE "VIHAAN" RESETLOGS ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 '+DATA/vihaan/redo01.log' SIZE 50M BLOCKSIZE 512, 9 GROUP 2 '+DATA/vihaan/redo02.log' SIZE 50M BLOCKSIZE 512, 10 GROUP 3 '+DATA/vihaan/redo03.log' SIZE 50M BLOCKSIZE 512 11 -- STANDBY LOGFILE 12 DATAFILE 13 '+DATA/vihaan/system01.dbf', 14 '+DATA/vihaan/sysaux01.dbf', 15 '+DATA/vihaan/undotbs01.dbf', 16 '+DATA/vihaan/users01.dbf' 17 CHARACTER SET WE8MSWIN1252 18 ; Control file created. SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- +DATA/vihaan/controlfile/current.263.844252891 +DATA/vihaan/controlfile/current.262.844252891 SQL> alter database open resetlogs; Database altered. SQL> alter session set nls_date_format ='dd-mm-yyyy hh24:mi:ss' ; Session altered. SQL> select * from anuj ; S_DATE ------------------- 07-04-2014 10:24:36 set linesize 200 SQL> r 1 select a.group#,a.member,b.status from v$logfile a, v$log b 2* where a.group#=b.group# GROUP# MEMBER STATUS ---------- -------------------------------------------------- ------------------------------------------------ 3 +DATA/vihaan/redo03.log UNUSED 2 +DATA/vihaan/redo02.log UNUSED 1 +DATA/vihaan/redo01.log CURRENT from grid user check control files [grid@unknown ~]$ asmcmd -p ASMCMD [+] > cd data ASMCMD [+data] > ls -lt Type Redund Striped Time Sys Name Y ASM/ Y VIHAAN/ ASMCMD [+data] > cd VIHAAN/ ASMCMD [+data/VIHAAN] > ls -lt Type Redund Striped Time Sys Name Y CONTROLFILE/ Y DATAFILE/ Y ONLINELOG/ Y TEMPFILE/ N archivelog/ N redo01.log => +DATA/VIHAAN/ONLINELOG/group_1.261.844089885 N redo02.log => +DATA/VIHAAN/ONLINELOG/group_2.284.844089885 N redo03.log => +DATA/VIHAAN/ONLINELOG/group_3.256.844089885 N sysaux01.dbf => +DATA/VIHAAN/DATAFILE/SYSAUX.266.844089693 N system01.dbf => +DATA/VIHAAN/DATAFILE/SYSTEM.265.844089693 N temp01.dbf => +DATA/VIHAAN/TEMPFILE/TEMP.260.844089891 N undotbs01.dbf => +DATA/VIHAAN/DATAFILE/UNDOTBS1.257.844089693 N users01.dbf => +DATA/VIHAAN/DATAFILE/USERS.258.844089693 ASMCMD [+data/VIHAAN] > cd CONTROLFILE/ ASMCMD [+data/VIHAAN/CONTROLFILE] > ls -s Block_Size Blocks Bytes Space Name 16384 615 10076160 16777216 Current.262.844252891 16384 615 10076160 16777216 Current.263.844252891 SQL> create pfile from spfile ; File created. My init file cat initvihaan.ora vihaan.__db_cache_size=184549376 vihaan.__java_pool_size=4194304 vihaan.__large_pool_size=4194304 vihaan.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment vihaan.__pga_aggregate_target=276824064 vihaan.__sga_target=364904448 vihaan.__shared_io_pool_size=0 vihaan.__shared_pool_size=163577856 vihaan.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/vihaan/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='+DATA/vihaan/controlfile/current.263.844252891','+DATA/vihaan/controlfile/current.262.844252891'#Oracle managed file *.db_block_size=8192 *.db_domain='' *.db_name='vihaan' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=vihaanXDB)' *.log_archive_dest_1='location=+data/vihaan/archivelog' *.memory_target=641728512 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1'
1 comment:
http://anuj-singh.blogspot.co.uk/2013/07/oracle-control-file-recovery.html
Post a Comment