In my previous articles I have given multiple steps to create database link. In this article, I would like to give you the steps to refresh database with real time industry example. There are ton’s of time DBA or PL/SQL developer needs to refresh the database. These simple steps will give the multiple queries to refresh database with an example. We can use source database as MYDATABASE and Target database as AMIT_DB
Database administrators often fail to restore old passwords or check if an object is invalid prior to approving a requester who wants their database refreshed from production to test or development databases. This can result in forgotten passwords and unnecessary delays.
Which are steps to refresh database?
Step 1 : To Check Name of Database Before Importing or Exporting database
Query :
Select name from v$database;
Output :
NAME
———
MYDATABASE
Step 2: Verify the space used of by schema before exporting the database.
Query :
Select owner,sum(bytes)/1024/1024 “MB” from dba_segments group by owner;
Output :
OWNER MB
—————————— ——————–
SYSTEM 34
SYS 656.786
WMSYS 1.88
AMIT 111
WKSYS 1.434
OUTLN .7654
DBSNMP .89
Step 3 : Need to Check the Username as well as tablespace for the database.
SQL> col ACCOUNT_STATUS for a10
SQL> col profile for a15
SQL> set lines 150
SQL Query :
Select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE from dba_users where username=’MYDATABASE’;
Output :
USERNAME ACCOUNT_ST DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE
—————————— ———- —————————— —————————— —————
MYDATABASE OPEN DATA01 TEMP DEFAULT
Step 4 : Note the schema password for export and do the same on the target database prior to import, it can help you when you need to keep the old password.
SQL Query :
SQL> set long 2000
SQL> select dbms_metadata.get_ddl(‘USER’,’MYDATABASE’) from dual;
DBMS_METADATA.GET_DDL(‘USER’,’MYDATABASE’)
————————————————————-
CREATE USER “AMIT_DB” IDENTIFIED BY VALUES ‘S:3AFD2F1934CCFB63E4793D84FC8
F1D663550D0D07EEADEE4005F8C46807F;5BE571F865FDADE0′
DEFAULT TABLESPACE “DATA01”
TEMPORARY TABLESPACE “TEMP”
Step 5 : Review the tablespaces used prior to export.
Query :
Select distinct tablespace_name from dba_segments where owner=’MYDATABASE’;
TABLESPACE_NAME
——————————
DATA01
Step 6 : Before exporting, verify the state of each object. After import, it can assist you in comparing your object.
Query :
Select object_type, count(1) from dba_objects where owner=’MYDATABASE’ group by rollup(object_type);
Output :
OBJECT_TYPE COUNT(1)
————— ———-
FUNCTION 45
INDEX 445
PACKAGE 22
PACKAGE BODY 22
PROCEDURE 556
SEQUENCE 1
TABLE 544
TRIGGER 54
TYPE 45
VIEW 9
869
11 rows selected.
Step 7 : Before exporting, verify the status of any invalid items. No incorrect object is on the source in this example.
Query :
Select distinct status from dba_objects where owner=’MYDATABASE’;
STATUS
——-
VALID
Step 8 : Before exporting, check the database dictionary. You now know where to export to and where your export file will be stored.
Query :
SELECT owner, directory_name, directory_path FROM all_directories;
Step 9 :
Although it is possible to import and export without a par file, that is what I like to do.
With the aid of the vi editor or any text file, create a par file. For demonstration purposes, I’m using a straightforward par file, but a complicated query can also be used.
vi MYDATABASE.par
DIRECTORY=DP_MYDATABASE
SCHEMAS=MYDATABASE
DUMPFILE=MYDATABASE_AMIT_14DEC2022.DMP
LOGFILE=EXPDP_MYDB_AMIT_14DEC2022.log
Step 10 :
I will now call the par file for this export. Using the command prompt is another option.
nohup expdp \’/ as sysdba\’ parfile=AMIT_DB.par &
Or
nohup expdp system/password DIRECTORY=DP_MYDATABASE DUMPFILE=MYDATABASE_AMIT_14DEC2022.DMP SCHEMAS=AMIT_DB LOGFILE=EXPDP_MYDB_AMIT_14DEC2022.log &
Step 11 : Then copy the export file you want to import to the target server. Navigate to where your export file is and copy it. Make sure you have permission to copy the dump to the target server at the specified location.
scp EXPDP_MYDB_AMIT_14DEC2022.log.log amit@targetserver:/orasav/AMIT_DB/bkp/exp .
**************************************************************targetserver****************************************************************************************
Step 12 :
Check the database name before importing. Do not forget to check the name of the database, we found an error in sevra dba and the import is empty in the database that is preselected.
Query :
select name from v$database;
NAME
———
AMIT_DB
Step 13 : Check whether the model is available in the target database or not, if so, then check the space usage before importing.
Query :
Select owner, sum(bytes)/1024/1024 from dba_segments group by owner;
Output :
OWNER SUM(BYTES)/1024/1024
——————— ——————–
SYSTEM 44.444
SYS 786.434
AMIT 406.25
OUTLN .5643
DBSNMP ,8898
Step 14 : Before importing, check the used tablespaces as a schema. You can create a tablespace or use remap_schema if needed.
Query :
SQL> col ACCOUNT_STATUS for a10
SQL> col profile for a15
SQL> set lines 150
Select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE from dba_users where username=’AMIT_DB’;
USERNAME ACCOUNT_ST DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE
—————————— ———- —————————— —————————— —————
RAJEEV OPEN DATA01 TEMP DEFAULT
Step 15 : As mentioned when you export the source database, you must copy the password before importing if you want to keep the old password.
Write down the scheme password before exiting the scheme. This allows you to save the old password if needed.
SQL Query :
SQL> set long 2000
SQL> select dbms_metadata.get_ddl(‘USER’,’AMIT_DB’) from dual;
DBMS_METADATA.GET_DDL(‘USER’,’AMIT_DB’)
——————————————————————————–
CREATE USER “AMIT_DB” IDENTIFIED BY VALUES ‘S:7BD39503F6BE84405B049490125F74E398DB1B6D80306F07F12699FF81B4;14F7A8346F70B02D’
DEFAULT TABLESPACE “DATA01”
TEMPORARY TABLESPACE “TEMP”
Step 16 : Discard the Schema if it is not needed. Datapump creates the schema automatically during import. Otherwise, you can run the below command after import to save the old password. Note. The syntax below is optional.
Query :
SQL> alter user AMIT_DB IDENTIFIED BY VALUES ‘S:7BD39503F6BE84405B049490125F74E398DB1B6D80306F07F12699FF81B4;14F7A8346F70B02D’;
Step 17 : Check database dictionary before import, if source and target are different, create pair file accordingly.
Query :
SELECT owner, directory_name, directory_path FROM all_directories;
Step 18 : Create par file with the help of vi editor or any text file for import the schema.
vi AMIT_DB.par
DIRECTORY=DP_AMIT_DB
SCHEMAS=AMIT_DB
DUMPFILE=EXPORT_AMIT_MYDBP_12-17-14.dmp
LOGFILE=IMPDP_MYDB_AMIT_12-17-14.log
Step 19 : Now I call the par file to import. This can also be done from the command line, as we did during export.
nohup impdp \’/ as sysdba\’ parfile=AMIT_DB.par &
Step 20 : After importing, check all objects and their status. If there is an invalid object, recompile it.
Query :
Select object_type,status,count(1) from dba_objects where owner=’HR’ group by object_type,status;
Output :
OBJECT_TYPE STATUS COUNT(1)
——————- ——- ———-
FUNCTION VALID 33
PACKAGE BODY VALID 1
PROCEDURE VALID 54
PACKAGE VALID 1
VIEW VALID 345
INDEX VALID 545
SEQUENCE VALID 12
FUNCTION INVALID 33
TRIGGER VALID 125
VIEW INVALID 1
TABLE VALID 211
OBJECT_TYPE STATUS COUNT(1)
——————- ——- ———-
TRIGGER INVALID 34
12 rows selected.
Step 21 : Check the status of objects if they are invalid after import. In this example I have a bad object and we need to recompile it if necessary because we know there are no bad objects in my source.
Query :
select distinct status from dba_objects where owner=’AMIT_DB’;
STATUS
——-
VALID
INVALID
ALTER TRIGGER AUDUPD_TM_PROFILE COMPILE;
******************************* END of database refresh ******************************
If any other object is invalid you can try to compile like below mentioned steps.
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = ‘INVALID’
and owner=’AMIT_DB’;
SELECT owner, object_type, object_name, status
FROM dba_objects
WHERE status = ‘INVALID’ and owner=’AMIT_DB’;
SQL> select ‘alter ‘||object_type||’ ‘||object_name||’ compile;’ from user_objects where object_type=’VIEW’ and status=’INVALID’;
ALTER FUNCTION GETMACHINE COMPILE;
Queries to Compile and Execute all Objects :
ALTER TRIGGER AUDINS_TM COMPILE;
ALTER VIEW VDK_SYNC_TRG COMPILE;
EXEC DBMS_DDL.alter_compile(‘PACKAGE’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);
EXEC DBMS_DDL.alter_compile(‘PACKAGE BODY’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);
EXEC DBMS_DDL.alter_compile(‘PROCEDURE’, ‘RAJEEV’, ‘USP_SELECT_ALL_DONNEES_CAP’);
EXEC DBMS_DDL.alter_compile(‘FUNCTION’, ‘TMS’, ‘GETMACHINE’);
EXEC DBMS_DDL.alter_compile(‘TRIGGER’, ‘TMS’, ‘AUDDEL_TM_PROFILE’);
I hope you like this article on steps to refresh database with one by one. If you like this article of steps to refresh database or if you have any issues with the same kindly comment in to comments section.