Database refresh is done in three ways. It depends on the version of the database.
In this scenario we copy the files from mount to mount and then recreate the control file.
Target Database: stage
Source Database: prod
Step1:
#################################Take the backup of user passwords###########
SQL>select name from gv$database;
SQL>spool alter.sql
SQL>select 'alter user '||username||' identified by values '''||password||''';' from dba_users;
SQL>spool off
#################################Backup of users#################
SQL>set echo off
SQL>set head off
SQL>set pagesize 0
SQL>set feedback off
SQL>spool pre_refresh_user.sql
SQL>select 'create user '||username||' identified by values '''||password||''' default tablespace '||default_tablespace||' temporary tablespace '||temporary_tablespace||' profile default ; ' from dba_users
where username in (
select username from dba_users where username like '%%' ) ;
SQL>spool off
###############################Backup of Grants of users########
SQL>spool sys_privs.sql
SQL>select 'grant '||privilege||' to '||grantee||' ; ' from dba_sys_privs where grantee in (select username from dba_users where username like '%%' ) ;
SQL>spool off
#############################Backup of grants on tables###########
SQL>spool owner.sql
SQL>select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||' ; ' from dba_tab_privs ;
SQL>spool off
###########################Backup of roles on grantee############
SQL>spool pre_refresh_role_privs.sql
SQL>select 'grant '||granted_role||' to '||grantee||' ; ' from dba_role_privs where grantee like '%%';
SQL>spool off
########################Backup of Database Links####################
SQL>Spool DB_Links.sql
SQL>SELECT
'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '''
||L.PASSWORD||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# ;
SQL>Spool off
##########################################################
Take the backup of pfile:
SQL> create pfile='location' from spfile;
#########################################################
COMPARE THE MOUNTS AND SPACE AVAILABILTY ON BOTH THE SERVERS(SOURCE AND TARGET)
#########################################################
CLEANUP THE FILES MOUNTS IN TARGET
##########################################################
PUT ALL THE TABLESPACES IN BEGIN BACKUP MODE ON SOURCE DATABASE(PROD)
SQL> select distinct status from v$backup;(before begin backup)
STATUS
------------------
NOT ACTIVE
SQL>spool alter.sql
SQL> select UNIQUE 'ALTER TABLESPACE '||a.tablespace_name||' BEGIN BACKUP;'
FROM sys.dba_data_files A, sys.dba_tablespaces B
WHERE A.tablespace_name = B.tablespace_name
AND A.status = 'AVAILABLE'
AND B.status = 'ONLINE';
SQL>spool off
SQL>@alter.sql
SQL> select distinct status from v$backup;
STATUS
------------------
ACTIVE
#########################################################
Now copy all the source prod database files to stage server
Example :
nohup scp /prod/u10/oradata/*.* /stage/u10/oradata/. &
copy all archive logs also.
check the count of each mount after copy as below
ls -ltr /prod/u012/oradata/*.dbf |awk '{s += $5 } END {print s/1024/1024/1024 }'
ls -ltr /stage/u012/oradata/*.dbf |awk '{s += $5 } END {print s/1024/1024/1024 }'
#############################################################
Now put all the tablespaces of source database in end backup mode:
SQL> select distinct status from v$backup;
STATUS
------------------
ACTIVE
SQL>SELECT UNIQUE 'ALTER TABLESPACE '||a.tablespace_name||' END BACKUP;'
FROM sys.dba_data_files A, sys.dba_tablespaces B
WHERE A.tablespace_name = B.tablespace_name
AND A.status = 'AVAILABLE'
AND B.status = 'ONLINE';
SQL>@alter.sql(spool in alter.sql)
SQL> select distinct status from v$backup;
STATUS
------------------
NOT ACTIVE
#############################################################
Altering the logswitch at source
SQL> Alter system switch logfile;
System altered.
copy the newly generated archive to target location
#################################################################
backup the control file to trace and copy it to target database(stage).
SQL>alter database backup controlfile to trace;
Database altered.
################################################################
Creating the controfile from trace fileS
Major changes
____________
1. CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS ARCHIVELOG
Change to
CREATE CONTROLFILE REUSE SET DATABASE "STAGE” RESETLOGS
NOARCHIVELOG
2. Before removing the temp files copy them on notepad (and keep checking archives at source)
3. :%s/PROD/STAGE/g (changing the source name to target name)
4. NOW Create the control file.
Recovering the database by applying archives that copied from source *************************************
Apply all copied archives as below providing the location of archives
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE until cancel;
Alter database open resetlogs;
Database altered.
THE STATUS SHOULD BE OPEN(check from v$instance)
SQL> SELECT * FROM V$INSTANCE;
##############################################################
Alter the tempfiles on target database.
SQL> select name from v$tempfile;(physical structure is created already)
no rows selected
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/stage/u012/oradata/temp01.dbf'
SIZE 2000M REUSE AUTOEXTEND OFF;
Tablespace altered.
SQL>Select global_name from global_name;
SQL> Update global_name set global_name='stage';
1 row updated.
SQL> Select global_name from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
stage
Run below queries on target database:
SQL>select name from v$datafile where name like '%/miss%';
SQL>select name from v$datafile where name like '%/MISS%';
SQL>select name from v$datafile;
SQL>select name from v$controlfile;
SQL>select member from v$logfile;
################################################################
Run all the backup scripts took at the beginning.
###############################################################
- Mount to Mount(if the version is <=9i)
- Active Duplicate(10g)
- Active Cloning(11g)
In this scenario we copy the files from mount to mount and then recreate the control file.
Target Database: stage
Source Database: prod
Step1:
#################################Take the backup of user passwords###########
SQL>select name from gv$database;
SQL>spool alter.sql
SQL>select 'alter user '||username||' identified by values '''||password||''';' from dba_users;
SQL>spool off
#################################Backup of users#################
SQL>set echo off
SQL>set head off
SQL>set pagesize 0
SQL>set feedback off
SQL>spool pre_refresh_user.sql
SQL>select 'create user '||username||' identified by values '''||password||''' default tablespace '||default_tablespace||' temporary tablespace '||temporary_tablespace||' profile default ; ' from dba_users
where username in (
select username from dba_users where username like '%%' ) ;
SQL>spool off
###############################Backup of Grants of users########
SQL>spool sys_privs.sql
SQL>select 'grant '||privilege||' to '||grantee||' ; ' from dba_sys_privs where grantee in (select username from dba_users where username like '%%' ) ;
SQL>spool off
#############################Backup of grants on tables###########
SQL>spool owner.sql
SQL>select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||' ; ' from dba_tab_privs ;
SQL>spool off
###########################Backup of roles on grantee############
SQL>spool pre_refresh_role_privs.sql
SQL>select 'grant '||granted_role||' to '||grantee||' ; ' from dba_role_privs where grantee like '%%';
SQL>spool off
########################Backup of Database Links####################
SQL>Spool DB_Links.sql
SQL>SELECT
'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '''
||L.PASSWORD||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# ;
SQL>Spool off
##########################################################
Take the backup of pfile:
SQL> create pfile='location' from spfile;
#########################################################
COMPARE THE MOUNTS AND SPACE AVAILABILTY ON BOTH THE SERVERS(SOURCE AND TARGET)
#########################################################
CLEANUP THE FILES MOUNTS IN TARGET
##########################################################
PUT ALL THE TABLESPACES IN BEGIN BACKUP MODE ON SOURCE DATABASE(PROD)
SQL> select distinct status from v$backup;(before begin backup)
STATUS
------------------
NOT ACTIVE
SQL>spool alter.sql
SQL> select UNIQUE 'ALTER TABLESPACE '||a.tablespace_name||' BEGIN BACKUP;'
FROM sys.dba_data_files A, sys.dba_tablespaces B
WHERE A.tablespace_name = B.tablespace_name
AND A.status = 'AVAILABLE'
AND B.status = 'ONLINE';
SQL>spool off
SQL>@alter.sql
SQL> select distinct status from v$backup;
STATUS
------------------
ACTIVE
#########################################################
Now copy all the source prod database files to stage server
Example :
nohup scp /prod/u10/oradata/*.* /stage/u10/oradata/. &
copy all archive logs also.
check the count of each mount after copy as below
ls -ltr /prod/u012/oradata/*.dbf |awk '{s += $5 } END {print s/1024/1024/1024 }'
ls -ltr /stage/u012/oradata/*.dbf |awk '{s += $5 } END {print s/1024/1024/1024 }'
#############################################################
Now put all the tablespaces of source database in end backup mode:
SQL> select distinct status from v$backup;
STATUS
------------------
ACTIVE
SQL>SELECT UNIQUE 'ALTER TABLESPACE '||a.tablespace_name||' END BACKUP;'
FROM sys.dba_data_files A, sys.dba_tablespaces B
WHERE A.tablespace_name = B.tablespace_name
AND A.status = 'AVAILABLE'
AND B.status = 'ONLINE';
SQL>@alter.sql(spool in alter.sql)
SQL> select distinct status from v$backup;
STATUS
------------------
NOT ACTIVE
#############################################################
Altering the logswitch at source
SQL> Alter system switch logfile;
System altered.
copy the newly generated archive to target location
#################################################################
backup the control file to trace and copy it to target database(stage).
SQL>alter database backup controlfile to trace;
Database altered.
################################################################
Creating the controfile from trace fileS
Major changes
____________
1. CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS ARCHIVELOG
Change to
CREATE CONTROLFILE REUSE SET DATABASE "STAGE” RESETLOGS
NOARCHIVELOG
2. Before removing the temp files copy them on notepad (and keep checking archives at source)
3. :%s/PROD/STAGE/g (changing the source name to target name)
4. NOW Create the control file.
Recovering the database by applying archives that copied from source *************************************
Apply all copied archives as below providing the location of archives
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE until cancel;
Alter database open resetlogs;
Database altered.
THE STATUS SHOULD BE OPEN(check from v$instance)
SQL> SELECT * FROM V$INSTANCE;
##############################################################
Alter the tempfiles on target database.
SQL> select name from v$tempfile;(physical structure is created already)
no rows selected
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/stage/u012/oradata/temp01.dbf'
SIZE 2000M REUSE AUTOEXTEND OFF;
Tablespace altered.
SQL>Select global_name from global_name;
SQL> Update global_name set global_name='stage';
1 row updated.
SQL> Select global_name from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
stage
Run below queries on target database:
SQL>select name from v$datafile where name like '%/miss%';
SQL>select name from v$datafile where name like '%/MISS%';
SQL>select name from v$datafile;
SQL>select name from v$controlfile;
SQL>select member from v$logfile;
################################################################
Run all the backup scripts took at the beginning.
###############################################################