Logical Recovery - Schema Refresh

Let us discuss the steps involved during schema refresh using datapump.

Prerequisite to check on Before Export
ON SOURCE SIDE
1. Check the Schema Exist or Not. Schema must be there in source database :

    col username for a10
    col account_status for a10
    col created for a20
    select username, account_status, created from dba_users where username='TES';
    USERNAME   ACCOUNT_ST CREATED
    ---------- ---------- --------------------
    TES	   OPEN       18-NOV-23

2. Check the schema size :

    SQL> select sum(bytes/1024/1024) "in MB" from dba_segments where owner='TES';

    in MB
    ----------
    4.0625

3. Check the privileges and roles :

    system privilege
    set pagesize 0
    set linesize 200
    select 'grant '||privilege||' to '||grantee||' ; ' from dba_sys_privs where grantee in ('TES');
    no rows selected

Object Privilege :

    select 'grant '||privilege||' on '||OWNER||'.'||table_name||' to '||grantee||' ; ' from dba_tab_privs WHERE OWNER in ('TES');
    no rows selected

Roles :

   
    set pagesize 0
    set linesize 200
    select 'grant '||granted_role||' to '||grantee||' ; ' from dba_role_privs where grantee in ('TES')

    'GRANT'||GRANTED_ROLE||'TO'||GRANTEE||';'
    --------------------------------------------------------------------------------
    grant CONNECT to TES ;
    grant RESOURCE to TES ;

4. Have to check the default tablespace of user :

    col username for a10
    col default_tablespace for a20
    col temporary_tablespace for a20
    select username,default_tablespace,temporary_tablespace from dba_users where username='TES'

    USERNAME   DEFAULT_TABLESPACE	TEMPORARY_TABLESPACE
    ---------- -------------------- --------------------
    TES	   TEST 		TEMP

5.To Check where the schema data are present across available tablespaces :

    select distinct tablespace_name,sum(bytes)/(1024*1024) MB from dba_segments where owner = 'TES' group by tablespace_name
    TABLESPACE_NAME 		       MB
    ------------------------------ ----------
    USERS				      .25
    TEST				   3.81255.

Quotas :

    SQL> select * from dba_ts_quotas where username='TES';

    TABLESPACE_NAME 	       USERNAME        BYTES  MAX_BYTES     BLOCKS
    ------------------------------ ---------- ---------- ---------- ----------
    MAX_BLOCKS DRO
    ---------- ---

USERS			       TES	      262144   52428800 	32
      6400 NO

TEST			       TES	     3997696   52428800        488
      6400 NO

6. Check the objects count of user :

    select object_type,count(*) from dba_objects where owner='TES' group by object_type

    OBJECT_TYPE		  COUNT(*)
    ----------------------- ----------
    PROCEDURE			 4
    TABLE				 7
    INDEX				 2

7. Before going to take the export :
Thing you have to check is mount point size where you’re going to store the export dumpfile, if mount point doesn’t have sufficient space export job gets fail.

8. Create directory :
Before creating directory make sure the directory exists in os level.

    SQL> create directory test_dump_dir as '/home/oracle/test';   
    Directory created.

9. Create Directory Object and Grant Read and Write Privileges on The Directory :
Directory object is a pointer pointing over a directory(which is created above) which you want your expdp utility to use when storing all exported files.

    SQL> grant read,write on DIRECTORY test_dump_dir to tes;
    Grant succeeded.

(grant read,write permission to user you want to perform export. Above I mentioned ‘tes’ user for example)

10. Now you can start the actual export operation for schema :
For single schema :

expdp tes/tes directory=test_dmp_dir SCHEMAS=tes dumpfile=user.dmp logfile=user.log parallel=4
Export: Release 12.2.0.1.0 - Production on Sat Nov 18 12:43:47 2023

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "TES"."SYS_EXPORT_SCHEMA_01": tes/******** directory=test_dump_dir SCHEMAS=tes dumpfile=user.dmp logfile=user.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "TES"."T3" 932.3 KB 87001 rows
. . exported "TES"."T1" 639.5 KB 60000 rows
. . exported "TES"."T2" 327.4 KB 30001 rows
. . exported "TES"."EMP" 8.773 KB 14 rows
. . exported "TES"."ACCOUNTS" 8.492 KB 7 rows
. . exported "TES"."U20" 5.992 KB 5 rows
. . exported "TES"."TXN" 0 KB 0 rows
Master table "TES"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TES.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/test/user.dmp
Job "TES"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Nov 18 12:44:23 2023 elapsed 0 00:00:35
for exporting multiple schema
Move the DUMPFILES to TARGET using scp server where you want to import
Note: Dont give password in above command instead mention it during runtime.mkdir

On TARGET SIDE
1. Check the mount point size, it should be more than schema size.

    [oracle@sdbt ~]$ df -h
    Filesystem      Size  Used Avail Use% Mounted on
    devtmpfs        829M     0  829M   0% /dev
    tmpfs           847M     0  847M   0% /dev/shm
    tmpfs           847M   34M  813M   4% /run
    tmpfs           847M     0  847M   0% /sys/fs/cgrhhhhoup
    /dev/sda2        56G   46G   11G  82% /
    tmpfs           170M   44K  170M   1% /run/user/54321

2. Create Directory Object ad Grant Read and Write Privileges on The Directory for import purpose :

    SQL> create directory test_dump_dir as '/home/oracle/test';
    Directory created.
    SQL> grant read,write on DIRECTORY test_dump_dir to public;
    Grant succeeded.

3. Now we can start import :

    [oracle@sdbt test]$ impdp directory=test_dump_dir dumpfile=user.dmp logfile=user1.log 

    Import: Release 12.2.0.1.0 - Production on Sat Nov 18 15:28:11 2023

    Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

    Username: / as sysdba

    Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
    Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA directory=test_dump_dir dumpfile=user.dmp logfile=user1.log 
    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 "TES"."T3"                                  932.3 KB   87001 rows
    . . imported "TES"."T1"                                  639.5 KB   60000 rows
    . . imported "TES"."T2"                                  327.4 KB   30001 rows
    . . imported "TES"."EMP"                                 8.773 KB      14 rows
    . . imported "TES"."ACCOUNTS"                            8.492 KB       7 rows
    . . imported "TES"."U20"                                 5.992 KB       5 rows
    . . imported "TES"."TXN"                                     0 KB       0 rows
    Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
    Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
    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
    Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
    ORA-39082: Object type PROCEDURE:"TES"."Q1" created with compilation warnings

    Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Sat Nov 18 15:30:03 2023 elapsed 0 00:01:35

4. After import Compare the Object Count with source database :

    SQL> select object_type,count(*) from dba_objects where owner='TES' group by object_type;

    OBJECT_TYPE		  COUNT(*)
    ----------------------- ----------
    PROCEDURE			 4
    TABLE				 7
    INDEX				 2

5. Check for invalid objects count :
Run UTLRP script to recompile invalid objects if necessary

    @?/rdbms/admin/utlrp.sql

« Previous Next Topic » (Logical Recovery - Schema Refresh to an existing schema)