Learn Database Concepts

RMAN> report obsolete; using target database control file instead of recovery catalog RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of obsolete backups and copies Type                 Key    Completion Time    Filename/Handle -------------------- ------ ------------------ -------------------- Archive Log          183    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_140_896707677.arc Archive Log          189    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_141_896707677.arc Archive Log          190    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_145_896707677.arc Archive Log          191    16-FEB-16         RMAN...

Simple example for restore and recover database from RMAN backup. Assuming that : I have a need to restore my DB as some important tables/data was dropped/removed and its really mission critical. I have already got some RMAN backups from past. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1728053248 bytes Fixed Size                  3046512 bytes Variable Size            1224737680 bytes Database Buffers          486539264 bytes Redo Buffers               13729792 bytes Database mounted. C:\Users\oracle>rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 19 12:46:12 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved. connected to target database: ORCLDB (DBID=1196XXXX70) RMA...

Perform a switch over test: ON PRIMARY DB SERVER : SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database; NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE --------- -------------------- ------------------------------ ---------------- ORCL  READ WRITE       ORCL      PRIM ON STANDBY DB SERVER : SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database; NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE --------- -------------------- ------------------------------ ---------------- ORCL  MOUNTED       ORCLSTB1      PHYSICAL STANDBY LETS CONNECT TO DGMGRL AND SWITCH OVER ROLES DGMGRL> switchover to 'ORCLSTB1'; Performing switchover NOW, please wait... Operation requires a connection to instance "ORCLSTB1" on database "ORCLSTB1" Connecting to instance "ORCLSTB1"... Connec...

If you have combination of multiple columns that you want to check duplicates.  For example  : Check duplicates for combination of   AGE,NAME,SEX,DOB,CITY Sql will be : select AGE,NAME,SEX,DOB,CITY,count(1)  from Employees  group by AGE,NAME,SEX,DOB,CITY  having count(1) >1;

-- Simple trigger to audit to audit basic schema changes : --- CREATE TABLE TO STORE AUDIT DATA CREATE TABLE DDL_AUDIT_LOG (   STAMP DATE , USERNAME VARCHAR2(30 BYTE) , OSUSER VARCHAR2(30 BYTE) , MACHINE VARCHAR2(30 BYTE) , TERMINAL VARCHAR2(30 BYTE) , OPERATION VARCHAR2(30 BYTE) , OBJTYPE VARCHAR2(30 BYTE) , OBJNAME VARCHAR2(30 BYTE) , OBJ_OWNER VARCHAR2(30 BYTE) ) TABLESPACE USERS ; -- NOW CREATE TRIGGER TO AUDIT CHANGES ALTER TRIGGER AUDIT_DDL_CHANGES DISABLECREATE TRIGGER AUDIT_DDL_CHANGES    AFTER create OR drop OR alter       ON ATOORPU.SCHEMA  -- Change SCOTT to your schema name!!!       -- ON DATABASE BEGIN   INSERT INTO ddl_audit_log VALUES         (SYSDATE,          SYS_CONTEXT('USERENV', 'SESSION_USER'),          SYS_CONTEXT('USERENV', 'OS_USER'),          SYS_CONTEXT('USERENV', 'HOST'),          SYS_CON...

Most of the legacy mode options will work in the data pump with its corresponding options. In legacy mode (imp utility) we have show=y option to spool the content of the dump file into sql scripts without doing the actual import. The same command can be used in data pump impdp also but it will get replaced with sqlfile option. oracle@Linux01:[/u01/app/oracle/dpump] $ expdp atoorpu directory=DPUMP dumpfile=test1.dmp logfile=test1.log tables=TEST Export: Release 11.2.0.4.0 - Production on Wed Feb 3 15:22:00 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "ATOORPU"."SYS_EXPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=test1.dmp logfile=test1.log tables=TEST Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TA...

Most of the legacy mode options will work in the data pump with its corresponding options. In legacy mode (imp utility) we have show=y option to spool the content of the dump file into sql scripts without doing the actual import. The same command can be used in data pump impdp also but it will get replaced with sqlfile option. oracle@Linux01:[/u01/app/oracle/dpump] $ expdp atoorpu directory=DPUMP dumpfile=test1.dmp logfile=test1.log tables=TEST Export: Release 11.2.0.4.0 - Production on Wed Feb 3 15:22:00 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "ATOORPU"."SYS_EXPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=test1.dmp logfile=test1.log tables=TEST Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TA...