Khurram Siddiqui

I am an IT professional with 10 years of professional experience,I have good proficiency on Oracle technologies, and at last 2 years of my career to study Real Application Clusters,data guard and participate actively on Oracle community ,If you want to hire me on Contract or to quote on project basis contact me at khurrampc@hotmail.com.

Friday, May 23, 2008

How it is easy to enable archivelog in 10g 


log_archive_start parameter is for enabling automatic archiving in 9i and "alter 
database archivelog" for changing database log mode to archive mode.

both log_archive_start parameter to TRUE within parameter file and "alter database 
archivelog" command should be executed within 9i box for turning on the archiving 
properly.But in 10g its two in one you can enable archivelog when yours database in 
mount state by executing "alter database archivelog" only.

Here i try to find out how easy to enable archivelog in 10g rather in 9i.First i 
will show you how to enable archivelog mode in 9i then i will show you how easy it 
is in 10g.


SQL> select banner from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> 

SQL> archive log list
   
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            c:\sdsarchive
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20

SQL> show parameter log_archive_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_start                    boolean     TRUE


   
The parameter log_archive_start is true within init parameter file now  make it 
false and then start database from this parameter file.




*.log_archive_start=FALSE

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup pfile=c:\initSDS.ora
ORACLE instance started.

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.


SQL> archive log list
   
Database log mode              Archive Mode
Automatic archival             Disabled
Archive destination            c:\sdsarchive
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20


SQL> show parameter log_archive_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_start                    boolean     FALSE
SQL> 


SQL> alter system switch logfile
  2  /

System altered.

though "Database log mode" is in "Archive mode" but its "Automatic archival" is  
disabled due to this upon log switching redo log is not getting archived within 
archive destination at c:\sdsarchive ,this automatic archival is enabled by 
log_archive_start parameter to TRUE.

Now change log_archive_start parameter from FALSE to TRUE in the parameter file and 
start database with this parameter file.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


*.log_archive_start=TRUE


SQL> startup pfile=c:\initSDS.ora
ORACLE instance started.

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

SQL> archive log list
   
Database log mode              Archive Mode
Automatic archival             Enabled 
Archive destination            c:\sdsarchive
Oldest online log sequence     20
Next log sequence to archive   22
Current log sequence           22


SQL> show parameter log_archive_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_start                    boolean     TRUE

SQL> alter system switch logfile
  2  /

System altered.

   
Now redo log get archived within  destination c:\sdsarchive ,



SQL>shutdown immediate
SQL>startup mount
SQL> alter database noarchivelog
  2  /

Database altered.

SQL> alter database open
  2  /

Database altered.

SQL> archive log list
   
Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            c:\sdsarchive
Oldest online log sequence     21
Current log sequence           23

SQL> alter system switch logfile
  2  /

System altered.

SQL> /

System altered.

You will not see any archive file within floder c:\sdsarchive with no archive mode 
but enabling automatic archival by using parameter log_archive_start to TRUE, either 
one of them database log mode to noarchivelog or log_archive_start set to false will 
not let archived the redo logs,both are necessary for enabling the archive mode 
properly in 9i.

But the case is different within 10g ,in 10g log_archive_start is deprecated if you 
set this parameter TRUE within parameter file then at startup you will get error ORA-
32006.

SQL> conn sys/sys as sysdba
Connected.
SQL> select name from v$database
  2  /

NAME
---------
PROD

SQL> select banner from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL> startup pfile=C:\oracle\product\10.1.0\admin\PROD\pfile\init.ora.412200817533
   
ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
Database opened.

SQL> archive log list
   
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
SQL> 

log_archive_start has no effect in 10g either you set it TRUE or FALSE.In 10g you 
can turn on archive log mode and automatic archival enabled only with a single 
command in mount mode is "alter database archivelog"

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.

SQL> archive log list
   
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     14
Next log sequence to archive   16
Current log sequence           16

SQL> alter database noarchivelog
  2  /

Database altered.

SQL> archive log list
   
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     14
Current log sequence           16

SQL> alter database archivelog
  2  /

Database altered.

SQL> archive log list
   
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     14
Next log sequence to archive   16
Current log sequence           16
SQL>
Posted by at

3 comments:

Anonymous said...

Thanks a lot Khurram,you have beautifully explained the topic

March 3, 2009 at 5:11 AM
Unknown said...

Thank you, the info provided saved me hours of troubleshooting Backup Exec with Oracle Agent. I don't know Oracle so the very detailed steps, up to the '/' provided was very helpful indeed. :D

May 6, 2009 at 8:25 PM
daspeac said...

I have heard about another way of microsoft outlook backup and restore. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues.

November 3, 2010 at 1:16 PM

Post a Comment

Subscribe to: Post Comments (Atom)

Followers

About Me

My photo
Melbourne, Victoria, Australia