Saturday, March 10, 2012

Executing Data Pump (EXPDP) from Physical Standby Database

Physical Standby Database:
SQL> alter database recover managed standby database cancel;

Production Database:

SQL> create database link dbstd_dblink
SQL> connect to scott identified by laser using 'dbstd';

SQL> show user
USER is "SCOTT"

SQL> conn sys/oracle@dbprd as sysdba
Connected.

SQL> grant read,write on directory data_pump_dir to scott;
Grant succeeded.

SQL> grant read,write on directory data_pump_dir to system;
Grant succeeded.

SQL> host

[oracle@nagpur Desktop]$ expdp directory=data_pump_dir dumpfile=scott.dmp log=scott.log network_link=dbstd_dblink
Export: Release 11.2.0.3.0 - Production on Tue Feb 21 19:36:18 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: scott/laser@dbprd

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=scott.log" Location: Command Line, Replaced with: "logfile=scott.log"
Legacy Mode has set reuse_dumpfiles=true parameter.
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@dbprd directory=data_pump_dir dumpfile=scott.dmp logfile=scott.log network_link=dbstd_dblink reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
. . exported "SCOTT"."SALGRADE"                    5.867 KB       5 rows
. . exported "SCOTT"."T1"                                   5.125 KB      11 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /cbsprddbs/oracle/admin/dbprd/dpdump/scott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:37:28

[oracle@nagpur Desktop]$

Restoration of scott schema in Primary database using the above taken backup:

SQL> select db_unique_name,name,database_role from v$database;

DB_UNIQUE_NAME               NAME     DATABASE_ROLE
------------------------------     ---------    ----------------
dbprd                                         dbPRD      PRIMARY

SQL> drop user scott cascade;
User dropped.

SQL> select username from all_users where username='SCOTT';
no rows selected

SQL> create user scott identified by laser default tablespace users;
User created.

SQL> grant connect,resource,create database link,debug connect session,debug any procedure to scott;
Grant succeeded.

SQL> host impdp directory=data_pump_dir dumpfile=scott.dmp log=impdp_scott.log remap_schema=scott:scott parallel=2
Import: Release 11.2.0.3.0 - Production on Tue Feb 21 20:38:07 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: sys/oracle@dbprd as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=impdp_scott.log" Location: Command Line, Replaced with: "logfile=impdp_scott.log"
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  sys/********@dbprd AS SYSDBA directory=data_pump_dir dumpfile=scott.dmp logfile=impdp_scott.log remap_schema=scott:scott parallel=2
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . imported "SCOTT"."EMP"                               8.570 KB      14 rows
. . imported "SCOTT"."SALGRADE"                    5.867 KB       5 rows
. . imported "SCOTT"."T1"                                   5.125 KB      11 rows
. . imported "SCOTT"."BONUS"                                 0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 20:38:25

The schema Scott is restored from the backup taken from the physical standby database.

No comments:

Post a Comment