Creating Physical Standby Database

• OS and platform must be the same as primary db's
•    Install soft and database with identical home directories
• Enable forced logging on your primary database:

SQL> ALTER DATABASE FORCE LOGGING;

select force_logging from v$database;

• Create password file on primary database if there is no already one. And copy it to stb server.

 Select * from v$pwfile_users;

$cd $ORACLE_HOME/dbs

$orapwd file=pwdRKRUS.ora password=xxxxxxxx force=y

• Enable Archiving on Primary. 

If your primary database is not already in Archive Log mode, enable the archive log mode: SQL>shutdown immediate; SQL>startup mount; SQL>alter database archivelog; SQL>alter database open; SQL>archive log list;

• Set Primary Database Initialization Parameters

db_name="RKRUS" instance_name="RKRUS" log_archive_dest_1='LOCATION=/u05/oradata/RKRUS/arch' log_archive_dest_2='SERVICE=rkrusstb LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RKRUSstb'   log_archive_format='log%t_%s_%r.arc'

•  Prepare Stanby Initialization Parameters (copy from primary DB and edit the following parameters)

db_name="RKRUS" db_unique_name="RKRUSstb" instance_name="RKRUS" log_archive_dest_1='LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,STANDBY_ROLE)' standby_file_management='AUTO' standby_archive_dest='/u01/arch' fal_server='rkrus' fal_client='rkrusstb' local_listener='NURSTB_LISTENERS'

•  Create standby controlfile on primary db

sqlplus / as sysdba alter database create standby controlfile as ‘/tmp/ctl.stb’;

•  Ensure that the standby database is down
• Copy and rename the control file to RKRUSstb to the directories defined in the init file on the standby database

/u01/oradata/rkrus/control01.ct, /u02/oradata/rkrus/control02.ctl"

• (RKRUSstb) Mount the database

sqlplus / as sysdba

 startup mount;

• (RKRUSstb)  Restore from backup

rman target / nocatalog;

run{

ALLOCATE CHANNEL ch1 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=nurbcvdb1,NB_ORA_POLICY=sol_oracle_stb,NB_ORA_SERV=netbackup)';

ALLOCATE CHANNEL ch2 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=nurbcvdb1,NB_ORA_POLICY=sol_oracle_stb,NB_ORA_SERV=netbackup)';

restore database;

}

•  Start the Media Recovery Process(MRP)

sqlplus / as sysdba

alter database recover managed standby database disconnect;

Trigger to log DROP operations on schema

create TABLE drop_log (
user_name     VARCHAR2(30),
machine varchar(30),
ddl_date      DATE,
ddl_type      VARCHAR2(30),
object_type   VARCHAR2(18),
owner         VARCHAR2(30),
object_name   VARCHAR2(128))
;


CREATE OR REPLACE TRIGGER drop_trig
before drop
on kpi.schema
declare
machine varchar(30);

BEGIN
 select machine into machine from v$session where sid = ( select sid from v$mystat where rownum=1 );

  INSERT INTO drop_log
  (user_name, machine, ddl_date, ddl_type,
   object_type, owner,
   object_name)
  VALUES
  (ora_login_user, machine, SYSDATE, ora_sysevent,
   ora_dict_obj_type, ora_dict_obj_owner,
   ora_dict_obj_name);
END drop_trig;
/

select * from drop_log;