Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Enable Archive log Mode for RAC database

Enable Archive log Mode for RAC database

Whether it is single instance database or multiple instance database, all changes made to the database are written into online redo log files. In an Oracle RAC environment, each instance have its own set of online redolog files that is known as thread. Each Oracle instance will use its set of online redologs in a circular fashion. If there is log switch occur then it will write to next online redo log file. If the database is in “Archive Log Mode”, Oracle will make a copy of the online redo log before it gets reused. A thread must contain at least two online redologs so that it can archive it properly before it is used.

In a RAC environment each instance have exclusive right access to its own set of redo log files. That means suppose I have a database named racdb and racdb database has two nodes, racdb1 instance and racdb2 instance. racdb1 instance can’t write to online redo log files of racdb2 instance. However each instance can read another instance’s current online redolog file to perform instance recovery if that instance was terminated abnormally. So one instance must have read access of another online redo log files. It is therefore a requirement that online redo log files to be located on a shared storage device.

Whenever you create your database manually then by default database is in noarchive log mode. If you create your database with database configuration assistant (dbca) then there is a checkbox and check the box will enable your database archival mode. But if your database is in noarchivelog mode and you want to enable the archivelog mode then you must shutdown the database. So while enabling archivelog mode in RAC database you must shutdown your all instances. The following is the procedure to enable a database from noarchivelog mode to archivelog mode in RAC environment.

Step 01: Login to any instance and check the archival settings and location.
SQL> conn / as sysdba
SQL> archive log list

Step 02: If you need to change archival location then you can change it by
ALTER SYSTEM SET archival_parameter=new_value scope=both;

Step 03: Shutdown all RAC instances. The following command will shutdown all instances of racdb cluster database.
$ srvctl stop database -d racdb

Step 04: Connect to any of the local instance and MOUNT the database:

$ sqlplus “/ as sysdba”
SQL> startup mount

Step 05: Enable archive log mode.

SQL> alter database archivelog;

Step 06: Shutdown the local instance:
SQL> shutdown immediate

Step 07: Startup all the instances using srvctl.

$ srvctl start database -d racdb

Optionally, bring any services (i.e. TAF) back up using srvctl:

$ srvctl start service -d racdb

Step 08: Login to the local instance and verify Archive Log Mode is enabled:

$ sqlplus “/ as sysdba”
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 81
Next log sequence to archive 82
Current log sequence 82
After enabling Archive Log Mode, each instance in the RAC configuration can automatically archive redologs.

December 15, 2007 Posted by | oracle | Leave a comment

Oracle pfile and spfile

Oracle pfile and spfile

There are two types of initialization parameter files in oracle:

1)Parameter Files (PFILES)
2)Server Parameter Files (SPFILES).

Comparison Between Pfile and Spfile:

1. Pfile is the text file that can be edited using a text editor. Spfile is binary file that cannot be edited directly by text editor. If you edit spfile it will corrupt.

2. When changes are made to the PFILE, the instance must be shut down and restarted before it takes effect. Most changes to the SPFILE can be made dynamically, while the instance is open and running.

3. Pfile is by default named as init{instance_name}.ora. Spfile is by default named as spfile{instance_name}.ora where instance_name will be replaced by your database name. By default spfile is located under $ORACLE_HOME/dbs in unix and windows and pfile will be located under $ORACLE_HOME\database location on windows.

4. Pfile can be created from an SPFILE using the create pfile from spfile command.Spfile can be created from a PFILE using the create spfile from pfile command.

5. In order to modify any parameter value in pfile you must open the pfile with any text editor and manually edit it. And in order to modify any parameter value inside spfile you must issue ALTER SYSTEM SET with SCOPE=SPFILE or SCOPE=BOTH. Pfile can never be edited with ALTER SYSTEM SET.

6. Spfile can be backed up by RMAN. pfile can’t be backed up by RMAN.


December 7, 2007 Posted by | oracle | Leave a comment