RMAN Backup Scripts to device backup strategy

Backup database and archivelogs:

allocate channel for maintenance device type disk;
delete force backup completed before 'sysdate - 1 + 1';
crosscheck archivelog all;
run {
allocate channel c1 type DISK ;
allocate channel c2 type DISK ;
backup
database
plus archivelog;
 }


Backup Archive logs:

RMAN> connect catalog *
run {
 allocate channel c1 type SBT PARMS="ENV=(NB_ORA_CLIENT=client_backup_interface,NB_ORA_POLICY=policy_name)";
allocate channel c2 type SBT PARMS="ENV=(NB_ORA_CLIENT=client_backup_interface,NB_ORA_POLICY=policy_name)";
backup
archivelog all ;
}


Delete Archivelog backed up 1 times to device type SBT and completed couple of days ago:

RMAN> connect catalog *
allocate channel for maintenance device type disk;
delete force archivelog all backed up 1 times to device type SBT completed before 'sysdate - 2';


Backup backupset to Tape:

RMAN> connect catalog *
 run {
allocate channel c1 type 'SBT' ;
 allocate channel c2 type 'SBT' ;
 backup backupset completed after "to_date('MM/DD/YYYY HH24:MI:SS','MM/DD/YYYY HH24:MI:SS')"
 ;
 }

Delete Obsolete Backups:

RMAN> connect catalog *
 allocate channel for maintenance device type DISK PARMS="ENV=(NB_ORA_CLIENT=client_backup_interface,NB_ORA_POLICY=policy_name)";
 crosscheck archivelog all;
 crosscheck backup;
 delete force expired archivelog all;
 delete force expired backup;
 allocate channel for maintenance device type SBT PARMS="ENV=(NB_ORA_CLIENT=client_backup_interface,NB_ORA_POLICY=policy_name)";
 delete force obsolete device type SBT;

Incrementally Updated Backups:

 run {
    allocate channel c1 type DISK;
    allocate channel c2 type DISK;
    recover copy of database with tag 'incr_update';
    delete force backup tag='DISKONLY';
    backup  incremental level 1 tag='DISKONLY' for recover of copy with tag 'incr_update'
    database;
 }

Flashback

Primary Database Flashback:
============================

create restore point gold;
flashback database to restore point gold;
alter database open resetlogs;


Standby Database Flashback:
============================

Convert this standby database to snapshot standby using this simple command:

SQL> alter database convert to snapshot standby;

SQL> shutdown immediate
ORA-01507: database not mounted
...
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

database opned in R/W mode and we can make changes in this database.

After your testing is completed, you would want to convert the snapshot standby database back to a regular physical standby database. Just follow the steps shown below:

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

...
Database mounted.
SQL> alter database convert to physical standby;

Database altered.

Now shutdown, mount the database and start managed recovery.

SQL> shutdown
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
...
Database mounted.

Start the managed recovery process:

SQL> alter database recover managed standby database disconnect;

Automatic Diagnostic Repository (ADR)

ADR is a simple file-based (directory structured) repository that contains diagnostic data generated from Oracle. It is a collection of items such as trace files, dumps, alert logs, etc. that can be used by administrators to investigate problems and the health of the database. ADR is the new unified directory structure that will hold all diagnostic data from all Oracle products and components—simplifying the search and correlation of information across all aspects of a single or multiple database instance.

ADRCI (ADR Command interface)introduced in Oracle Database Release 11g.

View diagnostic data within the Automatic Diagnostic Repository (ADR).

View Health Monitor reports.

Package incident and problem information into a zip file for transmission to Oracle Support.

Diagnostic data includes incident and problem descriptions, trace files, dumps, health monitor reports, alert log entries, and more.

ADRCI has a rich command set, and can be used in interactive mode or within scripts. In addition, ADRCI can execute scripts of ADRCI commands in the same way that SQL*Plus executes scripts of SQL and PL/SQL commands.

ADR data is secured by operating system permissions on the ADR directories, hence there is no need to log in to ADRCI.

adrci> help

HELP [topic]
Available Topics:
CREATE REPORT
ECHO
EXIT
HELP
HOST
IPS
PURGE
RUN
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL

There are other commands intended to be used directly by Oracle, type
"HELP EXTENDED" to see the list

