Friday, July 22, 2011

How to clear Apache Cache from Application without bouncing listener.

1. Navigate to "Functional Administrator" responsibility.
2. Once logged in click on the "Core Services" tab.
3. Click on "Caching Framework" link in the blue menu bar.
4. Click on "Global Configuration" link in the left vertical menu.
5. In the "Cache Policy" region click on the "Clear All Cache" button.
6. Click the "Yes" button to confirm the action.
7. Click the "Apply" button to apply the changes.

Apps DBA Free Lance
Sukhwinder Singh

Thursday, July 14, 2011

How to change the Redo Log File size in Oracle Database

We had 50 MB Redo Log Files, 1 Redo Thread, 2 Redo Log Groups and One member in each group.
We decided to increase the Redo Log size to 100MB.
The Redo Logs must be dropped and recreated for changing the redo log size. It can be done online without shutting down the database. However, you need to make sure that the Redo Group being dropped should be INACTIVE when you do this.
We have the following Log Groups and members:

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_RECOVE
------ ------- ----- --------- ---------
2 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO
1 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO
And the status of the Log Groups is:
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVE STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- --------------
1 1 12 52428800 1 NO CURRENT 1156736 27-JAN-11
2 1 11 52428800 1 YES ACTIVE 1156732 27-JAN-11
Here we see that the Group# 1 is being used Currently and the Group# 2 though not being used Currently, however is ACTIVE (means if the Database crashes now, you will need this Group for recovery.) We need to make this group Inactive before proceeding ahead:
For this, execute a checkpoint:
SQL> alter system checkpoint;

System altered.
Now again check the status of the Redo Groups:
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- --------
1 1 12 52428800 1 NO CURRENT 1156736 27-JAN-11
2 1 11 52428800 1 YES INACTIVE 1156732 27-JAN-11
The group 2 is now Inactive. Hence we can proceed with dropping this group:
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01567: dropping log 2 would leave less than 2 log files for instance BOTI (thread 1)
ORA-00312: online log 2 thread 1: '/app01/oratest/oradata/BOTI/BOTI/redo02.log'
What went wrong ???
It’s a basic requirement in Oracle Database that there should always be a minimum 2 Redo Log Groups available with the Database. Hence we can not drop any Redo Group if there are only 2 Groups.
To overcome this issue, we need to add one more Redo group to the database.
Execute the following step:
SQL> alter database add logfile group 3 '/app01/oratest/oradata/BOTI/BOTI/redo03.log' size 100M;

Database altered.
Now check the logfiles:
SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_RECOVE
---------- --------------------- --------------------- -
3 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo03.log NO
2 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO
1 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO
… and the status of the Groups:
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVE STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ---------
1 1 12 52428800 1 NO CURRENT 1156736 27-JAN-11
2 1 11 52428800 1 YES INACTIVE 1156732 27-JAN-11
3 1 0 104857600 1 YES UNUSED 0
The status of the new Group is UNUSED because it has never been used.
Now, we have 3 Redo Groups in our database. We can now proceed with Dropping Redo Group# 2.
SQL> alter database drop logfile group 2;

Database altered.
Also, delete the file ‘/app01/oratest/oradata/BOTI/BOTI/redo02.log’ from File system also.
Now add the Redo Group 2 back to the database with changed Redo size:
SQL> alter database add logfile group 2 '/app01/oratest/oradata/BOTI/BOTI/redo02.log' size 100M;

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_RECOVE
---------- --------------------- --------------------- --------
3 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo03.log NO
2 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO
1 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- --------------
1 1 12 52428800 1 NO CURRENT 1156736 27-JAN-11
2 1 0 104857600 1 YES UNUSED 0
3 1 0 104857600 1 YES UNUSED 0
Now we have to drop the 1st Redo Log Group. Before that, we need to change the status of this group:
SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ---- -----
1 1 12 52428800 1 YES ACTIVE 1156736 27-JAN-11
2 1 13 10485760 1 NO CURRENT 1157376 27-JAN-11
3 1 0 104857600 1 YES UNUSED 0
Still the Grpoup is in Active status. Issue a checkpoint:
SQL> alter system checkpoint;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- -------
1 1 12 52428800 1 YES INACTIVE 1156736 27-JAN-11
2 1 13 104857600 1 NO CURRENT 1157376 27-JAN-11
3 1 0 104857600 1 YES UNUSED 0
Now we can drop the Redo Group# 1.
SQL> alter database drop logfile group 1;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- -----
2 1 13 104857600 1 NO CURRENT 1157376 27-JAN-11
3 1 0 104857600 1 YES UNUSED 0

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBEs IS_RECOVE
---------- --------------------- ---------------------
3 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo03.log NO
2 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO
Delete the file ‘/app01/oratest/oradata/BOTI/BOTI/redo01.log’ from OS.
Now we need to add this group back to the database with 100MB Redo Log file:
SQL> alter database add logfile group 1 '/app01/oratest/oradata/BOTI/BOTI/redo01.log' size 100M;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ----------
1 1 0 104857600 1 YES UNUSED 0
2 1 13 104857600 1 NO CURRENT 1157376 27-JAN-11
3 1 0 104857600 1 YES UNUSED 0
Here we can see that all the Redo Groups are now showing 100MB size (column BYTES).
SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_RECOVE
---------- --------------------- -----------------
3 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo03.log NO
2 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO
1 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO
Though it’s suggested that you should keep 3 groups, if you want you can drop the Redo Group 3 which we added for this exercise.
SQL> alter database drop logfile group 3;

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_RECOVE
------- ------- -------------- ---------------------
2 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO
1 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
------ ---------- ---------- ---------- ---------- --------- ---------
1 1 0 104857600 1 YES UNUSED 0
2 1 13 104857600 1 NO CURRENT 1157376 27-JAN-11

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVE STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ----------
1 1 14 104857600 1 NO CURRENT 1157584 27-JAN-11
2 1 13 104857600 1 YES ACTIVE 1157376 27-JAN-11
Redo Log file size changed from 50M to 100M.

