DBA Fundamentals

As a DBA, you are responsible to recover any failures to a point of failure and also to perform your backup while online. Your organization is a 24x7 shop 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 at least be informed of the following basic fundamental subjects:

In this exercise you will learn how to Archive the database while the database is in the NOARCHIVELOG MODE and also learn how to maintain the online Redo Log files and checkpoints.
 

Connect to a database
Let's first, connect to SQL*Plus as the system/manager user.
SQL> CONNECT system/manager@yourhost AS SYSDBA
 

Check archive mode
Check to see, if you are in the archive mode or not.
SQL> ARCHIVE LOG LIST
 

Change a database to archive mode

First you should change the following parameters in the parameter file.

Shutdown the database and then start the instance and mount but do not open the database. To change the database mode to the NOARCHIVELOG or the ARCHIVELOG modes, you must shutdown and then startup the database with the MOUNT option. When you change the database mode, make sure to take a complete offline backup of the database. You will learn more about how to take a complete OFFLINE backup during the backup and recovery Hands-On exercises.

/

Now, change the archiving status of the database.
SQL> ALTER DATABASE ARCHIVELOG
/
Notice that this command will modify the contents of the control files. You can change it back by using the ALTER DATABASE NOARCHIVELOG statement.
 

Open a database
Open the SCHOOL database.
SQL> ALTER DATABASE OPEN
/
 

Just query the DBA_USERS view to verify that the database is open.
SQL> SELECT count(1)
FROM dba_users
/
 

Check an archive log mode
Check the archive log list again.
SQL> ARCHIVE LOG LIST
Notice that the database mode was changed. It is very important that after changing the database mode, to shutdown the database and then, take a complete OFFLINE backup.
 

Check Log file status
Query the V$LOG dictionary view and take notes on the STATUS column where the online redo log file is CURRENT.
SQL> SELECT * FROM v$log
/
 

Switch an Online Redo Log
To change the Online Redo Log file, use the ALTER SYSTEM SWITCH command. Now, switch the Online Redo Log file to the next Online Redo Log file.
SQL> ALTER SYSTEM SWITCH LOGFILE
/
 

Check log status

Query the V$LOG directory view again and take notes on the STATUS column.
SQL> SELECT * FROM v$log
/
Notice that the CURRENT value, is on a different group number. Remember that anytime the Online Redo Log file switches the CHECKPOINT PROCESS it tells the DB Writer to write all of the dirty blocks in the database.
 

View checkpoint parameters
Show all the checkpoint parameters.

SQL> SHOW PARAMETER checkpoint
Notice that the LOG_CHECKPOINT_INTERVAL is zero. That means, you have a checkpoint whenever the Online Redo Log file switches. The default LOG_CHECKPOINT_TIMEOUT is 1800, and it means that if the Redo Log file did not fill up within 30 minutes, the checkpoint will tell the DB Writer to write all of the dirty blocks in the database. If the LOG_CHECKPOINTS_TO_ALERT value is true, then any occurrence of checkpoint will be written in the database alert file.
 

Checkpoint Manually
Now, let's try to force a checkpoint to happen manually.
SQL> ALTER SYSTEM CHECKPOINT
/
 

Checkpoint every � hours
Set the checkpoint timeout to a 2-hour time interval.
SQL> ALTER SYSTEM SET log_checkpoint_timeout = 7200
 

Q: How do you configure a database to an archive mode?

Q: What are the benefits of changing a database mode to the archive mode?

Q: How do you maintain an Oracle Online redo log file?

Q: How do you monitor and maintain the checkpoint process?

Q: Describe the database NOARCHIVELOG mode.

Q: Describe the DBA_USERS view.

Q: Describe the V$LOG dictionary view.

Q: What does the STATUS column indicate in the V$LOG view?

Q: Describe the following parameters:

LOG_CHECKPOINT_INTERVAL parameter

LOG_CHECKPOINT_TIMEOUT parameter

LOG_CHECKPOINTS_TO_ALERT parameter

Q: Describe the ARCHIVE LOG LIST command.

Q: What does the following statement do?

/
Q: How do you switch an online redo log file?

Q: How do you perform a checkpoint manually?

Q: How do you perform a checkpoint automatically?