Learn Database Concepts

  Steps to quickly rebuild of existing standby database: There are situations where you will have to rebuild your existing standby database as a result of  various situations like primary db was restored from backup with open reset logs. 1. Disable log shipping to standby database (that you want to rebuild "alter system set log_archive_dest_state_2=defer"). 2. Take full bakup from PRIMARY DB. 3. Take standby controlfile backup. 4. Copy backup and standby control file to standby server. 5. Drop datalafiles and controlfiles on standby Database. 6. Copy new standby control files to all controlfile locations. 7. Mount standby Database 8. Restore standby database. 8.  Enable log shipping to standby database(alter system set log_archive_dest_state_2=enable). 9. Recover managed standby database (on standby).

Import schema from full db expdp backup: In some situations you might want to restore a single schema from entire EXPDP backup. In this example I want to explain how to import a single schema from full DB expdp backup. Lets backup the full database using the EXPDP: F:\>expdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8 Export: Release 11.2.0.4.0 - Production on Tue May 17 14:55:21 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Password: UDI-28002: operation generated ORACLE error 28002 ORA-28002: the password will expire within 7 days 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 Master table "ATOORPU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded Starting "ATOORPU"."SYS_EXPORT_SCHEMA_01":  atoorpu/******** directory=dpump dump...

Lets take a full Backup: F:\>expdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8 Export: Release 11.2.0.4.0 - Production on Tue May 17 14:55:21 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Password: UDI-28002: operation generated ORACLE error 28002 ORA-28002: the password will expire within 7 days 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 Master table "ATOORPU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded Starting "ATOORPU"."SYS_EXPORT_SCHEMA_01":  atoorpu/******** directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8 . . . . . Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX . . exported "SCOTT1"."DEPT"                ...

SELECT   E.EMPNO,   E.ENAME,   E.JOB,   D.DEPTNO,   D.LOC,   E.SAL FROM   scott.emp E LEFT JOIN SCOTT.DEPT D ON   E.DEPTNO=D.DEPTNO; SELECT   E.EMPNO,   E.ENAME,   E.JOB,   D.DEPTNO,   D.LOC,   E.SAL,   (     SELECT      grade     FROM       SCOTT.SALGRADE S     WHERE       E.SAL BETWEEN S.LOSAL AND S.HISAL   ) AS SALGRADE FROM   scott.emp E LEFT JOIN SCOTT.DEPT D ON   E.DEPTNO=D.DEPTNO;

Method 1: set ORACLE_HOME=D:\app\oracle\product\11.2.0\db_1 set ORACLE_SID=ORCL sqlplus -s "USERNAME/PASSWORD" @C:\Shell\Drop_objs.sql sqlplus -s "USERNAME/PASSWORD" @C:\Shell\Change_pass.sql Method 2: set ORACLE_HOME=D:\app\oracle\product\11.2.0\db_1 set ORACLE_SID=ORCL (ECHO select username from dba_users where username in ('SCOTT'); ECHO exit;) | sqlplus -s "USER/PASS" > C:\Shell\test_out.txt