DBA Hub

šŸ“‹Steps in this guide1/13

SCHEMA REFRESH FROM 11G TO 12C PDB - Bright DBA

SCHEMA REFRESH FROM 11G TO 12C PDB *** Assume doing schema refresh from Production to UAT Contents

oracle Oracle 11Gclusteringintermediate
by OracleDba
12 views
1

Overview

SCHEMA REFRESH FROM 11G TO 12C PDB *** Assume doing schema refresh from Production to UAT Contents
2

Section 2

On SOURCE (PROD) On TARGET (UAT)
3

Section 3

0. Environment SOURCE TARGET

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
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
4

Section 4

********** On Source 1. Capture Schema detailsĀ before export 2. Create Directory

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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>
5

Section 5

3. Export (Backup schema) 4. Transfer the backup to target

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
[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 ~]$

[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]$
6

Section 6

********* On Target Side 5. Verify schema details (Optional) in case of UAT sqlplus sys@pdb1 as sysdba

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
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>
7

Section 7

-- Before import verify the tablespace free space on target PDB1 6. Backup schema (Optional) in case of UAT nohup expdp \"sys@pdb1 AS SYSDBA\" directory=DUMP_DIR dumpfile=scott_uat_%U.dmp logfile=EXP_SCOTT_UAT.log schemas=scott_uat parallel=4 &

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
[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 &
8

Section 8

7. Drop schema objects ( PLEASE DO NOT RUN ON PROD ) sqlplus sys@pdb1 as sysdba *** DROP SCHEMA OBJECTS *** DROP OTHER OBJECTS (If required) *** Run the script

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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
9

Section 9

8. Verify object count sqlplus sys@pdb1 as sysdba 9. Purge Recycle bin (user_recyebin)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
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>
10

Section 10

*** PLEASE DO NOT EXECUTE FROM SYS USER alter session set current_schema=SCOTT_UAT; purge recyclebin; 10. Create directory

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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>
11

Section 11

sqlplus sys@pdb1 as sysdba 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 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 &

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
[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>

-- 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

Section 12

12. Validation 13. Gather schema stats

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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>
13

Section 13

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. 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.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!