Recovery using the SWITCH DATABASE TO COPY command

Recover database from copy

rm users*.dbf
 
rman target /
RMAN> shutdown abort
 
# Recover database from copy
RMAN> startup mount;
RMAN> switch database to copy;
RMAN> recover database;
RMAN> alter database open;
 
SQL> select name,status from v$datafile;

Recover tablespace from copy

rm users*.dbf
 
rman target /
 
# Recover tablespace from copy
RMAN> sql 'alter tablespace users offline immediate';
RMAN> switch tablespace users to copy;
RMAN> recover tablespace users;
RMAN> sql 'alter tablespace users online';
 
SQL> select file_name from dba_data_files where tablespace_name='USERS';
NAME
----------------------------
/data/users01.dbf
/backup/users02.dbf
 
 
# Move datafile from /backup/$ORACLE_SID to /data/$ORACLE_SID directory
# while the Database is online
SQL> alter tablespace users offline;
 
# Move the file /db_backup/ptdb1/users02.dbf using operating system command
cp /backup/users02.dbf /data/users02.dbf
 
# Update the database data dictionary
SQL> alter database rename file '/backup/users02.dbf' to '/data/users02.dbf';
 
# Take tablespace USERS online:
SQL> alter tablespace users online;
 
# Delete file from old location using operating system command
rm /backup/users02.dbf

Check tablespace size usage

Rewritten version from Top DBA Shell Scripts for Monitoring the Database based on MAXBYTES of each file within tablespace:

SELECT F.TABLESPACE_NAME,
       TO_CHAR ((T.ALLOCATED_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",
       TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
       TO_CHAR (T.TOTAL_SPACE - (T.ALLOCATED_SPACE - F.FREE_SPACE), '999,999') "FREE (MB)",
       TO_CHAR ((ROUND (((T.TOTAL_SPACE - (T.ALLOCATED_SPACE - F.FREE_SPACE))/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE
FROM   (
       SELECT       TABLESPACE_NAME,
                    ROUND (SUM (BLOCKS*(SELECT VALUE/1024
                                        FROM V$PARAMETER
                                        WHERE NAME = 'db_block_size')/1024)
                           ) FREE_SPACE
       FROM DBA_FREE_SPACE
       GROUP BY TABLESPACE_NAME
       ) F,
       (
       SELECT TABLESPACE_NAME,
       ROUND(SUM (MAXBYTES/1048576)) TOTAL_SPACE, 
       ROUND (SUM (BYTES/1048576)) ALLOCATED_SPACE
       FROM DBA_DATA_FILES
       GROUP BY TABLESPACE_NAME
       ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
--AND ROUND (((T.TOTAL_SPACE - (T.ALLOCATED_SPACE - F.FREE_SPACE))/T.TOTAL_SPACE)*100) < 10;

Restore archivelog from backup by logseq/scn

See details in RMAN effective use

set archivelog destination to '/disk1/oracle/temp_restore';
#restore archivelog from scn=460779 until scn =506141
restore archivelog from logseq 1 until logseq 10;

ADRCI usage examples

Usage example 1:

[oracle@host ~]$ adrci
adrci> show homes
set home diag/rdbms/orcl/orcl
# Output with enter to editor
show alert -p "ORIGINATING_TIMESTAMP >= '2014-02-24 00:00:00'"
# Output to termnal without enter to editor
show alert -p "ORIGINATING_TIMESTAMP >= '2014-02-24 00:00:00'" -term
# Show all 'ORA-%' and 'TNS-%' messages
show alert -p "ORIGINATING_TIMESTAMP >= '2014-02-24 00:00:00' AND (MESSAGE_TEXT LIKE '%ORA-%' or MESSAGE_TEXT LIKE '%TNS-%')" -term
# Show all 'ORA-%' and 'TNS-%' messages with exclude from output specified
show alert -p "ORIGINATING_TIMESTAMP > '2013-08-16 00:00:00' AND (MESSAGE_TEXT LIKE '%ORA-%' or MESSAGE_TEXT LIKE '%TNS-%') AND (MESSAGE_TEXT NOT LIKE '%TNS-12502%' AND MESSAGE_TEXT NOT LIKE '%TNS-12537%' AND MESSAGE_TEXT NOT LIKE '%ORA-609%')" -term
show alert -tail -f
exit

Usage example 2:

[oracle@host ~]$ adrci
adrci> show homes
set home diag/rdbms/orcl/orcl
show alert -tail -f
exit

Incident & Problem

adrci>
show problem
show incident
show incident -mode detail -p "incident_id=6201"
show trace /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc

Creation of Packages & ZIP files to send to Oracle Support

adrci>
ips create package problem 1 correlate all
ips generate package 2 in "/home/oracle"

Managing, especially purging of tracefiles

adrci>
show tracefile -rt
show control
set control (SHORTP_POLICY = 360)
set control (LONGP_POLICY = 2190)
show control
 
# Purge tracefiles manually
purge -age 2880 -type trace
show tracefile -rt
 
# Purge log directories for listener ADRCI Home
for i in `adrci exec="show homes"|grep listener`;do
# Purge ADRCI Homes log directories older 60 days ago
#for i in `adrci exec="show homes"|sed '1d'`;do
  du -hs $ORACLE_BASE/$i | sort -rh
  du -hs $ORACLE_BASE/$i/* | sort -rh
  du -hs $ORACLE_BASE/$i/trace | sort -rh
 
  # Purge listener log directory older 60 days ago
  # (60 days * 24h * 60 mins = 86400 mins)
  # -age  - The data older than  ago will be purged
  echo "adrci exec=\"set home $i;purge -age 86400\""
  adrci exec="set home $i;purge -age 86400";
  adrci exec="set home $i;show control;set control \(SHORTP_POLICY = 180\);set control \(LONGP_POLICY = 720\);show control";
  du -hs $ORACLE_BASE/$i | sort -rh
  du -hs $ORACLE_BASE/$i/* | sort -rh
  du -hs $ORACLE_BASE/$i/trace | sort -rh
done
# Check ADRCI Homes policy
for i in `adrci exec="show homes"|sed '1d'`;do
  adrci exec="set home $i;show control;";
done
# Set ADRCI Homes policy
for i in `adrci exec="show homes"|sed '1d'`;do
  adrci exec="set home $i;show control;set control \(SHORTP_POLICY = 180\);set control \(LONGP_POLICY = 720\);show control";
done

Read data from clob

Read data from clob:

declare
  v_body clob;
  offset number := 1;
  v_str varchar(32767);
  amount number := 32767;
begin
  v_body := rpad('Big clob data ', 32767 * 4, 'Big clob data ');
  while(offset <= dbms_lob.getlength(v_body)) loop
    dbms_lob.read(v_body, amount, offset, v_str);
 
    -- Operate with v_str:
    --dbms_output.put_line(v_str);
 
    offset := offset + amount;
  end loop;
end;