Tuesday, December 6, 2016

Useful Links

https://www.linkedin.com/pulse/restoring-oracle-database-new-server-sajjad-razmi?forceNoSplash=true

http://damir-vadas.blogspot.com/2010/02/how-to-find-correct-scn.html

https://www.linkedin.com/pulse/restoring-oracle-database-new-server-sajjad-razmi?forceNoSplash=true

http://arpitagrawaloracle.blogspot.com/2013/09/how-to-quickly-check-that-database-is.html

http://www.orafaq.com/maillist/oracle-l/2006/06/06/0311.htm

https://archive.sap.com/discussions/message/14033852#14033852

http://www.dba-oracle.com/t_v_datafile_header.htm

http://dba.stackexchange.com/questions/48800/failure-of-backup-due-to-limit-exceeded-for-recovery-files-since-cannot-reclaim

https://jhdba.wordpress.com/2008/07/16/25/


SCNs (System Change Numbers) and Checkpoints

System Change Numbers (SCNs)
system change number (SCN) is a logical, internal time stamp used by Oracle Database. SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction. Oracle Database uses SCNs to mark the SCN before which all changes are known to be on disk so that recovery avoids applying unnecessary redo. The database also uses SCNs to mark the point at which no redo exists for a set of data so that recovery can stop.
SCNs occur in a monotonically increasing sequence. Oracle Database can use an SCN like a clock because an observed SCN indicates a logical point in time and repeated observations return equal or greater values. If one event has a lower SCN than another event, then it occurred at an earlier time with respect to the database. Several events may share the same SCN, which means that they occurred at the same time with respect to the database.
Every transaction has an SCN. For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction have the same SCN. When a transaction commits, the database records an SCN for this commit.
Contrary to popular belief, it does not get generated only at the time of commit, though that’s what the name itself suggests. SCN is there all the time in the database, representing a time portion for the operation which is happening at that instant of time. It is not completely inaccurate to say that SCN doesn’t get generated with a commit, it does – but that’s not the sole way SCN is generated.
Oracle Database increments SCNs in the system global area (SGA). When a transaction modifies data, the database writes a new SCN to the undo data segment assigned to the transaction. The log writer process then writes the commit record of the transaction immediately to the online redo log. The commit record has the unique SCN of the transaction. Oracle Database also uses SCNs as part of its instance recovery and media recovery mechanisms.
SCN is 6 Byte (48bit) number represented as 2 parts – SCN_BASE and SCN_WRAP. An SCN_BASE is a 4 Byte (32 bits) number and SCN_WRAP is a 2 Byte (16 bits) number. Whenever SCN_BASE reaches its maximum (2 power 32 = 4294967296 ) , SCN_WRAP goes up by one and SCN_BASE will be reset to 0. This continues till SCN_WRAP reaches it’s maximum (i.e. 2 power 16 = 65536). SCN = (SCN_WRAP * 4294967296) + SCN_BASE.
Now the question arises in our mind – what happens when SCN reaches it’s maximum? and how the Oracle handles that situation?
Let’s say you have fresh database with both SCN_WRAP and SCN_BASE at value 0. As the database is up and running, SCN value keeps on increasing for every second. For SCN_BASE to reach it’s maximum, it will take around 136 years ( 4294967296/(365*24*60*60) – Assuming one transaction per second.). This is just for one Wrap and imagine you have still 65535 wraps to complete. So, no need to fear about reaching maximum.
Even if it does reach maximum, then SCN will be reseted to 0 useless. Need to take fresh backup’s.
SMON_SCN_TIME Table
The easiest way to see both of these values is from an internal table owned by SYS user - SMON_SCN_TIME. The following is an output from the same (11204).
SELECT s.SCN_WRP, s.SCN_BAS, s.SCN from SMON_SCN_TIME s where ROWNUM<3;
SCN_WRP    SCN_BAS   SCN
----------      ----------        ----------
1                 86376436    4381343732
1                86245363    4381212659
This table contains the entries of the SCNs generated. It stores the data in roughly 5-minute increments and holds 5 days’ worth of data. This means the table contains approximately 1440 records. The exact number of records will vary slightly since the storage increment is not exactly 5 minutes.
SELECT count(*) from SMON_SCN_TIME s;
COUNT(*)
----------
1690

