To set archive log, always login through SYSDBA
Now, lets check the current log mode either in "Archivelog" or "Noarchivelog":
C:\Documents and Settings\navneet>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 27 11:19:53 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2296
Current log sequence 2299
OR
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
We have checked that our Database is in "NOARCHIVE LOG". Below query will set the location where to save the archive logs.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=E:\ARCHIVE_LOG' SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 385876432 bytes
Database Buffers 142606336 bytes
Redo Buffers 5804032 bytes
Database mounted.
The below query will make database into archivelog.
SQL> ALTER DATABASE ARCHIVELOG;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
ARCHIVELOG
OR
SQL> ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:\ARCHIVE_LOG
Oldest online log sequence 2296
Next log sequence to archive 2299
Current log sequence 2299
SQL> alter system switch logfile;
Now database is in Archivelog mode, We have verified by checking archivelogs in directory.
E:\>dir
Volume in drive E has no label.
Volume Serial Number is AA1B-C0B9
Directory of E:\
12/09/2011 09:15 AM app
03/27/2012 11:43 AM 13,952,512 ARCHIVE_LOG2ARC0000002299_0769427405.0001
03/27/2012 11:46 AM 1,536 ARCHIVE_LOG2ARC0000002300_0769427405.0001
03/28/2012 05:39 AM 50,630,144 ARCHIVE_LOG2ARC0000002301_0769427405.0001
03/29/2012 05:38 AM 40,771,072 ARCHIVE_LOG2ARC0000002302_0769427405.0001
03/29/2012 06:30 PM 40,671,232 ARCHIVE_LOG2ARC0000002303_0769427405.0001
02/17/2009 11:33 AM 23,516,968 SkypeSetupFull.exe
6 File(s) 169,543,464 bytes
1 Dir(s) 38,746,923,008 bytes free
Your suggestions and queries are always warm welcomed.
No comments:
Post a Comment