Wednesday 17 June 2015

Steps to Change ArchiveLog Mode in oracle database.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 7 01:04:11 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             507514448 bytes
Database Buffers          335544320 bytes
Redo Buffers                5132288 bytes
Database mounted.
Database opened.

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Current log sequence           4

SQL> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /opt/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 3852M
recovery_parallelism                 integer     0

Here Archive location is /opt/app/oracle/flash_recovery_area  
By default archives are generating in flash_recovery_area

To change

SQL> alter system set db_recovery_file_dest='/opt/archives/test/' scope=spfile;

System altered.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             507514448 bytes
Database Buffers          335544320 bytes
Redo Buffers                5132288 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

No comments:

Post a Comment