Disaster Recovery using RMAN

I couldn’t do this justice, so I’ll just post the link:

http://www.orafusion.com/art_rman3.htm

RMAN point in time recovery

run {
allocate channel c1 type disk;
set until time “to_date(‘07/14/09 19:00′, ‘mm/dd/yy hh24:mi:ss’)”;
restore database;
recover database;
alter database open resetlogs;
}

Satrting and Stopping a RAC Cluster

From : http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle11gRAC/CLUSTER_10.shtml

At this point, everything has been installed and configured for Oracle RAC 11g. We have all of the required software installed and configured plus we have a fully functional clustered database.
With all of the work we have done up to this point, a popular question might be, “How do we start and stop services?”. If you have followed the instructions in this article, all services should start automatically on each reboot of the Linux nodes. This would include Oracle Clusterware, all Oracle instances, Enterprise Manager Database Console, etc.

There are times, however, when you might want to shutdown a node and manually start it back up. Or you may find that Enterprise Manager is not running and need to start it. This section provides the commands responsible for starting and stopping the cluster environment.

C:> hostname

n1

Stopping the Oracle RAC 11g Environment

The first step is to stop the Oracle instance. Once the instance (and related services) is down, then bring down the ASM instance. Finally, shutdown the node applications (Virtual IP, GSD, TNS Listener, and ONS).
C:> set ORACLE_SID=orcl1
C:> emctl stop dbconsole
C:> srvctl stop instance -d orcl -i orcl1
C:> srvctl stop asm -n n1
C:> srvctl stop nodeapps -n n1

Starting the Oracle RAC 11g Environment

The first step is to start the node applications (Virtual IP, GSD, TNS Listener, and ONS). Once the node applications are successfully started, then bring up the ASM instance. Finally, bring up the Oracle instance (and related services) and the Enterprise Manager Database console.
C:> set ORACLE_SID=orcl1
C:> srvctl start nodeapps -n n1
C:> srvctl start asm -n n1
C:> srvctl start instance -d orcl -i orcl1
C:> emctl start dbconsole

Start / Stop All Instances with SRVCTL

Start / Stop all of the instances and its enabled services. I just included this for fun as a way to bring down all instances!
C:> srvctl start database -d orcl

C:> srvctl stop database -d orcl

Verify RAC Cluster and Database Configuration

From : http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle11gRAC/CLUSTER_10.shtml

The following RAC verification checks should be performed on both Oracle RAC nodes in the cluster!

Overview

This section provides srvctl commands and SQL queries to validate a RAC configuration.
There are five node-level tasks defined for SRVCTL:

1. Adding and deleting node level applications.
2. Setting and unsetting the environment for node-level applications.
3. Administering node applications.
4. Administering ASM instances.
5. Starting and stopping a group of programs that includes virtual IP addresses, listeners, Oracle Notification Services, and Oracle Enterprise Manager agents (for maintenance purposes).

Status of all instances and services

$ srvctl status database -d orcl
Instance orcl1 is running on node n1
Instance orcl2 is running on node n2

Status of a single instance

$ srvctl status instance -d orcl -i orcl2
Instance orcl2 is running on node n2

Status of node applications on a particular node

$ srvctl status nodeapps -n n1
VIP is running on node: n1
GSD is running on node: n1
Listener is running on node: n1
ONS daemon is running on node: n1

Status of an ASM instance

$ srvctl status asm -n n1
ASM instance +ASM1 is running on node n1.

List all configured databases

$ srvctl config database
orcl

Display configuration for our RAC database

$ srvctl config database -d orcl
n1 orcl1 /u01/app/oracle/product/11.1.0/db_1
n2 orcl2 /u01/app/oracle/product/11.1.0/db_1

Display the configuration for node applications – (VIP, GSD, ONS, Listener)

$ srvctl config nodeapps -n n1 -a -g -s -l
VIP exists.: /n1-vip/192.168.1.200/255.255.255.0/eth0
GSD exists.
ONS daemon exists.
Listener exists.

Display the configuration for the ASM instance(s)

$ srvctl config asm -n n1
+ASM1 /oracle/product/11.1.0/db_1

All running instances in the cluster

SELECT
inst_id
, instance_number inst_no
, instance_name inst_name
, parallel
, status
, database_status db_status
, active_state state
, host_name host
FROM gv$instance
ORDER BY inst_id;

