Tuesday, March 11, 2008

Redo logs in RAC

In Rac database, each of instances has it's own thread number (specified in parameter file):
DBNAME_INSTNAME.thread=1
DBNAME_INSTNAME.thread=2

There is a column THREAD# in v$log that tells which Log Group is used by which instance:

SQL> select group#, thread#, bytes, members, status from v$log;

GROUP# THREAD# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ---------
1 1 52428800 2 CURRENT
2 1 52428800 2 INACTIVE
3 2 52428800 2 INACTIVE
4 2 52428800 2 CURRENT

You can use below commands to add log groups to each instance when the database on the RAC is in "opan":

SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 5 ( '+PDATA(ONLINELOG)', '+PFRA(ONLINELOG)') SIZE 51200K,
GROUP 6 ( '+PDATA(ONLINELOG)', '+PFRA(ONLINELOG)') SIZE 51200K;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 7 ( '+PDATA(ONLINELOG)', '+PFRA(ONLINELOG)') SIZE 51200K,
GROUP 8 ( '+PDATA(ONLINELOG)', '+PFRA(ONLINELOG)') SIZE 51200K;
SQL> select group#, thread#, bytes, members, status from v$log;
GROUP# THREAD# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 1 52428800 2 INACTIVE
2 1 52428800 2 INACTIVE
3 2 52428800 2 INACTIVE
4 2 52428800 2 CURRENT
5 1 52428800 2 INACTIVE
6 1 52428800 2 CURRENT
7 2 52428800 2 INACTIVE
8 2 52428800 2 INACTIVE

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+PDATA/pdb/onlinelog/group_1.383.636108915
+PFRA/pdb/onlinelog/group_1.273.636108915
+PDATA/pdb/onlinelog/group_2.396.636108917
+PFRA/pdb/onlinelog/group_2.274.636108917
+PDATA/pdb/onlinelog/group_3.431.636110325
+PFRA/pdb/onlinelog/group_3.275.636110325
+PDATA/pdb/onlinelog/group_4.422.636110325
+PFRA/pdb/onlinelog/group_4.276.636110325
+PDATA/pdb/onlinelog/group_5.567.649501249
+PFRA/pdb/onlinelog/group_5.2498.649501249
+PDATA/pdb/onlinelog/group_6.626.649501251
+PFRA/pdb/onlinelog/group_6.9012.649501251
+PDATA/pdb/onlinelog/group_7.489.649501427
+PFRA/pdb/onlinelog/group_7.14183.649501427
+PDATA/pdb/onlinelog/group_8.568.649501447
+PFRA/pdb/onlinelog/group_8.13797.649501447

Other views relate to redo logs and archive logs:

V$ARCHIVED_LOG - Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.
V$ARCHIVE_DEST - Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.
V$ARCHIVE_PROCESSES - Displays information about the state of the various archive processes for an instance.
V$BACKUP_REDOLOG - Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.
V$LOG_HISTORY - Contains log history information such as which logs have been archived and the SCN range for each archived log.
V$THREAD - Displays thread information.

LOG_ARCHIVE_TRACE initialization parameter to specify a trace level:
0 Disable archivelog tracing. This is the default.
1 Track archival of redo log file.
2 Track archival status for each archivelog destination.

Note:
1. If you restore RMAN backups of RAC database to a single instance on another node, you may need to rename the log files before opening the database in case the file path is different. And you may remove the redolog groups for redo threads of other instances. See Metalink Note 415579.1.
2. After I added more log groups to a RAC database, the below message in alert log went away:

Tue Feb 19 01:02:09 2008
Thread 2 advanced to log sequence 30618
Current log# 4 seq# 30618 mem# 0: +PDATA/pdb/onlinelog/group_4.422.636110325
Current log# 4 seq# 30618 mem# 1: +PFRA/pdb/onlinelog/group_4.276.636110325
Tue Feb 19 01:02:32 2008
Thread 2 cannot allocate new log, sequence 30619
Checkpoint not complete

Current log# 4 seq# 30618 mem# 0: +PDATA/pdb/onlinelog/group_4.422.636110325
Current log# 4 seq# 30618 mem# 1: +PFRA/pdb/onlinelog/group_4.276.636110325
Thread 2 advanced to log sequence 30619
Current log# 3 seq# 30619 mem# 0: +PDATA/pdb/onlinelog/group_3.431.636110325
Current log# 3 seq# 30619 mem# 1: +PFRA/pdb/onlinelog/group_3.275.636110325
Thread 2 cannot allocate new log, sequence 30620
Checkpoint not complete
Current log# 3 seq# 30619 mem# 0: +PDATA/pdb/onlinelog/group_3.431.636110325
Current log# 3 seq# 30619 mem# 1: +PFRA/pdb/onlinelog/group_3.275.636110325
Thread 2 advanced to log sequence 30620
Current log# 4 seq# 30620 mem# 0: +PDATA/pdb/onlinelog/group_4.422.636110325
Current log# 4 seq# 30620 mem# 1: +PFRA/pdb/onlinelog/group_4.276.636110325
Tue Feb 19 01:03:06 2008
Thread 2 cannot allocate new log, sequence 30621
Checkpoint not complete
Current log# 4 seq# 30620 mem# 0: +PDATA/pdb/onlinelog/group_4.422.636110325
Current log# 4 seq# 30620 mem# 1: +PFRA/pdb/onlinelog/group_4.276.636110325

No comments: