How to change archivelog mode of the oracle database

-

How to change archivelog mode of the database


Most of the High Availability and Point in time Recovery 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 archive logs are stored in a separate place usually can backed up regularly by your standard filesystem backup system (NetBackup, RMAN or whatever). Archive logs are utilized by RMAN, Data Guard, Flashback and many others.

Note: It is recommended to perform a backup of the database (after a shutdown normal or immediate) prior to changing the archive status of a database.

If you are going to enable archivelog mode on a real database that is 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 a little you'll run into problems down the road, so let’s specify some parameters too. Namely, consider LOG_ARCHIVE_DEST.

NOTE: Production database must be in archivelog mode.

-- Execute the following command to check the archival mode of your database.

To change the archival mode of the database, Please follow the following statement(s):

1.    Shutdown the database.
2.    If you wish you can specify log location in pfile, else they will go into some default place like DB_RECOVERY_FILE_DEST.
3.    Start the database in mount state.
4.    Alter the archivelog mode of the database.
5.    Alter the database into open mode.

You can also use pfile for this operation. You can specify as many as 10 different archive log destinations by using the parameters 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!
 
You should check whether init.ora for your particular instance is present or not in the default directory say <ORACLE_HOME/dbs>. If it not present then issue below command to create it before closing the database.
 
sql> create pfile from spfile;
 
Now you can shutdown the database to make necessary changes.

Once you are satisfied with the changes in initialization file, you should create the 
corresponding spfile by issuing:

sql> create spfile from pfile;

It will go into the default location.

############################
# Archive Log Destinations
############################
log_archive_dest_1='location=/u03/oraarch/mynewdb/arclog/'
# If you want to change archive log format.
LOG_ARCHIVE_FORMAT = 'mynewdb_arc_%t_%s_%r.arc'

ARCHIVELOG | NOARCHIVELOG

Use the ARCHIVELOG clause and NOARCHIVELOG clause only if your instance has the database mounted but not open.

To see current archivelog mode either use
Sql> archive log list
OR
SQL> select name, log_mode from v$database;

Noarchivelog    ===>>>   Archivelog

SQL> archive log list

Sql> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

SQL> archive log list

Archivelog    ===>>>   Noarchivelog
SQL> archive log list

Sql> shutdown immediate

SQL> startup mount

SQL> alter database noarchivelog;
Database altered.

SQL> alter database open;
Database altered.

SQL> archive log list

Comments

Post a Comment

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cer...
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0 ...
Read more

How to Drop or Remove or Decommission a Database in Oracle

-
How to Drop or Remove or Decommission a Database in Oracle Database Decommission steps for Oracle 10G and 11g databases Note: ~~~~~~~~ Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application. Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP). 1)   Please get the required approval from Business/Customer to proceed for database decommissioning. 2)   Send the Notification to Business/Customer that we are going to decommission the database. (if required) 3)   Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can...
Read more