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 II

 

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 27 | Lesson 28 | Lesson 29 |

 

Lesson 03

“My mother drew a distinction between achievement and success. She said that 'achievement is the knowledge that you have studied and worked hard and done the best that is in you. Success is being praised by others, and that's nice, too, but not as important or satisfying. Always aim for achievement and forget about success.' “ Helen Hayes (1900 - 1993)

 

Read first then play the video:

   DBA-VIDEO -Configuring the Database Archive mode (Advanced)

   

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.

 

“There are more things in heaven and earth, Horatio, Than are dreamt of in your philosophy.” William Shakespeare (1564 - 1616), "Hamlet", Act 1 scene 5

 

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?