Oradebug

Oradebug utility can be used to trace a user session. To trace a session its OS process ID is required which can be obtained by joining v$session and v$process views as shown below:

SQL> select s.username,S.sid,S.serial#,spid

 2  from v$session s, v$process p

 3  where s.username=p.username

 4  and s.paddr=p.addr

 5  and s.username='HNAIK';



USERNAME                              SID    SERIAL# SPID

------------------------------ ---------- ---------- ------------

HNAIK                                 154          4 5548

Now login to the database as SYSDBA: 

C:\Documents and Settings\hnaik> sqlplus / as sysdba



SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 29 14:37:40 2009



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





Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Start the tracing:

SQL> oradebug setospid 5548

Oracle pid: 20, Windows thread id: 5548, image: ORACLE.EXE (SHAD)



SQL> oradebug event 10046 trace name context forever,level 8;

Statement processed.

Stop the tracing:

SQL> oradebug event 10046 trace name context off;

Following command identifies the trace file that is created….

SQL> oradebug tracefile_name

c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_5548.trc

Oracle Documentation Library

Below page contains documents of all the versions of Oracle Database:

http://www.oracle.com/technology/documentation/index.html

--

Basic Grid Control commands

[oracle@naikh]export OMS_HOME=/u01/oracle/product/oms10g
[oracle@naikh]export AGENT_HOME=/u01/oracle/product/agent10g

Starting Grid Control Services

$AGENT_HOME/bin/emctl start agent
$OMS_HOME/opmn/bin/opmnctl startall


Stopping Grid Control Services

$AGENT_HOME/bin/emctl stop agent
$OMS_HOME/opmn/bin/opmnctl stoptall


Status of the Grid Control Services

$AGENT_HOME/bin/emctl status agent
$OMS_HOME/bin/emctl status oms
$OMS_HOME/opmn/bin/opmnctl status

Rebooting RAC servers

For some maintenance activity, if RAC servers need to be rebooted then the steps are:

1. Check the status of all the services
[oracle@naikh] cd $CRS_HOME/bin
[oracle@naikh] crs_stat -t

2. Stop the database
[oracle@naikh] srvctl stop database -d orcl

3. Stop nodeapps
[oracle@naikh] srvctl stop nodeapps hostname1
[oracle@naikh] srvctl stop nodeapps hostname2

4. Reboot the servers
Ask System admin to reboot the both the servers

5. Once the server is up check the status of all the services
[oracle@naikh] cd $CRS_HOME/bin
[oracle@naikh] crs_stat -t

6. Start the nodeapps if not automatically started.
[oracle@naikh] srvctl start nodeapps hostname1
[oracle@naikh] srvctl start nodeapps hostname2

7. Start the database if not automatically started.
[oracle@naikh] srvctl start database -d orcl

8. Make sure all the services are active
[oracle@naikh] cd $CRS_HOME/bin
[oracle@naikh] crs_stat -t

RMAN Recovery Catalog

This article gives the steps to create the recovery catalog.

1. Identify the database where catalog needs to be created.

Here I am using rcvcat as the catalog database

2. Create the tablespace to store the catalog information.

SQL> CREATE TABLESPACE CATALOG_DATA
DATAFILE '/u01/oradata/rvcvat/catalog_data_01.dbf'
SIZE 500M;

3. Create the user that is going to be the catalog owner

SQL> CREATE USER RMAN
IDENTIFIED BY RMAN
DEFAULT TABLESPACE CATALOG_DATA
TEMPORARY TABLESPACE TEMP;

4. Grant the roles and the privileges

SQL> GRANT RESOURCE TO RMAN;
SQL> GRANT CONNECT TO RMAN;

SQL> GRANT RECOVERY_CATALOG_OWNER TO RMAN;

SQL> ALTER USER RMAN DEFAULT ROLE ALL;
SQL> ALTER USER RMAN QUOTA UNLIMITED ON RMAN_DATA;

5. Create the catalog

[oracle@naikh]$ rman catalog rman/rman@rvccat
RMAN> create catalog;

6. Register the target database

[oracle@naikh]$ export ORACLE_SID=orcl

