Tag: ARCHIVELOG mode

To use hot backups, the database should operate in ARCHIVELOG mode.

1. Check whether the database is in ARCHIVELOG mode or not

SQL> SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
————
ARCHIVELOG

If not, place the database in archive log as follows, (assuming your database is up and running)

SQL> alter database close;
SQLPLUS>alter database archivelog;
SQL>alter database open;

Display online log sequence

SQL> archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:oracleproduct10.1.0db_2RDBMS
Oldest online log sequence 169
Next log sequence to archive 171
Current log sequence 171

2. Files that require to backup found using the following query ie. backup of data files , online redo log files ,control files, spfile.

SQL> SELECT NAME “FILE NEED BACKUP” FROM V$DATAFILE
2 UNION ALL
3 SELECT NAME FROM V$CONTROLFILE
4 UNION ALL
5 SELECT MEMBER FROM V$LOGFILE
6 UNION ALL
7 SELECT VALUE FROM V$PARAMETER WHERE NAME=’SPFILE’;

FILE NEED BACKUP
——————————————————————————–

D:CRMDATASYSTEM.DBF
D:CRMDATASYSAUX.DBF
D:CRMDATAUNDOTBS.DBF
D:CRMDATACRM.DBF
D:CRMCONTROLCRM_CTL1.CTL
D:CRMCONTROLCRM_CTL2.CTL
D:CRMCONTROLTICKET_CTL3.CTL
D:CRMREDOR1.LOG
D:CRMREDOR2.LOG
D:CRMREDOR3.LOG

10 rows selected.

SQL> SELECT S.STATUS, S.TABLESPACE_NAME “Tablespace”, D.FILE_NAME “Datafile”
2 FROM DBA_TABLESPACES S, DBA_DATA_FILES D
3 WHERE S.TABLESPACE_NAME = D.TABLESPACE_NAME;

STATUS Tablespace Datafile
———— —————- ———-

ONLINE SYSTEM
D:CRMDATASYSTEM.DBF

ONLINE SYSAUX
D:CRMDATASYSAUX.DBF

ONLINE UNDOTBS
D:CRMDATAUNDOTBS.DBF

STATUS Tablespace Datafile
———— —————- ———–

ONLINE CRM
D:CRMDATACRM.DBF

Force a log switch, so that Oracle will create an archived log file

SQL> ALTER SYSTEM SWITCH LOGFILE;

Backup control file

SQL> ALTER DATABASE BACKUP CONTROLFILE TO filespec;