Sunday 16 June 2013

Performance Tuning - Enable or disable Oracle "oraarch" to save hard disk space

There might be time where you would like to reduce the disk utilization in DEV or QAS environment.

Depends on the backup and restore strategy, disabling the "oraarch" / archive logging or deleting the files are possible as long as you have perform a full filesystem backup of the SAP systems as the safe side and the real-time transaction data is not the primary concern (recovery of database will be impossible in case of media failure).

Either using the "brtools' to turn off archive log or method with command prompt:

Steps for command prompt:

A) Check acrhivelog status

1) Login as <SID>adm

2) Shutdown SAP with SAPMMC (Windows environment) / stopsap (Unix environment)


3) Once SAP is down, open a command prompt / Putty session: 

4) Type -> sqlplus '/as sysdba' 

5) SQL> select  log_mode from v$database; 


Similar output if the database is in archive mode.
LOG_MODE
------------------
ARCHIVE LOG

B) To turn achive log off
1) SQL> shutdown immediate 
2) SQL> startup mount

3) SQL> alter database noarchivelog;

4) SQL> archive log list; --> to show archive disable

5) SQL> alter database open;

6) SQL> select  log_mode from v$database; --> check the status

Similar output if the database is in no archive mode.


LOG_MODE
------------------
NO ARCHIVE LOG

7) Once database open and up and running, go to SAPMMC and start SAP (Windows) / startsap (Unix).



B) To turn on achive log off


1) SQL> shutdown immediate 
2) SQL> startup mount

3) SQL> alter database archivelog;

4) SQL> alter database open;

5) SQL> select  log_mode from v$database; --> check the status

Similar output if the database is in archive mode.


LOG_MODE
------------------
ARCHIVE LOG

        
Steps for brtools:

1) Login as <SID>adm


2) Shutdown SAP with SAPMMC (Windows environment) / stopsap (Unix environment)


3) Once SAP is down, open a command prompt / Putty session: 


4) Type -> sqlplus '/as sysdba' 

5) SQL> brtools 

A) Check acrhivelog status

1 = Instance management

6 - Show instance status
Continue x2
2 - Show instance status

Information about the status of database instance R53

 1 - Instance number (number) ........... 1
 2 - Instance thread (thread) ........... 1
 3 - Instance status (status) ........... OPEN
 4 - Instance start time (start) ........ 2012-11-07 02.34.33
 5 - Oracle version (version) ........... 10.2.0.2.0
 6 - Database creation time (create) .... 2012-10-12 04.09.24
 7 - Last resetlogs time (resetlogs) .... 2012-08-20 07.11.28
 8 - Archivelog mode (archmode) ......... ARCHIVELOG
 9 - Archiver status (archiver) ......... STARTED
10 - Current redolog sequence (redoseq) . 18914
11 - Current redolog SCN (redoscn) ...... 16310535260
12 - Number of SAP connections (sapcon) . 26

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------


6) SQL> brtools

B) To turn achive log off

1 = Instance management
3 - Alter database instance
Continue x 2
Now you see the screen below
BR0280I BRSPACE time stamp: 2012-11-07 02.41.24
BR0656I Choice menu 207 - please make a selection
-------------------------------------------------------------------------------
Alter database instance main menu

 1 - Switch redolog file
 2 - Force database checkpoint
 3 - Set archivelog mode
 4 - Set noarchivelog mode
 5 - Show instance status
 6 * Exit program
 7 - Reset program status

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:

Choose 3 to set achive log, choose 4 to set noacrhivelog

Once done, press C to continue until Brtools will execute alter database command and restart database

7) Once database up and running, go to SAPMMC and start SAP (Windows) / startsap (Unix).






No comments:

Post a Comment