How to pin nodes in RAC

The following steps can be used to pin nodes in RAC.
Pinning a node means that the association of a node name with a node number is fixed. If a node is not pinned,node number may change if the lease expires while it is down. The lease of a pinned node never expires.
To find whether a node in a cluster is pinned,use the olsnodes command.
[grid@raclinux1 stage10g]$ olsnodes -t
raclinux1 Unpinned
raclinux2 Unpinned

Use the pin command to pin the nodes
[root@raclinux1 bin]# ./crsctl pin css -n raclinux1
CRS-4664: Node raclinux1 successfully pinned.
[root@raclinux1 bin]#

[root@raclinux2 bin]# ./crsctl pin css -n raclinux2
CRS-4664: Node raclinux2 successfully pinned.
[root@raclinux2 bin]#

[grid@raclinux1 stage10g]$ olsnodes -t
raclinux1 Pinned
raclinux2 Pinned

Loss of redolog group

Recovering after loss of members of the INACTIVE redo log group

You’re attempting to open your database and receive this message

ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo02.log’
ORA-00312: online log 2 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo02a.log’
The message indicates that two members of an online redo log group in your database have experienced a media failure.
To recover when you’ve lost all members of an inactive redo log group, perform the following steps

Verify that the log group status is INACTIVE.
 SQL> select group#,members,status from v$log;
GROUP#    MEMBERS STATUS
———- ———- —————-
1                      2             CURRENT
2                      2             INACTIVE
3                      2             ACTIVE

 Recreate the log group with the clear logfile command.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
(Here I manually deleted redolog files members of group 2)
SQL> startup
ORACLE instance started.
Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.

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

ORA-00312: online log 2 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo02.log’
ORA-00312: online log 2 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo02a.log’
If all members of an online redo log group are damaged, you won’t be able to open your database, oracle will allow you to only mount your database.
SQL> select status from v$instance;
STATUS
————
MOUNTED
SQL> select group#,status,archived,thread#,sequence# from v$log;
GROUP# STATUS           ARC    THREAD#  SEQUENCE#
———- —————- — ———- ———-
1           CURRENT          NO           1         11
3           INACTIVE         YES          1         10
2           INACTIVE         YES          1          9
If the status is INACTIVE  and ARCHIVED=YES,then this log group is no longer needed for crash recovery , therefore ,you can use the clear logfile command to recreate all members of a log group.
SQL> alter database clear logfile group 2;
Database altered.
 if STATUS = INACTIVE and ARCHIVED = NO

sql>	ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

 Recovering After loss of members of the CURRENT redo log group

All of the members of a current online redo log group in your database have experience media failure.
You lose all members of a current online redo log group. Here are possible reasons,

• Perform an incomplete recovery up to the last good SCN.
• If flashback is enabled, flash your database back to the last good SCN.
• If you’re using Oracle data guard, fail over to your physical or logical standby database.

SQL> select group#,status,archived,thread#,sequence# from v$log;
GROUP# STATUS           ARC    THREAD#  SEQUENCE#
———- —————- — ———- ———-
1               CURRENT          NO           1         17
2                INACTIVE         YES          1         15
3                INACTIVE         YES          1         16
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo01.log’
ORA-00312: online log 1 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo01a.log’

SQL> select group#,status,archived,thread#,sequence# from v$log;

