Turning on archive log mode and flashback

I was doing some testing with one of my DEV databases today and had to turn on ARCHIVELOG mode.

I wrote everything down as I went, so I could post them here to share with everyone else.

1. Set an archive log location.

SQL> alter system set \
log_archive_dest_1='location=/u02/db/ora10201/oradata/db1/archive' \
scope=spfile;

2. Shutdown the database

SQL> shutdown immediate;

3. Startup in mount mode.

SQL> startup mount;

4. Turn on archive loggin.

SQL> alter database archivelog;

5. Open the database.

SQL> alter database open;

6. Check archive log mode.

SQL> select log_mode from sys.v$database;

If you are using 10g and you also want to turn on FlashBack then you need to the steps above, then do these steps below as well.

1. Shutdown the database.

SQL> shutdown imemdiate;

2. Startup in mount exclusive mode.

SQL> startup mount exclusive;

3. Set the retention target. Change the value of 2880 according to your needs.

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;

4. Turn on flashback;

SQL> alter database flashback on;

5. Open the database.

SQL> alter database open;

6. Check that flash back is turned on.

SQL> select flashback_on from v$database;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s