adrci>
adrci> show alert

ADR Home = c:\oracle\sysadm\diag\rdbms\us\us:
*************************************************************************
Output the results to file: c:\temp\alert_3968_5208_us_3.ado
adrci>

How to monitor physical standby database

SQL> select * from v$dataguard_stats;

NAME VALUE UNIT TIME_COMPUTED
-------------------------------- ------------------------------ ------------------------------ ------------------------------
apply finish time +00 00:00:00.5 day(2) to second(1) interval 09-JUN-2009 13:37:22
apply lag +00 00:00:50 day(2) to second(0) interval 09-JUN-2009 13:37:22
estimated startup time 15 second 09-JUN-2009 13:37:22
standby has been open N 09-JUN-2009 13:37:22
transport lag +00 00:00:07 day(2) to second(0) interval 09-JUN-2009 13:37:22

$cat check_log_apply.sh
export ORACLE_SID=pfds1
echo $ORACLE_SID
/u01/sq/ora_1/oracle/db/10.2.0/bin/sqlplus -s "/ as sysdba" @/u01/sq/ora_1/oracle/db/10.2.0/scripts/check_log_apply.sql

$cat check_log_apply.sql
set head off
set feed off
spool /u12/log_apply_$ORACLE_SID.log
select substr(VALUE,5,2) from v$dataguard_stats where NAME='apply lag';
exit
spool off


#!/bin/ksh
###############################################################################
# Author: Saminathan Seerangan
###############################################################################

FIELD1=`cat /u12/log_apply_pfds1.log`
echo $FIELD1
if test $FIELD1 -ge 10
then
mailx -s "Log apply delay for PFDS " my_phone_number@vtext.com fi

How to attach or detach ORACLE_HOME to inventory

/u01/sq/ora_1/oracle/db/10.2.0/oui/bin/runInstaller -silent -attachHome -invPtrLoc /u01/sq/ora_1/oracle/oraInst.loc ORACLE_HOME="/u01/sq/ora_2/oracle/db/10.2.0" ORACLE_HOME_NAME="Superstack10g_10204" CLUSTER_NODES="{njqp1dsmdbc03,njqp1dsmdbc04}" -local


/u01/sq/ora_1/oracle/db/10.2.0/oui/bin/runInstaller -silent -detachHome -invPtrLoc /u01/sq/ora_1/oracle/oraInst.loc ORACLE_HOME="/u01/sq/ora_2/oracle/db/10.2.0" ORACLE_HOME_NAME="Superstack10g_10204" CLUSTER_NODES="{njqp1dsmdbc03,njqp1dsmdbc04}" -local

opatch lsinventory -detail -oh /u01/cr/ora_1/oracle/crs/10.2.0

Tablespace- How much I can shrick?