GROUP# STATUS           ARC    THREAD#  SEQUENCE#
———- —————- — ———- ———-
1                CURRENT          NO           1         17
3                INACTIVE         YES          1         16
2                INACTIVE         YES          1         15
SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-01109: database not open
First determine the last good SCN by querying the first_change# column from v$log, you’re missing only the current online redo logs, therefore you can perform an incomplete recovery up to but not including,
SQL> select group#,status,archived,thread#,sequence#,first_change# from v$log;
GROUP# STATUS           ARC    THREAD#  SEQUENCE# FIRST_CHANGE#
———- —————- — ———- ———- ————-
1             CURRENT          NO           1         17        574131
3             INACTIVE         YES          1         16        574125
2             INACTIVE         YES          1         15        574103
SQL>
In this case, you can restore and recover up to but not including, 574131
Operation :-
Losing  all members of your current online redo log group is arguably the worst thing taht can happen to your database. if you experience media failure with all members of the current online redo group, then you will lose any transactions contained in those logs. in this case, you will have to perform incomplete recovery before you can open your database.
[oracle@localhost root]$ rlwrap rman target sys/sys
Recovery Manager: Release 10.2.0.1.0 – Production on Mon May 31 10:51:10 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: TEST (DBID=2015564632, not open)
RMAN> restore database until scn 574131;
Starting restore at 31-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=150 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/system01.dbf
restoring datafile 00002 to /home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/undotbs01.dbf
restoring datafile 00003 to /home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/sysaux01.dbf
restoring datafile 00004 to /home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/backupset/2010_05_25/o1_mf_nnndf_TAG20100525T103307_5zpz9nft_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/backupset/2010_05_25/o1_mf_nnndf_TAG20100525T103307_5zpz9nft_.bkp tag=TAG20100525T103307
channel ORA_DISK_1: restore complete, elapsed time: 00:01:37
Finished restore at 31-MAY-10
RMAN> recover database until scn 574131;
Starting recover at 31-MAY-10
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 7 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_25/o1_mf_1_7_5zpzh95q_.arc
archive log thread 1 sequence 8 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_8_606k8n7x_.arc
archive log thread 1 sequence 9 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_9_606lkvgy_.arc
archive log thread 1 sequence 10 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_10_606mfskg_.arc
archive log thread 1 sequence 11 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_11_606ndqvj_.arc
archive log thread 1 sequence 12 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_12_606szwxo_.arc
archive log thread 1 sequence 13 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_13_606t3zs8_.arc
archive log thread 1 sequence 14 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_14_606t777d_.arc
archive log thread 1 sequence 15 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_15_606t908r_.arc
archive log thread 1 sequence 16 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_16_606t9jd0_.arc
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_25/o1_mf_1_7_5zpzh95q_.arc thread=1 sequence=7
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_8_606k8n7x_.arc thread=1 sequence=8
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_9_606lkvgy_.arc thread=1 sequence=9
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_10_606mfskg_.arc thread=1 sequence=10
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_11_606ndqvj_.arc thread=1 sequence=11
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_12_606szwxo_.arc thread=1 sequence=12
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_13_606t3zs8_.arc thread=1 sequence=13
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_14_606t777d_.arc thread=1 sequence=14
media recovery complete, elapsed time: 00:00:06
Finished recover at 31-MAY-10
RMAN> alter database open resetlogs;
database opened.
SQL> select status from v$instance;
STATUS
————
OPEN
SQL> select group#,members,status from v$log;
GROUP#    MEMBERS STATUS
———- ———- —————-
1                       2           INACTIVE
2                       2          CURRENT
3                       2          UNUSED

Recover from loss of active redo log file.
- shutdown the database

SHUTDOWN IMMEDIATE;

- restore the datafiles and proceed for an incomplete recovery until the missing log sequence
- mount the database

STARTUP MOUNT;

- check the status of the missing log file group from v$log view

select group#, sequence#, bytes, first_change#, to_char(first_time,’DD-MM-YY hh24:mi:ss’) tim, status from v$log;

- if STATUS = ACTIVE and ARCHIVED = NO

RECOVER DATABASE UNTIL TIME ‘yyyy-mm-dd:hh24:mi:ss’;

‘ss’ in the seconds should be 1 second less than the first_time of the missing redo log file. - open the database with resetlogs option

1	ALTER DATABASE OPEN RESETLOGS;

- take a solid backup

What happens during HOT BACKUP?

During hot backup, a script or begin backup command puts a tablespace into backup mode, then copies the datafiles to disk or tape, then takes the tablespace out of backup mode.

Many people are having a misconception regarding hot backup that, during hot backup DBWR process will stop writing into datafiles. Also, they say that while the datafiles are not writable, changes are stored somewhere in the SGA, the redologs, the rollback segments etc places and will be written back to datafiles when the tablespace is taken out of backup mode.

In fact, Oracle’s tablespace hot backup does not work this way at all. It absolutely does not stop writing the datafiles, and actually allows continued operation of the database almost exactly as during normal operation.

1. The tablespace is checkpointed 2. The checkpoint SCN in the datafile header freeze to increment with checkpoints 3. Full images of changed DB blocks are written to the redologs

The above three actions are  required to guarantee consistency once the file is restored and recovery is applied.
By freezing the checkpoint SCN in the file headers, any subsequent recovery on that backup copy of the file will know that it must begins at that SCN. Having an old SCN in the file header tells recovery that the file is an old one, and that it should look for the redolog file containing that SCN, and apply recovery starting there.
Note that checkpoints to datafiles in hot backup mode are not stopped during the backup, only the incrementing of the main checkpoint SCN flag will stop increasing.

Also we will observe that huge redo will be generated during hot backup. This is the result of the logging of full images of changed blocks in these tablespaces to the redologs.Normally, Oracle logs an entry in the redologs for every change in the database, but it does not log the whole image of the database block. By logging full images of changed DB blocks to the redologs during backup mode, Oracle eliminates the possibility of the backup containing fractured blocks.

