select  a.target_name as HOST,
to_char(a.rollup_timestamp,'dd-Mon-yy::hh24:mi') as "DAY-TIME",
sum(c.cpu_count) as "CPU COUNT" ,
round((sum(a.maximum*c.cpu_count)/ sum(c.cpu_count)),1) as "MAXIMUM CPU %",
round((sum(a.minimum*c.cpu_count)/ sum(c.cpu_count)),1) as "MINIMUM CPU %",
round((sum(a.average*c.cpu_count)/ sum(c.cpu_count)),1) as "AVERAGE CPU %"
from
mgmt$metric_hourly a ,
mgmt$target b ,
sysman.MGMT_ECM_HW c
where    a.metric_name = 'Load'
and      a.column_label = 'CPU Utilization (%)'
and      a.target_guid = b.target_guid
and      a. target_type = 'host'
and      c.hostname||'.'||c.domain = b.target_name
and rollup_timestamp > sysdate-31
and a.target_type='host'
and c.cpu_count !=0
--and round((sum(a.maximum*c.cpu_count)/ sum(c.cpu_count)),1) is not null
group by a.target_name, a.target_type,a.rollup_timestamp
order by 1;

DAILY REFRESH

sqlplus / nolog <<EOM

CONNECT / AS SYSDBA

STARTUP MOUNT EXCLUSIVE RESTRICT;
DROP DATABASE;

STARTUP FORCE NOMOUNT PFILE=/PATH/PFILE';

RMAN auxilary / log=rmandb.log <<EOF

run
{
DUPLICATE TARGET DATABASE TO <DBNAME> UNTIL TIME 'SYSDATE-24'
BACKUP LOCATION '/XYZ/ABC';
}
EOF

REFRESH

sqlplus / nolog <<EOM

CONNECT / AS SYSDBA

STARTUP FORCE NOMOUNT
WHENEVER SQLERROE EXIT FAILURE
SET TIMING ON
EXIT
EOF

RMAN TARGET SYS/PASSWORD catalog rman/password@catdb
auxilary / log=rmandb.log <<EOF

run
{
set untiltime "to_date('31-JUL-15 22:00:00','DD-MON-YY HH24:MI:SSS')";DUPLICATE TARGET DATABASE TO <DBNAME>;'
}
EOF

STEPS TO RESTORE THE DB FROM BACKUP
====================================

1)Backups are located in /u01/tmp location

2)Start the db in nomount stage

startup nomount pfile='$ORACLE_HOME/dbs/initxyzdb.ora';

3) Restore the controlfile

4) rman> set dbid=123456;

5) rmna> restore controlfile from backup;

6) start the db in mount stage

7) catalog the files.

catalog start with '/u01/tmp';

8) restore th db

rman target / cmdfile= abc.rman log=/tmp/abc.log

run
{
set newname to data file 1 to '/u01/xyz_1.dbf';
set newname to data file 2 to '/u01/xyz_2.dbf';
--
--
restore database
switch datafile all;
}

9) Create the controlfile
10) Alter database open reset logs;
11) add the temp file
12)creat the spfile;
13)shutdown and start the db with spfile;

DDL With the WAIT Option (DDL_LOCK_TIMEOUT) :

ORA-00054: resource busy" error

DDL commands require exclusive locks on internal structures. If these locks are not available the commands return with an "ORA-00054: resource busy" error message. This can be especially frustrating when trying to modify objects that are accessed frequently. To get round this Oracle 11g includes the DDL_LOCK_TIMEOUT parameter, which can be set at instance or session level using the ALTER SYSTEM and ALTER SESSION commands respectively.

The DDL_LOCK_TIMEOUT parameter indicates the number of seconds a DDL command should wait for the locks to become available before throwing the resource busy error message. The default value is zero.

ALTER SESSION SET ddl_lock_timeout=30;

Unusable indexes

Oracle indexes can go into a UNUSABLE state after maintenance operation on the table or if the index is marked as 'unusable' with an ALTER INDEX command. A direct path

load against a table or partition will also leave its indexes unusable.
Queries and other operations agains a table with unuable indexes will generate errors:


ORA-01502: index ‘string.string’ or partition of such index is in unusable state

#################################################################################
unusable indexes
#################################################################################
select OWNER,OBJECT_TYPE,status,count(*) from dba_objects where STATUS='!VALID' group by OWNER,OBJECT_TYPE,status;
select OWNER,OBJECT_TYPE,status,count(*) from dba_objects where status='INVALID' group by OWNER,OBJECT_TYPE,status;
SELECT owner,index_name,TABLE_OWNER,TABLE_NAME,PARTITIONED,TABLESPACE_NAME,STATUS
 FROM all_indexes
 WHERE owner NOT IN ('SYS', 'SYSTEM')
  AND status != 'VALID'
  AND
  (
   status != 'N/A'
   OR index_name IN
   (
    SELECT index_name
     FROM all_ind_partitions
     WHERE status != 'USABLE'
      AND
      (
       status != 'N/A'
       OR index_name IN
       (
        SELECT index_name
         FROM all_ind_subpartitions
         WHERE status != 'USABLE'
       )
      )
   )
  );

Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME ||';' from USER_IND_PARTITIONS;
Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from USER_IND_PARTITIONS;
Select 'ALTER INDEX '||INDEX_OWNER||'.'|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from DBA_IND_PARTITIONS
where INDEX_OWNER='ERXUATOWNER' and status='UNUSABLE';
Select 'ALTER INDEX '||INDEX_OWNER||'.'|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from DBA_IND_PARTITIONS
where INDEX_OWNER='ERXUATOWNER' and status='UNUSABLE';

#################################################################################
The following SQL comands can be used to detect unusable indexes:

Indexes:
SELECT owner, index_name, tablespace_name
FROM   dba_indexes
WHERE  status = 'UNUSABLE';

Index partitions:

SELECT index_owner, index_name, partition_name, tablespace_name
FROM   dba_ind_PARTITIONS WHERE  status = 'UNUSABLE';


The following SQL will print out a list of alter commands that can be executed to fix unusable indexes:

Indexes:

SELECT 'alter index '||index_name||' rebuild tablespace '||tablespace_name ||';'
FROM   dba_indexes WHERE  status = 'UNUSABLE';

Index partitions:

SELECT 'alter index '||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';' FROM   dba_ind_partitions WHERE  status = 'UNUSABLE';

Generic:

1. Installation
2. Upgradation
3. DB creation
4. Apply the patch

ASM:

1. How to create the ASM database?
2. How to install software
3. How to upgrade ASM software
4. How to apply the patch

RAC

1.Steps to Install RAC software
2.Steps to create RAC database

Standby:

1. How to build the Physical standby?
2. How to build the Logical standby?
3. How to create the Snapshot standby
4. How to convert Physical standby to Active standby?
5. How to build the Physical stabdby using duplicate command?

Cloning:

1. How to clone the database with RMAN

2. EXPORT AND IMPORT