Latest Entries »


this query will show the jobs running in the database

SET LINESIZE 80
column job format 9999
column log_user format A10
column next_date format A9
column next_sec format A8
column interval format A15
column what format A26

– scheduled jobs
select job, log_user, next_date, next_sec, interval, what
from dba_jobs;

–completed/failed jobs
select job, log_user, last_date, last_sec, broken,failures
from dba_jobs;


To find the sql text of session

select a.sql_text from v$sql a,v$session b , v$process c where a.sql_id=b.sql_id and b.paddr=c.addr and c.spid= ‘1234’;


using this query we can find the sid,serial# and pid of the session currently running.

set linesize 200
SELECT
a.sid,
a.serial#,
b.spid,
b.pid,
a.username,
a.osuser,
a.machine
FROM
v$session a,
v$process b
WHERE
a.username IS NOT NULL
AND
a.paddr=b.addr order by sid;


Cold or Physical Backup steps for production server :: prd11

Terminal #1:

<?php the_title() ?>

login in as oracle application user in oracle application server

$ Login :

$ Password:

$ cd $ORACLE_HOME

$ cd opmn

$ cd bin

$ opmnctl stopall

In the Oracle Enterprise Manager

open database “prd11” - change the tablespace sizes

tempfile size as 1000 MB

Terminal #2:

login in as oracle user in oracle database server::

$ Login:

$ Password:

$ lsnrctl stop

$ sqlplus '/as sysdba'

prd11 > alter system checkpoint;

> alter system switch logfile;

> shutdown immediate

Terminal #3:

login in as oracle user in oracle database server::

$ Login:

$ Password:

$ ORACLE_SID=<ORACLE_SID>; export ORACLE_SID

$ sqlplus “/as sysdba”

SQL> alter system checkpoint;

> alter system switch logfile;

> shutdown immediate;

> exit

Terminal #4:

login in as root user in oracle database server::

$ Login: as root

$ Password:

go to datafiles location

$ cd /data_disk1

$ cd dbf10g

$ pwd

he we have two cold backup locations. go for any one

$ cp * (/data_disk2/coldbackup/) or (/data_disk3/coldbackup/)

Terminal #5:

login in as root user in oracle database server::

$ Login: as root

$ Password:

go to control and redo log location

$ cd /oracle/ora10g/oracle/product/10.2.0/oradata/prd11

$ pwd

$ cp * (/data_disk2/coldbackup/) or (/data_disk3/coldbackup/)

Terminal #6:

login in as oracle user in oracle database server::

$ Login:

$ Password:

goto arch location

$ cd /arch10g

$ rm *.arc

$ ls

after completion of the backup in Terminal #4 & Terminal #5

Terminal #2

prd11 > startup

> exit

$ lsnrctl start

$ exit

Terminal #3

ORCL> startup

> exit

In the Oracle Enterprise Manager

open database “prd11” - change tempfile size as 27100MB

Terminal #1

$ pwd

/opmnctl/bin

$ opmnctl startall

$ exit

while taking the cold backup we can see the shutdown & startup in the alertlog file with the following command.

$ cd $ORACLE_HOME/admin/prd11/bdump

$ tail -f alert_prd11.log

to see the listener status after the startup of the db, use the command

$ lsnrctl status

to see the current listener no. use the command

$ ps -ef | grep lsnr

to see the db pmon use the command

$ ps -ef | grep pmon

to check the db instance is running or not type the command in SQL

$ sqlplus '/as sysdba'

> select instance_name, status from v$instance;

Hello world!


Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!

Follow

Get every new post delivered to your Inbox.