Typically, Oracle database blocks are a multiple of O/S blocks. For instance, most Unix filesystems have a default block size of 4k, while Oracle’s default block size is 8k. This means that the filesystem stores data in 4k chunks, while Oracle performs reads and writes in 8k chunks, or multiples. While backing up a datafile, your backup script makes a copy of the datafile from the filesystem, using O/S utilities such as copy, dd etc. As it is making this copy, it is reading in O/S-block sized increments. If the database writer happens to be writing a DB block into the datafile at the same time when backup is reading that block, your backup copy of the DB block could contain some O/S blocks before and after writing by DBWR. This kind of blocks which are having mismatched halves are called fractured blocks.

By logging the full block image of the changed block to the redologs, it guarantees that in the event of a recovery, any fractured that might be in the backup copy of the datafile will be resolved by replacing them with the full image of the block from the redologs.

ALTER SYSTEM SWITCH LOGFILE vs ALTER SYSTEM ARCHIVELOG CURRENT

 Both ALTER SYSTEM SWITCH LOGFILE(Asynchronous) and ALTER SYSTEM ARCHIVELOG 

 CURRENT(Synchronous) will force a log switch, but they do it in different 

 ways!

The 'ALTER SYSTEM ARCHIVELOG CURRENT' is the preferred one that should be
used for backup scripts. 

The reason being,when you do an 'ALTER SYSTEM SWITCH
LOGFILE', you get the prompt back immediately. This command performs a
database checkpoint, switches to the next log, In background signals the 

archiver to copy the logfile to the archive destination, and returns the 

prompt. Mind you,this command does not wait for the archive to complete. 

As the command indicates, it only performs a log switch. Hence, you might 

come across backup scripts which does an 'ALTER SYSTEM SWITCH LOGFILE' and 

then does a SLEEP for some time, thus allocating some time for the Archiver 

to finish archiving. 

Whereas, an 'ALTER SYSTEM ARCHIVE LOG CURRENT' does all of the
above, but does not return back to the prompt until the archive is
complete.This command is safer because it waits for the OS to acknowledge(ACK) 

that the redo log has been successfully written.

Also, 'ALTER SYSTEM SWITCH LOGFILE' archives only the current thread.
Whereas the 'ALTER SYSTEM ARCHIVELOG CURRENT' needs a thread to be
specified and If you miss the thread parameter, Oracle archives all redo
log file groups from all enabled threads, including logs previous to
current logs.  

Adding a voting disk and ocr file online

In 10.2 RAC it is now possible to add a voting disk online in addition to specifying more than one voting disk during the ClusterWare install.In 10G R1 there was no software level mirroring of the voting disk.

I tried this out for the first time today and things didn't go as I expected.

[root@dbrac1 ~]# /home/oracle/oracle/product/10.2.0/crs/bin/crsctl add css votedisk /dev/raw/raw4
Cluster is not in a ready state for online disk addition
You have to use the force option in the above command due to
Bug 4898020 ADDING VOTING DISK ONLINE CRASH THE CRS
which is fixed in the 10.2.0.4 patchset. This is again another case where clearly documented functionality in 10.2 does not work.

Before using the force option make sure the CRS stack is not up on any of the nodes.

The Cluster Ready Services Stack

The list in this section describes the processes that comprise CRS. The list includes components that are processes on Linux and UNIX operating systems, or services on Windows.

• Cluster Ready Services (CRS): The primary program for managing high availability operations in a cluster.
  The CRS daemon (crsd) manages cluster resources based on the configuration information that is stored in OCR for each resource. This includes start, stop, monitor, and failover operations. The crsd process generates events when the status of a resource changes. When you have Oracle RAC installed, the crsd process monitors the Oracle database instance, listener, and so on, and automatically restarts these components when a failure occurs.
• Cluster Synchronization Services (CSS): Manages the cluster configuration by controlling which nodes are members of the cluster and by notifying members when a node joins or leaves the cluster. If you are using certified third-party clusterware, then CSS processes interface with your clusterware to manage node membership information.
  The cssdagent process monitors the cluster and provides I/O fencing. This service formerly was provided by Oracle Process Monitor Daemon (oprocd), also known as OraFenceService on Windows. A cssdagent failure may result in Oracle Clusterware restarting the node.
