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 . . !!

Working with Redo Logs

Script to View Online RedoLog Information :

SQL> SELECT * FROM V$LOGFILE;

OR

SQL > SELECT a.group#

,a.thread# ,a.status grp_status

,b.member member ,b.status mem_status

,a.bytes/1024/1024 mbytes FROM v$log a, v$logfile b

WHERE a.group# = b.group#

ORDER BY a.group#, b.member;

Status for Online Redo Log of V$LOG View  :

CURRENT The log group is currently being written to by the log writer.

ACTIVE The log group is required for crash recovery and may or may not have been archived.

CLEARING The log group is being cleared out by an ALTER DATABASE CLEAR LOGFILE command.

CLEARING_CURRENT The current log group is being cleared of a closed thread.

INACTIVE The log group isn’t required for crash recovery and may or may not have been archived.

UNUSED The log group has never been written to; it was recently created.

Status of Online Redo Log File Members in V$LOGFILE View

INVALID The log file member is inaccessible or has been recently created.

DELETED The log file member is no longer in use.

STALE The log file member’s contents aren’t complete.

NULL The log file member is being used by the database.

How to Rename the Redolog files:

1 . Shut down the DB

2. Rename the redolog files on OS level (using ‘mv’ command in UNIX)

3. Startup mount

SQL> alter database rename file ‘<loc with prev file name> to <loc with current file name>;

Do the same for all renamed redolog files

SQL> alter database open;

How to Multiplex the redolog files:

To protect against a failure involving the redo log itself, Oracle Database allows a multiplexed redo log, meaning that two or more identical copies of the redo log can be automatically maintained in separate locations. For the most benefit, these locations should be on separate disks. Even if all copies of the redo log are on the same disk, however, the redundancy can help protect against I/O errors, file corruption, and so on. When redo log files are multiplexed, LGWR concurrently writes the same redo log information to multiple identical redo log files, thereby eliminating a single point of redo log failure.

Multiplexing is implemented by creating groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of the group. Each redo log group is defined by a number, such as group 1, group 2, and so on.

FOR EXAMPLE:

In group 1 all memebers will have same type of data, if one member is corrupted then another member is user for recovery

SQL> SELECT MEMBER FROM V$LOGFILE WHERE GROUP# = ‘1’;

SQL>ALTER DATABASE ADD LOGFILE MEMBER ‘<>loc with redolog file name> TO GROUP <group_num>;

Decommission of Oracle Database

Decommission : A process to remove, Retire or making it inactive

Every organization has their own documents (SOP – Standard Operation Procedure) maintain for  removing or decommissioning the active  databases

I am presenting some general steps below.

1.      Requirement : Business/Customer to proceed for database decommissioning.

2.    Notifying Business/Customer / Application team that  “we are going to decommission the database at _ _ specific time” .

3.      Making the fresh backup :  Raise a ticket and assign it to Storage / Backup team and Co-ordinate with storage / backup team to take required backup. And to keep the backup for specified time(retention period).

4.      Make a list of parameter file, datafiles, controlfiles, redolog files.

-          Details of DB links using query: select * from dba_db_links;

-          List of all the data files using query: select * from dba_data_files;

-          List of log files using query: select * from v$logfile;

-          List of control files using query: select * from v$controlfile;

5.      Bring down the database and stop the listener.

6.      Take whole database backup and make sure backup is completed without any errors/warnings.

7.    Make sure Storage team has taken the backup as specified in the raised ticket against them and has set the retention period correctly. You can take a Signoff mail from Storage/Backup team in case it is required.

8.   Remove the monitoring jobs entry from crontab and also remove the monitoring jobs (if any) running from third party tool(s) in the database and comment out the entry from oratab file located in /etc directory.

9.   Mark in your database repository/inventory or your DB records , that this specific Database is going to Decom under this specific request at this specific time. (This will help to notify other DBA in your team).

10.   Startup the database in restrict mode and give drop database command.

                        SQL> startup restrict mount ;

                        SQL> drop database;

11.  Check if the instance is still running or not. Shutdown the instance if it is still running. Check and verify Alertlog for the list of files deleted.

12. Now remove the tracefiles, archive log files, dump files, old backup files and respective Database directories.

13.  Be careful before deleting the physical files. Crosscheck and verify before deleting these file.

14.  Now remove the backup “entry” through which backup was getting triggered.

15.  Notify the customer/Application team about the backup ticket , backup information such as retention period and location of backup.

Note : Retention Period may be varied based on your business needs and SLA

See Also :

- Dataguard Creation

- Pre-Check for Dataguard SwitchOver  

- Common Wait events in AWR and Solutions 

- Most Common Daily Datapump Scenarios