How can we map SCN with Timestamp in versions prior to 10g?
It’s important to remember that this table doesn’t show why there would be any increase in the rate of increase in the SCN numbers. With a little formatting, it is possible to have an idea of the SCN numbers generated but the source of their growth won’t be evident from this table.
Every redo log file has both a log sequence number and low (FIRST_CHANGE#) and high (NEXT_CHANGE#) SCN. The low SCN records the lowest SCN recorded in the log file, while the high SCN records the highest SCN in the log file.
Another way to check this is from the view V$LOG_HISTORY. The view contains the SCN in the form of the columns FIRST_CHANGE# and NEXT_CHANGE# and we can see through these two columns the amount of SCNs generated in the database over a period of time. The “first_change#” is the lowest SCN that appears in the archived log file at a given sequence number of this thread. The “next_change#” is the lowest SCN that appears in the next archived log file.
select thread#, first_change#,next_change# from V$log_history;

THREAD#  FIRST_CHANGE#    NEXT_CHANGE#
----------     -------------                  ------------
1               4211590818                4211593771
1              4211593771                 4211596653
1              4211596653                 4211599617
1             4211599617                 4211602580
1             4211602580                 4211605360
1             4211605360                 4211608174
1             4211608174                 4211610989
1             4211610989                 4211624985
1             4211624985                 4211629091
As with SMON_SCN_TABLE it’s not possible to find the source of the increase in the generation of the SCN numbers from this table view. Still, you can use this view in the single instance as well as in an RAC environment.

SCN and Checkpoints
A checkpoint occurs when all modified database buffers in the Oracle SGA are written out to datafiles by the database writer (DBWn) process. The checkpoint process (CKPT) updates all datafiles and control files with the SCN at the time of the checkpoint and signals DBWn to write out the blocks. A successful checkpoint guarantees that all database changes up to the checkpoint SCN have been recorded in the datafiles. In other words, the checkpoint records the highest SCN of all changes to blocks such that all data blocks with changes below that SCN have been written to disk by DBWn. As a result, only those changes made after the checkpoint need to be applied during recovery.
Checkpoints occur automatically as follows:
§  Whenever a redo log switch takes place (or by issuing the command ‘alter system switch logfile’ or ‘alter system checkpoint’.)
§  When all dirty buffers in the Oracle SGA are written to datafiles by DBWR.
§  Every 3 seconds (incremental checkpoint)
§  When instance shutdown normal/transactional/immediate
§  Whenever Alter Tablespace [Offline Normal|Read Only|begin Backup]
§  Whenever the time set by the LOG_CHECKPOINT_TIMEOUT initialization parameter is reached
§  Whenever the amount of redo written reaches the number of bytes associated with the LOG_CHECKPOINT_INTERVAL
Typically, LOG_CHECKPOINT_INTERVAL is chosen so that checkpoints only occur on log switches. Oracle stores the SCN associated with the checkpoint in four places: three of them in the control file and one in the datafile header for each datafile:
1. System checkpoint SCN   =======>  (SYSTEM CHECKPOINT SCN in control file)
2. Datafile checkpoint SCN  =======>  (DATAFILE CHECKPOINT SCN in control file)
3. Stop SCN                          =======>  (STOP SCN in control file)
4. Start SCN                          =======>  (DATAFILE HEADER)
Why CONTROL FILE stored in two places to be divided (SYSTEM CHECKPOINT SCN, DATAFILE CHECKPOINT SCN?)  When you put a tablespace set to read-only, SCN will freeze to stop him, this time DATAFILE CHECKPOINT SCN is no longer incremental changes, but the overall SYSTEM CHECKPOINT SCN will continue to increase, but still advancing. So, this is why you need are stored in two places SCN.
During a clean shutdown, a checkpoint occurs and the stop SCN for each datafile is set to the start SCN of the datafile header. Now, all the 4 SCN values are same.
Upon startup, Start SCN value is checked with Datafile Checkpoint SCN, if they both match then Start SCN value is checked with Stop SCN and if they do match, Oracle opens the database without any problem. Since in our case as mentioned above all the 4 values are same and so we have a clean startup. Once the database is opened, stop SCN of the control file will be resetted to NULL again to indicate that the datafile is open for normal use. At instance startup, the datafile checkpoint SCN in the control file and the start SCN in the datafile header are checked for equality. If they don’t match, it is a signal that media recovery is required.
Purpose of Checkpoints
Oracle Database uses checkpoints to achieve the following goals:
           -    Reduce the time required for recovery in case of an instance or media failure
           -  Ensure that dirty buffers in the buffer cache are written to disk regularly
           -    Ensure that all committed data is written to disk during a consistent shutdown

The System Checkpoint SCN
After a checkpoint completes, Oracle stores the system checkpoint SCN in the control file. You can access the checkpoint SCN using the following SQL:

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
1677903

SQL> alter system checkpoint;

System altered.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
1679716

The Datafile Checkpoint SCN
After a checkpoint completes, Oracle stores the SCN individually in the control file for each datafile. The following SQL shows the datafile checkpoint SCN for a single datafile in the control file:
SQL> select name, checkpoint_change# from v$datafile where name like '%users01%';
NAME                                   CHECKPOINT_CHANGE#
-----------------------------------             --------------------
/u02/oradata/OMFD1/users01.dbf        292767

Partial Checkpoint SCN
Operational non-full checkpoints for subset of system i.e. tablespace or a datafile etc, would set checkpoint for affected entities only
SQL> select name, checkpoint_change# from v$datafile_header where name like '%01.dbf';

NAME                                                      CHECKPOINT_CHANGE#
----------------------------------------------------      ------------------
/u02/app/oracle/oradata/mask11g/system01.dbf               1685610
/u02/app/oracle/oradata/mask11g/sysaux01.dbf               1685610
/u02/app/oracle/oradata/mask11g/undotbs01.dbf              1685610
/u02/app/oracle/oradata/mask11g/users01.dbf                1685610

SQL> alter tablespace users read only;

Tablespace altered.

SQL> select name, checkpoint_change# from v$datafile_header where name like '%01.dbf';

NAME                                                      CHECKPOINT_CHANGE#
----------------------------------------------------      ------------------
/u02/app/oracle/oradata/mask11g/system01.dbf               1685610
/u02/app/oracle/oradata/mask11g/sysaux01.dbf               1685610
/u02/app/oracle/oradata/mask11g/undotbs01.dbf              1685610
/u02/app/oracle/oradata/mask11g/users01.dbf                1685618

SQL> alter tablespace users read write;

Tablespace altered.

SQL> select name,checkpoint_change# from v$datafile_header where name like '%01.dbf';

NAME                                                     CHECKPOINT_CHANGE#
----------------------------------------------------     ------------------
/u02/app/oracle/oradata/mask11g/system01.dbf              1685610
/u02/app/oracle/oradata/mask11g/sysaux01.dbf              1685610
/u02/app/oracle/oradata/mask11g/undotbs01.dbf             1685610
/u02/app/oracle/oradata/mask11g/users01.dbf                 1685642

The Start SCN
Oracle stores the checkpoint SCN value in the header of each datafile. This is referred to as the start SCN because it is used at instance startup time to check if recovery is required. The following SQL shows the checkpoint SCN in the datafile header for a single datafile:
SQL> select name, checkpoint_change# from v$datafile_header where name like '%users01%';

NAME                                                   CHECKPOINT_CHANGE#
-----------------------------------               --------------------
/u02/oradata/OMFD1/users01.dbf     292767

The Stop SCN
The stop SCN is held in the control file for each datafile. The following SQL shows the stop SCN for a single datafile when the database is open for normal use:

SQL> select name, last_change# from v$datafile where name like '%users01%';

NAME                                                    LAST_CHANGE#
-----------------------------------                ------------
/u02/oradata/OMFD1/users01.dbf
Under the normal conditions, where database is running without any problem, the System Checkpoint SCN, Datafile Checkpoint SCN and Start SCN have equal values. Whereas, Stop SCN will always be NULL under normal conditions for all datafiles that are online in read-write mode.
SCN after a Clean Shutdown resulting from a SHUTDOWN IMMEDIATE or SHUTDOWN NORMAL of the database, followed by STARTUP MOUNT, the previous queries on v$database and v$datafile return the following:
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
--------------------
293184

SQL> select name,checkpoint_change#,last_change# from v$datafile where name like '%user%';

NAME                                                  CHECKPOINT_CHANGE#    LAST_CHANGE#
-----------------------------------              --------------------                       --------------
/u02/oradata/OMFD1/users01.dbf     293184                                      293184

SQL> select name,checkpoint_change# from v$datafile_header where name like '%users01%';

NAME                                                    CHECKPOINT_CHANGE#
-----------------------------------                 --------------------
/u02/oradata/OMFD1/users01.dbf         293184

SCN after an Instance Crash
The previous example showed the behavior of the SCN after a clean shutdown. To demonstrate the behavior of the checkpoints after an instance crash, the following SQL creates a table (which performs an implicit commit) and inserts a row of data into it without a commit:

create table x(x number) tablespace users;
insert into x values(100);


If the instance is crashed by using SHUTDOWN ABORT, the previous queries on v$database and v$datafile return the following after the database is started up in mount mode:

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
--------------------
293185

SQL> select name,checkpoint_change#,last_change# from v$datafile where name like '%users01%';

NAME                                                 CHECKPOINT_CHANGE#     LAST_CHANGE#
-----------------------------------              --------------------                       --------------
/u02/oradata/OMFD1/users01.dbf     293185

SQL> select name,checkpoint_change# from v$datafile_header where name like '%users01%';

NAME                                                    CHECKPOINT_CHANGE#
-----------------------------------                 --------------------
/u02/oradata/OMFD1/users01.dbf        293185


In this case, the stop SCN is not set, which is indicated by the NULL value in the LAST_CHANGE# column. This information enables Oracle, at the time of the next startup, to determine that the instance crashed because the checkpoint on shutdown was not performed. If it had been performed, the LAST_CHANGE# and CHECKPOINT_CHANGE# values would match for each datafile as they did during a clean shutdown. If an instance crashes at shutdown, then instance crash recovery is required the next time the instance starts up.

Recovery from an Instance Crash
Upon the next instance startup that takes place after SHUTDOWN ABORT or a DBMS crash, the Oracle DBMS detects that the stop SCN for datafiles is not set in the control file during startup. Oracle then performs crash recovery. During crash recovery, Oracle applies redo log records from the online redo logs in a process referred to as roll forward to ensure that all transactions committed before the crash are applied to the datafiles. Following roll forward, active transactions that did not commit are identified from the rollback segments and are undone before the blocks involved in the active transactions can be accessed. This process is referred to as roll back. In our example, the following transaction was active but not committed at the time of the SHUTDOWN ABORT, so it needs to be rolled back:
SQL> insert into x values (100);
After instance startup, the X table exists, but remains empty. Instance recovery happens automatically at database startup without database administrator (DBA) intervention. It may take a while because of the need to apply large amounts of outstanding redo changes to data blocks for transactions that completed and those that didn’t complete and require roll back.

Recovery from a Media Failure
Up until this point, the checkpoint start SCN in the datafile header has always matched the datafile checkpoint SCN number held in the control file. This is reasonable because during a checkpoint, the datafile checkpoint SCN in the control file and the start SCN in the datafile header are both updated, along with the system checkpoint SCN. The following SQL shows the start SCN from the datafile header and datafile checkpoint SCN from the control file for the same file:
SQL> select 'controlfile' "SCN location",name, checkpoint_change# from v$datafile where name like     '%users01%'
union
select 'file header',name,checkpoint_change# from v$datafile_header where name like '%users01%';

SCN location     NAME                                               CHECKPOINT_CHANGE#
--------------      -----------------------------------             --------------------
controlfile      /u02/oradata/OMFD1/users01.dbf      293188
file header     /u02/oradata/OMFD1/users01.dbf      293188

Unlike the v$datafile view, there is no stop SCN column in the v$datafile_header view because v$datafile_header is not used at instance startup time to indicate that an instance crash occurred. However, the v$datafile_header does provide the Oracle DBMS with the information it requires to perform media recovery. At instance startup, the datafile checkpoint SCN in the control file and the start SCN in the datafile header are checked for equality. If they don’t match, it is a signal that media recovery is required.
For example, media recovery is required if a media failure has occurred and the original datafile has been replaced with a backup copy. In this case, the start SCN in the backup copy is less than the checkpoint SCN value in the control file, and Oracle requests archived redo logs—generated at the time of previous log switches—in order to reapply the changes required to bring the datafile up to the current point in time.
In order to recover the database from a media failure, you must run the database in ARCHIVELOG mode to ensure that all database changes from the online redo logs are stored permanently in archived redo log files.
You can identify files that need recovery after you have replaced a datafile with an older version by starting the instance in mount mode and running the following SQL:
SQL> select file#, change# from v$recover_file;
FILE#       CHANGE#
----------    ----------
4                313401
In this example, file 4 is the datafile in the USERS tablespace. By reexecuting the previous SQL to display the datafile checkpoint SCN in the control file and the start SCN in the datafile header, you can see that the start SCN is older due to the restore of the backup datafile that has taken place:
SQL> select 'controlfile' "SCN location",name,checkpoint_change#
       from v$datafile where name like '%users01%'
      union
      select 'file header',name,checkpoint_change#
     from v$datafile_header where name like '%users01%';
SCN location    NAME                                               CHECKPOINT_CHANGE#
--------------     -----------------------------------             --------------------
controlfile      /u02/oradata/OMFD1/users01.dbf     313551
file header     /u02/oradata/OMFD1/users01.dbf     313401
If you were to attempt to open the database, you would receive errors like the following:
ORA-01113: file 4 needs media recovery
ORA-01110: datafile 4: '/u02/oradata/OMFD1/users01.dbf'
You can recover the database by issuing RECOVER DATABASE from SQL*Plus while the database is in a mounted state. If the changes needed to recover the database to the point in time before the crash are in an archived redo log, then you will be prompted to accept the suggested name:
ORA-00279: change 313401 generated at 11/10/2001 18:50:23 needed for thread
ORA-00289: suggestion : /u02/oradata/OMFD1/arch/T0001S0000000072.ARC
ORA-00280: change 313401 for thread 1 is in sequence #72
Specify log: {=suggested | filename | AUTO | CANCEL}
If you respond to the prompt using AUTO, Oracle applies any archived redo logs it needs, followed by any necessary changes in the online redo logs, to bring the database right up to the last committed transaction before the media failure that caused the requirement for the restore.
So far, we’ve considered recovery scenarios where the goal is to recover the database to the most recent transaction. This is known as complete recovery. The RECOVER DATABASE command has several other options that enable you to recover from a backup to a point in time before the most recent transaction by rolling forward and then stopping the application of the redo log changes at a specified point. This is known as incomplete recovery. You can specify a time or an SCN as the recovery point. For example,
recover database until time '2001-11-10:18:52:00';
recover database until change 313459;
Before you perform incomplete recovery, it’s recommended that you restore a complete database backup first. After incomplete recovery, you must open the mounted database with ALTER DATABASE OPEN RESETLOGS. This creates a new incarnation of the database and clears the contents of the existing redo logs to make sure they can’t be applied.

Recovery from a Media Failure Using a Backup Control File
In the previous example, we had access to a current control file at the time of the media failure. This means that none of the start SCN values in the datafile headers exceeded the system checkpoint SCN number in the control file. To recap, the system checkpoint number is given by the following:
SQL> select checkpoint_change# from v$database;
You might be wondering why Oracle needs to maintain the last system checkpoint value in the control file as well as checkpoint SCNs in the control file for each datafile (as used in the previous example). There are two reasons for this. The first is that you might have read-only tablespaces in your database. In this case, the database checkpoint SCN increases, and the checkpoint SCN for the datafiles in the read-only tablespace remains frozen in the control file.
The following SQL report output shows a database with a read-write tablespace (USERS) and read-only tablespace (TEST). The start SCN in the file header and the checkpoint SCN in the control file for TEST are less than the system checkpoint value. Once a tablespace is read only, checkpoints have no effect on the files in it. The other read-write tablespace has checkpoint values that match the system checkpoint:
SCN location           NAME                                                CHECKPOINT_CHANGE#
--------------------     ----------------------------------              ----------------
controlfile               SYSTEM checkpoint                            355390
file header              /u02/oradata/OD2/users01.dbf           355390
file in controlfile    /u02/oradata/OD2/users01.dbf           355390
file header              /u02/oradata/OD2/test01.dbf             355383
file in controlfile    /u02/oradata/OD2/test01.dbf             355383
The second reason for the maintenance of multiple checkpoint SCNs in the control file is that you might not have a current control file available at recovery time. In this case, you need to restore an earlier control file before you can perform a recovery. The system checkpoint in the control file may indicate an earlier change than the start SCN in the datafile headers.
The following SQL shows an example where the system checkpoint SCN and datafile checkpoint SCN indicate an earlier change than the start SCN in the datafile header:
SQL> select 'controlfile'  "SCN location", 'SYSTEM checkpoint ' name, checkpoint_change#
       from v$database
       union
       select 'file in controlfile', name, checkpoint_change#
       from v$datafile where name like 'users01%'
       union
       select 'file header', name, checkpoint_change#
       from v$datafile_header where name like '%users01%';
       SCN location        NAME                                             CHECKPOINT_CHANGE#
       -------------------     ------------------------------               ------------------
       controlfile          SYSTEM checkpoint                          333765
       file header          /u02/oradata/OD2/users01.dbf        355253
       file in controlfile   /u02/oradata/OD2/users01.dbf     333765
If try you to recover a database in the usual way in this situation, Oracle detects that the control file is older than some of the datafiles, as indicated by the checkpoint SCN values in the datafile headers, and reports the following message:
SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
If you want to proceed with recovery in this situation, you need to indicate to Oracle that a noncurrent control file - possibly containing mismatches in the SCN values identified by the previous error messages - is about to be specified for recovery by using the following command:
recover database using BACKUP CONTROLFILE;
A consistent backup of a database or part of a database is a backup in which all read-write datafiles and control files have been checkpointed with respect to the same SCN.
An inconsistent backup s a backup in which all read-write datafiles and control files have not been checkpointed with respect to the same SCN. For example, one read-write datafile header may contain an SCN of 100 while others contain an SCN of 95. Oracle cannot open the database until these SCNs are consistent, that is, until all changes recorded in the online redo logs have been made to the datafiles.
Oracle enables you to restore an older backup and apply partial redo data, thereby recovering the database to a specified non-current time or SCN. This type of recovery is called incomplete recovery. You must open your database with a RESETLOGS operation after performing incomplete recovery in order to reset the online redo logs.
As a query enters the execution stage, the current system change number (SCN) is determined. As data blocks are read on behalf of the query, only blocks written with the observed SCN are used. Blocks with changed data (more recent SCNs) are reconstructed from data in the rollback segments, and the reconstructed data is returned for the query. Therefore, each query returns all committed data with respect to the SCN recorded at the time that query execution began. Changes of other transactions that occur during a query's execution are not observed, guaranteeing that consistent data is returned for each query. By default a block-level SCN is recorded for each block. That SCN is the latest SCN for any record in the block.


Crash recovery vs media recovery

STOP SCN equal NULL ==> CRASH RECOVERY NEED
DATAFILE HEADER START SCN and SCN CONTROLFILE not equal ==> MEDIA RECOVERY NEED


Difference Between "SCN and ORA_ROWSCN"

SCN is like ticker/end of every transaction in database whereas ORA_ROWSCN is an observed point in time. The ora_rowscn is a value associated to a block or a row on a block that represents the “time” the block/row was last modified.
By default, the ora_rowscn pseudo column reports the scn at block level. This means that rows sharing the same block will have the same ora_rowscn, and if one row were to be changed, then scn would be altered for both blocks.


RECOVERY DATABASE two common problems!!!

1) RECOVER DATABASE UNTIL CANCEL ==> OPEN DATABASE RESETLOG ==> DATAFILE HEADER SCN will be less than the DATAFILE SCN CONTROLFILE
this time will be unable to open the database, you must redo archive log media recovery ~~ until the datafile header of SCN = current scn. After the restore datafile, you can mount database and then went to check the controlfile and datafile header SCN.

2) RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE ===> OPEN  DATABASE RESETLOG ==> DATAFILE HEADER SCN will be greater than the DATAFILE  SCN CONTROLFILE
this time after Oracle RESTORE CONTROL FILE SYSTEM SCN CONTROL FILE will find inside will be less than the current DATAFILE HEADER SCN, nor equal to the current stored in the LOG SCN FILE inside, then you must use the RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE to DROP DATAFILE OR DROP TABLESPACE before the SCN.

Monday, November 7, 2016

Active Database Duplication (non-ASM to ASM on same host)

OS: Oracle

DB: 11.2.0.2.0

1. First of all, define storage to duplicate database (Ex:+DATA_DUP,+FRA_DUP)

2. Create a service for new database in tnsnames.ora file
   # tnsnames.ora Network Configuration File:         
     /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
   # Generated by Oracle configuration tools.
......
DUPDB3 =
     (DESCRIPTION =
      (ADDRESS_LIST =
        ADDRESS = (PROTOCOL = TCP)(HOST = orcl11g)(PORT = 1521))
      )
      (CONNECT_DATA =
        (SERVER=DEDICATED)
        (SERVICE_NAME = DUPDB3)
      )
     )

3. Register the database with the GRID listener statically.
   #Need to login as GRID user
   #[Loc:/u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora] 
   Before adding new duplicated database to listener, we have to stop listener.
   Then add
   SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (GLOBAL_DBNAME = ora11g)
       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
       (SID_NAME = ora11g)
     )
     .......
     (SID_DESC =
       (GLOBAL_DBNAME = DUPDB3)
       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
       (SID_NAME = DUPDB3)
     )
   )
   Then start listener again.
  
