DBA Daily Activity

The following activities are monitored on a Database :
Task/Machine
Schedule
Description
Connect to Database Hourly To monitor the availability of database server and Oracle listener.
Check Alert Logs Hourly To check the alert log file periodically to identify any logs for errors / warnings. It will automatically send an SMS to the administrator.
Check Tablespace Size Hourly To check the size of free tablespace and free space available. If the tablespace reaches the threshold limit, it will automatically send an SMS message to the administrator.
Verify Rollback Segments Shift To check the status of the rollback segments.
Verify if any resources locked Shift To identify any locked resources.
Verify Archive Logs Daily To check if the archive logs are created properly in the current directory and if standby configuration is enabled, check whether the archive logs are backed, transferred and applied properly.
Hit Ratio Daily To check the overall buffer cache hit ratio for the entire instance since it was started. If the ratio reaches the threshold limit, it will automatically send an SMS message to the administrator.
Check Listener Log File Size Daily To check the size of listener log files on each database instance. If the listener log file size reaches the threshold limit, notification will be sent to the administrator.
Size of Extents Daily To check the segments of each table space, number of extents, initial size of extent, next extent size, minimum extents, maximum extent and status.
Check Disk Space Daily To check the disk space availability for data files, log files and temp space. If the available size reaches the threshold limit, it will automatically send an SMS message to the administrator.
Check CPU Usage Daily Monitors the CPU performance and its usage.
Check Memory Usage Daily Monitors the memory usage, memory page faults, etc.
Check System Log Files Daily To check the Event Viewersystem log files. To see and watch for any OS related warnings or errors.
Check for Invalid Objects Weekly To identify invalid objects in the database.
Check for Chained Rows Weekly To identify chained rows and manag

Oracle Database shall be monitored using The Capacity Planner and Performance Manager applications, This includes special Performance Manager diagnostics such as Performance Overview, TopSessions, TopSQL and Lock Monitor .
Administrators will be able to investigate an event directly from the console’s event viewer through direct access to Performance Manager charts, diagnostic help and historical data related to metrics being monitored by the event.
This provides a logical step-by-step methodology for discovering and investigating performance problems.
In addition, events and Performance Manager charts can share common metric thresholds, allowing consistent performance thresholds to be used by administrators whether monitoring in lights-out mode or real time. With 9i, events can also be registered directly from Performance Manager charts.

New patching strategy@Oracle 11.2.0.2

Oracle changed patching strategy.

Starting with the 11.2.0.2 patch set, Oracle Database patch sets are full installations of the Oracle Database software. This means that you do not need to install Oracle Database 11g Release 2 (11.2.0.1) before installing Oracle Database 11g Release 2 (11.2.0.2).
Note the following changes with the new patch set packaging:
New installations consist of installing the most recent patch set, rather than installing a base release and then upgrading to a patch release.
Direct upgrades from previous releases to the most recent patch set are supported.
Out-of-place patch set upgrades recommended, in which you install the patch set into a new, separate Oracle home. In-place upgrades are supported, but not recommended.
For detail information read :

Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2 [ID 1189783.1]

Oracle RAC Basic

What is Cluster?Cluster consists of two or more independent nodes. Cluster software hides structure of nodes. Due to cluster software all nodes are acting as single server.

What is Real Application Cluster?It is component of Oracle for managing two or more instances on different node which are sharing single database. RAC software manages data access and makes consistent image of database.

What is Node?Each node contains separate CPU and memory and self contained server in cluster. It contains single instance of RAC database.

What is Interconnect?Using cluster interconnect each node communicates messages to other nodes. In short using interconnect all nodes are talking to each other. Interconnect transfers all messages of database from all connected nodes.

What is Shared Disk?Oracle real application cluster database must be accessible from all connected nodes (Instances). Due to this database must be on shared disk which is accessed by all nodes. RAW device file system available in Unix/Linux and unformatted disk partition available in Windows for making shared disk.

What is Cluster Manager?Cluster Manager manages and monitoring connected nodes. It regulates messages and activity of nodes in cluster.

What is Oracle Cluster file system (OCFS)?OCFS is shared file system and made by Oracle itself for Oracle Real Application Cluster. It allows to accessing single Oracle Home for all nodes. Without using OCFS we should need to install separate Oracle Home on each nodes in Cluster.

