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>

3 comments:

Anonymous said...

Thanks a lot Khurram,you have beautifully explained the topic

trionine 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

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.

Followers

About Me

My photo
Melbourne, Victoria, Australia