This doc is helpful in the context when we are refreshing database from prod to non prod and want to keep intact all the grant,users,password and db link in the Target environment after refresh.
If there is no such requirement than steps 1 to 12 can be ignored and continue from step 13 .
=============== Presteps ================
1.Check the size of the database:
========================
Spool database_size.log
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select
round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
,
round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
,
round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select
bytes
from
v$datafile
union
all
select
bytes
from
v$tempfile
union
all
select
bytes
from
v$log) used
,
(select sum(bytes) as p
from dba_free_space) free
group by free.p
/
spool off;
========================
1) Take backup of all the existing user in the database
set echo off
set head off
set pagesize 0
set feedback off
set linesize 2000
spool pre_refresh_user.sql
select 'create user "'||a.username||'" identified by values '''||decode(b.spare4,NULL,NULL,b.spare4||';')||b.password||
''' default tablespace '||a.default_tablespace||' temporary tablespace '||a.temporary_tablespace||' profile '||a.profile||' ; '
from dba_users a, user$ b
where a.username = b.name ;
spool off
=================
2) Pull all the system privilege exist in the database.
set echo off
set head off
set pagesize 0
set feedback off
set linesize 2000
spool sys_privs.sql
select 'grant '||privilege||' to "'||grantee||'" ;' from dba_sys_privs where grantee not in 'SYS';
spool off
=================
3)Pull all the user password from the database.
set echo off
set head off
set pagesize 0
set feedback off
set linesize 200
spool passwords.sql
select 'alter user "'||a.username||'" identified by values '''||decode(b.spare4,NULL,NULL,b.spare4||';')||b.password||''';'
from dba_users a, user$ b
where a.username = b.name ;
spool off
=============
4)Pull all the object privilege exists in the database.
spool owner.sql
select 'grant '||privilege||' on '||owner||'."'||table_name||'" to "'||grantee||'"; ' from dba_tab_privs ;
spool off
==============================
5) Pull all the Role exist in the database.
set echo off
set head off
set pagesize 0
set feedback off
set linesize 200
spool pre_refresh_role_privs.sql
select 'grant '||granted_role||' to "'||grantee||'";' from dba_role_privs where grantee like '%%';
spool off
================
6) Pull all existing profiles from Database
spool profile.sql
select distinct profile from dba_profiles;
spool off;
spool profile_ddl.sql
set lines 1000 pages 1000;
select dbms_metadata.get_ddl('PROFILE',a.profile) from dba_profiles a;
spool off;
====================
7) Take backup of profile assign to users.
spool user_profile.sql
select 'alter user "'||username||'" profile '||profile||';' from dba_users;
spool off;
============================
8) Take Backup of temporary tablespaces and their Allocated spaces
spool temp_undo_tablespace.sql
set head on
a)
SELECT tablespace_name,
SUM(bytes_used)/1024/1024 "USED MB",
SUM(bytes_free)/1024/1024 "FREE MB"
FROM V$temp_space_header
GROUP BY tablespace_name;
b)
col file_name for a60
select file_name,bytes/1024/1024 from dba_temp_files
where tablespace_name like '%TEMP%';
c)
col name for a25
select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
(select tablespace_name tbl,sum(bytes)/1024/1024/1024 TSZ from dba_data_files
where tablespace_name like '%%' group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024/1024 FSZ from dba_free_space
where tablespace_name like '%%' group by tablespace_name) b
Where a.tbl=b.tblsp;
d)
col file_name for a60
select file_name,bytes/1024/1024 from dba_data_files
where tablespace_name ='UNDOTBS1';
spool off;
==============================================================
9)Check the listener name and services pointed to the database :
$ ps -ef|grep inh > listener_services.sql
$ lsnrctl status <listener_name> | grep <database_name> >> listener_services.sql
===========================
10) Take backup of all the DB Link :
SQL> create directory <directory name> as 'path'
$ expdp full=y include=DB_LINK dumpfile=dblink.dmp directory=prechecks logfile=dblink.log
=================================
11)Jobs information
spool jobs.sql
select * from dba_jobs;
select * from dba_scheduler_jobs;
select count(*) from dba_jobs;
select count(*) from dba_scheduler_jobs;
spool off;
$ expdp full=y include=procobj dumpfile=jobs.dmp directory=prechecks logfile=jobs.log
========================================================================
12)Make a copy of the pfile and spfile in your current backup location
SQL>sqlplus / as sysdba
SQL> create pfile=’/backup location/initdbname.ora’ from spfile;
SQL> cd $ORACLE_HOME/dbs
$ cp spfiledbname.ora /backup location/spfiledbname.ora
With that, the pre-steps are now complete!!! Starting the DB refresh.
Before Proceeding with DB restore make sure all the required backup pieces are ready in target machine.
13)Shutdown the Target DB
SQL> sqlplus / as sysdba
SQL> shut immediate;
14)startup the DB in mount mode using the pfile in restrict mode Use pfile mentioned in steps 12.
SQL> startup mount pfile=’/backup location/initdbname.ora’ restrict;
(It is good practice to copy the pfile in different location and use that pfile during refresh .)
15)drop the destination database :
SQL> drop database;
16)edit the pfile: Comment out the log_file_name_convert & db_file_name_convert parameters
db_name='source database' (Usually Source DB if target and source db name is different otherwise no change required)
db_unique_name='destination database' (QA or DEV DB which is getting refreshed)
17)Set up the environment of the database to be refreshed :
export ORACLE_SID=<Source_databasename>
echo $ORACLE_SID
export ORACLE_HOME=<$ORACLE_HOME of the destination database>
check oracle home from cat /var/opt/oracle/oratab or env|grep ora
echo $ORACLE_HOME
18)startup nomount the database using the pfile edited above in step 15 :
SQL> startup nomount pfile=’ /backup location/initdbname.ora’;
SQL> exit
19)Connect RMAN to start restore :
$ rman target /
20)Restore the controlfile using latest controlfile copy which is in your backup location i.e. controlfile of the production database :
RMAN> restore controlfile from ‘/backup location/name_of_controlfile’;
((control file should be the latest control file amongst the no of controlefiles ))
21)Mount the database :
RMAN> alter database mount;
22)Now we need to catalog the backup Pieces which we are going to restore :
RMAN> catalog start with '/backup location/’; ( Location where the backup pieces are available)
RMAN> exit;
23)Now we need to execute set newname command to define a new location for the restored datafile in target machine.
Login to Target database (usually production)
$ sqlplus / as sysdba
SQL> set lines 300
SQL> select ' set newname for datafile ' || file# || ' to '''||name||''';' from v$datafile;
The output would look like below (of course the number of datafiles will be much more than what is shown below)
---------------------------------------------------------------------------------------------------------------
set newname for datafile 31 to '/u02/oradata/islam/system/undotbs04.dbf';
set newname for datafile 34 to ‘/u02/oradata/islam/datafile/edwcclyubal10_003.dbf';
change the name of the database to the name of the destination database (for all the datafiles)
----------------------------------------------------------------------------------------------------------------------
set newname for datafile 31 to '/u02/oradata/islam2/system/undotbs04.dbf';
set newname for datafile 34 to ‘/u02/oradata/islam2/datafile/edwccluyubal10_003.dbf';
24)Create a shell script which will complete your restore :
$ sh restore_dbname.sh & (this will start the restore by executing the commands in the shell script)
vi restore_dbname.sh
rman target / log=/backup location/restore_dbname.log << EOF
run
{
set newname for datafile 1 to '/u02/oradata/system/system_001.dbf';
set newname for datafile 2 to '/u02/oradata/system/undotbs01.dbf';
set newname for datafile 3 to '/u02/oradata/system/sysaux_004.dbf';
set newname for datafile 4 to '/u02/oradata/datafile/assetedwdata01_006.dbf';
set newname for datafile 5 to '/u02/oradata/system/undotbs02.dbf';
set newname for datafile 6 to '/u02/oradata/datafile/edwdata01_008.dbf';
set newname for datafile 7 to '/u02/oradata/datafile/edwindx01_024.dbf';
set newname for datafile 8 to '/u02/oradata/datafile/edwdata01_007.dbf';
set newname for datafile 9 to '/u02/oradata/datafile/edwindx01_023.dbf';
set newname for datafile 10 to '/u02/oradata//system/undotbs03.dbf';
restore database;
switch datafile all;
recover database;
}
exit
EOF
:wq!
25)Tail the log file and monitor the progress of the restore periodically :
$ tail –f /backup location/restore_dbname.log (this is the log mentioned in the shell script)
26)Once the restore is complete, it will fail with an error and return the message :
"archivelog sequence not found."
27)Now, rename the existing redo log members with new names and clear the old log groups :
select * from v$logfile;
check the redologfiles
SQL> alter database rename file ‘/u02/oradata/sourcedb/system/redo06a.log’ to ‘/u02/oradata/targetdb/system/redo06a.log’;
SQL> alter database rename file ‘/u02/oradata/sourcedb/mirror/redo06b.log’ to ‘/u02/oradata/targetdb/mirror/redo06b.log’;
Follow similar steps for all the members
28)Clear the Log group :
SQL> alter database clear logfile group 6; (Follow similar steps for all the members)
29)Now, open the database with resetlogs option :
SQL> alter database open resetlogs;
30 )create new tempfiles of same size as per presteps taken in step 8 and delete all the other extra tempfile.(This steps is only applicable when source and target have different temp tablespace structure) :
select * from v$tempfile;
check the tempfiles from the presteps and create exactly similar as per
step 8.
Add new tempfile with privious size and drop the existing one.
SQL> alter tablespace TEMP add tempfile '/u02/oradata/dbname/datafile/TEMP01.dbf' size 4096m;(New)
SQL>alter database tempfile '/u02/oradata/sourcedb/system/temp_users01.dbf' drop;(Old)
SQL> alter tablespace TEMP add tempfile ‘/path/name’ size xx G;
31)shutdown the database :
SQL> shut immediate;
32)startup mount with the pfile in the backup location and change db name and db id using NID :
$ sqlplus / as sysdba
idle> startup mount pfile='pfile_location/initdbname.ora';
$ nid target=sys dbname=’destination database’
Note: After nid command completed successfully DB will shutdown automatically and need to start with resetlog option.
33)Bring up the database with original PFILE from $ORACLE_HOME/dbs/ location.
SQL> startup mount pfile='$ORACLE_HOME/dbs/initdbname.ora';
SQL> alter database noarchivelog;(If db was running in no archive log mode )
34)Open the database with resetlogs option :
SQL> alter database open resetlogs;
35)Check if the db name has changed appropriately and all its files are in proper location :
SQL> select name,open_mode from v$database;
SQL> select name from v$datafile;
SQL> select name from v$tempfile;
SQL> select member from v$logfile;
SQL> select name from v$controlfile;
36)Create a spfile from the pfile(This creates a spfile in $ORACLE_HOME/dbs location#) :
SQL> create spfile from pfile=’/backup location/initdbname.ora’;
37)Now, restart the database :
SQL> shut immediate;
SQL> startup
38)Check the listener status:
SQL> lsnrctl status <listener_name> |grep <database_name>
Database Restore Completed Here continue with Post Steps to restore the Grants ,Users , Roles,Db Link (Steps 39 to 52 are only as per requirement if no such requirement , can be ignore)
====================================
39)Run pre_refresh_user.sql(step 1) :
====================================
SQL> spool pre_refresh_user_post.log
SQL> @pre_refresh_user.sql
SQL> spool off;
==============================
40)Run sys_privs.sql(step 2) :
==============================
SQL> spool sys_privs_post.log
SQL> @sys_privs_pre.sql
SQL> spool off;
==========================================
Steps for password sql
===========================
change the profile here whice allow you to change the password.
SQL> spool passwords_post.log
SQL> @passwords.sql
SQL> spool off;
step d) reset the profiles resource limit to the pre existing values
==============================
41)Run passwords.sql(step 3) :
==============================
SQL> spool passwords_post.log
SQL> @passwords.sql
SQL> spool off;
=========================================================================
42)If you got any error like 'ORA-28007: the password cannot be reused' :
=========================================================================
SQL> select username,profile from dba_users where username in ('<u1>','<u2>','<u3>','<u4>','<u5>','<u6>');
SQL> SELECT PROFILE,LIMIT FROM DBA_PROFILES where RESOURCE_NAME='PASSWORD_REUSE_TIME';
=====================================================
43)Change PASSWORD_REUSE_MAX parameter to UNLIMITED :
=====================================================
SQL> alter profile <profile name> LIMIT PASSWORD_REUSE_MAX to UNLIMITED;
SQL> spool passwords_post1.log
SQL> @passwords.sql
SQL> spool off;
======================================================================================
44)Roll back PASSWORD_REUSE_MAX parameter to Original value which is in profiles.sql :
======================================================================================
SQL> alter profile <profile name> LIMIT PASSWORD_REUSE_MAX <value/DEFAULT>;
==========================
45)Run owner.sql(step 4) :
==========================
SQL> spool owner_post.log
SQL> @owner.sql
SQL> spool off;
===========================================
46)Run pre_refresh_role_privs.sql(step 5) :
===========================================
SQL> spool pre_refresh_role_privs_post.log
SQL> @pre_refresh_role_privs.sql
SQL> spool off;
=================================
47)Run user_profile.sql(step 7) :
=================================
SQL> spool user_profile_.log
SQL> @user_profile.sql
SQL> spool off;
============================
48)Drop the Database links :
============================
SQL> spool dblink_drop.log
SQL> set lines 1000 pages 1000
SQL> col OWNER for a20
SQL> col DB_LINK for a20
SQL> col HOST for a15
SQL> select * from dba_db_links;
==login with DB link owner
SQL> drop database link DB_LINK_source_TO_target;
Database link dropped.
SQL> conn / as sysdba
Connected.
========================================================
49)Create the Database links from the dumpfile(step 9) :
========================================================
SQL> create directory <directory name used for export> as '/u02/oradata/global/';
SQL> exit
$ impdp full=y include=DB_LINK dumpfile=dblink.dmp logfile=databaselink_post.log directory=prechecks
========================================================
50)Create the Database jobs from the dumpfile(step 10) :
========================================================
$ impdp full=y include=procobj dumpfile=jobs.dmp logfile=jobs_post.log directory=prechecks
===============================================
51)Create Password file : in cd $ORACLE_HOME/dbs
=================================================
$ orapwd file=orapw<dbname> password=stioerug4_qa3UX
========================================================================
52)Check the connectivity from SQL Developer to the respective schemas in Database by using service name :
#################### Database Refresh Completed ####################
