Putting a Database in ARCHIVELOG Mode
- First, validate that the database is in NOARCHIVELOG mode using the V$DATABASE
column LOG_MODE:
SQL> Select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG- Next, look at the settings for the parameters (note that we have removed some of
the LOG_ARCHIVE_DEST_n parameter to save trees) LOG_ARCHIVE_DEST_1 and LOG_
ARCHIVE_FORMAT:
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- -----------
log_archive_dest_1 string
log_archive_dest_10 string
SQL> show parameter log_archive_format
NAME TYPE VALUE
------------------------------------ ----------- -----------
log_archive_format string ARC%S_%R.%T3. Create the archive log directory c:\oracle\arch\orcl
4. You want to modify LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT so that they
are set correctly. LOG_ARCHIVE_DEST_1 should be set to c:\oracle\arch\orcl and
LOG_ARCHIVE_FORMAT should be orcl_%r_%t_%s.arc. You will use the ALTER SYSTEM
command to set these parameters. You will then check to make sure they are set
correctly.
Alter system set log_archive_dest_1='location=c:\oracle\arch\orcl';
-- Note that we have to use the scope=spfile on this next parameter.
-- This is because it's not dynamic!
Alter system set log_archive_format='orcl_%r_%t_%s.arc' scope=spfile;- Next, shut down the database in a consistent manner with the SHUTDOWN IMMEDIATE
command:
SQL> shutdown immediate
Now mount the database with the STARTUP MOUND command:
SQL> startup mount;- Put the database in ARCHIVELOG mode with the ALTER DATABASE ARCHIVELOG
command:
SQL> alter database archivelog;Open the database for operations:
SQL> alter database open;Make sure the database is in ARCHIVELOG mode:
SQL> Select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG- It is a good idea to make sure that everything is configured correctly and that the
archived redo logs are getting generated in the place where you expect them to
be generated. So, first you will force an archive-log switch with the ALTER SYSTEM SWITCH LOGFILE command. This will cause a log switch to the next redo log group, and ARCH will need to copy the redo log to an archived redo log:
SQL> alter system switch logfile;
System altered.Look in the c:\oracle\arch\orcl directory. You should see a file in that directory:
ORCL_658485967_1_2.ARC
The ORCL_659495967_1_2.ARC file is your archive log file, so ARCH is copying the log file
to the correct location.
11. you need to know what data files need to be backed up:
SQL> Select file_name from dba_data_files;
FILE_NAME
--------------------------------------------
C:\ORACLE\ORADATA\ORCL\USERS01.DBF
C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
C:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
C:\ORACLE\ORADATA\ORCL\REVEAL_DATA_01.DBF
C:\ORACLE\ORADATA\ORCL\REVEAL_INDEX_01.DBF
C:\ORACLE\ORADATA\ORCL\USERS02.DBF
7 rows selected.12. You should note the current online redo log sequence number at this point. You will
need this, plus all log sequences generated during the backup, to be able to perform
your recovery. You can get this number from the V$LOG view:
SQL> select group#, sequence#, status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 13 INACTIVE
2 14 CURRENT
3 12 INACTIVEIn this case, you see that you will need all log files from sequence number 14 on in
order to restore the backup you are preparing to use.
13.You now need to put the database in hot backup mode. Oracle Database 12c provides
the command ALTER DATABASE BEGIN BACKUP for this purpose. You can also back up
specific tablespaces with the ALTER TABLESPACE BEGIN BACKUP command:
SQL> alter database begin backup;
Database altered.14. The database data files are now ready to be backed up. You will copy the files to a
directory that you will create called c:\backup\orcl\backup2
15. Now copy all the database data files to this directory. In this case, all the files are in
the directory c:\oracle\oradata\orcl, and the filenames all end with an extension
of .DBF, so the command to copy them is pretty easy. Once you have started the data
file copy, go get something to eat. It might take a while.
15. Having patiently waited for the backup to complete, you now need to take the database
out of hot backup mode.
SQL> alter database end backup;
Database altered.16. Next, you need to determine the current log file sequence number. You will need the
earlier log file that you identified and all log files generated during the backup up to
the current log file to be able to restore this backup. The query is the same as the
query against the V$LOG view that we showed you earlier in this chapter:
SQL> select group#, sequence#, status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 13 INACTIVE
2 14 ACTIVE
3 15 CURRENTIn this example, you can see that during the backup you had a log file switch, from
sequence number 14 to sequence number 15. You see that 15 is the current sequence
number. You know now that you will need to back up the logs with sequence numbers
14 and 15 in order to be able to restore this backup.
16. You now need to force a log switch so the log with sequence number 15 (the current
online redo log sequence number) will be archived. To do this, you issue the ALTER
SYSTEM SWITCH LOGFILE command. This will cause Oracle to switch to the next log
file (sequence 16), and the current archive log (sequence 15) will be copied to the
archive-log directory by the ARCn processes.
SQL> Alter system switch logfile;
System altered.17. Having switched log files, you need to wait for ARCH to complete copying the last log
file to the archive-log directory. You can check for this completion by looking at the
V$ARCHIVED_LOG view:
SQL> Select sequence#, archived, status from v$archived_log
2 Where sequence# between 14 and 15;
SEQUENCE# ARC S
---------- --- -
14 YES A
15 YES A18. Now back up all archived redo logs, ensuring that all logs with numbers between
sequence x and sequence y are backed up.
copy all archived redo logs from the directory to your backup directory