How to do schema refresh in Oracle
1) datapump
2) Transportable Tablespace
We use datapump if the schema size is less in size. The datapump automatically copies the PL/SQL code , JAVA classes , Sequences etc to the destination database which is not possible with the Transportable Tablespace(in 12c we can do this). Below is the schema refresh using the first method.
1) First check the tablespaces in use by the schemas in the source and destination.
SQL> select tablespace_name from dba_segments where owner=’DEV’;
TABLESPACE_NAME
——————————
DEV
SQL> select tablespace_name from dba_segments where owner=’PROD’;
TABLESPACE_NAME
——————————
PROD
2) Check the privilages and roles assigned in PROD and DEV.
======= Privialges at the DEV side =============
SQL> select GRANTEE,GRANTED_ROLE from dba_role_privs where GRANTEE=’DEV’;
GRANTEE GRANTED_ROLE
—————————— ——————-
DEV DBA
select ‘GRANT ‘||GRANTED_ROLE||’ TO ‘||GRANTEE||’;’ from dba_role_privs where GRANTEE=’DEV’;
‘GRANT’||GRANTED_ROLE||’TO’||GRANTEE||’;’
———————————————
GRANT DBA TO DEV;
SQL> select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE=’DEV’;
GRANTEE PRIVILEGE
—————————— ———————-
DEV CREATE SESSION
DEV UNLIMITED TABLESPACE
SQL> select ‘GRANT ‘||PRIVILEGE||’ TO ‘||GRANTEE||’;’ from dba_sys_privs where GRANTEE=’DEV’;
‘GRANT’||PRIVILEGE||’TO’||GRANTEE||’;’
——————————————————————————-
GRANT CREATE SESSION TO DEV;
GRANT UNLIMITED TABLESPACE TO DEV;
SQL> select GRANTEE,TABLE_NAME,PRIVILEGE from dba_tab_privs where GRANTEE=’DEV’;
GRANTEE TABLE_NAME PRIVILEGE
———— —————————- ———
DEV TEST SELECT
DEV TEST INSERT
select ‘GRANT ‘||PRIVILEGE||’ ON OWNER.’|| TABLE_NAME || ‘ TO ‘ ||GRANTEE||’;’ from dba_tab_privs where GRANTEE=’DEV’;
—————————————————–
GRANT SELECT ON TEST.TEST TO DEV;
GRANT INSERT ON TEST.TEST TO DEV;
GRANT UPDATE ON TEST.TEST TO DEV;
======= Privialges at the production side =============
SQL> select GRANTEE,GRANTED_ROLE from dba_role_privs where GRANTEE=’PROD’;
GRANTEE GRANTED_ROLE
—————————— ———————
PROD RESOURCE
SQL> select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE=’PROD’;
GRANTEE PRIVILEGE
—————————— ———————
PROD UNLIMITED TABLESPACE
PROD CREATE SESSION
SQL> select GRANTEE,TABLE_NAME,PRIVILEGE from dba_tab_privs where GRANTEE=’PROD’;
no rows selected
3) Estimate the size of the backup using the expdp and check the filesystem which have the required space.
[oracle@node2 tmp]$ expdp schemas=PROD estimate_only=YES
Export: Release 11.2.0.1.0 – Production on Wed Mar 9 22:10:23 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting “SYS”.”SYS_EXPORT_SCHEMA_01″: /******** AS SYSDBA schemas=PROD estimate_only=YES
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated “PROD”.”PROD_TABLE” 64 KB
Total estimation using BLOCKS method: 64 KB
Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 22:10:57
As this is a testing I need very less space.
4) Create the directory in the file system where space exists and oracle have write permissions.
[oracle@node2 tmp]$ cd /u01/
[oracle@node2 u01]$ mkdir DATAPUMP
[oracle@node2 u01]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 9 22:13:40 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> create directory PROD_BKP as ‘/u01/DATAPUMP’;
Directory created.
5) take the export backup of the schema.
[oracle@node2 u01]$ expdp directory=PROD_BKP schemas=PROD logfile=PROD_BKP.log dumpfile=PROD_BKP.dmp
Export: Release 11.2.0.1.0 – Production on Wed Mar 9 22:25:29 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting “SYS”.”SYS_EXPORT_SCHEMA_01″: /******** AS SYSDBA directory=PROD_BKP schemas=PROD logfile=PROD_BKP.log dumpfile=PROD_BKP.dmp
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “PROD”.”PROD_TABLE” 5.015 KB 1 rows
Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/DATAPUMP/PROD_BKP.dmp
Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 22:26:39
6) Copy the backup to the Destination server in the filesystem where you have enough space. Create a directory like point 4 as above.
7) drop the existing schema in the DEV. # as we need the same schema as production.
SQL> drop user DEV cascade;
User dropped.
8) import the schema backup took at production.
[oracle@node2 u01]$ impdp directory=PROD_BKP schemas=PROD logfile=PROD_IMP.log dumpfile=PROD_BKP.dmp remap_schema=PROD:DEV remap_tablespace=PROD:DEV
Import: Release 11.2.0.1.0 – Production on Wed Mar 9 22:38:08 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Master table “SYS”.”SYS_IMPORT_SCHEMA_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_SCHEMA_01″: /******** AS SYSDBA directory=PROD_BKP schemas=PROD logfile=PROD_IMP.log dumpfile=PROD_BKP.dmp remap_schema=PROD:DEV
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
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 “DEV”.”PROD_TABLE” 5.015 KB 1 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYS”.”SYS_IMPORT_SCHEMA_01″ successfully completed at 22:38:17
NOTE :-
*) Now we imported the schema but we can create with the old name “DEV” by using the remap_schema. But change the password after import as this is created with PROD password.
*) we have done remap_tablespace from PROD to DEV as tablespaces are different and we have gathered the information in the first step.
9) Now if you want the privilages granted to DEV before import , grant the same privilages as we have gathered the same in second step.
SQL> GRANT DBA TO DEV;
Grant succeeded.
SQL> GRANT SELECT ON TEST.TEST TO DEV;
GRANT INSERT ON TEST.TEST TO DEV;
GRANT UPDATE ON TEST.TEST TO DEV;
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
Viswamitra's blog 