Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 26 March 2012

Convert into Archivelog Mode

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;
or
SQL> select log_mode from v$database;
NOARCHIVELOG

SQL> Create Pfile=’D:\Orcl3_Pfile.sql’ from Spfile;
SQL> Shutdown Immediate;
Update this Pfile.
log_archive_dest_1='location=’C:\archive'
log_archive_start=TRUE
SQL> Startup mount pfile=’D:\Orcl3_Pfile.sql’;
SQL>Alter database archivelog;
SQL>Alter database open;
To change the Destination of Archive log File
Archive log list;
Show Parameter Archive;
SQL> select DEST_NAME,STATUS,DESTINATION from V$ARCHIVE_DEST;
SQL> select * from v$parameter where name = 'log_archive_dest';

It will show the current destination of Archivelog
SQL>Alter system set log_archive_dest_1='location=C:\oracle1\oradata\SHAAN\Archive';
SQL>alter system archive log start;

Differences concerning backups: NoarchiveLog and Archivelog Mode
Noarchivelog Mode
Archivelog Mode
Must backup entire database
Can backup parts of database (datafiles tablespaces)
DB must be shut down
No Need to Shutdown database
Only entire DB can be restored            
Tablespaces can be restored
In case of a failure, all changes since
the last backup will be lost
All committed transactions will be restorable
Hot Backup is not Possible
Hot Backup is possible

There are several system views that can provide us with information regarding archives, V$DATABASE
Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode and whether MANUAL (archiving mode) has been specified.
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
Displays all redo log groups for the database and indicates which need to be archived.
V$LOG_HISTORY
Contains log history information such as which logs have been archived and the SCN range for each archived log. Using these tables we can verify that we are infact in ARCHIVELOG mode:

 While maintaining database I came across some situation related to Archivelog.
Problem 1 Archivelog: SWITCH LOGFILE hangs [Oracle 9i]
Alter system switch logfile cause my Oracle 9i database hangs. On digging further, I found the cause my database archive log format was illegal. When I am setting the proper log format it starts switching automatic.
ALTER SYSTEM SET log_archive_format='%s.arc' SCOPE=spfile;

Problem 2 with Archivelog: Thread 1 cannot allocate new log sequence all online redolog needed archiving  hangs [Rman Backup Script]
While maintaining database I came across a situation, where I found Rman automated backup script running continuous there was no error in Backup logfile on checking Alert.log  found the message Thread cannot allocate new log sequence 10 all online log needed archiving further, I found the cause database automatice archival was disable. When I am enabling it is working fine.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Disable
Archive destination            d:\oracle\Archive
Oldest online log sequence     9
Next log sequence to archive   11
Current log sequence           11
SQL>shutdown immediate;
SQL>startup mount;
SQL> alter system archive log start;
SQL>alter database open;
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            d:\oracle\Archive
Oldest online log sequence     9
Next log sequence to archive   11
Current log sequence           11
Problem 3 with Archivelog: SWITCH LOGFILE hangs [Oracle 9i]
I found the command ALTER SYSTEM SWITCH LOGFILE  hangs and there was nothing I could do about it. On digging further, I found the cause my database was in manual archive log mode i.e. automatic archiving was disabled.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Disabled
Archive destination C:\oracle\ora92\RDBMS
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12
In this case when all the online redo logs get filled up we need to manually archive the online redo log files.
Until then, the “ALTER SYSTEM SWITCH LOGFILE” command will make the database wait till the online redo log files are archived with the command

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
You will need to issue the above command from a new session.


0 comments:

Post a Comment