ORA-16086 standby database does not contain available standby logfiles

Cause: The primary database is in "no data loss" mode, but the standby database does not contain any "standby logfiles".

Action: Add one or more standby logfiles to the standby database. This can be done while the standby database is mounted.
Increase the recovery area size of standby database. You can check the location of archived redo log file by,
SQL> connect / as sysdba
SQL> archive log list

If you see that archive destination is USE_DB_RECOVERY_FILE_DEST then the location will be specified by parameter DB_RECOVERY_FILE_DEST.
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST
+RECOVERY

Note that you have sufficient storage under +RECOVERY and also you have set proper size of the parameter db_recovery_file_dest_size.

To check the size of parameter db_recovery_file_dest_size issue,
SQL> show parameter db_recovery_file_dest_size

In order to increase the size of db_recovery_file_dest_size parameter issue,
SQL> alter system set db_recovery_file_dest_size = 1000G scope=both sid='*';

Read More

ORA-19555 invalid LOG_ARCHIVE_MIN_SUCCEED_DEST parameter value

Cause: The value of parameter LOG_ARCHIVE_MIN_SUCCEED_DEST was not set within the valid range.

Action:Specify a correct value for parameter LOG_ARCHIVE_MIN_SUCCEED_DEST.If the archive log parameters LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST are in use, set parameter LOG_ARCHIVE_MIN_SUCCEED_DEST to either 1 or 2.

Read More

Recovering a Database in NOARCHIVELOG Mode

In NOARCHIVELOG mode the recovery is very simple. All you have to do is to restore the database files from your backup to the original location  and start the database. If one of your data file got corrupted. The time try to start the Oracle Instance, it will give you an error. In this case shutdown the database. Restore all the data files, redo log files and control file back to their original location from your last whole/complete cold backup.Then start the Oracle database again and then connecting as SYSDBA.There are some steps as following:

1) If the database is open then shutdown the database
Shutdown immediate

2) If possible, correct the media problem so that the backup database files can be
restored to their original locations.
3) Restore the most recent whole database backup. Restore all of the
datafiles and control files of the whole database backup, not just the damaged
files. The following example restores a whole database backup:
% cp /opt/luck1/BACKUP/tbs* /opt/luck # restores datafiles
% cp /opt/luck1/BACKUP/cf.f /opt/luck # restores control file
 

4) Because online redo logs are not backed up, you cannot restore them with the
datafiles and control files. In order to allow Oracle to reset the online redo logs,
you must have to do incomplete recovery:
RECOVER DATABASE UNTIL CANCEL
CANCEL
 

5) Open the database in RESETLOGS mode.
ALTER DATABASE OPEN RESETLOGS;

Read More

Dropping Redo Log Group or Member

Dropping  Redo Log  Group:

To increase or decrease the size of online redo log groups you add or drop online redo log groups. To drop a redo log you must have the alter database system privilege.Use the alter database drop logfile group you can drop the redo log group.

Alter database drop logfile group 3;

But before drop a redo log group follow the following restrictions:

• An insatnce requires at least two groups.
• You cannot be dropped active or current group means a group can drop only if it is inactive.
• Make sure a redo log group is archived.Whan an online redo log group is dropped the system files are not deleted.

 Dropping Redo Log Member:  

If you want to drop any invalid online redo log member you can drop member by using alter database drop logfile member command:

Alter Database drop logfile member'/opt/luck/redo03.log';

When a redo log member is dropped from the database, the operating system file is not deleted from disk. The control files of the database are updated to drop the member from the database structure. But brfore drop a redo log member you have to follow the following restrictions:

• If you want to drop a redo log member and that member is the last valid member of the group, you cannot drop that member.
• If the group is current, you must force a log file switch before you can drop the member.
• If the database is running in ARCHIVELOG mode and the log file group to which the member belongs is not archived, then the member cannot be dropped.
• When an online redo log member is dropped, the operating system file is not deleted.

Read More

Status Of Redo Log File

To see the status of redo log file you have the following two dynamic views:

1) V$LOG

2) V$LOGFILE

SQL>desc v$log

This command will provide you information about GROUP#, THREAD#, SEQURNE#, BYTES, MEMBERS, STATUS, FIRST_TIME. You can see the status of redo log file by using STATUS column:

SQL>select status from v$log;

 STATUS

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

UNUSED- Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.

CURRENT- Current redo log. This implies that the redo log is active. The redo log could be open or closed.

ACTIVE- Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.

CLEARING- Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.

CLEARING CURRENT-  Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.

INACTIVE- Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.

SQL>desc v$logfile

V$LOGFILE will provide you information about GROUP#, STATUS, TYPE, MEMBER. You can see status of redo log file by using STATUS column:

SQL> select status from v$logfile;

STATUS

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

INVALID- File is inaccessible 

STALE- This redo log file member is new and has never been used.

DELETED- File is no longer used

Null- The redo log file is in use

Read More

Multiplexing Redo log File

Multiplex redo log by adding group:

Redo log files are used to record changes made to the database. Redo log files are important for the recovery purpose.So thats why in some cases you might need to create additional log file groups. To create a new group of online redo log files use the following SQL command:
Alter database add logfile group 3 ('/opt/luck/redo03_00.log',
                                                  '/opt/luck/redo03_01.log') size 10m;

Multiplexing redo log by adding member:

You can add new members to existing redo log file groups using the following ALTER DATABASE ADD LOGFILE MEMBER command:

Alter database add member '/opt/luck/redo02.log' to group 1,

                                         '/opt/luck/redo02.log' to group 2,

                                         '/opt/luck/redo03.log' to group 3;

Use the fully specified name of the log file members; otherwise the files are created in a default directory of the database server. If the file already exists, it must have the same size, and you must specify the REUSE option.

Read More

How Redo Logs Work

The online redo log of a database consists of two or more online redo log files. Oracle needs a minimum of two files to guarantee that one is always available for writing while the other is being archived.The Oracle server sequentially records all changes made to the database in the redo log buffer. The redo entries are written from the redo log buffer to one of the online redo log groups called the current online redo log group by the LGWR process. LGWR writes under the following situations:

• When a transaction commits
• When the redo log buffer becomes one-third full
• When there is more than a megabyte of changed records in the redo log buffer
• Before the DBWn writes modified blocks in the database buffer cache to the data files

LGWR writes to online redo log files in a circular fashion.Each redo log file group is identified by a log sequence number that is overwritten when log is reused.LGWR writes on the online redo log files continueslly. When the current online redo log grooup is filled log writer starts writing to the next group. When the last online redo log file is filled, LGWR returns to the first online redo log group and starts writing again.

Read More

Online Redo Log File

Each transaction is recorded in the redo logs.Redo logs stroe all changes made to the database. In the oracle database every instance has online redo log to protect the database in any case of an instance failure.Every oracle database instance has its own online redo log group. 

Online redo log files are filled with redo records are also called redo entry. If you change any value in a table then you generate a redo record that describe changes to the data segment block for the table and the transaction table of the rollback segments.Redo entries record all data that you can use to reconstruct all changes made to the database.Online redo log also protects rollback data, when you recover the database using redo data.

 In the above fig you can see a set of identical copies of online redo log files. DBA can set up the oracle database to maintain copies of online redo log files to avoid losing database information. The Log Writer LGWR background process writes the same information to all online redo log files in a group.So, Oracle server needs minimum two online rredo log file groups for the normal operation of database.In each group each online redo log file is known as member and each member in a group has Log Sequence Number.The current log sequence number is stored in the control file and in the header of all data files.
Using the alert log,v$logfile, v$loghist, v$recovery_log, you can monitor redo logs.

Read More