Apps Dba
Sukhwinder Singh

How to Drop Database in Oracle 10g

In order to drop the database start the database in restrict mode and bring it in mount state as shown:
sqlplus / as sysdba
SQL> shutdown immediate;
oracle database closed
oracle database dismounted
oracle instance shutdown
SQL> startup restrict mount;
SQL> drop database;
Database dropped
SQL> exit
Thus u will find that all the files associated with the database will be deleted.

Apps DBA
Sukhwinder Singh

Enable/Disable Archive Log Mode 10g/11g

Enable Archive Log Mode
The following are the steps required to enable archive log mode on an Oracle 10g or 11g database.
Verify the database log mode.
[oracle@ora1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 8 12:02:52 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected
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 25
Current log sequence 27
SQL>

The log mode is No Archive Mode. Note that Archive destination is USE_DB_RECOVERY_FILE_DEST. You can determine the path by looking at the parameter RECOVERY_FILE_DEST.

SQL> show parameter recovery_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 3852M
SQL>


By default, archive logs will be written to the flash recovery area. If you do not want to write archive logs to the flash recovery area you can set the parameter LOG_ARCHIVE_DEST_n to the location in which you wish to write archive logs.

SQL> alter system set log_archive_dest_1='LOCATION=/u02/app/oracle/oradata/orcl/arch' scope = both;
System altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u02/app/oracle/oradata/orcl/arch
Oldest online log sequence 25
Current log sequence 27
SQL>


Now we shutdown the database and bring it backup in mount mode.
SQL> shutdown 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             511708752 bytes
Database Buffers          331350016 bytes
Redo Buffers                5132288 bytes
Database mounted.
SQL>
Lastly all that is needed it set archive log mode and open the database.
SQL> alter database archivelog;
 Database altered.
 SQL> alter database open;
 Database altered.
 SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/app/oracle/oradata/orcl/arch
Oldest online log sequence     25
Next log sequence to archive   27
Current log sequence           27
SQL>


We can now see that archive log mode is enabled. Notice that Automatic archive is enabled as well. In Oracle 9i an earlier another parameter needed to be set in order to enable automatic archiving. This in no longer the case in 10g and 11g as automatic archiving is enabled when the database is placed in archive log mode.
You can switch to the log file to see that an archive is written to archive log location.
SQL> alter system switch logfile;
 System altered.
 SQL> host
[oracle@ora1 ~]$ ls /u02/app/oracle/oradata/orcl/arch
1_27_711369564.dbf
[oracle@ora1 ~]$ exit
exit
 SQL> 

SQL> !ls -lrt /home/oracle/archivelog
total 44496
-rw-r----- 1 oracle oracle 45509632 2009-03-20 14:43 1_167_677948664.dbf
-rw-r----- 1 oracle oracle 1024 2009-03-20 14:43 1_168_677948664.dbf


Disable Archive Log Mode
Note: It is recommended to perform a backup of the database (after a shutdown normal or immediate) prior to changing the archive status of a database.
The following are the steps required to disable archive log mode on an Oracle 10g or 11g database.
Verify the database log mode.
[oracle@ora1 ~]$ sqlplus / as sys<span class="searchterm2">db</span>a
 SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 8 12:54:05 2010
 Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 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              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/app/oracle/oradata/orcl/arch
Oldest online log sequence     26
Next log sequence to archive   28
Current log sequence           28
SQL>
The Database log mode is Archive mode. Next we shut down the database and bring up back up in mount mode.
SQL> shutdown 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             511708752 bytes
Database Buffers          331350016 bytes
Redo Buffers                5132288 bytes
Database mounted.
SQL>

All that is left is to disable archive log mode and open the database.
SQL> alter database noarchivelog;
 Database altered.
 SQL> alter database open;
 Database altered.
 SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/app/oracle/oradata/orcl/arch
Oldest online log sequence     26
Current log sequence           28
SQL>

for 9i database
SQL>shutdown immediate;

Edit below parameters in pfile(init.ora) file.


1. LOG_ARCHIVE_START=TRUE



As you can see, ARCHIVELOG mode has been disabled.
Apps DBA
Sukhwinder Singh

Enabling flashback on Oracle

Flashback query is not enabled by default and must be turned on in following sequence. We will set retention to 10 hours (600 minutes), set recovery size up to 2 GB in file “/recovery/flashback”:
shutdown immediate;
startup mount;
alter database archivelog;
alter system set DB_FLASHBACK_RETENTION_TARGET=600;
alter system set DB_RECOVERY_FILE_DEST_SIZE=2G;
alter system set DB_RECOVERY_FILE_DEST=’/recovery/flashback’;
alter database flashback on;
alter database open;

Apps DDBA Free Lance
Sukhwinder singh