Assaf Feuerstein`s Knowledge Press

This quick procedure will discuss what the commands needed to run on oracle in cases you might be using a script to create a snapshoted database and start the oracle instance on a different server.

In this case you need to put the source database in backup mode, take a snapshot, take it out of backup mode, copy the archive files to the target database, recover and open the database.

1. Put source database in backup mode:

 ALTER SYSTEM ARCHIVE LOG CURRENT;
 SELECT NEXT_CHANGE#-1 FROM V$ARCHIVED_LOG WHERE SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG);
 ALTER DATABASE BEGIN BACKUP;

Please notice that in the second line i keep the sequence number for later use in determining which archive files i will need.

2. Check that the entire database is in backup mode:

SELECT STATUS FROM V$BACKUP;

3. Now that the database is in backup mode we can take the snapshot and take the database out of backup mode:

 ALTER DATABASE END BACKUP;
 ALTER SYSTEM ARCHIVE LOG CURRENT;
 ALTER DATABASE BACKUP CONTROLFILE TO '/tmp/backup.ctl';
 ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/backup_trace.ctl';
 SELECT NEXT_CHANGE#-1 FROM V$ARCHIVED_LOG WHERE SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG);

Please notice that here i also take backup for the control file to be used in the recovery process, and also i keep now another sequence number, the one of the end of the backup.

4. Now i mount the target database`s mount points and i need to find which archive files i have to copy to the target database:

SELECT NAME FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND NEXT_CHANGE# BETWEEN $S_BEGIN_SEQ AND (SELECT MAX(NEXT_CHANGE#) FROM V$ARCHIVED_LOG);

Also dont forget to copy the backedup control file over the target databases own control files!

5. Now lets do the recover itself:

 STARTUP MOUNT;
 RECOVER AUTOMATIC DATABASE UNTIL CHANGE <last sequence number> USING BACKUP CONTROLFILE;

 ALTER DATABASE OPEN RESETLOGS;

At this point the database has gone a recovery and is ready to open.

It is important to remember that the database was copied in backup mode and therefore is in backup mode in the target database, the recovery process will take out the database from backup mode right after it will open the database and reset the logs.

First lets check the current log mode:
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Mar 20 14:33:05 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 165
Current log sequence 167

SQL> select name, log_mode from v$database;

NAME LOG_MODE
——— ————
TEST NOARCHIVELOG

SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string /home/oracle/product/11g/db/db
s/spfiletest.ora
Now lets try to set the archive log destination:

SQL> alter system set log_archive_dest_1=’/home/oracle/archivelog’ scope=spfile;
alter system set log_archive_dest_1=’/home/oracle/archivelog’ scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to “log_archive_dest_1″ not allowed with SPFILE

I faced the above error, thought need to enable archiving first:

SQL> alter system set log_archive_start=TRUE
2 scope=spfile;

System altered.

But I received the error again.

SQL> alter system set log_archive_dest_1=’/home/oracle/archivelog’ scope=spfile;
alter system set log_archive_dest_1=’/home/oracle/archivelog’ scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to “log_archive_dest_1″ not allowed with SPFILE

Checked the error description:
SQL> !oerr ora 16179
16179, 00000, “incremental changes to \”%s\” not allowed with SPFILE”
// *Cause: Incremental changes to a log_archive_dest_n parameter cannot
// be made when using an SPFILE.
// *Action: Specify either LOCATION or SERVICE plus all other attributes
// to be set in one ALTER SYSTEM/SESSION SET command.

So I did some research and found that when using spfile and trying to set a parameter like log_archive_dest_1, we need to give
complete format of the parameter like:

SQL> alter system set log_archive_dest_1=’location=/home/oracle/archivelog’ scope=spfile;

System altered.
(Optionally we can also give other parameters in this statement like optional, reopen etc.)

Now let shutdown the database and do a startup mount:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 310380672 bytes
Database Buffers 104857600 bytes
Redo Buffers 6131712 bytes
Database mounted.

Enabling archivelog mode

SQL> alter database archivelog;

Database altered.

Open the database:

SQL> alter database open;

Database altered.

Test if archivelog is set properly:

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archivelog
Oldest online log sequence 165
Next log sequence to archive 167
Current log sequence 167
SQL> select name, log_mode from v$database;

NAME LOG_MODE
——— ————
TEST ARCHIVELOG

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> !ls -lrt /home/oracle/archivelog
total 44496
-rw-r—– 1 oracle oracle 45509632 2009-03-20 14:43 1_167_677948664.dbf
-rw-r—– 1 oracle oracle 1024 2009-03-20 14:43 1_168_677948664.dbf

So now we have enabled archivelog mode and have some archivelog file also!!!