iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 08

"The nice thing about being a celebrity is that if you bore people they think it's their fault." - Henry Kissinger (1923)

 

Read first then play the video:  

   DBA-VIDEO -Configure the database to the archive mode

   

Configure the database to the archive mode

 

Introduction

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:

 

Archiving the database

Maintaining the Online Redo Log files

Maintaining the checkpoints

Using the NOARCHIVELOG mode

Using the ARCHIVELOG mode

Using the DBA_USERS view

Using the V$LOG dictionary view

The STATUS column

Using the LOG_CHECKPOINT_INTERVAL parameter

Using the LOG_CHECKPOINT_TIMEOUT parameter

Using the LOG_CHECKPOINTS_TO_ALERT parameter

Commands:

ARCHIVE LOG LIST

SHUTDOWN IMMEDIATE

STARTUP MOUNT PFILE=init.ora

ALTER DATABASE ARCHIVELOG

ALTER DATABASE OPEN

ALTER SYSTEM SWITCH LOGFILE

ALTER SYSTEM CHECKPOINT

SHOW PARAMETER checkpoint

ALTER SYSTEM SET log_checkpoint_timeout = 7200

 

Hands-on

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@school 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.

log_archive_dest = /u01/app/oracle/admin/<database_name>/arch

log_archive_start = true

log_archive_format = log%s.arc

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.

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

PFILE=%ORACLE_HOME%.ora

/

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
/


Check a database is 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

 

"Education is a progressive discovery of our own ignorance." - Will Durant

 

Questions:

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?

SQL> STARTUP MOUNT

               PFILE=%ORACLE_HOME%.ora

/
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?