Archive Log on Oracle 10g R2

What is to be done?

1. Set an existing Database in Oracle 10g R2 into Archive mode.
2. Try to recover a database from a cold backup and subsequent Archives.

Pre-requisites:

- A running Oracle Database (version used 10g R2 10.2.0.4) which is not in Archive mode.
- Stand-by database
- Sufficient disk space to keep multiple versions of Cold Backup.
- OS: Windows 2003 Server SP2 or Windows 2008 Server

Part I: Putting a database into archive mode.

Step 1: Take a cold backup of the database.
Step 2: Generate a PFILE from existing SPFILE and incorporate Archive parameters in PFILE
Step 3: Shut the database down.
Step 4: After securing a copy of the existing SPFILE overwrite it with the altered PFILE
Step 5: Mount the database but do not open it for users to log on.
Step 6: Alter the Database to start archiving.
Step 7: Open the Database.
 

Steps explained:

1. Take a cold backup of the database:

A cold backup can be taken using the RMAN Utility. But for this test, a very simple backup was taken. Database was turned off and all the control files, data files and Log files were copied to a different location.
To check if the database is currently archive mode or not use the following command from SYS User:
SQL> select log_mode from gv$database;
LOG_MODE
-------------------
NOARCHIVELOG

2. Generate a PFILE from existing SPFILE and incorporate Archive parameters in PFILE

From SYS User generate a PFILE (Oracle initialization Parameters file) using the command.
SQL> create pfile from spfile;
File created
The new PFILE will be created in the default location /database. To create it in some other location use create pfile=’’ from spfile;
In the PFILE created add the following two parameters for the Archive Logs.
log_archive_format='LOG_%S_%R_%T.ARC'
log_archive_dest=
Other options available for format are:
%s - log sequence number
%S - log sequence number, zero filled
%t - thread number
%T - thread number, zero filled
%a - activation ID
%d - database ID
%r - resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database.

3. Shut the database down

Login to the database through Command Prompt with the SYS user. If you have multiple databases on the same machine, the Windows global variable ORACLE_SID needs to set. It can be done by the command:
C:> set ORACLE_SID=
Connect to an idle instance of SQL Plus using the command.
C:> sqlplus /nolog
SQL> connect SYS as sysdba
Enter password:
Connected.
Shut down the database. Use immediate or abort if users are logged on.
SQL> shutdown immediate

4. After securing a copy of the existing SPFILE overwrite it with the altered PFILE

Make a copy of the SPFILE in the location, /database and create the new SPFILE from the PFILE with the command.
SQL> create spfile from pfile;
File created.
If pfile is in a different location from /database provide the full location as pfile=’’;

5. Mount the database but do not open it for users to log on.

Mount the database with the command.
SQL> startup mount
ORACLE instance started
Total system global area …
….
Database mounted.

6. Alter the Database to start archiving.

Alter the database to start the archiving process with the command,
SQL> alter database archivelog;
Database altered.

7. Open the Database.

Now the database can be opened for use.
SQL> alter database open;
Database altered.
To test if archiving is turned on run the same SQL statement as before.
SQL> select log_mode from gv$database;
LOG_MODE
---------------
ARCHIVELOG
The archiving on the database has successfully been activated.