Tag Archives: datapump export

SCHEMA REFRESH FROM 11G TO 12C PDB

SCHEMA REFRESH FROM 11G TO 12C PDB

*** Assume doing schema refresh from Production to UAT

Contents
___________________________________________________________________________________________________

On SOURCE (PROD)

0. Environment
1. Capture Schema details before export
2. Create Directory
3. Export (Backup schema)
4. Transfer the backup to target

On TARGET (UAT)

5. Verify schema details (Optional) in case of UAT
6. Backup schema (Optional) in case of UAT
7. Drop schema objects (DO NOT do on PROD)
8. Verify object count
9. Purge Recycle bin (user_recyebin)
10. Create directory
11. Import (Restore schema backup)
12. Validation
13. Gather schema stats

___________________________________________________________________________________________________


0. Environment

SOURCE

Hostname        : RAC1.RAJASEKHAR.COM
Database Name   : DELL
Schema Name     : SCOTT
TABLESPACE_NAME : USERS
DB VERSION      : 11.2.0.4.0

TARGET

Hostname        : RAC2.RAJASEKHAR.COM
CDB Name        : CDB1
PDB NAME        : PDB1
Schema Name     : SCOTT_UAT
TABLESPACE_NAME : SCOTT_UAT_DATA
DB VERSION      : 12.2.0.1.0


********** On Source

1. Capture Schema details before export

sqlplus / as sysdba

spool pre-verify.log

set lines 180
select name,open_mode from v$database;
select username,account_status,default_tablespace from dba_users where username='&owner';
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner='&owner' group by owner;
select owner,object_type,count(*) from dba_objects where owner='&owner' and object_name not like 'BIN$%' group by object_type,owner order by object_type;
select count(*) from dba_objects where owner='&owner' and status='INVALID';

spool off

SQL> set lines 180
SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
DELL READ WRITE

SQL> select username,account_status,default_tablespace from dba_users where username='SCOTT';

USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE
------------------------------ -------------------------------- ------------------------------
SCOTT OPEN USERS

SQL> select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner='SCOTT' group by owner;

OWNER SIZE in MB
------------------------------ ----------
SCOTT .3125

SQL> select owner,object_type,count(*) from dba_objects where owner='SCOTT' and object_name not like 'BIN$%' group by object_type,owner order by object_type;

OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------- ----------
SCOTT INDEX 2
SCOTT TABLE 4

SQL>
SQL> select count(*) from dba_objects where owner='SCOTT' and status='INVALID';

COUNT(*)
----------
0

SQL>


2. Create Directory

[oracle@rac1 ~]$ mkdir -p /u02/DUMP_DIR

[oracle@rac1 ~]$ chmod 775 /u02/DUMP_DIR
[oracle@rac1 ~]$


[oracle@rac1 DUMP_DIR]$ df -h /u02/DUMP_DIR
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_home
43G 23G 19G 56% /u02

[oracle@rac1 DUMP_DIR]$

SQL> create or replace directory DUMP_DIR as '/u02/DUMP_DIR';

Directory created.

SQL>

SQL> SET LINES 190
SQL> col DIRECTORY_PATH for a40;
SQL> select * from dba_directories where DIRECTORY_NAME='DUMP_DIR';

OWNER DIRECTORY_NAME DIRECTORY_PATH
-------- --------------- ----------------
SYS DUMP_DIR /u02/DUMP_DIR

SQL>


3. Export (Backup schema)

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [DELL] ?
[oracle@rac1 ~]$

nohup expdp \'/ as sysdba\' directory=DUMP_DIR dumpfile=scott_%U.dmp logfile=EXP_SCOTT.log schemas=scott parallel=4 &

[oracle@rac1 ~]$ expdp \'/ as sysdba\' directory=DUMP_DIR dumpfile=scott_%U.dmp logfile=EXP_SCOTT.log schemas=scott parallel=4