4. Add a line in the file /etc/oratab to reflect the new database instance
   DUPDB3:/u01/app/oracle/product/11.2.0/dbhome_1:N

5. Create initialization parameter file initDUPDB3.ora with just one parameter, database name. Other    
   required parameters will be set in duplicate command using spfile parameter.
   #set database name for duplicated (auxiliary instance) database
   db_name=DUPDB3

6. Create password file for new database
   [oracle11g@orcl11g Desktop]$ cd $ORACLE_HOME/dbs
   [oracle11g@orcl11g dbs]$ orapwd file=orapwDUPDB3 entries=5
   OR just copy from source
   [oracle11g@orcl11g~]cp $ORACLE_HOME/dbs/orapwDUPDB2 $ORACLE_HOME/dbs/orapwDUPDB3

7. Set environment for target database as SID
   [oracle11g@orcl11g oracle]$ . oraenv
   ORACLE_SID = [ora11g] ? DUPDB2
   The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
   is /u01/app/oracle

8. Start target(source) database in mount state (for noarchivelog mode this time).
   (If the source database is open, then archiving must be enabled)
   [oracle11g@orcl11g~]$ sqlplus / as sysdba
    SQL> startup mount;
    Oracle instance started
    Total System Global Area     217157632 bytes
    Fixed Size                     2211928 bytes
    Variable Size                159387560 bytes
    Database Buffers              50331648 bytes
    Redo Buffers                   5226496 bytes
    database mounted