What is Oracle Clusterware?In previous version of oracle (Oracle 9i) it is called as Cluster Manager. Oracle clusterware monitors all components like instances and listeners. There are two components in Oracle clusterware, those are Voting Disk and OCR.

What is Voting Disk?Voting Disk is shared disk component. Information of shared storage & nodes reside in Voting Disk. It is accessed by the all nodes during cluster operations. Every node pings Voting Disk, if will be failing to ping cluster immediate serves as communication failure & the node is evicted from cluster.

What is OCR?OCR means Oracle Cluster Registry. It stores cluster configuration information. It is also shared disk component. It must be accessed by all nodes in cluster environment.It also keeps information of Which database instance run on which nodes and which service runs on which database.The daemon OCSSd manages the configuration info in OCR and maintains the changes to cluster in the registry.

Mutiplex Controlfiles on ASM diskgroup

 How can I multiplex my controlfile that is on ASM diskgroup? I use ASM on 10gR2.
I use rman autobackup controlfile to solve this case, But I have to startup database with resetlogs.
If USE RAC, Need to start database one node.
And I need to use pfile (assume /opt/pfile), that easily to modify for testing.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA1/db/control01.ctl
$ export ORACLE_SID=db1
$ sqlplus / as sysdba
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA1/db/spfiledb.ora
SQL> create pfile='/opt/pfile' from spfile;
$ grep control_files /opt/pfile
*.control_files='+DATA1/db/control01.ctl'
SQL> shutdown
SQL> startup mount pfile='/opt/pfile';
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA1/db/control01.ctl

I need DBID value and autobackup controlfile:
$ rman target /
connected to target database: DB (DBID=2157822341, not open)
RMAN> backup current controlfile;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/opt/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/opt/%F';
new RMAN configuration parameters are successfully stored
RMAN> backup current controlfile;
Starting backup at 12-NOV-08
.
.
.
Starting Control File and SPFILE Autobackup at 12-NOV-08
piece handle=/opt/c-2157822341-20081112-00 comment=NONE
Finished Control File and SPFILE Autobackup at 12-NOV-08
RMAN> shutdown immediate;
database dismounted
Oracle instance shut down
DBID=2157822341
AUTOBACKUP CONTROLFILE=/opt/c-2157822341-20081112-00
Modified control_files parameter on /opt/pfile file:
*.control_files='+DATA1/db/control01.ctl','+DATA1/db/control04.ctl'
After that begin to recovery controlfile (startup nomount mode)
$ sqlplus / as sysdba
SQL> startup nomount pfile='/opt/pfile';
use rman =>
$ rman target /
connected to target database: db (not mounted)
RMAN> SET DBID 2157822341
RMAN> run {
2> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/opt/%F';
3> RESTORE CONTROLFILE FROM AUTOBACKUP;
4> }
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 12-NOV-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 instance=db1 devtype=DISK
.
.
.
database name (or database unique name) used for search: DB
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20081112
channel ORA_DISK_1: autobackup found: /opt/c-2157822341-20081112-00
channel ORA_DISK_1: control file restore from autobackup complete
output filename=+DATA1/db/control01.ctl
output filename=+DATA1/db/control04.ctl
Finished restore at 12-NOV-08
$ sqlplus / as sysdba
SQL> alter database mount;
Database altered.
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open RESETLOGS;
Database altered.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA1/db/control01.ctl, +DATA1/db/control04.ctl
Now I have multiplex controlfiles(+DATA1/db/control01.ctl,+DATA1/db/control04.ctl) on ASM. I can create spfile from pfile (/opt/pfile file) and stop/start anyway.
SQL> create spfile='+DATA1/db/spfiledb.ora' from pfile='/opt/pfile'
**** However, If we don't need to use this idea, we may restore controlfile backup to new file and then change control_files parameter.
Or ... use asmca for help (copy file in ASM Disk Group)
Some Idea:
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/orcl/controlfile/current.277.741286407
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
RMAN> startup nomount;
Oracle instance started
Total System Global Area 626327552 bytes
Fixed Size 2229080 bytes
Variable Size 390073512 bytes
Database Buffers 226492416 bytes
Redo Buffers 7532544 bytes
RMAN> restore controlfile to '+DATA' from '+DATA/orcl/controlfile/current.277.741286407';
Starting restore at 25-APR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 instance=orcl_2 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 25-APR-11
ASMCMD> ls +DATA/orcl/CONTROLFILE/
Current.277.741286407
current.285.749426511
SQL> alter system set control_files='+DATA/orcl/controlfile/current.277.741286407','+DATA/orcl/controlfile/current.285.749426511' scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2229080 bytes
Variable Size 390073512 bytes
Database Buffers 226492416 bytes
Redo Buffers 7532544 bytes
Database mounted.
Database opened.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/orcl/controlfile/current.277.741286407, +DATA/orcl/controlfile/current.285.749426511