Export: Release 11.2.0.4.0 - Production on Tue Aug 14 21:08:17 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_02": "/******** AS SYSDBA" directory=DUMP_DIR dumpfile=scott_%U.dmp logfile=EXP_SCOTT.log schemas=scott parallel=4
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
. . exported "SCOTT"."DEPT" 5.937 KB 4 rows
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
. . exported "SCOTT"."EMP" 8.570 KB 14 rows
Processing object type SCHEMA_EXPORT/ROLE_GRANT
. . exported "SCOTT"."SALGRADE" 5.867 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
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/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Master table "SYS"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_02 is:
/u02/DUMP_DIR/scott_01.dmp
/u02/DUMP_DIR/scott_02.dmp
/u02/DUMP_DIR/scott_03.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at Tue Aug 14 21:08:39 2018 elapsed 0 00:00:21

[oracle@rac1 ~]$


4. Transfer the backup to target

[oracle@rac1 DUMP_DIR]$ scp scott*.dmp oracle@rac2:/u02/DUMP_DIR_12C

oracle@rac2's password:
scott_01.dmp 100% 32KB 32.0KB/s 00:00
scott_02.dmp 100% 36KB 36.0KB/s 00:00
scott_03.dmp 100% 172KB 172.0KB/s 00:00
[oracle@rac1 DUMP_DIR]$


********* On Target Side

5. Verify schema details (Optional) in case of UAT

sqlplus sys@pdb1 as sysdba

spool before_Drop_schema.log

set lines 180
select name,open_mode from v$database;
select username,account_status,default_tablespace from dba_users where username='&owner';
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner='&owner' group by owner;
select owner,object_type,count(*) from dba_objects where owner='&owner' and object_name not like 'BIN$%' group by object_type,owner order by object_type;

-- Before import verify the tablespace free space on target

set linesize 132 tab off trimspool on
set pagesize 105
set pause off
set echo off
set feedb on

column "TOTAL ALLOC (MB)" format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format 9,999,990.00
column "FREE (MB)" format 9,999,990.00
column "% USED" format 990.00

select a.tablespace_name,
a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
(nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from ( select tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from dba_data_files
group by tablespace_name ) a,
( select tablespace_name, sum(bytes) tot_used
from dba_segments
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
--and (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
--and a.tablespace_name in ('NONEED')
--and a.tablespace_name not like 'UNDO%'
and a.tablespace_name='&tbs'
--- like 'Noneed%'
order by 1
--order by 5
/


col username for a20
select * from dba_ts_quotas where username='&username';

select count(*) from dba_objects where owner='&owner' and status='INVALID';

spool off

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
SQL> select * from dba_ts_quotas where username='SCOTT_UAT';

TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
------------------------------ -------------------- ---------- ---------- ---------- ---------- ---
SCOTT_UAT_DATA SCOTT_UAT 0 -1 0 -1 NO

SQL> select username,account_status,default_tablespace from dba_users where username='SCOTT_UAT';

USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE
-------------------- -------------------------------- ------------------------------
SCOTT_UAT OPEN SCOTT_UAT_DATA

SQL>


6. Backup schema (Optional) in case of UAT

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [CDB1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$

nohup expdp \"sys@pdb1 AS SYSDBA\" directory=DUMP_DIR dumpfile=scott_uat_%U.dmp logfile=EXP_SCOTT_UAT.log schemas=scott_uat parallel=4 &


7. Drop schema objects (PLEASE DO NOT RUN ON PROD

sqlplus sys@pdb1 as sysdba

*** DROP SCHEMA OBJECTS

set head off
set pagesize 0
set linesize 300

spool schemaname_drop_obj.sql

select 'drop '||object_type||' '||owner||'.'||object_name||decode(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') from dba_objects where owner in ('&owner') and object_type not in ('DATABASE LINK','JOB') order by object_type,object_name
/

spool off;
set head on


*** DROP OTHER OBJECTS (If required)

set head off
set pagesize 0
set linesize 300

spool schemaname_drop.sql

select 'DROP '||decode(object_type, 'PACKAGE BODY', 'PACKAGE',object_type)|| ' '||owner||'.'||object_name||decode(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') 
from dba_objects where owner=upper('&owner') and object_type not in ('TABLE')
/

spool off;
set head on

*** Run the script
!cat schemaname_drop_obj.sql
@schemaname_drop_obj.sql


8. Verify object count

sqlplus sys@pdb1 as sysdba

SQL> select owner,object_type,count(*) from dba_objects where owner='SCOTT' and object_name not like 'BIN$%' group by object_type,owner order by object_type;

no rows selected

SQL>


9. Purge Recycle bin (user_recyebin)

Users can purge the recycle bin of their own objects and release space for objects by using the following statement:

*** PLEASE DO NOT EXECUTE FROM SYS USER

[oracle@rac1 ~]$ sqlplus sys@pdb1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 14 23:01:03 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session set current_schema=SCOTT_UAT;

Session altered.

SQL> purge recyclebin;

Recyclebin purged.

SQL> SELECT * FROM USER_RECYCLEBIN;

no rows selected

SQL>


10. Create directory

[oracle@rac2 ~]$ ls -ltr /u02/DUMP_DIR_12C

-rw-r-----. 1 oracle dba 32768 Aug 14 21:44 scott_01.dmp
-rw-r-----. 1 oracle dba 36864 Aug 14 21:44 scott_02.dmp
-rw-r-----. 1 oracle dba 176128 Aug 14 21:44 scott_03.dmp

[oracle@rac2 ~]$

sqlplus sys@pdb1 as sysdba
SQL> create or replace directory DUMP_DIR_12C as '/u02/DUMP_DIR_12C';

Directory created.

SQL>

SQL> SET LINES 190
SQL> col owner for a20
SQL> col DIRECTORY_PATH for a40;
SQL> col DIRECTORY_NAME for a20

SQL> select * from dba_directories where DIRECTORY_NAME='DUMP_DIR_12C';

OWNER DIRECTORY_NAME DIRECTORY_PATH
-------- --------------- ----------------
SYS DUMP_DIR /u02/DUMP_DIR

SQL>


11. Import (Restore schema backup)

-- Lets assume you don't know the source tablespace, then you can't use REMAP_TABLESPACE. In this scenario you can use these two clauses while import "transform=segment_attributes:n transform=OID:n" instead REMAP_TABLESPACE

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [CDB1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$


nohup impdp \"sys@pdb1 AS SYSDBA\" directory=DUMP_DIR_12C dumpfile=scott_%U.dmp logfile=IMP_SCOTT.log remap_schema=SCOTT:SCOTT_UAT transform=segment_attributes:n transform=OID:n parallel=4 &


[oracle@rac2 ~]$ impdp \"sys@pdb1 AS SYSDBA\" directory=DUMP_DIR_12C dumpfile=scott_%U.dmp logfile=IMP_SCOTT.log remap_schema=SCOTT:SCOTT_UAT transform=segment_attributes:n transform=OID:n parallel=4

Import: Release 12.2.0.1.0 - Production on Tue Aug 14 22:35:34 2018

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

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
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SYS"."SYS_IMPORT_FULL_01": "sys/********@pdb1 AS SYSDBA" directory=DUMP_DIR_12C dumpfile=scott_%U.dmp logfile=IMP_SCOTT.log remap_schema=SCOTT:SCOTT_UAT transform=segment_attributes:n transform=OID:n parallel=4
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT_UAT" already exists

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 "SCOTT_UAT"."DEPT" 5.937 KB 4 rows
. . imported "SCOTT_UAT"."EMP" 8.570 KB 14 rows
. . imported "SCOTT_UAT"."SALGRADE" 5.867 KB 5 rows
. . imported "SCOTT_UAT"."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/CONSTRAINT/REF_CONSTRAINT
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue Aug 14 22:35:44 2018 elapsed 0 00:00:08

[oracle@rac2 ~]$


12. Validation

sqlplus sys@pdb1 as sysdba

select owner,object_type,count(*) from dba_objects where owner='SCOTT_UAT' and object_name not like 'BIN$%' group by object_type,owner order by object_type;

OWNER OBJECT_TYPE COUNT(*)
--------------- -------------------- ----------
SCOTT_UAT INDEX 2
SCOTT_UAT TABLE 4

SQL>
SQL> select count(*) from dba_objects where owner='SCOTT_UAT' and status='INVALID';

COUNT(*)
----------
0   <-------

SQL>


13. Gather schema stats

exec dbms_stats.gather_schema_stats(ownname=>'&schema_name', CASCADE=>TRUE,ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4);

[oracle@rac1 ~]$ sqlplus sys@pdb1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 14 22:42:24 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> exec dbms_stats.gather_schema_stats(ownname=>'&schema_name', CASCADE=>TRUE,ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4);
Enter value for schema_name: SCOTT_UAT  <----

PL/SQL procedure successfully completed.

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using. Please note we are not responsible for any data loss.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com