Restoring After Losing One Member of the Multiplexed redo log Group - in a OMF and NON-OMF system

Restoring After Losing One Member of the Multiplexed Group

If you are seeing an ORA-00312 in your alert log it should be because of one of the member of the redo log group is experiencing media failure. You should see lines similar to these near the bottom of your alert.log file:

Mon Aug 29 18:28:02 2016

Errors in file /u01/app/oracle/diag/rdbms/ttrec/TTREC1/trace/TTREC1_arc1_1752.trc:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/data2/TTREC/onlinelog/o1_mf_2_cvf5qv5t_.log'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

If your online redo log file members are multiplexed, the log writer(lgwr) will continue to function as long as it can successfully write to one member of the current log group. If the problem is temporary, then as soon as the online redo log file becomes available, the log writer will start to write to the online redo log file as if there was never an issue.

If the media failure is permanent then you’ll need to replace the disk and drop and re-create the bad member to its original location. If you don’t have the option of

replacing the bad disk, then you’ll need to drop the bad member and re-create it in an alternate location.

For permanent media failures, here are the instructions for dropping and re-creating one member of an online redo log group:

1. Identify the online redo log file experiencing media failure.

2. Ensure that the online redo log file is not part of the current online log group.

3. Drop the damaged member.

4. Add a new member to the group.

Here I am going to delete one of the multiplexed redo log file and later I will recreate it in the same location.

SQL>select member, a.group#, a.status, b.status from v$log a, v$logfile b where  a.group# = b.group#  order by a.group#, member;

MEMBER                                                                     GROUP# STATUS           STATUS

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

/data1/TTREC/onlinelog/o1_mf_1_cw7vjldp_.log                                    1 ACTIVE

/data2/TTREC/onlinelog/o1_mf_1_cw7vjljy_.log                                    1 ACTIVE

/data1/TTREC/onlinelog/o1_mf_2_cvf5qv2z_.log                                    2 CURRENT

/data2/TTREC/onlinelog/o1_mf_2_cvf5qv5t_.log                                    2 CURRENT

/data1/TTREC/onlinelog/o1_mf_3_cw7ogdtj_.log                                    3 ACTIVE

/data2/TTREC/onlinelog/o1_mf_3_cw7ogdyv_.log                                    3 ACTIVE

6 rows selected.

SQL>

I deleted one of the current redo log member.

[oracle@ehrsvpitoel02]:[TTREC1] $ rm /data2/TTREC/onlinelog/o1_mf_2_cvf5qv5t_.log

I did a log switch

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> exit

I could see following error message in my alert log file.

Mon Aug 29 18:28:02 2016

Errors in file /u01/app/oracle/diag/rdbms/ttrec/TTREC1/trace/TTREC1_arc1_1752.trc:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/data2/TTREC/onlinelog/o1_mf_2_cvf5qv5t_.log'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

I just checked the status of my redo log file -

[oracle@ehrsvpitoel02]:[TTREC1] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 29 18:28:17 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics

and Real Application Testing options

SQL> @redo

MEMBER                                                                     GROUP# STATUS           STATUS

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

/data1/TTREC/onlinelog/o1_mf_1_cw7vjldp_.log                                    1 CURRENT

/data2/TTREC/onlinelog/o1_mf_1_cw7vjljy_.log                                    1 CURRENT

/data1/TTREC/onlinelog/o1_mf_2_cvf5qv2z_.log                                    2 ACTIVE

/data2/TTREC/onlinelog/o1_mf_2_cvf5qv5t_.log                                    2 ACTIVE

/data1/TTREC/onlinelog/o1_mf_3_cw7ogdtj_.log                                    3 ACTIVE

/data2/TTREC/onlinelog/o1_mf_3_cw7ogdyv_.log                                    3 ACTIVE

6 rows selected.