We can use Same plan for above to 10gR2. ALSo..

Moving Datafile In ASM to ASm diskGroup

Today I got  chance To move datafile from one disk group to another Diskgroup. Using below plan (1) We can move system + sysaux database files as well.
With Oracle ASM, when we need to move database file from one diskgroup to another. we have to use "rman" to help.
Option 1-
About moving system + sysaux database files, we need database (mount)
SQL> select file_name, FILE_ID from dba_data_files where file_name like '%sys%';
FILE_NAME FILE_ID
--------------------------------------------------- --------
+DATA/db/datafile/sysaux.260.699468081 3
+DATA/db/datafile/system.259.688768099 1
SQL> SELECT name FROM v$asm_diskgroup;
NAME
--------------------
OLD
NEW
We need to move database files to NEW diskgroup
SQL> shutdown immediate;
SQL> startup mount;
Connect target database by "rman" command-line :
$ rman target /
connected to target database: DB (DBID=2157822341)
RMAN> REPORT SCHEMA;

File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 560 SYSTEM *** +DATA/db/datafile/system.259.688768099
3 500 SYSAUX *** +DATA/db/datafile/sysaux.260.699468081
Backup AS COPY "database files" to NEW diskgroup, then SWITCH DATAFILE to use NEW diskgroup
RMAN> run {
BACKUP AS COPY DATAFILE 1 FORMAT "+NEW";
BACKUP AS COPY DATAFILE 3 FORMAT "+NEW";
SWITCH DATAFILE "+DATA/db/datafile/sysaux.260.699468081" TO COPY;
SWITCH DATAFILE "+DATA/db/datafile/system.259.688768099" TO COPY;
}
Open database...
SQL> alter database open;
SQL> select file_name, FILE_ID from dba_data_files where file_name like '%sys%';

FILE_NAME FILE_ID
--------------------------------------------------- --------
+NEW/db/datafile/sysaux.291.700906921 3
+NEW/db/datafile/system.294.700906903 1
RMAN> REPORT SCHEMA;

File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 560 SYSTEM *** +NEW/db/datafile/system.294.700906903
3 500 SYSAUX *** +NEW/db/datafile/sysaux.291.700906921
After make sure...(no error and database files used in NEW diskgroup), then DELETE old datafilecopy(original)...
RMAN> run {
DELETE DATAFILECOPY "+DATA/db/datafile/sysaux.260.699468081";
DELETE DATAFILECOPY "+DATA/db/datafile/system.259.688768099";
}
reference: http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmasmmi.htm

If not "SYSTEM" database file. we can move database file while database open...
SQL> select file_name, FILE_ID from dba_data_files where file_name like '%users%';

FILE_NAME FILE_ID
------------------------------------------------------------------ --------
+DATA/db/datafile/users.258.699468081 4
SQL> SELECT name FROM v$asm_diskgroup;

NAME
--------------------
OLD
NEW
We need to move database file (4) to NEW diskgroup
Connect target database by "rman" command-line
$ rman target /
connected to target database: DB (DBID=2157822341)
Check...
RMAN> REPORT SCHEMA;

