Schema Refresh
In this blog we are telling about How to do oracle schema referesh else how to do backup and restore by using oracle data pump,from production to TEST environment.
oracle configurationintermediate
by OracleDba
13 views
📋Steps in this guide1/43
1
Overview
Oracle Database schema Refresh Activity ?
Solution:
No.
Description
Command / Screenshot
1
2
Section 2
Find the size of the schema
- . oraenv → BATCH46
. oraenv → BATCH46
- sqlplus / as sysdba
sqlplus / as sysdba
- To find the size of the schema, execute the following:
To find the size of the schema, execute the following:
. oraenv → BATCH46
sqlplus / as sysdba
To find the size of the schema, execute the following:
COL OWNER FORMAT A15
3
Section 3
SELECT owner, tablespace_name,
round(Sum(bytes)/1024/1024,2) AS SIZE_in_MB
FROM dba_segments
WHERE owner like Upper(‘&User_Name’)
GROUP BY rollup(owner, tablespace_name);
2
4
Section 4
Find the directory where schema level export can be taken using data pump
set linesize 200
set pagesize 200
column directory_path format a20
Select directory_name, directory_path FROM dba_directories order by 1;
Note: I will use ADDP directory to take an export of the entire refresh schema
5
Section 5
3.
Take a backup of the schema using Data Pump
- Exit of our sqlplus
Exit of our sqlplus
- To start the export using Data Pump, execute the following:
To start the export using Data Pump, execute the following:
Exit of our sqlplus
To start the export using Data Pump, execute the following:
expdp \
6
Section 6
system/abc123 \
directory = ADDP \
dumpfile = ADDB_full_dp.dmp \
logfile = ADDB_full_dp.log \
schemas=REFRESH
4
7
Section 7
Verify the dumpfile are created
ls -ltrh /u18/BATCH46/ADDP
5
Find the status of the constraints.
- . oraenv → BATCH46
. oraenv → BATCH46
- sqlplus / as sysdba
sqlplus / as sysdba
- To check the status of all FOREIGN KEY and CHECK constraints, execute the following:
To check the status of all FOREIGN KEY and CHECK constraints, execute the following:
. oraenv → BATCH46
8
Section 8
sqlplus / as sysdba
To check the status of all FOREIGN KEY and CHECK constraints, execute the following:
SET LINESIZE 200
SET PAGESIZE 200
COL CONSTRAINT_NAME FORMAT A20
select
9
Section 9
owner,
constraint_name,
constraint_type,
status
from
dba_constraints
10
Section 10
where
owner=’REFRESH’
and
constraint_type IN (‘R’,’C’);
6
Disable all constraints
11
Section 11
BEGIN
FOR a IN
(
select
a.owner,
a.table_name,
12
Section 12
a.constraint_name
from
all_constraints a,
all_constraints b
where
a.constraint_type = ‘R’
13
Section 13
and
a.r_constraint_name = b.constraint_name
and
a.r_owner = b.owner
and
b.owner=’REFRESH’
14
Section 14
union
SELECT
a.owner,
a.table_name,
a.constraint_name
FROM
15
Section 15
all_constraints a
WHERE
a.constraint_type = ‘C’
and
a.owner=’REFRESH’
)
16
Section 16
LOOP
dbms_utility.exec_ddl_statement(‘alter table ‘ || a.owner || ‘.’ || a.table_name || ‘ DISABLE constraint ‘ || a.constraint_name);
END LOOP;
END;
/
7
17
Section 17
Check the status of the constraints again
- Execute the following and as you can see all constraints are disabled.
Execute the following and as you can see all constraints are disabled.
Execute the following and as you can see all constraints are disabled.
select
owner,
constraint_name,
18
Section 18
constraint_type,
status
from
dba_constraints
where
owner= ‘REFRESH’
19
Section 19
and
constraint_type IN (‘R’,’C’);
8
Check tables and row count the schema
select
owner,
20
Section 20
table_name,
num_rows
from
dba_tables
where
owner= ‘REFRESH’ ;
21
Section 21
8
Now truncate all the tables in the schema
BEGIN
FOR a IN
(
select owner, table_name from dba_tables where owner=’REFRESH’
22
Section 22
)
LOOP
dbms_utility.exec_ddl_statement(‘ Truncate table ‘ || a.owner || ‘.’ || a.table_name);
END LOOP;
END;
/
23
Section 23
9
Gather schema stats
exec dbms_stats.gather_schema_stats ( ‘REFRESH’ , dbms_stats.auto_sample_size);
10.
Check table and row count again in schema. All tables should have zero rows.
select
24
Section 24
owner,
table_name,
num_rows
from
dba_tables
where
25
Section 25
owner= ‘REFRESH’ ;
At this point, schema has all the tables truncated.
No.
Description
Command / Screenshot
11
26
Section 26
Take an export on Production.
- Putty to production server
Putty to production server
- Su – oracle
Su – oracle
- . oraenv → ProdDB
. oraenv → ProdDB
- Execute the following command on Prod to start export using Data Pump.
Execute the following command on Prod to start export using Data Pump.
Putty to production server
Su – oracle
. oraenv → ProdDB
Execute the following command on Prod to start export using Data Pump.
27
Section 27
Please note that you may have to change the “directory=ABC_SCHEMA” to the directory which is created in ProdDB.
expdp \
system \
directory=ABC_SCHEMA \
dumpfile=Prod_REFRESH.dmp \
logfile=Prod_REFRESH.log \
28
Section 28
schemas=REFRESH
12
Send the .dump file to destination server where you have to import data only
cd /u18/TESTDB/APP
scp *Prod_REFRESH* DestinationHOST:/u18/TESTDB/APP/.
13
29
Section 29
Log back into the Destination SErver
- Putty to Destination Server
Putty to Destination Server
- Su – oracle
Su – oracle
- . oraenv → TESTDB
. oraenv → TESTDB
Putty to Destination Server
Su – oracle
. oraenv → TESTDB
14
30
Section 30
Lets say if the schema will need to be refreshed from the another a prod .dmp file without recreating the tables, then execute the following:
impdp \
system \
directory=ADDP \
dumpfile=ADDB_full_dp.dmp \
logfile=Prod_REFRESH_IMP.log \
31
Section 31
schemas=REFRESH \
content= DATA_ONLY
15
Enable the constraints
BEGIN
FOR a IN
32
Section 32
(
select
a.owner,
a.table_name,
a.constraint_name
from
33
Section 33
all_constraints a,
all_constraints b
where
a.constraint_type = ‘R’
and
a.r_constraint_name = b.constraint_name
34
Section 34
and
a.r_owner = b.owner
and
b.owner=’REFRESH’
union
SELECT
35
Section 35
a.owner,
a.table_name,
a.constraint_name
FROM
all_constraints a
WHERE
36
Section 36
a.constraint_type = ‘C’
and
a.owner=’REFRESH’
)
LOOP
dbms_utility.exec_ddl_statement(‘alter table ‘ || a.owner || ‘.’ || a.table_name || ‘ ENABLE constraint ‘ || a.constraint_name);
37
Section 37
END LOOP;
END;
/
16
Verify constraints are enabled.
select
38
Section 38
owner,
constraint_name,
constraint_type,
status
from
dba_constraints
39
Section 39
where
owner= ‘REFRESH’
and
constraint_type IN (‘R’,’C’);
17
Collect the fresh statistics on the schema
40
Section 40
exec dbms_stats.gather_schema_stats ( ‘REFRESH’ , dbms_stats.auto_sample_size);
18
Verify statistics are collected.
select
owner,
table_name,
41
Section 41
num_rows
from
dba_tables
where
owner= ‘REFRESH’ ;
(OR)
42
Section 42
set linesize 200
set pagesize 200
select
owner,
table_name,
num_rows,
43
Section 43
to_char(last_analyzed, ‘HH24:MI:SS MM-DD-YYYY’) as Last_Analyzed
from
dba_tables
where
owner=’REFRESH’;
Comments (0)
Please to add comments
No comments yet. Be the first to comment!