Database Refresh

Database Refresh

Jawaji Mahesh

Wissen GE Internal

Team Business Solutions Table of Contents •Intoduction •Overview •Process •Verification

Wissen GE Internal

Introduction Database Refresh is performed on client’s request Refreshes (OR) if Schema is almost equal to the database size itself.

Wissen GE Internal

Overview Database refresh is performed from Source Database to Target Database . There are two types of Database Refresh.

•

Hot Refresh. Hot database refresh is done from Production databases to staging or development databases.( Source database will be in archive log mode and database will be up and running during the refresh).

•

Cold Refresh. Cold Database refresh is done from staging to Development or Vice-versa (Source will be down during the refresh since archive log is not enabled and we cannot put the database in hot backup mode).

Wissen GE Internal

Database Refresh Process 1.1 Source Server name, Database name, and database version 1.2 Target Server name, Database name and database version 1.3 Pre Refresh Steps in Target database

1.3.1 Check the source and target database versions. If versions are same proceed with refresh If target base Version is higher than source database, then refresh target database to version of Source database and then upgrade to the version (prior to refresh) using catpath.sql or DBUA. This file is present in $ORACLE_HOME/dbs Wissen GE Internal

1.3.2 Check the mount point occupancy of source and target databases. df -k |grep |sort If mount points in target are less than those in source then confirm first from User/Onsite team if datafiles can be stored in mount points other than the targets mount point. 1.3.3 Take the backup of following from the target database in the location /<servername>/ORACLE_ADMIN//scripts/refresh/    

Backup of all schema passwords Backup of sys privs other than default users (sys,System,dbsnmp,outln …) Backup of table privs other than default users Backup of roles other than default roles

Wissen GE Internal

2. Source Database 2.1 Steps for Source Database. (If Hot refresh) •

Place the database in begin backup mode. select distinct status from v$backup; - to check if all tablespaces are already in begin backup or not

•

If status is NOT ACTIVE then keep table spaces in begin backup mode. 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';

select distinct status from v$backup; Should be ACTIVE (means tablespaces are in begin backup mode) 2.2 Shut Down the Source Database. (If Cold Refresh) Wissen GE Internal

3. Steps for Target Database 3.1 Shut down the target database and remove the datafiles from the target mount points. Verify the mount points occupancy using the following command df -k |grep |sort 3.2 Copy the files from source mount to target mount points, check the no. of files copied with mtpt occupancy and it should be equal nohup scp /<sourcedb>/u010/oradata/*.* <servername of targetdb>://u010/oradata/. > nohup.out & We should maintain three mount points copy at a time and monitor the CPU usage on Source Server and Target Server. Wissen GE Internal

3.3 After all the mount points file copy completed check the size of the datafiles copied is same or not by issuing following command in both source and target. ls -ltr /<sourcedb>/u0*/oradata/*.* |awk '{s += $5 } END {print s/1024/1024/1024 }' ls - ltr //u0*/oradata/*.* |awk '{s += $5 } END {print s/1024/1024/1024 }'

Wissen GE Internal

4. Source Database. 4.1 Place the source database in end backup mode (If Hot Refresh) 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'; select distinct status from v$backup; Should be INACTIVE (means tablespaces are in end backup mode) Startup the Source database (If Cold Refresh) 4.2 Take the backup of control file and switch the archive logs. alter database backup control file to trace ;----( available at udump location) alter system switch logfile ; Wissen GE Internal

scp the control file and archives logs that were generated when database was in begin backup to the target’s create folder location. $ORACLE_ADMIN//create

Target Database 5. Make changes to control file and most likely changes are :%s/<sourcedb>// (initially) STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "<sourcedatabase>" NORESETLOGS ARCHIVELOG (After Changes) STARTUP NOMOUNT CREATE CONTROLFILE REUSE SET DATABASE "" RESETLOGS NOARCHIVELOG If EXTENT_MANAGEMENT=LOCAL then preserve temp table spaces information from control file before deleting. Wissen GE Internal

Step 6: Connect to Target database and run the create control file script. Step 7: Recover target database issuing following command (if Hot Refresh) RECOVER DATABASE USING BACKUP CONTROL FILE UNTIL CANCEL; Apply all the archives which were copied from source and give cancel. If it shows : “Media Recovery Cancelled” – then only open the database If Cold Refresh no need to Perform recovery. Just bring up the database by creating the control file first using above mentioned process and give : alter database open resetlogs; Step 8: Now open the database. alter database open resetlogs;

Wissen GE Internal

Post Steps

• • •

Create the temp file using the saved syntax from control file and after changing the mount point location from source to target. Revert backup the passwords and privileges taken in Step 1.3 Update the Global name to Target database. select * from global_name ; update global_name set global_name ='';

Wissen GE Internal

Verification - To be done at both Source and target • • • • • • •

select name from v$datafile ; select member from v$logfile ; select name from v$controlfile ; select name from v$tempfile ; select name from v$datafile where name like '%MISS%'; select file_name from dba_data_files where file_name like '%MISS%'; Select * from v$recover_file;

Wissen GE Internal

Wissen GE Internal