Oracle LOGON trigger not working

Issue: Oracle Logon trigger based on schema or database level working only from sys & not working from any other schema

Trigger Code:

create or replace trigger restrictuser

  after LOGON ON ADMIN.SCHEMA

BEGIN

  if (sys_context('USERENV', 'SESSION_USER') = 'ADMIN' and

     sys_context('USERENV', 'OS_USER') = 'oracle9i') then

    RAISE_APPLICATION_ERROR(-20001, 'Sorry, you are not allowed here!');

  END if;

END;

Requirement:

Trigger should be created in non-sys schema to restrict OS_USER oracle9i from logging into DB user ADMIN

Archivelog Mode

Enabling database archive log mode has following benefits :

- Online database backup can be taken using RMAN or user managed backup method (hotbackup/copy) - Archive log files can be mined using LOGMINOR utility to track past changes which has caused damaged to database objects like mistakenly executed delete , update , insert operations or package/procedure/function alteration. LOGMINOR provides equivalent undo statement to roll back the DML operations & session info to identify the performer of such destructive activity 

- Archive log along with redo log enables database cloning & helps Recovery operations 

- Archive log are mandatory in replication environments like logical/physical standby 

Steps to enable Database Archivelog Mode:

Oracle database archivelog can be enabled in simple 3 steps as below …

Step1: Set archivelog re-requisite init parameters Online

log_archive_dest allows to specify the os file system destination for archive log file storage

 alter system set log_archive_dest_1='LOCATION=/archive/ATS';

 show parameter log_archive_dest_1

Oracle Silent Installation

In this Blog i will guide you to perform silent installation i.e. without using oracle installation GUI page.

Benefits:
Silent Installation is very much useful to avoid slowness caused by memory thirsty java GUI pages
Its is performed from command line & allows great control over installation steps
Less time consuming if re-start of the installation required
No hectic X-Window configuration

Background:
Silent installation method i have used for the first time while patching 10g (10.2.0.1) oracle home with 10.2.0.5 patch , it was very critical situation when i sat down for a production database 10205 upgrade & little bit over confident that GUI has to work while patching existing 10201 oracle home with 10205 as it worked in test environment .

Only different condition was that i was doing this activity from home via a VPN.

It was 5 Hrs of downtime , first 1 Hrs 30 min i struggle to open xclock itself which is basic GUI testing tool used before opening any GUI. Finally i gave up.I Called up my manager that i am unable to perform it , he suggested/reminded few steps to open GUI but that also failed , till then i had lost almost 2 Hrs of 5 Hrs downtime.

RMAN Backup Restoration example

This blog will guide you through the basic steps of Backup , Restoration & Recovery Using RMAN

Step1 : Online RMAN datafile , control-file & archive-log Backup at Source(MW4-S)
Step2 : Configuring Destination (IEWE-S) database server for restoration & recovery
Step3 : Starting up the instance at destination in nomount
Step4 : restoring control-file
Step5 : Cataloging Backup-sets
Step6 : restoring datafiles
Step7 : Recovery & Open resetlogs


MW4-S is the source database server & IEWE-S is destination where database will be cloned
Environment used is Oracle11g & OS platform Solaris 10 

Let's begin then ..

Step1 : Online RMAN datafile , control-file & archive-log Backup at Source(MW4-S)
Database should be in mount or open mode as a prerequisite of RMAN backup because in the absence of recovery catalog control file is the source of metadata to identify file locations & SCN (system change number , for consistency check)

Import dump using pipe

Background:

Export dump compression using pipe

Same as export using pipe, import can be done by following below steps

Import using Pipe

Step1

Create nods equal to dump files & point it to a pipe

mknod /u01/orabak/export/imp_pipe1 p

mknod /u01/orabak/export/imp_pipe2 p

mknod /u01/orabak/export/imp_pipe3 p