9. Set environment for duplicated database as SID
   [oracle11g@orcl11g oracle]$ . oraenv
   ORACLE_SID = [DUPDB2] ? DUPDB3
   The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

10. Start auxiliary database in nomount state.
    [oracle11g@orcl11g~]$ sqlplus / as sysdba
    SQL> startup nomount;
    Oracle instance started
    Total System Global Area     217157632 bytes
    Fixed Size                     2211928 bytes
    Variable Size                159387560 bytes
    Database Buffers              50331648 bytes
    Redo Buffers                   5226496 bytes

11. Connect to target and auxiliary instance.
    [oracle11g@orcl11g~]$ rman target sys/222333@DUPDB2 auxiliary=sys/222333@DUPDB3
    Recovery Manager: Release 11.2.0.1.0 - Production on Thu Aug 25 18:07:33 2016
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    connected to target database: DUPDB2 (DBID=740794324, not open)
    connected to auxiliary database: DUPDB3 (not mounted)
    RMAN>

12. Now we will duplicate database using SPFILE option in command. There are many alternatives to define 
    parameters in SPFILE. Most of them will be shown. For full description refer to documentation Backup
    and Recovery User's Guide.

    Option 1:
    RMAN> duplicate target database to DUPDB3 from active database
          db_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP'
          SPFILE parameter_value_convert '/u01/app/oracle/oradata/dupdb2','+DATA_DUP'
            SET control_files='+DATA_DUP','+FRA_DUP'
            SET log_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP',
                                      '/u01/app/oracle/flash_recovery_area/dupdb2','+FRA_DUP'
            SET db_create_file_dest='+DATA_DUP'
            SET db_recovery_file_dest='+FRA_DUP'
          password file;

    Option 2:
    RMAN>duplicate target database to DUPDB3 from active database
          SPFILE parameter_value_convert '/u01/app/oracle/oradata/dupdb2','+DATA_DUP'
            SET control_files='+DATA_DUP','+FRA_DUP'
            SET log_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP',
                                      '/u01/app/oracle/flash_recovery_area/dupdb2','+FRA_DUP'
            SET db_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP/dupdb3'
            SET db_recovery_file_dest='+FRA_DUP'
          password file;

    Option 3:
    RMAN>duplicate target database to DUPDB3 from active database
        SPFILE parameter_value_convert '/u01/app/oracle/oradata/dupdb2','+DATA_DUP'
            SET control_files='+DATA_DUP','+FRA_DUP'
            SET log_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP',
                               '/u01/app/oracle/flash_recovery_area/dupdb2','+FRA_DUP'
            SET db_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP'
         password file;
    Note: If FRA not defined in spfile then when DUPDB2's FRA is defined for DUPDB3 as  
          db_recovery_file_dest='/u01/app/oracle/flash_recovery_area/dupdb2'. And same think is also defined for new
          created files as db_create_file_dest='/u01/app/oracle/oradata'.

    Option 4:
    RMAN>duplicate target database to DUPDB3 from active database
         SPFILE
            SET control_files='+DATA_DUP','+FRA_DUP'
            SET log_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP',
                                      '/u01/app/oracle/flash_recovery_area/dupdb2','+FRA_DUP'
            SET db_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP'
            SET db_recovery_file_dest='+FRA_DUP'
            SET db_create_file_dest='+DATA_DUP'
          password file;

    Option 5: It doesn't work because db_file_name_convert parameter is not defined.
    RMAN>duplicate target database to DUPDB3 from active database
          SPFILE parameter_value_convert '/u01/app/oracle/oradata/dupdb2','+DATA_DUP'
            SET control_files='+DATA_DUP','+FRA_DUP'
            SET log_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP',
                                      '/u01/app/oracle/flash_recovery_area/dupdb2','+FRA_DUP'
            SET db_create_file_dest='+DATA_DUP'
            SET db_recovery_file_dest='+FRA_DUP'
         password file;
        Note: In all options the following warning message occurs, which not affect the duplication process:
          RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
          Cause: It was not possible to convert ASM Oracle Managed Files names using DB_FILE_NAME_CONVERT parameter. RMAN changed these invalid names to     
                        the converted disk group name instead.
           Action: No action is required. This is an informational message only. If the automatic change is incorrect, use one of the following options instead of using 
                          DB_FILE_NAME_CONVERT for ASM Oracle Managed Files: 1) use RMAN command SET NEWNAME for each Oracle Managed File. 
                          2) set DB_CREATE_FILE_DEST initialization parameter in auxiliary instance and not specify DB_FILE_NAME_CONVERT.
           
        Duplication process continue...
