Oracle DBA Snippets 1 ……


I am now in the land of Oracle again. I am no way DBA class, I leave that to a really good third party www.inoapps.co.uk, but our budget does not run for many of the tasks that I am involved with at the minute, so I have had to delve into to the black art of the Oracle DBA :).

I know enough about Oracle to get me around, but somethings still need Google. The following bits are just random SQL snippets that I am using in the next few weeks. This really is for me more that anyone reading this.

NOTE:- most of the scripts need DBA access so from the console sqlplus / as sysdba, I have also noted when you are writing back so be careful.

Most of the  SQL was lifted from various websites, I will try to add the link when I remember as credit should be given to the guys who wrote it. So lets start :-

I had to do and RMAN duplicate and wanted to know the status and how much had been done.

http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=28128

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, 
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" 
FROM V$SESSION_LONGOPS 
WHERE OPNAME LIKE 'RMAN%' 
AND OPNAME NOT LIKE '%aggregate%' 
AND TOTALWORK != 0 
AND SOFAR <> TOTALWORK ;

 

To open a database after a duplicate you may need

alter database open resetlogs;

To see the status of a database

set lines 2000
select startup_time, status from v$instance;

 

To look at the Job Queue Processes and set to another number, this is the jobs run by the DMS_SCHEDULE

http://docs.oracle.com/cd/E10513_01/doc/install.310/e10496/db_install.htm#CIHBJIDB

WARNING UPDATING THE DATABASE

SELECT VALUE FROM v$parameter WHERE NAME = ‘job_queue_processes’;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0;
commit;

 

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s