Yesterday I discussed about backup Overview & cold backup if you have not gone though it you can read it from
Yesterday I mentioned that I am going to post about hot backup scripts , here it comes
Enable Archive Log
First check if your database is in Archive mode
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
If you see Automatic archival is enabled that means database is in archive mode . If not then
SQL>ALTER DATABASE ARCHIVELOG;
SQL>ALTER DATABASE OPEN;
SQL>alter system set LOG_ARCHIVE_DEST_1 =’LOCATION=/your/archive/location/arch’ scope=spfile;
Steps are for database 10g , These commands may vary according to your database version.
After that for Hotbackup
Change tablespace mode to begin backup like
alter tablespace tablespace_name begin backup; you have to do this for all tablesapces in your database except temporary tablespace (This can be tedious work if you are doing it manually so here is script to do so )
SQL>set head off
SQL>select ‘alter tablespace ‘ tablespace_name ‘ begin backup;’ from dba_tablespaces;
This will create file beginbackup.sql with entry for all tablespaces, remove any unnecessary lines & then execute this script into SQL like
SQL>@beginbackup.sql (Once you execute this script this will put all tablespaces in to begin backup mode)
Now create backup of your control file in Human Readable format like
alter database backup controlfile to trace as ‘/some/path’; you can reuse it by removing comment at beginning & replace them with connect / as sysdba
1. Copy all your datafiles, redo logs and control file from your database server to backup location.
after datafiles are copied don’t forget to end backup for all tablespace here is the scripts
SQL>set head off
SQL>select ‘alter tablespace ‘ tablespace_name ‘ end backup;’ from dba_tablespaces;
This will create file endbackup.sql with entry for all tablespaces, remove any unnecessary lines & then execute this script into SQL like
SQL>@endbackup.sql (Once you execute this script this will put all tablespaces in to end backup mode)
This completes your datafiles backup but you still have to backup database software & oracle Applications Middle Tier
2. Copy database software $ORACLE_HOME from server to backup location
3. Copy Apps Middle tier all TOPs (APPL_TOP, COMMON_TOP, ORA_TOP)
TIP: Sometime if you copy application tier while apache is Up , you copy httpd.pid or httpd_pls.pid from $IAS_ORACLE_HOME/Apache/Apache/logs/httpd.pid and if in restored Instance Apache doesn’t start & complain about httpd.pid or httpd_pls.pid already exists , you can simply delete these two files from restored Instance & start Apache Web Server.
Please note I am not in town for another 4 days so I’ll not be able to Upadte this Site till Tuesday. Thanks in Advance for your patience.
Oracle ACE, Author, Speaker and Founder of K21 Technologies & K21 Academy : Specialising in Design, Implement, and Trainings.