everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

DBA Fundamentals

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
<< Previous

Chapter # 30

Next >>


 

Configuring the Database Archive mode

 

Introduction

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:

 

Configuring the Database Archive Mode

Setting the database to the ARCHIVELOG mode

Querying archive log information

Making a directory.

Enabling the automatic archival process

Setting an archive destination.

Using the Server Parameter File (SPFILE)

Commands:

ARCHIVE LOG LIST

ALTER DATABASE CLOSE

ALTER SYSTEM SET log_archive_start=true SCOPE=spfile

ALTER SYSTEM SET log_archive_dest='c:'

ALTER SYSTEM SET log_archive_format='arc%S.%T' SCOPE=spfile

The %S entry

The %T entry

Shutdown and Startup Database

SHUTDOWN IMMEDIATE

STARTUP

 

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?

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
/
 

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?

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.