Start database after force shutdown (server power off)

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 17 14:01:59 2013

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2232840 bytes

Variable Size 826281464 bytes

Database Buffers 234881024 bytes

Redo Buffers 5541888 bytes

Database mounted.

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],

[12091], [76121], [76123], [], [], [], [], [], [], []

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> exit

Check control file location

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 17 14:05:04 2013

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2232840 bytes

Variable Size 834670072 bytes

Database Buffers 226492416 bytes

Redo Buffers 5541888 bytes

Database mounted.

SQL> show parameter control;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string /u02/app/oracle/oradata/tetris

11/control01.ctl, /u02/app/ora

cle/fast_recovery_area/tetris1

1/control02.ctl

control_management_pack_access string DIAGNOSTIC+TUNING

SQL> select a.member,a.group#,b.status from v$logfile a ,v$log b where a.group#=b.group# and b.status='CURRENT';

MEMBER

--------------------------------------------------------------------------------

GROUP# STATUS

---------- ----------------

/u02/app/oracle/oradata/tetris11/redo01.log

1 CURRENT

/u02/app/oracle/oradata/redo/redo01_2.log

1 CURRENT

SQL> exit

Check backups

$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri May 17 14:06:51 2013

connected to target database: TETRIS11 (DBID=3526243642, not open)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups

===============

Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

------- -- -- - ----------- --------------- ------- ------- ---------- ---

1578 B 0 A DISK 10-DEC-12 1 1 YES BACKUP_TETRIS11_00_121012020001

1581 B F A DISK 10-DEC-12 1 1 NO TAG20121210T020301

1582 B 0 A DISK 11-DEC-12 1 1 YES BACKUP_TETRIS11_FU_121112013009

1583 B F A DISK 11-DEC-12 1 1 NO TAG20121211T013246

1584 B 0 A DISK 11-DEC-12 1 1 YES BACKUP_TETRIS11_00_121112020001

1585 B F A DISK 11-DEC-12 1 1 NO TAG20121211T020309

1586 B F A DISK 11-DEC-12 1 1 YES BACKUP_TETRIS11_00_121112020001

1587 B F A DISK 11-DEC-12 1 1 NO TAG20121211T020315

RMAN> exit

Create controlfile to trace

bash-3.2$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 17 14:34:02 2013

SQL> conn / as sysdba

Connected.

SQL> show parameter control;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string /u02/app/oracle/oradata/tetris

11/control01.ctl, /u02/app/ora

cle/fast_recovery_area/tetris1

1/control02.ctl

control_management_pack_access string DIAGNOSTIC+TUNING

SQL>

SQL> oradebug setmypid;

Statement processed.

SQL> Alter session set tracefile_identifier='controlfilerecreate' ;

Session altered.

SQL> Alter database backup controlfile to trace ;

Database altered.

SQL> Oradebug tracefile_name ;

/u02/app/oracle/diag/rdbms/tetris11/tetris11/trace/tetris11_ora_1158_controlfilerecreate.trc

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> exit

bash-3.2$ more tetris11_ora_1158_controlfilerecreate.trc

Trace file /u02/app/oracle/diag/rdbms/tetris11/tetris11/trace/tetris11_ora_1158_controlfilerecreate.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /u02/app/oracle/product/11.2.0/dbhome_1

System name: SunOS

Node name: b11scp2

Release: 5.10

Version: Generic_147441-01

Machine: i86pc

Instance name: tetris11

Redo thread mounted by this instance: 1

Oracle process number: 19

Unix process pid: 1158, image: oracle@b11scp2 (TNS V1-V3)

*** 2013-05-17 14:41:13.213

*** SESSION ID:(387.53) 2013-05-17 14:41:13.213

*** CLIENT ID:() 2013-05-17 14:41:13.213

*** SERVICE NAME:() 2013-05-17 14:41:13.213

*** MODULE NAME:(sqlplus@b11scp2 (TNS V1-V3)) 2013-05-17 14:41:13.213