• Oracle ASM: Provides disk management for Oracle Clusterware and Oracle Database.
• Cluster Time Synchronization Service (CTSS): Provides time management in a cluster for Oracle Clusterware.
• Event Management (EVM): A background process that publishes events that Oracle Clusterware creates.
• Oracle Notification Service (ONS): A publish and subscribe service for communicating Fast Application Notification (FAN) events.
• Oracle Agent (oraagent): Extends clusterware to support Oracle-specific requirements and complex resources. This process runs server callout scripts when FAN events occur. This process was known as RACG in Oracle Clusterware 11g release 1 (11.1).
• Oracle Root Agent (orarootagent): A specialized oraagent process that helps crsd manage resources owned by root, such as the network, and the Grid virtual IP address.

The Cluster Synchronization Service (CSS), Event Management (EVM), and Oracle Notification Services (ONS) components communicate with other cluster component layers on other nodes in the same cluster database environment. These components are also the main communication links between Oracle Database, applications, and the Oracle Clusterware high availability components. In addition, these background processes monitor and manage database operations.

[root@dbrac1 init.d]# ps -ef | grep d.bin | grep -v grep
should return no rows.


[root@dbrac1 init.d]# /home/oracle/oracle/product/10.2.0/crs/bin/crsctl add css votedisk /dev/raw/raw4 -force
Now formatting voting disk: /dev/raw/raw4
successful addition of votedisk /dev/raw/raw4.
[root@dbrac1 init.d]#

Check that the newly added disk can be seen from both nodes.

[root@dbrac1 init.d]# /home/oracle/oracle/product/10.2.0/crs/bin/crsctl query css votedisk
0. 0 /dev/raw/raw2
1. 0 /dev/raw/raw4

located 2 votedisk(s).


[root@dbrac2 init.d]# /home/oracle/oracle/product/10.2.0/crs/bin/crsctl query css votedisk
0. 0 /dev/raw/raw2
1. 0 /dev/raw/raw4

located 2 votedisk(s).


The same error is encountered should you wish to delete the newly added disk

[root@dbrac1 bin]# ./crsctl delete css votedisk /dev/raw/raw4
Cluster is not in a ready state for online disk removal
[root@dbrac1 bin]# ./crsctl delete css votedisk /dev/raw/raw4 -force
successful deletion of votedisk /dev/raw/raw4.

Again please note that the force option should be used only after the CRS stack
is shutdown on all nodes. Failure to do so could result in OCR corruption.

Adding OCR

From 10g R2 we can multiplex the ocr

Adding OCR device again (CRS was online) 
[root@node1-pub ~]# ocrconfig -replace ocrmirror /u02/ocfs2/ocr/OCRfile_1 or /dev/sda1
                                                                                (destination_file or disk)
[root@node1-pub ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       3852
         Available space (kbytes) :     258268
         ID                       :  744414276
         Device/File Name         : /u02/ocfs2/ocr/OCRfile_0
                                    Device/File integrity check succeeded
         Device/File Name         : /u02/ocfs2/ocr/OCRfile_1
                                    Device/File integrity check succeeded

         Cluster registry integrity check succeeded

You can have at most 2 OCR devices (OCR itself and its single Mirror) in a cluster. Adding extra Mirror gives you below error message

[root@node1-pub ~]# ocrconfig -replace ocrmirror /u02/ocfs2/ocr/OCRfile_2
PROT-21: Invalid parameter

Changing the location of an existing OCR

1. [root@node1-pub ~]# ocrconfig -replace ocr destination_file
2.[root@node1-pub ~]# ocrconfig -replace ocr disk

 

How to restore the lost archivelogs before it is shipped to the Standby

Today ,the day following the weekend we realised all the archivelogs are deleted before being shipped to the standby
We usually keep only 2 days archivelogs so that to avoid asm diskgroup from getting exhausted.
The script which deletes the archivelog is
ORACLE_HOME=/oracle/app/oracle/product/10.2.0;export ORACLE_HOME
ORACLE_SID=;export ORACLE_SID
/oracle/app/oracle/product/10.2.0/bin/rman target / << EOF
delete archivelog until time ‘trunc(sysdate)-2′;
y
EOF
today on monday i realised that archivelogs genareated were not shipped to physical standby because we had not enabled the dataguard at weekends.So we detected the archive log gap from V$archive_gaps
select * from gv$archive_gaps;
Inst_id thread# sequence# gap
1 5630-5645
2 4512-4536
The above command on standby database gives us the missing archive log sequence from the nodes
the archive logs are restored on the primary using rman as follows
rman target /
restore archivelog from logseq 5630 until logseq 5645 thread 1;
restore archivelog from logseq 4512 until logseq 4536 thread 2;
after restoring the archive logs start the shipping of archive logs as follows:
alter system set log_archive_dest_state_2=enable;
& it was observed things were back to normal. also the alert log of the standby gives the indication of missing archivelogs
from rman u can verify the archivelogs are present at the primary or not?
RMAN> list archivelog from logseq 5630