DBA Fundamentals

You, as a DBA, are responsible to recover any failures to a point of failure and also to perform your backup while in an online status. Your shop is 24x7 and you are not able to shutdown the database. Therefore, the database has to be in an archive mode. Your job�s responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

Hands-on
In this exercise you will learn how to change the database mode from the NOARCHIVELOG mode to the ARCHIVELOG mode.

Now, connect to SQLPlus as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA
 

Check a database is in archive mode
Let's first check if the database is in the ARCHIVELOG mode.
SQL> ARCHIVE LOG LIST
This result indicates that the database is not currently in the ARCHIVELOG mode.
 

Change a database to an archive mode
First, close the database if the database is open. If the database is not open then startup the database with the MOUNT option. Since the database is open, we should close the database, and then change the database mode from the NOARCHIVELOG mode to the ARCHIVELOG mode.
SQL> --- Close the database.
SQL> ALTER DATABASE CLOSE
/

SQL> ------ Set the database to the ARCHIVELOG mode.
SQL> ALTER DATABASE ARCHIVELOG
/

Query the archive log information.
SQL> ARCHIVE LOG LIST
Notice that the Database Log Mode was changed to the Archive Mode.

Now, make a directory called archivelogs and then enable the automatic archival to be performed in the new archive destination.
SQL> ----- First make a directory.
SQL> HOST MKDIR c:

SQL> -- And then enable automatic archival process and set the archive destination.
SQL> ALTER SYSTEM ARCHIVE LOG START TO 'c:'
/

Query the archive log information.
SQL> ARCHIVE LOG LIST
Notice that the Automatic Archival was enabled and the archivelog destination was changed to a new location.

Make sure that the automatic archival process, the archive log destinations, and the archive format are all set in the Server Parameter File (SPFILE).

Note that the Server Parameter File is the same as the database parameter file. The only difference is: it is in a binary format code and can not be read or edited. It is used to change the Oracle system parameters dynamically by using the ALTER SYSTEM SET command.
.
ALTER SYSTEM SET log_archive_start=true SCOPE=spfile
/
ALTER SYSTEM SET log_archive_dest='c:' SCOPE=spfile
/
ALTER SYSTEM SET log_archive_format='arc%S.%T' SCOPE=spfile
/
-- Note that the %S entry includes the online redo log sequence number. The %T entry includes the database instance or thread number. Remember that the SCOPE parameter will guarantee that the new configurations will be written in the Server Parameter File.
 

Activate archive mode
Now, shutdown the database, then connect to the system as the SYSTEM/MANAGER user, and startup the database using the Server Parameter File (SPFILE). Notice that we get the (ORA-01109: database not open) error message. We got this message because we closed the database but not the instance.
SQL> ------------>>> Shutdown
SQL> SHUTDOWN IMMEDIATE
SQL> ------------>>> Connect
SQL> CONNECT system/manager AS SYSDBA
SQL> ------------>>> Startup
SQL> STARTUP

Query the archive log information again.
SQL> ARCHIVE LOG LIST
The database is now in the ARCHIVE mode.
 

Questions:

Q: How do you configure your database to an archivelog mode?

Q: How do you query your database�s archive log information?

Q: How do you set an archive log destination?

Q: What is the Server Parameter file (SPFILE)?

Q: What do the following statements do?

Q: You, as a DBA, are responsible to recover any failures to a point of failure and also to perform your backup while in online status. Your shop is 24x7 and you are not able to shutdown the database. Therefore, the database has to be in an archive mode. You should change you database mode from noarchivelog to archivelog mode. What are the steps that you should perform to change your database mode?