select
a.tablespace_name,
a.file_id,
a.file_name,
a.bytes file_size_in_bytes,
(c.block_id+(c.blocks-1)) * &block_size HWM_BYTES,
a.bytes - ((c.block_id+(c.blocks-1)) * &block_size) SAVING
from dba_data_files a,
(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b,
dba_extents c
where a.file_id = b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by 6;

Rebuild Standby Database

How to rebuild standby database from Primary?

Rebuild or Recover Standby Database

Backup Primary DB using RMAN or use the existing RMAN backup if the backup copy is relatively new.

Copy RMAN backup pieces to Standby DB host. Make sure directory structure is same as Primary DB host.


Shutdown standby database;

Create backup controlfile and standby control from primary.

ALTER DATABASE BACKUP CONTROLFILE TO '/tmp/dbname.ctl’;

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/s_dbname.ctl';


Copy both control file to Standby DB server.

Replace the existing control file on Standby DB with Primary DB control file that we copied(dbname.ctl, not s_dbname.ctl)

On Standby DB side

$ rman

Recovery Manager: Release 9.2.0.6.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target

connected to target database (not started)

RMAN>startup mount;

In Standby host, run the following RMAN command.
RMAN>run {
allocate channel c1 type disk;
allocate channel c2 type disk;
restore database;
}

SQL>shutdown immediate;

Copy archive logs that are generated after RMAN backup from Primary to standby DB server.

scp oracle@primat_machine:/u05/archive/DBNAME/0002S000000079*.ARC oracle@standby_machine:/u05/archive/DBNAME/

Replace the existing controlfile with STANDBY controlfile (s_dbname.ctl);


SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;


SQL> recover automatic standby database;

check the alert log while the above command is running

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;




standby_machine oracle DBNAME /opt/oracle/admin/DBNAME/bdump
$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Jan 23 15:48:43 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 252709304 bytes
Fixed Size 731576 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 319488 bytes
SQL> alter database mount standby database;

Database altered.

SQL> recover automatic standby database;
ORA-00279: change 105230635 generated at 01/23/2007 12:28:59 needed for thread
1
ORA-00289: suggestion : /u05/archive/DBNAME/0001S0000001248.ARC
ORA-00280: change 105230635 for thread 1 is in sequence #1248
ORA-00278: log file '/u05/archive/DBNAME/0001S0000001248.ARC' no longer needed
for this recovery
ORA-16145: archival for thread# 1 sequence# 1248 in progress


Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 105270320 generated at 01/23/2007 12:57:47 needed for thread
1
ORA-00289: suggestion : /u05/archive/DBNAME/0001S0000001249.ARC
ORA-00280: change 105270320 for thread 1 is in sequence #1249
ORA-00278: log file '/u05/archive/DBNAME/0001S0000001248.ARC' no longer needed
for this recovery


ORA-00279: change 105270379 generated at 01/23/2007 12:57:50 needed for thread
2
ORA-00289: suggestion : /u05/archive/DBNAME/0002S0000000799.ARC
ORA-00280: change 105270379 for thread 2 is in sequence #799
ORA-00278: log file '/u05/archive/DBNAME/0002S0000000798.ARC' no longer needed
for this recovery


ORA-00279: change 105344106 generated at 01/23/2007 13:26:49 needed for thread
1
ORA-00289: suggestion : /u05/archive/DBNAME/0001S0000001250.ARC
ORA-00280: change 105344106 for thread 1 is in sequence #1250
ORA-00278: log file '/u05/archive/DBNAME/0001S0000001249.ARC' no longer needed
for this recovery


ORA-00279: change 105395671 generated at 01/23/2007 13:49:06 needed for thread
1
ORA-00289: suggestion : /u05/archive/DBNAME/0001S0000001251.ARC
ORA-00280: change 105395671 for thread 1 is in sequence #1251
ORA-00278: log file '/u05/archive/DBNAME/0001S0000001250.ARC' no longer needed
for this recovery


ORA-00332: archived log is too small - may be incompletely archived
ORA-00334: archived log: '/u05/archive/DBNAME/0001S0000001251.ARC'


SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

standby_machine oracle DBNAME /opt/oracle/admin/DBNAME/bdump
$ ps -ef|grep -i mrp
oracle 6350 1 1 16:26:59 ? 0:11 ora_mrp0_DBNAME1
oracle 7092 22916 0 16:28:35 pts/3 0:00 grep -i mrp

SQL background execution

cat exec.sh
x=`${ORACLE_HOME}/bin/dekr /export/home/oracle/scripts/dba/.Schedulejobs/perf.dat`
${ORACLE_HOME}/bin/sqlplus -s <<EOS
username/password
set pages 200 lines 130
set long 50000
start script_name.sql
exit;
EOS

Statspack Purge Snapshot Job

Statspack Purge Snapshot Job
=============================
create PROCEDURE "PERFSTAT"."PURGE_SNAPS" as
begin

delete from perfstat.stats$snapshot
where snap_time < (sysdate-30)
and rownum < 101;

while sql%found
loop

commit;

delete from perfstat.stats$snapshot
where snap_time < (sysdate-30)
and rownum < 101;

end loop;

commit;

end;
/



exec dbms_job.submit(:jobno, 'perfstat.purge_snaps;', trunc(sysdate+7), 'trunc(sysdate+7)', TRUE, 1);

Resource Intensive Query

select s.sid,s.serial#,s.username,s.program,s.sql_hash_value,s.taddr
from v$session s,v$process p
where s.paddr=p.addr and p.spid=&spid;

select sql_text from v$sql where hash_value in (
select s.sql_hash_value
from v$session s,v$process p
where s.paddr=p.addr and p.spid=&spid);