20 ASM Realtime | Interview Questions | 3+ years

Download link : https://www.scribd.com/document/461028011/Asm-Interview-Scenarios-Oracle-dba-Help-Blog

Happy Learning ..!

How to check ORA Error on DB Server – Beginner Tip

ORA errors are always displayed with error code only.  Because it is difficult for Oracle to put every errors description in Alert log .

Now Assume , you are stucked in a situation..  you got an ORA error, but you are not aware of what to do now.

Aaaa- ha… !

Here I will tell you a quick tip –

Always use this utility before heading to Google and search errors

To check ORA errors description on database server itself, go to shell prompt and type as below.

General format :

$ oerr ora  < error_code >

Example  :

$ oerr ora 01555

Output :  

ORA-01555: snapshot too old: rollback segment number string with name "string" too small

Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.

Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments

Also read  :  Working with Redo Logs

ORA-01194: file 1 needs more recovery to be consistent

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/dump01/files/data/system01'

If you try to give “alter database open resetlogs” command after cloning the database  using cold backup you probably see this error.

To troubleshoot this, follow these steps

SQL>  recover database;
ORA-00283: recovery session cancelled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
 

So, you must recover using the backed up controlfile . Why ? Because the controlfile has information of archives that need to be applied during recovery.

Lets do this

SQL>  recover database until cancel using BACKUP CONTROLFILE;
ORA-00283: recovery session cancelled due to errors

Viola…!!

Now shutdown the database

SQL>shut immediate

Go to $ORACLE_HOME/dbs and locate the pfile of respective database.

And  add below parameter in the pfile

 _allow_resetlogs_corruption=true

SQL> startup mount pfile= ‘$ORACLE_HOME/dbs/initDBNAME.ora’;

ORACLE instance started.
Total System Global Area 1069252608 bytes
Fixed Size                  2188408 bytes
Variable Size             436214664 bytes
Database Buffers          616562688 bytes
Redo Buffers               14286848 bytes
Database mounted.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST      MOUNTED

SQL> alter database open resetlogs;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST      READ WRITE

Done . . !!