Starting Duplicate Db at 23-SEP-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwDUPDB2' auxiliary format
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwDUPDB3'   targetfile
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileDUPDB2.ora' auxiliary format
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileDUPDB3.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileDUPDB3.ora''";
}
executing Memory Script

Starting backup at 23-SEP-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK
Finished backup at 23-SEP-16

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileDUPDB3.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name = ''DUPDB3'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  control_files =
 ''+DATA_DUP'', ''+FRA_DUP'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''/u01/app/oracle/oradata/dupdb2'', ''+DATA_DUP'', ''/u01/app/oracle/flash_recovery_area/dupdb2'', ''+FRA_DUP'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''/u01/app/oracle/oradata/dupdb2'', ''+DATA_DUP'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_recovery_file_dest =
 ''+FRA_DUP'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_create_file_dest =
 ''+DATA_DUP'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DUPDB3'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  control_files =  ''+DATA_DUP'', ''+FRA_DUP'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/u01/app/oracle/oradata/dupdb2'', ''+DATA_DUP'', ''/u01/app/oracle/flash_recovery_area/dupdb2'', ''+FRA_DUP'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/u01/app/oracle/oradata/dupdb2'', ''+DATA_DUP'' comment= '''' scope=spfile

sql statement: alter system set  db_recovery_file_dest =  ''+FRA_DUP'' comment= '''' scope=spfile

