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:oracle1oradataSHAANArchive’;

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:

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s