File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +DATA/db/datafile/users.258.699468081
Bring database file to offline
RMAN> SQL "ALTER DATABASE DATAFILE ' ' +DATA/db/datafile/users.258.699468081 ' ' OFFLINE";
or
RMAN> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';
Backup as copy database file to NEW diskgroup
RMAN> BACKUP AS COPY DATAFILE "+DATA/db/datafile/users.258.699468081" FORMAT "+NEW";
or
RMAN> BACKUP AS COPY DATAFILE 4 FORMAT "+NEW";
Switch database file to NEW diskgroup
RMAN> SWITCH DATAFILE "+DATA/db/datafile/users.258.699468081" TO COPY;
and we need "RECOVER"...
RMAN> RECOVER DATAFILE 4;
Bring database file to online:
RMAN> SQL 'ALTER DATABASE DATAFILE 4 ONLINE';
Check...
RMAN> REPORT SCHEMA;
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +NEW/db/datafile/users.257.700906939
SQL> select file_name, FILE_ID from dba_data_files where file_name like '%users%';

FILE_NAME FILE_ID
------------------------------------------------------------------ --------
+NEW/db/datafile/users.257.700906939 4
After Make sure... DELETE old datafilecopy(original):
RMAN> DELETE DATAFILECOPY "+DATA/db/datafile/users.258.699468081";
Option 2-

If not System Tablespace... We don't need stop database and We can offline tablespace... when online talblespace, we don't need recover...

SQL> select file_id, file_name from dba_data_files where tablespace_name='TEST_DATA';
FILE_ID FILE_NAME
---------- --------------------------------------------------
9 +DATA/orcl/datafile/test_data.1335.721759717
SQL> alter tablespace TEST_DATA read only;
Tablespace altered.
SQL> alter tablespace TEST_DATA offline;
Tablespace altered
RMAN> backup as copy datafile 9 format '+DATA2';
Starting backup at 26-APR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=+DATA/orcl/datafile/test_data.1335.721759717
output file name=+DATA2/orcl/datafile/test_data.993.749500909 tag=TAG20110426T184147 RECID=38 STAMP=749500915
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 26-APR-11
RMAN> SWITCH DATAFILE 9 to copy;
datafile 9 switched to datafile copy "+DATA2/orcl/datafile/test_data.993.749500909"
SQL> alter tablespace TEST_DATA online;
Tablespace altered.
SQL> alter tablespace TEST_DATA read write;
Tablespace altered.
SQL> select file_id, file_name from dba_data_files where tablespace_name='TEST_DATA';
FILE_ID FILE_NAME
---------- --------------------------------------------------
9 +DATA2/orcl/datafile/test_data.993.749500909

How to Tune query that cannot modify ?

Query cannot be modified as it is hardcoded in an application or is generated by application code.

“Query Tuning issues can often be alleviated by modification of the query in question, either to add

hints or to change the query structure to avoid an issue. However in some cases the query cannot be

modified because it is hardcoded in an application or is generated by application code.

Oracle provides the option of changing the execution plan for running queries, without changing the

code itself. This feature also allows us to insulate our application execution plans against any

structural changes or upgrades. This feature is known as Stored Outlines and the idea is to store

the execution plan in Oracle provided tables, which will later be referred to by the optimizer to
execute the queries, thus providing optimizer plan stability.

Stored outlines are global and apply to all identical statements, regardless of the user firing it.
Under user SYS run $ORACLE_HOME/rdbms/admin/dbmsol.sql”

PARAMETERS TO BE SET
“CREATE_STORED_OUTLINES   Setting this to TRUE or an appropriate category name will automatically enable creating and storing
of outlines for every subsequent query fired. A DEFAULT category is used unless specified.