sql statement: alter system set  db_create_file_dest =  ''+DATA_DUP'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2388422656 bytes

Fixed Size                     2215664 bytes
Variable Size               1358954768 bytes
Database Buffers            1006632960 bytes
Redo Buffers                  20619264 bytes

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATA_DUP/dupdb3/controlfile/current.280.923341755'', ''+FRA_DUP/dupdb3/controlfile/current.278.923341755'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''DUPDB2'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DUPDB3'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '+DATA_DUP/dupdb3/controlfile/current.276.923341755';
   restore clone controlfile to  '+FRA_DUP/dupdb3/controlfile/current.276.923341755' from
 '+DATA_DUP/dupdb3/controlfile/current.276.923341755';
   sql clone "alter system set  control_files =
  ''+DATA_DUP/dupdb3/controlfile/current.276.923341755'', ''+FRA_DUP/dupdb3/controlfile/current.276.923341755'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATA_DUP/dupdb3/controlfile/current.280.923341755'', ''+FRA_DUP/dupdb3/controlfile/current.278.923341755'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''DUPDB2'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DUPDB3'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    2388422656 bytes

Fixed Size                     2215664 bytes
Variable Size               1358954768 bytes
Database Buffers            1006632960 bytes
Redo Buffers                  20619264 bytes

Starting backup at 23-SEP-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_DUPDB2.f tag=TAG20160923T194920 RECID=15 STAMP=923341762
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 23-SEP-16

Starting restore at 23-SEP-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=137 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 23-SEP-16

sql statement: alter system set  control_files =   ''+DATA_DUP/dupdb3/controlfile/current.276.923341755'', ''+FRA_DUP/dupdb3/controlfile/current.276.923341755'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2388422656 bytes

Fixed Size                     2215664 bytes
Variable Size               1358954768 bytes
Database Buffers            1006632960 bytes
Redo Buffers                  20619264 bytes

database mounted
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set newname for datafile  1 to "+DATA_DUP/system.dbf";
   set newname for datafile  2 to "+DATA_DUP/sysaux.dbf";
   set newname for datafile  3 to "+DATA_DUP/undotbs1.dbf";
   set newname for datafile  4 to "+DATA_DUP/users.dbf";
   set newname for datafile  5 to "+DATA_DUP/example.dbf";
   set newname for datafile  6 to "+DATA_DUP/rcat_data_01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format "+DATA_DUP/system.dbf"   
   datafile  2 auxiliary format "+DATA_DUP/sysaux.dbf"   
   datafile  3 auxiliary format "+DATA_DUP/undotbs1.dbf"   
   datafile  4 auxiliary format "+DATA_DUP/users.dbf"   
   datafile  5 auxiliary format "+DATA_DUP/example.dbf"   
   datafile  6 auxiliary format "+DATA_DUP/rcat_data_01.dbf"   ;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 23-SEP-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/dupdb2/users.dbf
