Setup Oracle Database Archivelog mode

A short musical video tutorial showing how easily you can configure Oracle Database to use archivelog mode. Using archivelog mode is highly recommended in any production database and allows RMAN hot backups (online backups) . You can also use RMAN without archivelog, but you’ll have to shutdown the database to backup it (cold backup).

Let’s system of a down :) Beware… highly advisable to TURN OFF sound if you don’t like hard rock :P

can be done easily with the following statements:

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

If your database version is prior 10g then after shutdown and before open database you must set log_archive_start = TRUE in your init.ora and/or spfile.ora. After that, login as "sys" and you can check it out with:

SQL> archive log list;

SQL> select log_mode from v$database;

Note: In this case, we need to do a quick restart of the database to enable it to "Archive Log Mode". So please ensure your the required outage window.

How can one improve Import/ Export performance?

EXPORT:
1) Set the BUFFER parameter to a high value (e.g. 2Mb -- entered as an integer "2000000")
2) Set the RECORDLENGTH parameter to a high value (e.g. 64Kb -- entered as an integer "64000")
3) Use DIRECT=yes (direct mode export)
4) Stop unnecessary applications to free-up resources for your job.
5) If you run multiple export sessions, ensure they write to different physical disks.
6) DO NOT export to an NFS mounted filesystem. It will take forever.

IMPORT:
1) Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.
2) Place the file to be imported on a separate physical disk from the oracle data files
3) Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file
4) Set the LOG_BUFFER to a big value and restart oracle.
5) Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)
6) Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)7) Use COMMIT=N in the import parameter file if you can afford it.
8) Use STATISTICS=NONE in the import parameter file to avoid time consuming to import the statistics
9) Remember to run the indexfile previously created.

Common Import/ Export Problems

ORA-00001: Unique constraint (...) violated
You are importing duplicate rows. Use IGNORE=YES to skip tables that already exist (imp will give an error if the object is re-created).

ORA-01555: Snapshot too old
Ask your users to STOP working while you are exporting or try using parameter CONSISTENT=NO

ORA-01562: Failed to extend rollback segment
Create bigger rollback segments or set parameter COMMIT=Y while importing

IMP-00015: Statement failed ... object already exists...
Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.

Enabling ARCHIVELOG Mode

This is a good review of enabling archive log - an article coming from cuddletech(http://www.cuddletech.com/articles/oracle/node58.html)

Most of the High Availability features of Oracle require you to enable ARCHIVELOG mode for your database. When you enable this mode redo logs will be archived instead of overwritten. The archivelogs are stored in a seperate place usually can backed up regularly by your standard filesystem backup system (NetBackup or whatever). Archive logs are utilized by RMAN, Data Guard, Flashback and many others.

If your going to enable archivelog mode on a real database thats important to you, I would recommend shutting down the database and doing a cold backup just in case. Keeping a “final noarchivelog mode backup” seems to be a good and excepted practice.

Enabling archive mode is simple, just connect to your database in mounted but closed mode (startup mount) and alter the database. But if you don’t tune alittle you’ll run into problems down the road, so lets specify some parameters too. Namely, consider LOG_ARCHIVE_DEST.

Lets start by checking the current archive mode.

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE

------------

NOARCHIVELOG

So we’re in NOARCHIVELOG mode and we need to change. We can use a database alter statement, but that won’t be perminant, so lets just update the pfile directly. The pfile should be in either $ORACLE_BASE/admin/SID/pfile or $ORACLE_HOME/admin/SID/pfile. I’ll add the following lines to the end of the file:

#############################

Archive Log Destinations -benr(10/15/04)

############################

log_archive_dest_1='location=/u02/oradata/cuddle/archive'

log_archive_start=TRUE

Note that we’re not actually required to specify the location of the log destination, but if you don’t it’ll end up in strange places (in my test it went to $ORACLE_HOME/dbs making a mess). You can specify as many as 10 diffrent archive log destinations by using the paramters log_archive_dest_1 through log_archive_dest_10. Remember, if you run out of space in your archive log destination the database will shut down!

Now we can startup the database in mount mode and put it in archivelog mode.

[oracle@vixen pfile]$sqlplus sys/passwd as sysdba;

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 15 16:00:58 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 184549376 bytes

Fixed Size 1300928 bytes

Variable Size 157820480 bytes

Database Buffers 25165824 bytes

Redo Buffers 262144 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

You can see here that we put the database in ARCHIVELOG mode by using the SQL statement “alter database archivelog”, but Oracle won’t let us do this unless the instance is mounted but not open. To make the change we shutdown the instance, and then startup the instance again but this time with the “mount” option which will mount the instance but not open it. Then we can enable ARCHIVELOG mode and open the database fully with the “alter database open” statement.

There are several system views that can provide us with information reguarding archives, such as:

V$DATABASE - Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode and whether MANUAL (archiving mode) has been specified.

V$ARCHIVED_LOG - Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.

V$ARCHIVE_DEST - Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.

V$ARCHIVE_PROCESSES - Displays information about the state of the various archive processes for an instance.

V$BACKUP_REDOLOG - Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.

V$LOG - Displays all redo log groups for the database and indicates which need to be archived.

V$LOG_HISTORY - Contains log history information such as which logs have been archived and the SCN range for each archived log.

Using these tables we can verify that we are infact in ARCHIVELOG mode:

SQL> select log_mode from v$database;

LOG_MODE

------------

ARCHIVELOG

SQL> select DEST_NAME,STATUS,DESTINATION from V$ARCHIVE_DEST;