PLSQL command reference

To Make all Tablespace READ ONLY

begin
  for s in (select tablespace_name from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP')) loop
    execute immediate 'alter tablespace  "'||s.tablespace_name||'" read only';
  end loop;
end;
/

Make all tablespace NOLOGGING

begin
  for s in (select tablespace_name from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS01','UNDOTBS02','TEMP','TEMP_RRS') and LOGGING='LOGGING') loop
    execute immediate 'alter tablespace  "'||s.tablespace_name||'" NOLOGGING';
  end loop;
end;
/

To make all file autoextend on

begin
  for s in (select file_id from dba_data_files where autoextensible='NO') loop
    execute immediate 'alter database datafile '||s.file_id||' autoextend on';
  end loop;
end;
/

OS watcher Black box

OSWatcher (Includes: [Video]) (Doc ID 301137.1)





==> ./startOSWbb.sh 120 72

==> Setting the archive log directory to/opt/app/oswbb/oswbb/archive

Testing for discovery of OS Utilities...
VMSTAT found on your system.
IOSTAT found on your system.
MPSTAT found on your system.
NETSTAT found on your system.
TOP found on your system.

Testing for discovery of OS CPU COUNT
oswbb is looking for the CPU COUNT on your system
CPU COUNT will be used by oswbba to automatically look for cpu problems

CPU COUNT found on your system.
CPU COUNT = 32

Discovery completed.

Starting OSWatcher Black Box v7.1  on Tue Apr 29 09:44:48 EDT 2014
With SnapshotInterval = 120
With ArchiveInterval = 72

OSWatcher Black Box - Written by Carl Davis, Center of Expertise,
Oracle Corporation
For questions on install/usage please go to MOS (Note:301137.1)
If you need further assistance or have comments or enhancement
requests you can email me Carl.Davis@Oracle.com

Data is stored in directory: /opt/app/oswbb/oswbb/archive

Starting Data Collection...

oswbb heartbeat:Tue Apr 29 09:44:53 EDT 2014
oswbb heartbeat:Tue Apr 29 09:46:53 EDT 2014\




To collect OS Watcher files for a particular day use below command. 

# cd /opt/oracle.oswatcher/osw/archive
# find . -name '*13.03.15*' -print -exec zip /tmp/osw_`hostname`.zip {} \; 

{where 13- year 03- Month 15-day}

Real Application Command reference

Location of cluster alert log

$GRID_HOME/log/hostname



1. Stop crs on Node1 

crsctl stop crs 

if not stopping 

crsctl stop crs -f 

2. Remove the socket files on node1 

rm -rf /var/tmp/.oracle 
rm -rf /tmp/.oracle 


4. crsctl stat res -t -init 

5. crsctl stat res -t 

From 11.2.0.2 onwards, Cluster Health Monitor(CHM/OS) note 1328466.1 data can also be collected, i.e.:

# $GRID_HOME/bin/diagcollection.sh --chmos --incidenttime 04/24/201420:00:00 --incidentduration 05:00

This command will collect data from 2012-02-18 05:00 to 10:00 for 5 hours. incidenttime is specified as MM/DD/YYYY24HH:MM:SS, incidentduration is specified as HH:MM.

Upgrading OWB from 11.2.0.2 to 11.2.0.4

After I upgraded my database from 11.2.0.2 to 11.2.0.4 , I found that all the compoenents except Oracle Warehouse Builder was upgraded

Oracle Application Express : 4.2.4.00.08 : VALID
OWB : 11.2.0.2.0 : VALID
Oracle Enterprise Manager : 11.2.0.4.0 : VALID
OLAP Catalog : 11.2.0.4.0 : VALID
Spatial : 11.2.0.4.0 : VALID
Oracle Multimedia : 11.2.0.4.0 : VALID
Oracle XML Database : 11.2.0.4.0 : VALID
Oracle Text : 11.2.0.4.0 : VALID
Oracle Expression Filter : 11.2.0.4.0 : VALID
Oracle Rules Manager : 11.2.0.4.0 : VALID
Oracle Workspace Manager : 11.2.0.4.0 : VALID
Oracle Database Catalog Views : 11.2.0.4.0 : VALID
Oracle Database Packages and Types : 11.2.0.4.0 : VALID
JServer JAVA Virtual Machine : 11.2.0.4.0 : VALID
Oracle XDK : 11.2.0.4.0 : VALID
Oracle Database Java Packages : 11.2.0.4.0 : VALID
OLAP Analytic Workspace : 11.2.0.4.0 : VALID
Oracle OLAP API : 11.2.0.4.0 : VALID

I am sure that our application is not using OWB , but still I wanted have it upgraded to latest version .

All we did was :

@?/owb/UnifiedRepos/clean_owbsys.sql

@?/owb/UnifiedRepos/cat_owb.sql

and done

COMP_NAME||':'||VERSION||':'||STATUS

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

OWB : 11.2.0.4.0 : VALID

Automatic Stats Jobs

script to check stats job stats

for 11g
_________________________________________________________________________________________________________
set pages 999
set lines 180
col client_name for a35
col JOB_STATUS for a10
col JOB_START_TIME for a47
col JOB_DURATION for a15
select client_name, job_status, job_start_time, job_duration from dba_autotask_job_history
where client_name='auto optimizer stats collection' order by job_start_time;



for 10g
_______________________________________________________________________________________________________________________________

set pages 999
set lines 300
col JOB_NAME for a25
col STATUS for a10
col ACTUAL_START_DATE for a40
col RUN_DURATION for a15
select JOB_NAME,STATUS,ACTUAL_START_DATE,RUN_DURATION from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name ='GATHER_STATS_JOB' order by ACTUAL_START_DATE;

For 11g
Troubleshooting Scheduler Autotasks issues (Doc ID 1561498.1)

How to Force Table Statistics to be Gathered on Every Execution of Automatic Statistics and GATHER_DATABASE_STATS jobs (Doc ID 1490594.1)
exec dbms_stats.set_table_prefs('SCOTT','EMP','STALE_PERCENT',0);

BEGIN
dbms_auto_task_admin.enable();
END;

• How to enable auto stats collection?

  If for some reason automatic optimizer statistics collection is disabled, you can enable it using the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
  
  

  exec DBMS_AUTO_TASK_ADMIN.ENABLE(
   client_name => 'auto optimizer stats collection', 
   operation => NULL, 
   window_name => NULL);

• How to disable the auto stats collection?

  In situations when you want to disable automatic optimizer statistics collection, you can disable it using the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
  
  

  exec DBMS_AUTO_TASK_ADMIN.DISABLE(
   client_name => 'auto optimizer stats collection', 
   operation => NULL, 
   window_name => NULL);

• How can I check the status of the 'auto optimizer stats collection'?

  The status of the automatic statistics collection can be checked using:
  
  

  select client_name, JOB_SCHEDULER_STATUS 
   from DBA_AUTOTASK_CLIENT_JOB
   where client_name='auto optimizer stats collection';

  
  The possible Job status:
  
  • DISABLED
  • RETRY SCHEDULED
  • SCHEDULED
  • RUNNING
  • COMPLETED
  • BROKEN
  • FAILED
  • REMOTE
  • SUCCEEDED
  • CHAIN_STALLED

• How can I check whether or not the database has the 'auto optimizer stats collection' job enabled to run during the next maintenance window?

  SELECT CLIENT_NAME,
         STATUS
  FROM   DBA_AUTOTASK_CLIENT
  WHERE  CLIENT_NAME = 'auto optimizer stats collection';

• How can I see the history of the automatic stats job for each day?

  
  

  SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed
   FROM dba_autotask_client_history
   WHERE client_name like '%stats%';
   
                                                    JOBS    JOBS     JOBS
   CLIENT_NAME                     WINDOW_NAME      CREATED STARTED  COMPLETED
   ------------------------------- ---------------- ------- -------- ----------
   auto optimizer stats collection THURSDAY_WINDOW        1        1          1
   auto optimizer stats collection SUNDAY_WINDOW          3        3          3
   auto optimizer stats collection MONDAY_WINDOW          1        1          1
   auto optimizer stats collection SATURDAY_WINDOW        2        2          2

• How to manually execute the Optimizer Statistics Auto Task?

  
  In 11g the Auto-Task infrastructure replaced the need for the gather_stats_job and you can execute the following command to accomplish manual statistics collection:
  
  

  SQL> exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;

RMAN command reference

LIST COMMAND

RMAN> LIST ARCHIVELOG ALL;
RMAN> LIST BACKUP OF ARCHIVELOG ALL;
RMAN> LIST BACKUP;
RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST BACKUP OF DATAFILE 1;
RMAN> LIST BACKUP SUMMARY;
RMAN> LIST INCARNATION;
RMAN> LIST BACKUP BY FILE;
RMAN> LIST COPY OF DATABASE ARCHIVELOG ALL;
RMAN> LIST COPY OF DATAFILE 1, 2, 3;
RMAN> LIST BACKUP OF DATAFILE 11 SUMMARY;
RMAN> LIST expired Backup of archivelog all summary;
RMAN> LIST Backup of tablespace Test summary;
RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437;
RMAN> LIST CONTROLFILECOPY “/tmp/cntrlfile.copy”;
RMAN> LIST BACKUPSET OF DATAFILE 1;
RMAN> LIST FAILURE;
RMAN> LIST FAILURE 641231 detail;
RMAN> LIST Backup of Controlfile;
RMAN> LIST Backup of Spfile;
RMAN> LIST Backup of Tablespace Test;
RMAN> LIST expired Backup;
RMAN> LIST expired Backup summary;
RMAN> LIST expired Backup of Archivelog all;
RMAN> LIST expired Backup of datafile 10;
RMAN> LIST recoverable backup;


COLD BACKUP


run
{
allocate channel d1 device type disk;
allocate channel d2 device type disk;
allocate channel d3 device type disk;
allocate channel d4 device type disk;
backup database as compressed backupset format '/dumps/DCOE01PR/cold_before_upgrade/dcoe01pr_DB_b4_upgrade_%u_%D_%M';
Backup current controlfile format '/dumps/DCOE01PR/cold_before_upgrade/dcoe01pr_CF_b4_upgrade_%u_%D_%M'; 
release channel d1;
release channel d2;
release channel d3;
release channel d4;

}



spool log to rman_debug_restore01.log
spool trace to rman_debug_restore01.trc
set echo on
run
{
allocate channel d1 device type disk;
allocate channel d2 device type disk;
allocate channel d3 device type disk;
allocate channel d4 device type disk;
allocate channel d5 device type disk;
allocate channel d6 device type disk;
allocate channel d7 device type disk;
allocate channel d8 device type disk;
allocate channel d9 device type disk;
allocate channel d10 device type disk;
allocate channel d11 device type disk;
allocate channel d12 device type disk;
allocate channel d13 device type disk;
allocate channel d14 device type disk;
allocate channel d15 device type disk;
allocate channel d16 device type disk;
debug all;
restore database check readonly;
debug off;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
release channel d7;
release channel d8;
release channel d9;
release channel d10;
release channel d11;
release channel d12;
release channel d13;
release channel d14;
release channel d15;
release channel d16;
}
spool trace off

spool log off

restore database check readonly;