[oracle@naikh]$ rman target / catalog rman/rman@rvccat

RMAN> register database;

Applying RMAN Incremental Backup To Standby Database

We can use RMAN incremental backup to roll forward the physical standby database in the following situations:
1. when an archive sequence is missing
2. when lots of archives needs to be applied

Steps:

1. Take the current SCN of the standby database

SQL> select to_char(current_scn) scn from v$database;
SCN
---------------
23339995

2. Stop the redo apply on the standby database

SQL> alter database recover managed standby database cancel;

3. Take the incremental backup of the production database from the current SCN of the standby database

RMAN> run {
allocate channel c1 type disk format '/backup/%d_incr_s%s_p%p' maxpiecesize 2g;
allocate channel c2 type disk format '/backup/%d_incr_s%s_p%p' maxpiecesize 2g;
allocate channel c3 type disk format '/backup/%d_incr_s%s_p%p' maxpiecesize 2g;
allocate channel c4 type disk format '/backup/%d_incr_s%s_p%p' maxpiecesize 2g;
backup as compressed backupset incremental from scn 23339995 database;
}

4. Transfer the incremental backup to the standby system

$ scp /backup/* usename@standbyhost:/tmp/

5. Catalog all the incremental backup pieces to the standby database

RMAN> catalog start with '/tmp/' ;

6. Apply incremental backup to the standby database

RMAN> recover database noredo;

7. Create new standby controlfile at the production database, copy this to standby system and replace current standby controlfile with this new one.

SQL> alter database create standby controlfile as '/backup/standby_incr.ctl';

8. Start redo apply on the physical standby database

SQL> alter database recover managed standby database disconnect;


Note:
If you have added new datafile to the production and if it is not at created at standby you have to restore the datafile.
RAMN> restore datafile 56,57,58;

Password Expiration Notification

This is a simple script to notify about password expiration to users.

#!/bin/bash

# Set the environment variables
export ORACLE_HOME=/opt/oracle/product/102/DB
export ORALCE_SID=oradb
export PATH=$PATH:$ORACLE_HOME/bin

# Declar the vaiable
NOTIFY_LIST=user_name@abc.com
DIFF_FILE=/tmp/pass_exp.log

# Get expiry date
EXP_DATE=`chage -l oracle grep "Password Expires" awk '{print $4"-"$3"-"$5}'sed 's/,//'`

# Calculte in how many days password will expire
sqlplus usr/pass@$ORACLE_SID<<>
--CREATE TABLE pwd_expire (expire_date date);
INSERT INTO pwd_expire values (TO_DATE('$EXP_DATE','DD-MON-YYYY'));
COMMIT;
SET ECHO OFF FEEDBACK OFF
SPOOL $DIFF_FILE
SELECT ROUND(expire_date-sysdate) diff FROM pwd_expire;
SPOOL OFF
--DROP TABLE pwd_expire;
TRUNCATE TABLE pwd_expire;
EXIT

EOF

DAYS=`tail -2 $DIFF_FILEhead -1awk '{print $1}'`


if [ $DAYS -le 5 ]
then
mailx -s "Oracle Password will expires in $DAYS" $NOTIFY_LIST < /dev/null
fi

AXEL: Download accelerator for Linux

AXEL is a download accelerator for Linux. This utility also can be used to transfer files from one machine to another.

Download:
axel -n 16 -a http://download.com/download_file.gz

File Transfer:
axel -n 16 -a ftp://username/password@remote_host//temp/expdp_naikh_01.dmp

axel -n 16 -a ftp://username/password@remote_host//temp/expdp_naikh*

Applying Oracle Patchset

Aplying patchset p5337014_10203_LINUX.zip to upgrade from 10201 to 10203.

1. Shut down the database
SQL> shut immediate

2. Stop the listener
$ lsnrctl stop

3. Unzip the patchset and run OUI
$ ./runinstaller

4. Start the listener
$ lsnrctl start

5. Run the following commnads
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF

6. Restart the database
SQL> SHUT IMMEDIATE
SQL> STARTUP

7. Run the utlrp.sql script to recompile all invalid PL/SQL packages
SQL> @?/rdbms/admin/utlrp.sql