INST_ID  INST_NO INST_NAME  PAR STATUS  DB_STATUS    STATE     HOST
-------- -------- ---------- --- ------- ------------ --------- -------
1        1 orcl1      YES OPEN    ACTIVE       NORMAL n1
2        2 orcl2      YES OPEN    ACTIVE       NORMAL n2

All data files which are in the disk group

select name from v$datafile
union
select member from v$logfile
union
select name from v$controlfile
union
select name from v$tempfile;

NAME
-------------------------------------------
+FLASH_RECOVERY_AREA/orcl/controlfile/current.256.641647319
+FLASH_RECOVERY_AREA/orcl/onlinelog/group_1.257.641647335
+FLASH_RECOVERY_AREA/orcl/onlinelog/group_2.258.641647359
+FLASH_RECOVERY_AREA/orcl/onlinelog/group_3.259.641655841
+FLASH_RECOVERY_AREA/orcl/onlinelog/group_4.260.641655867
+ORCL_DATA1/orcl/controlfile/current.256.641647315
+ORCL_DATA1/orcl/datafile/example.263.641647479
+ORCL_DATA1/orcl/datafile/indx.270.641663255
+ORCL_DATA1/orcl/datafile/sysaux.260.641647411
+ORCL_DATA1/orcl/datafile/system.259.641647373
+ORCL_DATA1/orcl/datafile/undotbs1.261.641647427
+ORCL_DATA1/orcl/datafile/undotbs2.264.641647497
+ORCL_DATA1/orcl/datafile/users.265.641647521
+ORCL_DATA1/orcl/datafile/users.269.641663137
+ORCL_DATA1/orcl/onlinelog/group_1.257.641647323
+ORCL_DATA1/orcl/onlinelog/group_2.258.641647349
+ORCL_DATA1/orcl/onlinelog/group_3.266.641655831
+ORCL_DATA1/orcl/onlinelog/group_4.267.641655855
+ORCL_DATA1/orcl/tempfile/temp.262.641647449

19 rows selected.

All ASM disk that belong to the ‘ORCL_DATA1′ disk group

SELECT path
FROM v$asm_disk
WHERE group_number IN (select group_number
from v$asm_diskgroup
where name = ‘ORCL_DATA1′);

PATH
----------------------------------
ORCL:VOL1
ORCL:VOL2

To Kill a user session

The NT Approach

To kill the session via the NT operating system, first identify the session as follows:

SELECT s.sid,
       p.spid,
       s.osuser,
       s.program
FROM   v$process p,
       v$session s
WHERE  p.addr = s.paddr
ORDER by 1;

       SID SPID      OSUSER                         PROGRAM
---------- --------- ------------------------------ ---------------
         1 310       SYSTEM                         ORACLE.EXE
         2 300       SYSTEM                         ORACLE.EXE
         3 309       SYSTEM                         ORACLE.EXE
         4 299       SYSTEM                         ORACLE.EXE
         5 302       SYSTEM                         ORACLE.EXE
         6 350       SYSTEM                         ORACLE.EXE
        20 412       SYSTEM                         DBSNMP.EXE
        43 410       USER1                          SQLPLUSW.EXE
        33 364       USER2                          SQLPLUSW.EXE

The SID and SPID values of the relevant session can then be substituted into the following command issued from the command line:

C:> orakill ORACLE_SID spid

The session thread should be killed immediately and all resources released.

The normal way is to kill the sid,serial# from v$session:

sql> alter system kill session 'sid,serial#' immediate;

Undo corruption – Needs Recovery

I had a problem the other day with a missing UNDO tablespace.  I created a new one, however, the segments in the old tablespace were set to NEEDS RECOVERY.  Do not attempt this on a production database.  If you have the same problem, even on a test instance, you should contact Oracle Support.

DO NOT TRY THIS AT “HOME” : http://forums.oracle.com/forums/thread.jspa?threadID=698075

JDBC Connect String – RAC

This is the JDBC connect string syntax for use when RAC is involved:

jdbc:oracle:thin:@(DESCRIPTION=

     (ADDRESS=(PROTOCOL=TCP)(HOST=rac-a)(PORT=1521))

     (ADDRESS=(PROTOCOL=TCP)(HOST=rac-b)(PORT=1521))

     (FAILOVER=on)

     (CONNECT_DATA=

          (SERVER=DEDICATED)

          (SERVICE_NAME=orcl)

     )

)

Add to RMan Catalog

catalog start with  ’D:\my_rman_backup_location’;

List RMan Backups

list backup summary;

Restore Database with RMan

run {
allocate channel c1 type disk;
restore database;
recover database;
}