*** ACTION NAME:() 2013-05-17 14:41:13.213

*** TRACE CONTINUED FROM FILE /u02/app/oracle/diag/rdbms/tetris11/tetris11/trace/tetris11_ora_1158.trc ***

*** 2013-05-17 14:41:13.212

-- 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="tetris11"

-- 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=USE_DB_RECOVERY_FILE_DEST'

-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'

-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'

-- LOG_ARCHIVE_DEST_1='NOREGISTER 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 "TETRIS11" NORESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 9344

LOGFILE

GROUP 1 (

'/u02/app/oracle/oradata/tetris11/redo01.log',

'/u02/app/oracle/oradata/redo/redo01_2.log'

) SIZE 50M BLOCKSIZE 512,

GROUP 2 (

'/u02/app/oracle/oradata/tetris11/redo02.log',

'/u02/app/oracle/oradata/redo/redo02_2.log'

) SIZE 50M BLOCKSIZE 512,

GROUP 3 (

'/u02/app/oracle/oradata/tetris11/redo03.log',

'/u02/app/oracle/oradata/redo/redo03_2.log'

) SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u02/app/oracle/oradata/tetris11/system01.dbf',

'/u02/app/oracle/oradata/tetris11/sysaux01.dbf',

'/u02/app/oracle/oradata/tetris11/undotbs01.dbf',

'/u02/app/oracle/oradata/tetris11/users01.dbf',

'/u02/app/oracle/oradata/tetris11/tab01',

'/u02/app/oracle/oradata/tetris11/idx01',

'/u02/app/oracle/oradata/tetris11/TIGOTBS_01.DBF',

'/u02/app/oracle/oradata/tetris11/TIGOIDX_01.DBF',

'/u02/app/oracle/oradata/tetris11/BNA01.DBF',

'/u02/app/oracle/oradata/tetris11/TMPTBS.DBF'

CHARACTER SET WE8ISO8859P1

;

-- Configure RMAN configuration record 1

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

-- Configure RMAN configuration record 2

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');

-- Configure RMAN configuration record 3

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO REDUNDANCY 2');

-- Configure RMAN configuration record 4

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1');

-- 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 '/u02/app/oracle/fast_recovery_area/TETRIS11/archivelog/2013_05_17/o1_mf_1_1_%u_.arc';

-- 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;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/app/oracle/oradata/tetris11/temp01.dbf' REUSE;

-- 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 "TETRIS11" RESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 9344

LOGFILE

GROUP 1 (

'/u02/app/oracle/oradata/tetris11/redo01.log',

'/u02/app/oracle/oradata/redo/redo01_2.log'

) SIZE 50M BLOCKSIZE 512,

GROUP 2 (

'/u02/app/oracle/oradata/tetris11/redo02.log',

'/u02/app/oracle/oradata/redo/redo02_2.log'

) SIZE 50M BLOCKSIZE 512,

GROUP 3 (

'/u02/app/oracle/oradata/tetris11/redo03.log',

'/u02/app/oracle/oradata/redo/redo03_2.log'

) SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u02/app/oracle/oradata/tetris11/system01.dbf',

'/u02/app/oracle/oradata/tetris11/sysaux01.dbf',

'/u02/app/oracle/oradata/tetris11/undotbs01.dbf',

'/u02/app/oracle/oradata/tetris11/users01.dbf',

'/u02/app/oracle/oradata/tetris11/tab01',

'/u02/app/oracle/oradata/tetris11/idx01',

'/u02/app/oracle/oradata/tetris11/TIGOTBS_01.DBF',

'/u02/app/oracle/oradata/tetris11/TIGOIDX_01.DBF',

'/u02/app/oracle/oradata/tetris11/BNA01.DBF',

'/u02/app/oracle/oradata/tetris11/TMPTBS.DBF'

CHARACTER SET WE8ISO8859P1

;

-- Configure RMAN configuration record 1

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

