Turn on Oracle Audit

Step 1: Enable Oracle Audit (DB rebounce is required):

audit_trail = DB / DB_EXTENDED / OS / TRUE / FALSE / NONE

Note:
OS: Trace log will be recorded on $ORACLE_HOME/rdbms/audit
DB: Trace log will be recorded on sys.aud$.
While sys.aud$ data is changed, dba_audit_trail is updated accordingly.
DB = TRUE
DB_EXTENDED has two more column info than DB, eg. sql_text
FALSE = NONE

OS Audit Trail
Even if OS audit trail is not enabled, Oracle will still write default actions to OS audit trail:
* instance startup
* instance shutdown
* connections to the database as SYSOPER or SYSDBA

Step 2: 3 levels of audit options

Statement Auditing
Eg.
AUDIT SELECT BY SCOTT BY ACCESS WHENEVER SUCCESSFUL;
/* audits all select statements performed successfully by SCOTT *?

AUDIT SELECT TABLE, UPDATE TABLE BY SCOTT, BLAKE;
AUDIT SELECT ANY TABLE, UPDATE ANY TABLE BY SCOTT, BLAKE;

DataGuard Switchover & Failover

Status Check:

select FORCE_LOGGING, DATABASE_ROLE, PROTECTION_LEVEL, PROTECTION_MODE, OPEN_MODE, LOG_MODE from v$database;
archive log list;
select max(sequence#) from v$archived_log where applied='YES';
select process,status,sequence# from v$managed_standby;

Open Standby Database (read only):

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
alter database open read only;
alter database recover managed standby database disconnect from session;

Restart Standby Database:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate;
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;

SwitchOver Process:

On Primary:
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SHUTDOWN IMMEDIATE;
STARTUP nomount;
alter database mount standby database;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT from session;

On Standby:
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SHUTDOWN IMMEDIATE;
STARTUP;
select name, open_mode from v$database;
archive log list;

FailOver Process:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH SKIP STANDBY LOGFILE;
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SHUTDOWN IMMEDIATE;
STARTUP;
select name, open_mode from v$database;

Force FailOver Process:

SELECT name,value FROM v$parameter WHERE name='parallel_server';
NAME VALUE
-------------------- --------------------
parallel_server FALSE

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
alter database mount standby database;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
alter database mount;
alter database open;

Creating a Standby Database using RMAN - 10g - Unix

Pre-required/Assumes:

* On Primary:
* db_name = ctgbp
* db_unique_name = ctgbp

* On Standby:
* db_name = ctgbp
* db_unique_name = ctgbpdr

* Directory structure is the same on both primary and standby machines.

* TNSNAMES entry STANDBY points to standby db on both machines.

CTGBDR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.250.2)(PORT = 1521))
)
(CONNECT_DATA = (SID = ctgbp)
)
)

* TNSNAMES entry PROD points to prod db on both machines.

CTGBP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.65.51)(PORT = 1521))
)
(CONNECT_DATA = (SID = ctgbp)
)
)

* Backup is copying locally to disk at the same location as source_backup_directory

RMAN> show all;using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/ctgb/backup/ctgbp/control_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ctgb/backup/ctgbp/backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 2048 M;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ctgb/backup/ctgbp/snapcf_ctgbp.f';

1. Create Password File for Standby Database

Login to the standby database server and create a password file:
$ orapwd file=/u01/app/oracle/product/9.2.0/dbs/orapwctdbp password=change_on_install

2. Create a Standby Controlfile

Login to the Primary database server
$ ORACLE_SID=ctdbp; export ORACLE_SID
$ rman target /
RMAN> backup current controlfile for standby format='/orabackup/rman/ctdbp/stby_cfile.%U';

3. Record Last Log Sequence of Primary DB

$ sqlplus "/ as sysdba"
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
208

4. Make a full backup of Primary DB

$ rman target /
RMAN> backup database;

(2nd option:
Use old full backup of PROD; Create a Standby Controlfile; Backup New Archive Log Files (RMAN> backup archivelog;)
)

5. Initialization Parameter Setting

On Primary:
SQL> create pfile='/u01/app/oracle/product/9.2.0/dbs/initctdbp.ora' from spfile;

Copy the initialization parameter to the STDBY database, change at least the following parameters for the Standby database:

db_name='ctgbp'
db_unique_name='ctgbpdr'
fal_client = 'ctgbpdr' /* STDBY tnsname */
fal_server = 'ctgbp' /* PROD tnsname */
log_archive_dest_2='service=CTGBP optional reopen=300 valid_for=(online_logfiles,primary_role) db_unique_name=ctgbp'
log_archive_dest_state_2 = 'defer'

Change parameters on Primay db also:

db_name='ctgbp'
db_unique_name='ctgbp'
fal_client = 'ctgbp' /* PROD tnsname */
fal_server = 'ctgbp'dr /* STDBY tnsname */
log_archive_dest_1='location=/ctgb/flashback/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=ctgbp'
log_archive_dest_2='service=CTGBDR optional reopen=300 valid_for=(online_logfiles,primary_role) db_unique_name=ctgbdr'
log_archive_dest_state_2 = 'defer'

6. Start the Standby (Auxiliary) Instance

$ export ORACLE_SID=ctgbdr;
$ sqlplus "/ as sysdba"
SQL> startup nomount

7. Create Standby Database

Run the following:
$ rman target sys/password@PROD auxiliary sys/password@STDBY
RMAN>run {
# Set the last log sequence number
set until sequence = 208 thread = 1;
# Allocate the channel for the duplicate work
allocate auxiliary channel ch1 type disk;
# Duplicate the database to ORA920
duplicate target database for standby dorecover nofilenamecheck ;
}

(2nd option:
RMAN>run {duplicate target database for standby dorecover nofilenamecheck ;}

RMAN> exit;

8. Put the Standby in Managed Recovery Mode

On the standby database:
$ sqlplus "/ as sysdba"
SQL> recover standby database; /* optional */
SQL> alter database recover managed standby database disconnect;
Database altered.