Home » SQL & PL/SQL » SQL & PL/SQL » Re: Backup
| Re: Backup [message #19536] |
Tue, 26 March 2002 07:33  |
Siva Ram
Messages: 22
Registered: November 2001
|
Junior Member |
|
|
Hello all
This is the document which describes a detailed step-by-step solution for BACKUP.
Hot Backup: This type of backup is done while the database is still up, running, being accessed and updated. This type of backup should be used only where continuous 24/7 operations are required. If you have even a half hour window when your database can be down, do a COLD BACKUP.
NOTE: NEVER backup the online redo log files when doing hot backups. Restoring a backup of an online redo log file would cause corruption during database recovery. The online redo log files are the most vulnerable items in the hot backup scheme. Forcing log switches can catch the pending datafile updates in the redo log and gives some coverage in this area.
You cannot backup the online control files themselves, but you can backup a special copy of them, either a binary copy or a textual copy.
The steps are classified as:
Ř Pre Backup Checklist (Steps from 1 – 11)
Ř Backup Procedure (Steps from 12 – 15)
Ř Backup Confirmation (Steps 22 – 29)
Ř Closing Server Manager (Steps 30 and 31)
The following are the steps to perform "HOT BACKUPs" in Oracle 8.1.6
1) Connect to Server Manager
C:> svrmgrl
O/p SVRMGR>
2) Connect as Internal
SVRMGR> Connect Internal;
O/p Connected
3) Check whether the database is running or not
SVRMGR> Select name, log_mode, created from v$database
(Note down the Name, which is the Databasename, and Log_mode
which should be ARCHIVELOG)
If NO output is displayed, it means that database is not running. Issue the command “STARTUP” to start Oracle.
4) Check the parameters of the database in use
SVRMGR> show parameter db
Note down the db_domain and db_name values.
db_name.db_domain will be the host string
5) Check the archival mode, archive destination; oldest online log sequence, next log sequence and current log sequence
SVRMGR> archive log list
The database Log mode should show as Archive mode and Automatic archival should show as Enabled and Archive destination should the path for the archives, say C:Oracleoradata<db_name>archive.
If either of the values are not the same as said above, follow these steps:
a) Shutdown the database in normal mode i.e.,
SVRMGR> shutdown normal
b) Open the init.ora file that is located in C:Oracleadmin<db_name>pfile using WordPad or Notepad
c) Add the following lines (parameters)
Log_archive_dest = true
Log_archive_dest_1 = “location=C:Oracleoradata<db_name>archive
Log_archive_format = %%ORACLE_SID%%T%TS%S.ARC
d) Save the file
e) Shutdown the instance in normal mode i.e.,
SVRMGR>shutdown normal
f) Restart the database with mount i.e., STARTUP MOUNT
g) Place the database in archived log mode using
SVRMGR> Alter database archivelog;
h) Open the database for normal processing using
SVRMGR> Alter database open;
i) Check the archive log list using
SVRMGR> archive log list
The database Log mode should show now as Archive mode and Automatic archival should show as Enabled and Archive destination should me C:Oracleoradata<db_name>archive.
6) Check SGA details, only to check the memory available
SVRMGR> show sga
7) Display the entire path of data file, tablespace name and corresponding field
SVRMGR>Select substr(file_name, 1,40) as FileName, substr(tablespace_name, 1,15) as TableSpaceName, File_id from DBA_DATA_FILES;
(Note down the tablespace name, path of the corresponding datafile, and file_id)
8) Check the log archive destination, format, start, and standy archive destination
SVRMGR> show parameter archive
(Note down the log_archive_dest_1, log_archive_format, standby_archive_dest values. Should show as set in step 5.
9) Check the contents of Oradata folder (C:Oracleoradata) and check the .dbf, .ctl, redo<n> files. The dates should be current date.
10) Check the contents of archive folder (C:Oracleoradataarchive)
11) Check the contents of backup folder (C:Oracleadmin<db_name>backuporadata).
There should be three folders with names as archive, oradata, and pfile including .dbf, .ctl, REDO<n>, .trc file(s).
12) Begin backup for SYSTEM tablespace (That is in the order of file_id from DBA_DATA_FILES, refer to Step 7 output)
SVRMGR> alter tablespace system begin backup;
13) Copy the corresponding data file to the backup folder
SVRMGR> host copy c:oracleoradata<db_name>system01.dbf
c:Oracleadmin<db_name>backuporadata
14) Check whether the file is copied or not
SVRMGR> host dir c:Oracleadmin<db_name>backuporadata
15) End backup for SYSTEM tablespace
SVRMGR> alter tablespace system end backup;
16) Repeat the steps 12 - 15 for the remaining table spaces i.e., for RBS, USERS, TEMP, TOOLS, INDX, DRSYS
17) Force a log switch to archive the current log to catch all of the commited transactions
in it
SVRMGR> alter system switch logfile;
18) Check the archival mode, archive destination, oldest online log sequence, next log sequence and current log sequence
SVRMGR> archive log list
Note/observe the change in next log sequence and current log sequence numbers.
19) After 10 seconds (advisable), copy the archivelog files to backup folder
SVRMGR> host copy C:Oracleoradata<db_name>archive*.*
C:Oracleadmin<db_name>backuparchive
(Check the dates of the archive files, Winzip files)
20) Create a control file to backup binary copy
SVRMGR> alter database backup controlfile to 'C:Oracleadmin
<db_ name>backuporadatacontrolfile.ctl';
21) Create a control file to backup textual copy i.,e trace file
SVRMGR> alter database backup controlfile to trace;
(By default this .trc file is placed in C:Oracleadmin<db_name>udump
22) Copy the .trc file created to backup folder
SVRMGR> host copy C:Oracleadmin<db_name>dump<filename>.trc C:Oracleadmin<db_name>backuporadata
(Open the .trc file and check the contents of it)
23) Check the status of backup files
SVRMGR> Select * from v$backup;
Observe the Time column. Current date indicates backup was completed successfully. Also the Status of each file in this view should be NOT ACTIVE.
24) Check whether the backup has performed successfully or not
SVRMGR> Select name, created, log_mode, controlfile_time from v$database;
The Controlfile_time should be current date and Log_mode should be ARCHIVELOG.
25) Check whether the archiving took place or not
SVRMGR> Select substr(name,1,60), first_time, completion_time from v$archived_log;
The Completion_time should be current date.
26) Check the status of the datafile
SVRMGR> Select status, enabled, substr(name,1,45) as Name from v$datafile;
Status should be System or Online
27) Check the status of the controlfile
SVRMGR> Select status, substr(name,1,45) as Name from v$controlfile;
Status should be blank
28) Check the status of the logfile
SVRMGR> Select status, member from v$logfile;
One of the files Status should be STALE
29) Display the status of backup files
SVRMGR> Select * from v$backup;
Observe the time column, current date indicates backup was completed successfully.
30) Disconnect from Server Manager
SVRMGR> Disconnect;
31) Exit Server Manager
SVRMGR> Exit;
O/p C:>
if any one of u need in the form of a document, let me know.
Thank you
Siva Ram
|
|
|
|
| Re: Backup [message #20432 is a reply to message #19536] |
Thu, 23 May 2002 06:51  |
Tony Dimuna
Messages: 1
Registered: May 2002
|
Junior Member |
|
|
Thanks for providing the insight on backup process. I do hope this would be used as a checklist to build a backup procedure. Do you have a simliar document for Recovery. Please I would be pleased if you can send this in a document format to my e-mail address preferably for oracle 8i and above.
Tony
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 20:21:26 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