-- Configure RMAN configuration record 2

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');

-- Configure RMAN configuration record 3

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO REDUNDANCY 2');

-- Configure RMAN configuration record 4

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1');

-- 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 '/u02/app/oracle/fast_recovery_area/TETRIS11/archivelog/2013_05_17/o1_mf_1_1_%u_.arc';

-- 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 '/u02/app/oracle/oradata/tetris11/temp01.dbf' REUSE;

-- End of tempfile additions.

*** 2013-05-17 14:41:21.702

Processing Oradebug command 'tracefile_name '

*** 2013-05-17 14:41:21.710

Oradebug command 'tracefile_name ' console output:

/u02/app/oracle/diag/rdbms/tetris11/tetris11/trace/tetris11_ora_1158_controlfilerecreate.trc

*** 2013-05-17 14:41:49.724

Stopping background process MMNL

*** 2013-05-17 14:41:50.724

Stopping background process MMON

*** 2013-05-17 14:41:51.746

ksukia: Starting kill, flags = 1

ksukia: killed 0 out of 0 processes.

*** 2013-05-17 14:41:51.762 4320 krsh.c

ARCH: Archival disabled due to shutdown: 1089

*** 2013-05-17 14:41:52.763

*** 2013-05-17 14:41:52.763 4320 krsh.c

ARCH: Archival disabled due to shutdown: 1089

bash-3.2$

Backup controlfiles

bash-3.2$ cd /u02/app/oracle/oradata/tetris11/

bash-3.2$ ls -l

total 50915808

-rw-r----- 1 ora11gr2 oinstall 104865792 May 17 14:02 BNA01.DBF

-rw-r----- 1 ora11gr2 oinstall 104865792 May 17 14:02 TIGOIDX_01.DBF

-rw-r----- 1 ora11gr2 oinstall 2147491840 May 17 14:02 TIGOTBS_01.DBF

-rw-r----- 1 ora11gr2 oinstall 314580992 May 17 14:02 TMPTBS.DBF

-rw-r----- 1 ora11gr2 oinstall 10764288 May 17 14:41 control01.ctl

-rw-r----- 1 ora11gr2 oinstall 555753472 May 17 14:02 idx01

-rw-r----- 1 ora11gr2 oinstall 52429312 May 17 14:02 redo01.log

-rw-r----- 1 ora11gr2 oinstall 52429312 May 17 03:32 redo02.log

-rw-r----- 1 ora11gr2 oinstall 52429312 May 17 08:57 redo03.log

-rw-r----- 1 ora11gr2 oinstall 1258299392 May 17 14:02 sysaux01.dbf

-rw-r----- 1 ora11gr2 oinstall 1384128512 May 17 14:02 system01.dbf

-rw-r----- 1 ora11gr2 oinstall 7574921216 May 17 14:02 tab01

-rw-r----- 1 ora11gr2 oinstall 406855680 May 17 11:34 temp01.dbf

-rw-r----- 1 ora11gr2 oinstall 10721697792 May 17 14:02 undotbs01.dbf

-rw-r----- 1 ora11gr2 oinstall 1315971072 May 17 14:02 users01.dbf

bash-3.2$ cp control01.ctl control01.ctl_old

bash-3.2$ ls -l

total 50936848

-rw-r----- 1 ora11gr2 oinstall 104865792 May 17 14:02 BNA01.DBF

-rw-r----- 1 ora11gr2 oinstall 104865792 May 17 14:02 TIGOIDX_01.DBF

-rw-r----- 1 ora11gr2 oinstall 2147491840 May 17 14:02 TIGOTBS_01.DBF

-rw-r----- 1 ora11gr2 oinstall 314580992 May 17 14:02 TMPTBS.DBF

-rw-r----- 1 ora11gr2 oinstall 10764288 May 17 14:41 control01.ctl

-rw-r----- 1 ora11gr2 oinstall 10764288 May 17 14:51 control01.ctl_old