“”USE_STORED_OUTLINES This parameter enables the use of public stored outlines. Setting USE_STORED_OUTLINES to TRUE causes
the execution plan stored in the DEFAULT category to take effect. Set the parameter to a proper category name to explicitly use a different category.”
“QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally for the database”
Example:
“EXAMPLE  1
select * from Joshi;
Execution Plan
———————————————————-
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=15 Bytes=852)
1    0   TABLE ACCESS (FULL) OF ‘Joshi’ (Cost=1 Card=15 Bytes=852)”
CREATE OUTLINES AND THEN UPDATE THE OL$HINTS VIEW AS FOLLOWING :
“CREATE OR REPLACE OUTLINE ORIGINALSQL ON
select * from Joshi;
CREATE OR REPLACE OUTLINE HINTSQL ON
select /*+ index(Joshi) */  * from Joshi;
UPDATE OUTLN.OL$HINTS
SET OL_NAME=DECODE(OL_NAME,’HINTSQL’,'ORIGINALSQL’,'ORIGINALSQL’,'HINTSQL’)
WHERE OL_NAME IN (‘ORIGINALSQL’,'HINTSQL’);
SET FOLLOWING PARAMETERS
“alter session set query_rewrite_enabled=true;
alter session set use_stored_outlines=true;”
NOTE THE INDEX SCAN WITHOUT ADDITION OF ANY HINT
select * from Joshi;
Query cannot be modified as it is hardcoded in an application or is generated by application code. “Query Tuning issues can often be alleviated by modification of the query in question, either to addhints or to change the query structure to avoid an issue. However in some cases the query cannot bemodified because it is hardcoded in an application or is generated by application code. Oracle provides the option of changing the execution plan for running queries, without changing thecode itself. This feature also allows us to insulate our application execution plans against anystructural changes or upgrades. This feature is known as Stored Outlines and the idea is to storethe execution plan in Oracle provided tables, which will later be referred to by the optimizer toexecute the queries, thus providing optimizer plan stability.Stored outlines are global and apply to all identical statements, regardless of the user firing it.

Under user SYS run $ORACLE_HOME/rdbms/admin/dbmsol.sql” PARAMETERS TO BE SET

“CREATE_STORED_OUTLINES   Setting this to TRUE or an appropriate category name will automatically enable creating and storingof outlines for every subsequent query fired. A DEFAULT category is used unless specified.

“”USE_STORED_OUTLINES This parameter enables the use of public stored outlines. Setting USE_STORED_OUTLINES to TRUE causesthe execution plan stored in the DEFAULT category to take effect. Set the parameter to a proper category name to explicitly use a different category.” “QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally for the database”

Example:”EXAMPLE  1

select * from Joshi;

Execution Plan

———————————————————-

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=15 Bytes=852)

1    0   TABLE ACCESS (FULL) OF ‘Joshi’ (Cost=1 Card=15 Bytes=852)”

CREATE OUTLINES AND THEN UPDATE THE OL$HINTS VIEW AS FOLLOWING : “CREATE OR REPLACE OUTLINE ORIGINALSQL ON select * from Joshi;

CREATE OR REPLACE OUTLINE HINTSQL ONselect /*+ index(Joshi) */  * from Joshi;

UPDATE OUTLN.OL$HINTS SET OL_NAME=DECODE(OL_NAME,’HINTSQL’,'ORIGINALSQL’,'ORIGINALSQL’,'HINTSQL’) WHERE OL_NAME IN (‘ORIGINALSQL’,'HINTSQL’); SET FOLLOWING PARAMETERS “alter session set query_rewrite_enabled=true;alter session set use_stored_outlines=true;”

NOTE THE INDEX SCAN WITHOUT ADDITION OF ANY HINT

select * from Joshi;

Rman Catalog Usefull Info ..

Q. What is Catalog database and How to Configure it ?

A. This is a separate database which contains catalog schema. You can use the same target database as the catalog database but its not at all recommended.

Q. How Many catalog database I can have ?

A.  You can have multiple catalog database for the same target database . But at a time you can connect to only 1 catalog database via RMAN. Its not recommended to have multiple catalog database.

Q. Is this mandatory to use catalog database ?

A.  No ! its a optional one.

Q. What is the advantage of catalog database ?

A.  Catalog database is a secondary storage of backup metadata. Its very useful in case you lost the current controlfile, as all the backup information are there in the catalog schema. Secondly from contolfile the older backup information are aged out depending upon the control_file_record_keep_time. RMAN catalog database mainten the history of data.

Q. What is the difference between catalog database & catalog schema ?

A.  Catalog database is like any other database which contains the RMAN catalog user’s schema.

Q. What happen if catalog database lost ?

 A.  Since catalog database is a option one there is no direct effect  of loss of catalog  database. Create  a new catalog database and register the target database with the newly createdcatalog one. All the backup information from the target database current controlfile will be updated to the catalog schema. If any backup information which is aged out from the target database then you need to manually catalog those backup pieces.