Oracle DBA Snippets 1 ……Posted: March 6, 2014
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.
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
WARNING UPDATING THE DATABASE
SELECT VALUE FROM v$parameter WHERE NAME = ‘job_queue_processes’;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0;