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
___________________________________________________________________________________________________
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
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>
[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>
[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]$
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
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>
[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 ~]$
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>
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