output file name=+DATA_DUP/users.dbf tag=TAG20160923T194949
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:11:26
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/dupdb2/system.dbf
output file name=+DATA_DUP/system.dbf tag=TAG20160923T194949
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/dupdb2/sysaux.dbf
output file name=+DATA_DUP/sysaux.dbf tag=TAG20160923T194949
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/dupdb2/undotbs1.dbf
output file name=+DATA_DUP/undotbs1.dbf tag=TAG20160923T194949
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/dupdb2/example.dbf
output file name=+DATA_DUP/example.dbf tag=TAG20160923T194949
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/dupdb2/rcat_data_01.dbf
output file name=+DATA_DUP/rcat_data_01.dbf tag=TAG20160923T194949
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 23-SEP-16

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=923342862 file name=+DATA_DUP/system.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=16 STAMP=923342862 file name=+DATA_DUP/sysaux.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=923342862 file name=+DATA_DUP/undotbs1.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=18 STAMP=923342862 file name=+DATA_DUP/users.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=19 STAMP=923342862 file name=+DATA_DUP/example.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=20 STAMP=923342862 file name=+DATA_DUP/rcat_data_01.dbf

contents of Memory Script:
{
   recover
   clone database
   noredo
    delete archivelog
   ;
}
executing Memory Script

Starting recover at 23-SEP-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=137 device type=DISK

Finished recover at 23-SEP-16

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name =
 ''DUPDB3'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2388422656 bytes

Fixed Size                     2215664 bytes
Variable Size               1358954768 bytes
Database Buffers            1006632960 bytes
Redo Buffers                  20619264 bytes

sql statement: alter system set  db_name =  ''DUPDB3'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2388422656 bytes

Fixed Size                     2215664 bytes
Variable Size               1358954768 bytes
Database Buffers            1006632960 bytes
Redo Buffers                  20619264 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB3" RESETLOGS NOARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      584
 LOGFILE
  GROUP  1 ( '+data_dup', '+fra_dup' ) SIZE 50 M  REUSE,
  GROUP  2 ( '+data_dup', '+fra_dup' ) SIZE 50 M  REUSE,
  GROUP  3 ( '+data_dup', '+fra_dup' ) SIZE 50 M  REUSE
 DATAFILE
  '+DATA_DUP/system.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
 "+data_dup";
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA_DUP/sysaux.dbf",
 "+DATA_DUP/undotbs1.dbf",
 "+DATA_DUP/users.dbf",
 "+DATA_DUP/example.dbf",
 "+DATA_DUP/rcat_data_01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data_dup in control file

cataloged datafile copy
datafile copy file name=+DATA_DUP/sysaux.dbf RECID=1 STAMP=923342897
cataloged datafile copy
datafile copy file name=+DATA_DUP/undotbs1.dbf RECID=2 STAMP=923342897
cataloged datafile copy
datafile copy file name=+DATA_DUP/users.dbf RECID=3 STAMP=923342897
cataloged datafile copy
datafile copy file name=+DATA_DUP/example.dbf RECID=4 STAMP=923342897
cataloged datafile copy
datafile copy file name=+DATA_DUP/rcat_data_01.dbf RECID=5 STAMP=923342897

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=923342897 file name=+DATA_DUP/sysaux.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=923342897 file name=+DATA_DUP/undotbs1.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=923342897 file name=+DATA_DUP/users.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=923342897 file name=+DATA_DUP/example.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=923342897 file name=+DATA_DUP/rcat_data_01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 23-SEP-16
 
12. Now define SPFILE to start from ASM and edit the pfile to point to the SPFILE in ASM:
   
    SQL> startup nomount pfile='$ORACLE_HOME/dbs/initDUPDB3.ora';
ORACLE instance started.
Total System Global Area 2388422656 bytes
Fixed Size           2215664 bytes
Variable Size          1358954768 bytes
Database Buffers 1006632960 bytes
Redo Buffers              20619264 bytes
SQL> create spfile='+DATA_DUP' from pfile='$ORACLE_HOME/dbs/initDUPDB3.ora';
File created.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 2388422656 bytes
Fixed Size           2215664 bytes
Variable Size          1358954768 bytes
Database Buffers 1006632960 bytes
Redo Buffers              20619264 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;

NAME   TYPE      VALUE
------- -------  ------------------------------
spfile  string   /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileDUPDB3.ora
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

(first we search initial location and name of spfile in ASM then use mkalias if needed and define new name and location for spfile as in ASM to non-ASM post)

cat > initDUPDB3.ora
   SPFILE='+DATA_DUP/dupdb3/spfileDUPDB3.ora'
   PRESS CTRL+D


SQL> startup;
ORACLE instance started.

Total System Global Area 2388422656 bytes
Fixed Size           2215664 bytes
Variable Size          1358954768 bytes
Database Buffers 1006632960 bytes
Redo Buffers              20619264 bytes
Database mounted.
Database opened.
SQL> sho parameter spfile;

NAME        TYPE   VALUE
----------  ------- ------------------------------
spfile      string  +DATA_DUP/dupdb3/spfiledupdb3.ora
  
That is it!!!