What are steps to refresh database with example?

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.