Still the  views is  not giving any information regarding lost member(I should expect a INVALID status on v$logfile.status column. So I tried a database bounce as I want to see whether the column is reflecting properly after a bounce – this is not really required though.

SQL> shut immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size                  3712904 bytes

Variable Size            1996490872 bytes

Database Buffers          134217728 bytes

Redo Buffers               13062144 bytes

Database mounted.

Database opened.

SQL> @redo

MEMBER                                                                     GROUP# STATUS           STATUS

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

/data1/TTREC/onlinelog/o1_mf_1_cw7vjldp_.log                                    1 CURRENT

/data2/TTREC/onlinelog/o1_mf_1_cw7vjljy_.log                                    1 CURRENT

/data1/TTREC/onlinelog/o1_mf_2_cvf5qv2z_.log                                    2 INACTIVE

/data2/TTREC/onlinelog/o1_mf_2_cvf5qv5t_.log                                    2 INACTIVE         INVALID

/data1/TTREC/onlinelog/o1_mf_3_cw7ogdtj_.log                                    3 INACTIVE

/data2/TTREC/onlinelog/o1_mf_3_cw7ogdyv_.log                                    3 INACTIVE

6 rows selected.

SQL>

After restart, its clear that database is complaining about missed redo log file – I am not sure why this view is not populating at run time. Now I will drop and recreate the INVALID redo log file.

SQL> alter database drop logfile member '/data2/TTREC/onlinelog/o1_mf_2_cvf5qv5t_.log';

Database altered.

Note:- You can drop and add online redo log file members while your database is in either a mounted state or an open state. If you attempt to drop a member of a current log group, Oracle will throw an ORA-01609 error specifying that the log is current and you cannot drop one of its members. If the failed member is in the current log group, then use the alter system switch logfile command to make the next group the current group.

SQL> @redo

MEMBER                                                                     GROUP# STATUS           STATUS

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

/data1/TTREC/onlinelog/o1_mf_1_cw7vjldp_.log                                    1 CURRENT

/data2/TTREC/onlinelog/o1_mf_1_cw7vjljy_.log                                    1 CURRENT

/data1/TTREC/onlinelog/o1_mf_2_cvf5qv2z_.log                                    2 INACTIVE

/data1/TTREC/onlinelog/o1_mf_3_cw7ogdtj_.log                                    3 INACTIVE

/data2/TTREC/onlinelog/o1_mf_3_cw7ogdyv_.log                                    3 INACTIVE

SQL> alter database add logfile member  '/data2/TTREC/onlinelog/o1_mf_2_cvf5qv5t_.log' to group 2;

alter database add logfile member  '/data2/TTREC/onlinelog/o1_mf_2_cvf5qv5t_.log' to group 2

*

ERROR at line 1:

ORA-01276: Cannot add file /data2/TTREC/onlinelog/o1_mf_2_cvf5qv5t_.log.  File has an Oracle Managed Files file name.

SQL>

If your redo logs are configured through OMF- You have to keep in mind that it's not possible to add an additional log group member that is an OMF (that is, ALTER DATABASE ADD LOGFILE MEMBER TO GROUP 2).

SO if your redo logs are configured using OMF,  you have to drop the entire redo log group and then recreate it .

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

Database altered.

SQL>  ALTER DATABASE add LOGFILE GROUP 2;

Database altered.

SQL> @redo

MEMBER                                                                     GROUP# STATUS           STATUS

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

/data1/TTREC/onlinelog/o1_mf_1_cw7vjldp_.log                                    1 CURRENT

/data2/TTREC/onlinelog/o1_mf_1_cw7vjljy_.log                                    1 CURRENT

/data1/TTREC/onlinelog/o1_mf_2_cw7yyz4t_.log                                    2 UNUSED

/data2/TTREC/onlinelog/o1_mf_2_cw7yyz8w_.log                                    2 UNUSED

/data1/TTREC/onlinelog/o1_mf_3_cw7ogdtj_.log                                    3 INACTIVE

/data2/TTREC/onlinelog/o1_mf_3_cw7ogdyv_.log                                    3 INACTIVE

6 rows selected.

Once the newly created member becomes part of the CURRENT log group, its status should change to NULL. A NULL member status indicates that the database is using the online redo log file.

SQL> alter system switch logfile ;

System altered.

SQL>

SQL> /

System altered.

SQL>

SQL> @redo

MEMBER                                                                     GROUP# STATUS           STATUS

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

/data1/TTREC/onlinelog/o1_mf_1_cw7vjldp_.log                                    1 ACTIVE

/data2/TTREC/onlinelog/o1_mf_1_cw7vjljy_.log                                    1 ACTIVE

/data1/TTREC/onlinelog/o1_mf_2_cw7yyz4t_.log                                    2 ACTIVE

/data2/TTREC/onlinelog/o1_mf_2_cw7yyz8w_.log                                    2 ACTIVE

/data1/TTREC/onlinelog/o1_mf_3_cw7ogdtj_.log                                    3 CURRENT

/data2/TTREC/onlinelog/o1_mf_3_cw7ogdyv_.log                                    3 CURRENT

6 rows selected.

SQL>

Recovering After Losing One Member of the Multiplexed Group (in non-omf)

Search for ORA-00312 in your database alert log file that identifies which member of the log group is experiencing media failure.

Once you’ve identified the bad online redo log file, execute the following query to check whether that online redo log file’s group has a CURRENT status:

SQL> select  a.group#, a.thread#,  a.status grp_status, b.member member, b.status mem_status from v$log a, v$logfile b where a.group# = b.group# order by a.group#, b.member;

If the failed member is in the current log group, then use the alter system switch

logfile command to make the next group the current group. Then drop the failed member as follows:

SQL> alter database drop logfile member '<\directory\member>';

Then re-create the online redo log file member:

SQL> alter database add logfile member '<\new directory\member>' to group <group#>;

If an unused log file already happens to exist in the target location, you can use the reuse parameter to overwrite and reuse that log file. The log file must be the same size as the other log files in the group.

SQL> alter database add logfile member '\directory\member>' reuse to group <group#>;

How to invoke a shell script from a windows batch file

From  last few days I was  searching for a script that can invoke a shell script from windows batch file.After a prolonged search on google I made it. So thought of sharing here -

Create a text file with some name , for example Check_Service and save it as .bat. The content of my Check_Servic.bat is,

C:\Users\mahesh\Desktop\PT\putty.exe -ssh oracle@10.81.155.9 -pw Myoracle123 -m "C:\Users\mahesh\Desktop\PT\work3.txt" -t

And the content of  C:\Users\mahesh\Desktop\PT\work3.txt  is here ,

sh /home/oracle/mp/srvctl_status_db_DI.sh
/bin/bash 

And the content of my srvctl_status_db_DI.sh


for database in DIOES DIIAM DIHIM
do
export ORACLE_SID=${database}1
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
srvctl status database -d $database
echo ------------------------------------------------------
done

Its a simple script to check the status of your cluster database . 

Double click the batch file -

OCP 12c - RMAN sample question and answer.

1. How is block-change tracking enabled?

A. With ALTER DATABASE ENABLE BLOCK CHANGE TRACKING

B. With ALTER SYSTEM ENABLE BLOCK CHANGE TRACKING

C. With an init.ora parameter change

D. With an spfile parameter change

2. What type of backup is stored in a proprietary RMAN format?

A. Backup set

B. Image copy

C. Backup section

D. Backup group

3. Consider the following command:

Backup database plus archivelog delete input;

How many backup sets would be created by this command if the following were true:

■■ Control-file auto backups were enabled.

■■ The size of backup sets was not restricted.

■■ One channel was allocated.

A. 1

B. 2

C. 3

D. 4

E. 5

Note:- 

The following four backup sets would be created:

■■ One for an archive log backup before the main backup.

■■ One for the main backup. Since we are using a single channel with no backup-set

size restriction, RMAN would create a single backup set.

■■ One for an archive log backup after the main backup.

■■ One for the control-file autobackup.

4. Which command creates an image copy?

A. BACKUP AS COPY

B. BACKUP COPY

C. COPY AS BACKUP

D. COPY BACK

5. Compressed backups work with which of the following commands?

A. COPY AS BACKUP

B. BACKUP AS COPY

C. BACKUP

D. COPY

Note:- Compressed backups work only with backup sets, not image copies. Thus, compressed

backups will work only with the BACKUP command.

6. Which is the correct command to back up the database, back up the archived redologs, and then remove the backed-up archived redo logs?

A. BACKUP DATABASE

B. BACKUP DATABASE AND ARCHIVELOGS

C. BACKUP DATABASE PLUS ARCHIVELOGS

D. BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT

E. BACKUP DATABASE AND ARCHIVELOG DELETE INPUT

7. Which of the following best describes a full backup?

A. All data files of a database

B. All data files, archive logs, and control files

C. All data files and control files

D. All the used blocks in a data file

8. Which type of backup backs up only data blocks modified since the most recent

backup at the same level or lower?

A. Differential incremental backup

B. Different incremental backup

C. Cumulative backup

D. Cumulative incremental backup

9. Which type of backup must be performed first with an incremental backup?

A. Level 1

B. Level 0

C. Level 2

D. Level 3

10. Which backup option defines a user-defined name for a backup?

A. FORMAT

B. NAME

C. TAG

D. FORMAT U%

11. Given the following steps, which would be the correct order to create a backup of an Oracle database in NOARCHIVELOG mode?

a. SHUTDOWN IMMEDIATE from RMAN

b. Log into RMAN

c. STARTUP MOUNT from RMAN

d. BACKUP DATABASE

e. ALTER DATABASE OPEN

f. BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT

A. b, c ,a, d, e

B. b, a, c, f, e

C. a, c, e, d

D. b, a, c, e, f

E. b, a, c, d, e

12. Which of the following most closely represents an image copy?

A. Unix cp command of a file

B. Bit-by-bit copy of a file

C. Windows COPY command of a file

D. All of the above

Note:- Image copies are similar to operating-system copy commands. These equate to bit-by-bit copies of a file.

13. Which dynamic view displays the status of block-change tracking?

A. V$BLOCK_CHANGE

B. V$BLOCK_CHANGE_TRACKING

C. V$BLOCKCHANGE

D. V$BLOCK_TRACKING

14. What feature comes into play to help ensure the completion of the backup should one of three backup devices fail during a backup that is using three different channels?

A. Channel failover

B. Restartable backups

C. Reschedulable backups

D. Automatic backup recovery

E. Channel recovery

15. What command would you use to set a persistent setting in RMAN so that backups are all written to a tape device?

A. CONFIGURE DEFAULT DEVICE TYPE TO TAPE MEDIA

B. CONFIGURE DEFAULT DEVICE TYPE TO TAPE

C. CONFIGURE DEFAULT DEVICE TYPE TO SBT

D. CONFIGURE DEFAULT DEVICE TYPE TO SBT_TAPE

16. The CONTROL_FILE_RECORD_KEEP_TIME initialization parameter should be set to what value? (Choose all that apply.)

A. The initialization parameter should be set to 0 when the RMAN repository is being used.

B. The initialization parameter should be set to greater than 0 with the RMAN repository utilizing the recovery catalog only.

C. The initialization parameter should be set to greater than 0 with the RMAN repository utilizing the control file or the recovery catalog.

D. The initialization parameter should be set to 0 with the RMAN repository utilizing

the control file or the recovery catalog.

E. The initialization parameter should never be set to 0 if you are using RMAN.

17. Given the following steps, which would be the correct order to create a backup of an Oracle database in ARCHIVELOG mode with control-file autobackups enabled?

a. BACKUP ARCHIVELOG ALL

b. BACKUP DATABASE ALL

c. BACKUP CONTROLFILE

d. BACKUP ARCHIVELOG, DATABASE, CONTROLFILE DELETE INPUT

e. BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT

A. e

B. a, b, a, c

C. d

D. b, a, c

E. b, a, c, d, e

18. Which of the following statements are true about the BACKUP command? (Choose all that apply.)

A. The BACKUP command cannot be used to make image copies of a data file.

B. The BACKUP command can improve performance by multiplexing backup files.

C. The BACKUP command can take advantage of the block-change tracking capability.

D. The BACKUP command cannot store data in incremental backups.

E. The BACKUP command can store data in cumulative incremental backups only.

19. Which command is used to configure RMAN to perform a compressed backup for every backup executed?

A. BACKUP AS COMPRESSED BACKUP SET DATABASE

B. BACKUP AS COMPRESSED COPY OF DATABASE

C. CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUP SET

D. CONFIGURE DEVICE TYPE DISK BACKUP TYPE COMPRESS

E. BACKUP DATABASE COMPRESS

20. You issue the following command:

RMAN>configure backup optimization on;

What is the result of this command on your backups?

A. An incremental backup strategy will be used automatically.

B. Read-only data files will not be backed up as long as backups of those files already exist and those backups meet established retention criteria.

C. RMAN will configure itself for maximum performance at the cost of CPU.

D. RMAN will configure itself for minimal OS/CPU impact at the cost of time to

back up the database.

E. RMAN will automatically compress backups.