Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes

---- Example error in Standby DB ------

ORA-01578: 
ORA-01110: 
ORA-26040: Data block was loaded using the NOLOGGING option

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

--- Backup Server ---

col FIRST_NONLOGGED_SCN format  999999999999999
SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0 ;

     FILE# FIRST_NONLOGGED_SCN
---------- -------------------
         4       2229616324279
         6       2229616324282
         7       2229616324285
         8       2229486974087
         9       2229486973946
        10       2229486973946
        11       2229486973954
        19       2229616324228
        20       2229616324228
        21       2229616324231
        22       2229486973959
        24       2229486974422
        25       2229616324273
        27       2229486974437
        28       2229486974454
        29       2229616324276



 recovery cancel;
 ALTER DATABASE DATAFILE 4 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 6 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 7 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 8 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 9 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 10 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 11 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 19 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 20 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 21 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 22 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 24 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 25 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 27 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 28 OFFLINE FOR DROP;
 ALTER DATABASE DATAFILE 29 OFFLINE FOR DROP;








-- PRIMARY --
BACKUP INCREMENTAL FROM SCN 2229616324279 DATAFILE 4 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229616324282 DATAFILE 6 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229616324285 DATAFILE 7 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229486974087 DATAFILE 8 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229486973946 DATAFILE 9 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229486973946 DATAFILE 10 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229486973954 DATAFILE 11 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229616324228 DATAFILE 19 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229616324228 DATAFILE 20 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229616324231 DATAFILE 21 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229486973959 DATAFILE 22 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229486974422 DATAFILE 24 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229616324273 DATAFILE 25 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229486974437 DATAFILE 27 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229486974454 DATAFILE 28 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';
BACKUP INCREMENTAL FROM SCN 2229616324276 DATAFILE 29 FORMAT '/u04/rbackup/ForStandby_%U' TAG 'FOR STANDBY';


SCP the backup from primary to backup;


-- Backup --

rman connect > calatog start with

 ALTER DATABASE DATAFILE 4 online;
 ALTER DATABASE DATAFILE 6 online;
 ALTER DATABASE DATAFILE 7 online;
 ALTER DATABASE DATAFILE 8 online;
 ALTER DATABASE DATAFILE 9 online;
 ALTER DATABASE DATAFILE 10 online;
 ALTER DATABASE DATAFILE 11 online;
 ALTER DATABASE DATAFILE 19 online;
 ALTER DATABASE DATAFILE 20 online;
 ALTER DATABASE DATAFILE 21 online;
 ALTER DATABASE DATAFILE 22 online;
 ALTER DATABASE DATAFILE 24 online;
 ALTER DATABASE DATAFILE 25 online;
 ALTER DATABASE DATAFILE 27 online;
 ALTER DATABASE DATAFILE 28 online;
 ALTER DATABASE DATAFILE 29 online;


- recover DATAFILE 4,6,7,8,9,10,11,19,20,21,22,24,25,27,28,29; # For manual standby 

or

- recover DATAFILE 4,6,7,8,9,10,11,19,20,21,22,24,25,27,28,29 NOREDO;


- Now perform recovery standby db


For more detail Below the reference doc ID.

Reference :

Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes [ID 958181.1]

RMAN real usage of MAXSETSIZE, MAXPIECESIZE, FILESPERSET, SECTION SIZE, MAXOPENFILES

http://www.dbarj.com.br/en/2015/02/rman-real-usage-maxsetsize-maxpiecesize-filesperset-section-size-maxopenfiles/

http://oracleinaction.com/tune-rman-i/

RECOVER ORACLE DATAFILES WITH NO DOWNTIME

What will happen if dbf file accidentally deleted when database is still open and how to recover it?

And Lots of time interviewer asking this question (So first method is here and 2nd method is restore RMAN backup). Anyways come to the point and here we go.....

On Unix/Linux, when a file is deleted, but a process still has the file open, the file is still there in the filesystem, and only the inode is removed.
But the process can continue to use its file handle, and the file can also be accessible under /proc/<pid>/fd .

In the following example, we use that behavior to recover a lost datafile after is has been dropped from the os (with rm) but the datafile is still open by the background processes.

First, we create a tablespace, and populate a table in it.

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

Create a tablespace: 

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

SQL> create tablespace TEST datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 10M;

Tablespace created.

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

Create a table in tablespace: 

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

SQL> create table ORATAB tablespace TEST as select * from dba_objects;

Table created.

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

Check that table data is accessible: 

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

SQL> select count(*) from ORATAB;

  COUNT(*)

----------

     71902

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

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

Then, we remove the datafile from unix CLI.

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

here is the datafile 

ls -l /u01/app/oracle/oradata/orcl/test_rm.dbf

-rw-r—– 1 oracle dba 10493952 Sep 19 15:21 24 /u01/app/oracle/oradata/orcl/test01.dbf

we ‘accidently’ remove the datafile 

rm /u01/app/oracle/oradata/orcl/test01.dbf

ls -l /u01/app/oracle/oradata/orcl/test01.dbf

ls: /u01/app/oracle/oradata/orcl/test01.dbf: no such file or directory

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

Here the datafile is lost.

Now we connect again.

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

sqlplus / as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

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

Check if table data is accessible & you getting below error some times: 

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

SQL> select count(*) from ORATAB;

select * from ORATAB

*

ERROR at line 1:

ORA-01116: error in opening database file 5

ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/test01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

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

The datafile is lost and data is not accessible.

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

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

However, the datafile should still have an open file descriptor by an oracle background process

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

Check the dbwriter pid: 

ps -edf | grep dbw

[oracle@host01 ~]$ ps -edf | grep dbw

oracle    6350     1  0 13:09 ?        00:00:01 ora_dbw0_orcl

oracle    8237  8212  0 15:20 pts/1    00:00:00 grep dbw

List the deleted file handles for that DBWRITER process.

ls -l /proc/_PID_OF_DBWRITER/fd | grep deleted

or

Check its opened file descriptors for our file: 

[oracle@host01 ~]$ ls -l /proc/6350/fd | grep test

lrwx------ 1 oracle oinstall 64 Sep 19 15:21 24 -> /u01/app/oracle/oradata/orcl/test01.dbf (deleted)

here it is: 

[oracle@host01 ~]$ ls -l /proc/6350/fd/24

lrwx------ 1 oracle oinstall 64 Sep 19 15:21 /proc/6350/fd/24 -> /u01/app/oracle/oradata/orcl/test01.dbf (deleted)

In some other unix, lsof may be needed to map the file descriptor with the deleted file name

first we set a symbolic link so that oracle can see it as it was before the delete:

    ln -s /proc/6350/fd/24 -> /u01/app/oracle/oradata/orcl/test01.dbf

here data is accessible, but that will be lost if dbwriter closes it file handle (i.e if the database is closed)

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

However we can now set the tablespace read only so that it is checkpointed, and no writes occurs on it.

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

SQL> alter tablespace TEST read only;

Tablespace altered.

Now copy the file safely.

then we drop the symbolic link: 

     rm /u01/app/oracle/oradata/orcl/test01.dbf

     ls -l /u01/app/oracle/oradata/orcl/test01.dbf

     ls: /u01/app/oracle/oradata/orcl/test.dbf: No such file or directory

and we can now copy the file 

    cp -p /proc/6350/fd/24 /u01/app/oracle/oradata/orcl/test01.dbf

    ls -l /u01/app/oracle/oradata/orcl/test01.dbf

-rw-r—– 1 oracle dba 10493952 Sep 19 14:54 /u01/app/oracle/oradata/orcl/test01.dbf

And datafile is now available again.

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

We have it back, lets put the tablespace back in read/write 

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

SQL> alter tablespace test_rm read write;

Tablespace altered.

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

Check data is still there: 

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

SQL> select count(*) from ORATAB;

  COUNT(*)

----------

     71902

This is not to be used like that in production. This is unsupported and may behave differently on different unix/linux or oracle flavours.

COMPRESSED INCREMENTAL BACKUP

COMPRESSED INCREMENTAL ONLINE BACKUP PLUS ARCHIVELOG FILES AND AUTO DELETE OF ARCHIVELOG


INCREMENTAL LEVEL 0 BACKUP

RUN
{
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0
DATABASE PLUS ARCHIVELOG;
DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES TO DISK;
}


INCREMENTAL LEVEL 1 BACKUP

RUN
{
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1
DATABASE PLUS ARCHIVELOG;
DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES TO DISK;
}

Reference:

http://www.thegeekstuff.com/2013/08/oracle-rman-backup/

https://oracle-base.com/articles/10g/rman-enhancements-10g

RMAN BACKUP DETAIL

~~~~~~~~~~~~~~~~~~~~~~~~
# RMAN backup history
~~~~~~~~~~~~~~~~~~~~~~~~
set lines 150
set pages 900
col start for a20
col end for a20
col status format a11
col input_bytes_display format a10
col output_bytes_display format a10
col status_weight format 99999
SELECT
    to_char(start_time,'YYYY.MM.DD HH24:MI:SS') "Start" ,
    to_char(end_time,'YYYY.MM.DD HH24:MI:SS') "End",
    status,
    status_weight,
    input_type,
    input_bytes_display,
    output_bytes_display
FROM
    V$RMAN_BACKUP_JOB_DETAILS
WHERE
    ( start_time between (SYSDATE - 8) and  (SYSDATE ))
order by
    start_time;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# This script will report on all backups – full, incremental and archivelog backups –
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# This script will report all on full and incremental backups, not archivelog backups –
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
RMAN Incremental Report Generation Query
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set pages 100 lines 200 feedback off markup html on
alter session set nls_date_format='DD-MON-YYYY HH24:MI';
spool c:\backup.html append
select host_name,instance_name from v$instance;
select
(select host_name from v$instance) AS "Host_NAME",
(Select name from v$database) as "DB_NAME",
start_time,end_time,elapsed_seconds/60/60 as "DURATION(HOURS)", INPUT_TYPE,
(r.status) as status,(b.incremental_level) as incremental_level
from v$RMAN_BACKUP_JOB_DETAILS r
inner join
(select distinct session_stamp,incremental_level from v$backup_set_details) b on
r.session_stamp = b.session_stamp where incremental_level is not null
and r.start_time > sysdate - 7
and INPUT_TYPE <>'ARCHIVELOG' order by 3;
spool off;
exit

Backup Pluggable Databases

Oracle 12c New Feature: How to backup pluggable databases

Oracle 12c introduced the new multi-tenant feature called Pluggable Databases (PDB). We will show how to take a backup of the pluggable database components in this post.

Setup for RMAN with Oracle 12c

In order to use the Oracle 12c Recovery Manager (RMAN) utility for pluggable database backups, you need to first enable archivelog mode.

Once archivelog mode is enabled, we can take a backup of the pluggable database

rman target sysbackup

Now we can verify that the backup image is available from RMAN for our pluggable database

Backup for root component of Oracle 12c Pluggable Databases

Backup database ;    # Both database backup CDB and PDB all       

Pluggable database backup above the command.

In a nutshell, an Oracle 12c PDB consists of two parts: a root component and a seed component that includes the data. Earlier we performed a full database backup of the entire pluggable database but let us say that we just want to backup the root itself. We can do so with the RMAN command BACKUP DATABASE ROOT as shown in the following example:

Now let us verify the root backup for our PDB with Oracle 12c:

Stay tuned when we visit how to restore pluggable databases with RMAN and Oracle 12c!

RESTORE & RECOVER root (Container)

RMAN> restore datafile 6;

RMAN>restore database root;

RMAN>recover database root;

RESTORE & RECOVER PDB

RMAN> restore datafile 29 pluggable database pdb1;   # restore datafile 29;

RMAN> restore pluggable database pdb1;           # restore pluggable database pdb1,pdb2,pdb3;

RMAN> recover pluggable database pdb1;

DataBase Refresh / Cloning

Database Refresh / Cloning / Migration

---------------------------
Non-ASM 10g/11g 
---------------------------
Reference: 

http://oraclegurukul.blogspot.in/2012/12/rman-cloning-10g-11g.html

----------------------------------------------------
DB Refresh DataPump
----------------------------------------------------
Reference:

https://born4dba.wordpress.com/2012/05/19/schema-refresh-using-data-pump/

-----------------------------------------------------------------------
11g ASM DB Refresh With DataPump
-----------------------------------------------------------------------
Reference:

http://select-star-from.blogspot.in/2013/08/schema-refresh-using-asm-diskgroups.html

--------------------------------------------------------------------------------------------------------------
RAC 10g/11g RMAN Cloning/Refresh RAC ASM to ASM & ASM to NON ASM 
--------------------------------------------------------------------------------------------------------------
Reference: 

https://simplyoracle.blogspot.in/2010/04/rac-database-refreshcloning-on-asm.html
https://dbmentors.blogspot.in/2012/04/rman-duplicate-database-from-rac-asm-to.html
https://gavinsoorma.com/2012/04/rman-duplicate-database-rac-to-rac/
https://nikhilappsdba.blogspot.in/2014/01/cloning-11g-rac-asm-to-non-asm.html
https://www.pythian.com/blog/duplicate-from-active-database-using-rman-step-by-step/
https://tsbreddy.blogspot.in/2012/06/rman-refresh-steps.html
https://dbarock.blogspot.in/2012/08/database-refresh-using-rman.html

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

Migration :

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

NON-ASM to ASM in 10g Different host

http://gavinsoorma.com/2010/04/creating-an-asm-duplicate-database-from-a-non-asm-database/

http://oraclegurukul.blogspot.in/2014/04/non-asm-to-asm-with-rman.html

NON-ASM to ASM in 11g (Different host Duplicate Command)

http://dbaworkshop.blogspot.in/2010/10/rman-how-to-duplicate-non-asm-active.html

NON-ASM 10g to ASM 11g (Different host)

https://blogs.oracle.com/RobinAntony/entry/converting_10gr2_non_asm_database

Non-ASM 11g Database To ASM (Same Host)

https://orasteps.wordpress.com/2013/04/23/migration-of-non-asm-11g-database-to-asm/

NON-ASM to ASM in 11g Different host

1- Connect to 11g database and take RMAN full backup.

    #rman target /

    RMAN> backup as backupset database;

2- Copy 10g database backup files and archive files to 11g database server.

Install and Configure Standalone Grid infrastructure and mount the ASM DISK(+DATA)

Install the 11.2.0.3 Software alone.

The steps for 11g database:

3- Create temporary pfile in $ORACLE_HOME/dbs

*.audit_file_dest=’/oracle/admin/orcl/adump’

*.compatible=’11.2.0.0.0’

*.control_files=’+DATA/orcl/controlfile/current.1′,’+DATA/orcl/controlfile/current.2′

*.db_block_size=8192

*.db_create_file_dest=’+DATA’

*.db_create_online_log_dest_1=’+RECO’

*.db_name=’ORCL’

*.diagnostic_dest=’/oracle’

*.job_queue_processes=0

*.open_cursors=300

*.pga_aggregate_target=1G

*.processes=150

*.remote_login_passwordfile=’EXCLUSIVE’

*.sga_target=2G

*.undo_tablespace=’UNDOTBS1′

4- Open the database in NOMOUNT state.

    # rman target /

    RMAN> startup nomount;

5- Restore controlfile from backup.

    RMAN>LIST BACKUP OF CONTROLFILE

    RMAN> restore controlfile from ‘<backup piece name>′;

6- Open the database in MOUNT state.

    RMAN> alter database mount;

7- Catalog RMAN backup files and archive log files.(Ensure you have restored the backup copy)

    RMAN> catalog start with ‘/oracle/ora11g/orcl/backup’;

    RMAN> catalog start with ‘/oracle/ora11g/orcl/archive’;

8- Restore 10g database backup to +DATA diskgroup and perform incomplete recovery.

    RMAN> run

    {

    allocate channel c1 device type disk;

    SET NEWNAME FOR DATAFILE 1 TO ‘+DATA’;

    SET NEWNAME FOR DATAFILE 2 TO ‘+DATA’;

    SET NEWNAME FOR DATAFILE 3 TO ‘+DATA’;

    SET NEWNAME FOR DATAFILE 4 TO ‘+DATA’;

    restore database until sequence 4;

    switch datafile all;

    recover database until sequence 4;

    }

    ###shutdown the  database

    asmcmd>cd DATA/ControlFile

Resolving Gaps in Data Guard Apply Using Incremental RMAN BAckup

Recently, we had a glitch on a Data Guard (physical standby database) on infrastructure.
This is not a critical database; so the monitoring was relatively lax. And that being done by an outsourcer does not help it either.
In any case, the laxness resulted in a failure remaining undetected for quite some time and it was eventually discovered only when the
customer complained. This standby database is usually opened for read only access from time to time.
This time, however, the customer saw that the data was significantly out of sync with primary and raised a red flag. Unfortunately,
at this time it had become a rather political issue.

Since the DBA in charge couldn’t resolve the problem, I was called in. In this post, I will describe the issue and how it was resolved. In summary,
there are two parts of the problem:

(1) What happened
(2) How to fix it

What Happened

Let’s look at the first question – what caused the standby to lag behind. First, I looked for the current SCN numbers of the primary and standby databases.


On the primary:
SQL> col current_scn format  999999999999999
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1447102


On the standby:
SQL> col current_scn format  999999999999999
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1301571

Clearly there is a difference. But this by itself does not indicate a problem; since the standby is expected to lag behind the primary (this is an asynchronous non-real time apply setup). The real question is how much it is lagging in the terms of wall clock. To know that I used the scn_to_timestamp function to translate the SCN to a timestamp:

SQL> select scn_to_timestamp(1447102) from dual;

SCN_TO_TIMESTAMP(1447102)
-------------------------------
18-DEC-09 08.54.28.000000000 AM

I ran the same query to know the timestamp associated with the SCN of the standby database as well (note, I ran it on the primary database, though; since it will fail in the standby in a mounted mode):

SQL> select scn_to_timestamp(1301571) from dual;

SCN_TO_TIMESTAMP(1301571)
-------------------------------
15-DEC-09 07.19.27.000000000 PM

This shows that the standby is two and half days lagging! The data at this point is not just stale; it must be rotten.

The next question is why it would be lagging so far back in the past. This is a 10.2 database where FAL server should automatically resolved any gaps in archived logs. Something must have happened that caused the FAL (fetch archived log) process to fail. To get that answer, first, I checked the alert log of the standby instance. I found these lines that showed the issue clearly:

…
Fri Dec 18 06:12:26 2009
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 700
Fetching gap sequence in thread 1, gap sequence 700-700
… …
Fri Dec 18 06:13:27 2009
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 700-700
DBID 846390698 branch 697108460
FAL[client]: All defined FAL servers have been attempted.
…

Going back in the alert log, I found these lines:

Tue Dec 15 17:16:15 2009
Fetching gap sequence in thread 1, gap sequence 700-700
Error 12514 received logging on to the standby
FAL[client, MRP0]: Error 12514 connecting to DEL1 for fetching gap sequence
Tue Dec 15 17:16:15 2009
Errors in file /opt/oracle/admin/DEL2/bdump/del2_mrp0_18308.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Tue Dec 15 17:16:45 2009
Error 12514 received logging on to the standby
FAL[client, MRP0]: Error 12514 connecting to DEL1 for fetching gap sequence
Tue Dec 15 17:16:45 2009
Errors in file /opt/oracle/admin/DEL2/bdump/del2_mrp0_18308.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

This clearly showed the issue. On December 15th at 17:16:15, the Managed Recovery Process encountered an error while receiving the log information from the primary. The error was ORA-12514 “TNS:listener does not currently know of service requested in connect descriptor”.
This is usually the case when the TNS connect string is incorrectly specified. The primary is called DEL1 and there is a connect string called DEL1 in the standby server.

The connect string works well. Actually, right now there is no issue with the standby getting the archived logs; so there connect string is fine - now.
The standby is receiving log information from the primary. There must have been some temporary hiccups causing that specific archived log not to
travel to the standby. If that log was somehow skipped (could be an intermittent problem), then it should have been picked by the FAL process
later on; but that never happened. Since the sequence# 700 was not applied, none of the logs received later – 701, 702 and so on – were applied either.
This has caused the standby to lag behind since that time.

So, the fundamental question was why FAL did not fetch the archived log sequence# 700 from the primary. To get to that, I looked into the alert
log of the primary instance. The following lines were of interest:

…
Tue Dec 15 19:19:58 2009
Thread 1 advanced to log sequence 701 (LGWR switch)
Current log# 2 seq# 701 mem# 0: /u01/oradata/DEL1/onlinelog/o1_mf_2_5bhbkg92_.log
Tue Dec 15 19:20:29 2009Errors in file /opt/oracle/product/10gR2/db1/admin/DEL1/bdump/del1_arc1_14469.trc:
ORA-00308: cannot open archived log '/u01/oraback/1_700_697108460.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Tue Dec 15 19:20:29 2009
FAL[server, ARC1]: FAL archive failed, see trace file.
Tue Dec 15 19:20:29 2009
Errors in file /opt/oracle/product/10gR2/db1/admin/DEL1/bdump/del1_arc1_14469.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed.
Archiver continuing
Tue Dec 15 19:20:29 2009
ORACLE Instance DEL1 - Archival Error. Archiver continuing.
…

These lines showed everything clearly. The issue was:

ORA-00308: cannot open archived log '/u01/oraback/1_700_697108460.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory

The archived log simply was not available. The process could not see the file and couldn’t get it across to the standby site.

Upon further investigation I found that the DBA actually removed the archived logs to make some room in the filesystem without
realizing that his action has removed the most current one which was yet to be transmitted to the remote site. The mystery surrounding why the
FAL did not get that log was finally cleared.


==========
Solution
==========
Now that I know the cause, the focus was now on the resolution. If the archived log sequence# 700 was available on the primary, I could have easily copied it over to the standby, registered the log file and let the managed recovery process pick it up. But unfortunately, the file was gone and I couldn’t just recreate the file. Until that logfile was applied, the recovery will not move forward. So, what are my options?

One option is of course to recreate the standby - possible one but not technically feasible considering the time required. The other option is to apply the incremental backup of primary from that SCN number. That’s the key – the backup must be from a specific SCN number. I have described the process since it is not very obvious. The following shows the step by step approach for resolving this problem. I have shown where the actions must be performed – [Standby] or [Primary].

1. [Standby] Stop the managed standby apply process:

SQL> alter database recover managed standby database cancel;

Database altered.

2. [Standby] Shutdown the standby database

3. [Primary] On the primary, take an incremental backup from the SCN number where the standby has been stuck:

------------------------------------------- Rman Backup Status ------------------------------------------

SELECT OPERATION, STATUS, MBYTES_PROCESSED, START_TIME, END_TIME from V$RMAN_STATUS;

select
  sid,
  start_time,
  totalwork
  sofar,
 (sofar/totalwork) * 100 pct_done
from
   v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%';


select sid, spid, client_info,
   event,  seconds_in_wait,
   p1, p2, p3
 from  v$process p, v$session s
 where p.addr = s.paddr
 and client_info like 'rman channel=%';
-----------------------------------------------------------------------------------------------------------

RMAN> run {
2> allocate channel c1 type disk format '/u01/oraback/%U.rmb';
3> backup incremental from scn 1301571 database;
4> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=139 devtype=DISK

Starting backup at 18-DEC-09
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/oradata/DEL1/datafile/o1_mf_system_5bhbh59c_.dbf
… …
piece handle=/u01/oraback/06l16u1q_1_1.rmb tag=TAG20091218T083619 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:06
Finished backup at 18-DEC-09
released channel: c1

4. [Primary] On the primary, create a new standby controlfile:

SQL> alter database create standby controlfile as '/u01/oraback/DEL1_standby.ctl';

Database altered.

5. [Primary] Copy these files to standby host:

oracle@oradba1 /u01/oraback# scp *.rmb *.ctl oracle@oradba2:/u01/oraback
oracle@oradba2's password:
06l16u1q_1_1.rmb 100% 43MB 10.7MB/s 00:04
DEL1_standby.ctl 100% 43MB 10.7MB/s 00:04

6. [Standby] Bring up the instance in nomount mode:

SQL> startup nomount


7. [Standby] Check the location of the controlfile:

SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/oradata/standby_cntfile.ctl


8. [Standby] Replace the controlfile with the one you just created in primary.

9. $ cp /u01/oraback/DEL1_standby.ctl /u01/oradata/standby_cntfile.ctl

10.[Standby] Mount the standby database:

SQL> alter database mount standby database;

11.[Standby] RMAN does not know about these files yet; so you must let it know – by a process called cataloging. Catalog these files:

$ rman target=/

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Dec 18 06:44:25 2009

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

connected to target database: DEL1 (DBID=846390698, not open)
RMAN> catalog start with '/u01/oraback';

using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/oraback

List of Files Unknown to the Database
=====================================
File Name: /u01/oraback/DEL1_standby.ctl
File Name: /u01/oraback/06l16u1q_1_1.rmb

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/oraback/DEL1_standby.ctl
File Name: /u01/oraback/06l16u1q_1_1.rmb


12.Recover these files:

RMAN> recover database;

Starting recover at 18-DEC-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oradata/DEL2/datafile/o1_mf_system_5lptww3f_.dbf
...…
channel ORA_DISK_1: reading from backup piece /u01/oraback/05l16u03_1_1.rmb
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oraback/05l16u03_1_1.rmb tag=TAG20091218T083619
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

starting media recovery

archive log thread 1 sequence 8012 is already on disk as file /u01/oradata/1_8012_697108460.dbf
archive log thread 1 sequence 8013 is already on disk as file /u01/oradata/1_8013_697108460.dbf
… …

13. After some time, the recovery fails with the message:

archive log filename=/u01/oradata/1_8008_697108460.dbf thread=1 sequence=8009
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/18/2009 06:53:02
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/oradata/1_8008_697108460.dbf'
ORA-00310: archived log contains sequence 8008; sequence 8009 required
ORA-00334: archived log: '/u01/oradata/1_8008_697108460.dbf'

 ELSE

----I'm Getting this error
starting media recovery
unable to find archive log
archive log thread=1 sequence=37391
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/05/2016 10:33:44
RMAN-06054: media recovery requesting unknown log: thread 1 seq 8008 lowscn 2224474915633

RMAN> shutdown immediate


This happens because we have come to the last of the archived logs. The expected archived log with sequence# 8008 has not been generated yet.


14.At this point exit RMAN and start managed recovery process:

SQL> alter database recover managed standby database disconnect from session;

Database altered.

15.Check the SCN’s in primary and standby:

[Standby] SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1447474

[Primary] SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1447478

Now they are very close to each other. The standby has now caught up.

Use Roll Forward Feature12c R2 onwards:

Roll Forward Physical Standby Using RMAN Incremental Backup in Single Command (Doc ID 2431311.1) 

Rman backup using standby database

Rman backup using standby database

Some companies want to schedule backup from the standby database to reduce the load in the production environment. Infact standby serve this purpose without any additional effort.

Backup of standby database is similar to production. To take consistent backup we need to cancel the recovery mode and bring the database in mount mode, otherwise the backup will be in inconsistent mode.

The rman user id should be part of sysdba

$ rman target rman/oracle@db1 catalog rmanUser/passwd@catalog
RMAN> backup database;
RMAN> backup archivelog all;

Note:

1.Both the primary database and standby database should use the same recovery catalog


2.The control file and SPFILE must be backed up on the primary database. 


3. It is not advisable to run the backup from both the standby and primary database.
    When you run delete obsolete ,you may get the below error if backup has been taken from both primary and standby databases.

             RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
             RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
             RMAN-06210: List of Mismatched objects

4. if at all you would like to take backup from both the primary and standby,Use a different recovery catalog schema for each of these two targets.  still you need to take the controlfile backup from the primary database.

RMAN Duplicate from Active Database - ASM to non-ASM

 

In this post I want to explain how to create duplicate database from active 11gR2 database using RMAN. There are many blog posts covering that subject but most of them are covering non-ASM to non-ASM, ASM to ASM or non-ASM to ASM duplications.
I want to cover ASM to non-ASM duplication from active database as this subject is not widely covered.

Active duplication from ASM to non-ASM (or other duplications) can be little tricky. You could easily lose quite of time on troubleshooting if you don't comply all prerequisites during preparation. I will show you examples of some errors you could experience.

I haven't used active database duplication in production and practiced much with this feature. But still was confident that I will perform task successfully from the first time. After few hours of troubleshooting and few failed attempts I ended up on detailed reading Oracle Documentation - what I should do at the first place.
Learn on my mistakes :)


So let’s start with little demo.


- Create password file for auxiliary instance

The easiest method would be to copy password file from the source database.

$ scp orapwora11gr2 ora11gr2@192.168.56.107:/u01/app/ora11gr2/product/11.2.0/dbhome_1/dbs

ora11gr2@192.168.56.107's password:

orapwora11gr2                                                                                             

100% 1536     1.5KB/s   00:00

- Network setup

For auxiliary database create static instance registration to listener.
Add to listener.ora:

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = ora11gr2)

      (ORACLE_HOME = /u01/app/ora11gr2/product/11.2.0/dbhome_1)

      (SID_NAME = ora11gr2)

    )

  )

For both databases tnsnames.ora would be:

duplicate =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.107)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ora11gr2)

      (UR=A)

    )

  )

ora11gr2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.109)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ora11gr2)

      (UR=A)

    )

  )

Start both listeners.


- Create needed directories on target host

$ mkdir -p /u01/app/ora11gr2/admin/ora11gr2/adump

$ mkdir -p /u01/oradata/fra

$ mkdir -p /u01/oradata/ora11gr2/datafiles

$ mkdir -p /u01/oradata/ora11gr2/redologs

- Create simple parameter file and test connectivity

$ cd $ORACLE_HOME/dbs

$ cat initora11gr2.ora

db_name=ora11gr2

Start NOMOUNT auxiliary database using specified parameter file

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 12 00:26:18 2013

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

Connected to an idle instance.

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initora11gr2.ora

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

SQL>

Test connectivity.

Target:

$ sqlplus sys/oracle@ora11gr2 as sysdba

Source:

$ sqlplus sys/oracle@duplicate as sysdba

I am executing this commands on target host but you could also execute them on source.

Run this command to establish connection between databases.

$ rman target sys/oracle@ora11gr2 auxiliary sys/oracle@duplicate

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 12 00:39:00 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA11GR2 (DBID=138112863)

connected to auxiliary database: ORA11GR2 (not mounted)

- We are ready to test duplicate scenarios


Let's start with this simple script.

run

{

         DUPLICATE TARGET DATABASE TO “ora11gr2”

         FROM ACTIVE DATABASE

         DB_FILE_NAME_CONVERT '+DATA1/ora11gr2/datafile/','/u01/oradata/ora11gr2/datafiles/'

         SPFILE

         SET LOG_FILE_NAME_CONVERT '+DATA1/ora11gr2/onlinelog/','/u01/oradata/ora11gr2/redologs/'

         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump';

}

I get error:

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 10/12/2013 00:39:29

RMAN-06136: ORACLE error from auxiliary database: ORA-00200: control file could not be created

ORA-00202: control file: '+data1'

ORA-17502: ksfdcre:4 Failed to create file +data1

ORA-15001: diskgroup "DATA1" does not exist or is not mounted

ORA-15077: could not locate ASM instance serving a required diskgroup

ORA-29701: unable to connect to Cluster Synchronization Service

Oracle wants to create control file is on '+DATA1' but I don't have ASM instance running on target host. I will change location for control file adding "SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl'" to the script.

As directory structure on target host is completely different I will change location for DIAGNOSTIC_DEST also.

run

{

         DUPLICATE TARGET DATABASE TO "ora11gr2"

         FROM ACTIVE DATABASE

         DB_FILE_NAME_CONVERT '+DATA1/ora11gr2/datafile/','/u01/oradata/ora11gr2/datafiles/'

         SPFILE

         SET LOG_FILE_NAME_CONVERT '+DATA1/ora11gr2/onlinelog/','/u01/oradata/ora11gr2/redologs/'

         SET DIAGNOSTIC_DEST '/u01/app/ora11gr2'

         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump'

         SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl';

}

This time I get:

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 10/12/2013 00:50:24

RMAN-05501: aborting duplication of target database

RMAN-05517: tempfile +DATA1/ora11gr2/tempfile/temp.280.821541379 conflicts with file used by target database

With specified DB_FILE_NAME_CONVERT parameter location for temporary file is not changed. My temporary file is not in "+DATA1/ora11gr2/datafile/", but in "+DATA1/ora11gr2/tempfile/".


I will use "SET NEWNAME FOR TEMPFILE" to change location.

run

{

         SET NEWNAME FOR TEMPFILE 1 TO '/u01/oradata/ora11gr2/datafiles/temp01.dbf';

         DUPLICATE TARGET DATABASE TO "ora11gr2"

         FROM ACTIVE DATABASE

         DB_FILE_NAME_CONVERT '+DATA1/ora11gr2/datafile/','/u01/oradata/ora11gr2/datafiles/'

         SPFILE

         SET LOG_FILE_NAME_CONVERT '+DATA1/ora11gr2/onlinelog/','/u01/oradata/ora11gr2/redologs/'

         SET DIAGNOSTIC_DEST '/u01/app/ora11gr2'

         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump'

         SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl';

}

Again error:

...

contents of Memory Script:

{

   backup as copy reuse

   archivelog like  "+FRA1/ora11gr2/archivelog/2013_10_12/thread_1_seq_222.1249.828580067" auxiliary format

 "+FRA1"   ;

   catalog clone recovery area;

   switch clone datafile all;

}

executing Memory Script

Starting backup at 12.10.2013 01:07:51

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=222 RECID=247 STAMP=828580069

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 10/12/2013 01:07:52

RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/12/2013 01:07:52

ORA-17628: Oracle error 19505 returned by remote Oracle server

Oracle cannot perform recovery because '+FRA' diskgroup is missing on target host.

Now it is obvious that I could save some time if I had checked parameter file on the source host before duplication.

In my case RMAN copies parameter file from source to the target host. As I have different directory structure on the target host I have to update directory locations using SET commands.

run

{

         SET NEWNAME FOR TEMPFILE 1 TO '/u01/oradata/ora11gr2/datafiles/temp01.dbf';

         DUPLICATE TARGET DATABASE TO "ora11gr2"

         FROM ACTIVE DATABASE

         DB_FILE_NAME_CONVERT '+DATA1/ora11gr2/datafile/','/u01/oradata/ora11gr2/datafiles/'

         SPFILE

         SET LOG_FILE_NAME_CONVERT '+DATA1/ora11gr2/onlinelog/','/u01/oradata/ora11gr2/redologs/'

         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump'

         SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl'

         SET DB_RECOVERY_FILE_DEST '/u01/oradata/fra'

         SET DB_CREATE_FILE_DEST '/u01/oradata/ora11gr2/datafiles/'

         SET DIAGNOSTIC_DEST '/u01/app/ora11gr2';

}

Excerpt from RMAN log:

…

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 12.10.2013 09:34:14

Ah, finally!
Finished duplication without errors.

Let's check locations of database files on the target host.

SQL> select name from v$datafile

    union all

    select name from v$tempfile

    union all

    select name from v$controlfile

    union all

    select member from v$logfile;

NAME

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

/u01/oradata/ora11gr2/datafiles/system.268.824220237

/u01/oradata/ora11gr2/datafiles/sysaux.283.824220237

/u01/oradata/ora11gr2/datafiles/undotbs1.279.824220239

/u01/oradata/ora11gr2/datafiles/users.270.824220237

/u01/oradata/ora11gr2/datafiles/example.297.824220239

/u01/oradata/ora11gr2/datafiles/secure1.277.825195489

/u01/oradata/ora11gr2/datafiles/insecure1.267.825195681

/u01/oradata/ora11gr2/datafiles/lobdata.266.827849207

/u01/oradata/ora11gr2/datafiles/dbfstbs.265.827850825

/u01/oradata/ora11gr2/datafiles/temp01.dbf

/u01/oradata/ora11gr2/datafiles/control01.ctl

/u01/oradata/ora11gr2/redologs/group_3.282.821541363

/u01/oradata/ora11gr2/redologs/group_3.281.821541367

/u01/oradata/ora11gr2/redologs/group_2.292.821541357

/u01/oradata/ora11gr2/redologs/group_2.291.821541361

/u01/oradata/ora11gr2/redologs/group_1.294.821541349

/u01/oradata/ora11gr2/redologs/group_1.293.821541355

17 rows selected.

Hm... I don't like this ASM file naming for my duplicate database.

Again, modify script.

run

{

  SET NEWNAME FOR DATAFILE 1 TO '/u01/oradata/ora11gr2/datafiles/system01.dbf'; 

  SET NEWNAME FOR DATAFILE 2 TO '/u01/oradata/ora11gr2/datafiles/sysaux01.dbf';

  SET NEWNAME FOR DATAFILE 3 TO '/u01/oradata/ora11gr2/datafiles/undotbs01.dbf';

  SET NEWNAME FOR DATAFILE 4 TO '/u01/oradata/ora11gr2/datafiles/users01.dbf'; 

  SET NEWNAME FOR DATAFILE 5 TO '/u01/oradata/ora11gr2/datafiles/example01.dbf';

  SET NEWNAME FOR DATAFILE 6 TO '/u01/oradata/ora11gr2/datafiles/secure1.dbf';

  SET NEWNAME FOR DATAFILE 7 TO '/u01/oradata/ora11gr2/datafiles/insecure1.dbf';

  SET NEWNAME FOR DATAFILE 9 TO '/u01/oradata/ora11gr2/datafiles/lobdata.dbf';

  SET NEWNAME FOR DATAFILE 10 TO '/u01/oradata/ora11gr2/datafiles/dbfstbs.dbf';

  SET NEWNAME FOR TEMPFILE 1 TO '/u01/oradata/ora11gr2/datafiles/temp01.dbf'; 

         DUPLICATE TARGET DATABASE TO "ora11gr2"

         FROM ACTIVE DATABASE

                     LOGFILE

      GROUP 1 ('/u01/oradata/ora11gr2/redologs/redo01a.log', 

               '/u01/oradata/ora11gr2/redologs/redo01b.log') SIZE 50M REUSE, 

      GROUP 2 ('/u01/oradata/ora11gr2/redologs/redo02a.log', 

               '/u01/oradata/ora11gr2/redologs/redo02b.log') SIZE 50M REUSE,

      GROUP 3 ('/u01/oradata/ora11gr2/redologs/redo03a.log', 

               '/u01/oradata/ora11gr2/redologs/redo03b.log') SIZE 50M REUSE

         SPFILE

         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump'

         SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl'

         SET DB_RECOVERY_FILE_DEST '/u01/oradata/fra'

         SET DB_CREATE_FILE_DEST '/u01/oradata/ora11gr2/datafiles/'

         SET DIAGNOSTIC_DEST '/u01/app/ora11gr2';

}

Check database files.

SQL> select name from v$datafile

  union all

    select name from v$tempfile

    union all

    select name from v$controlfile

    union all

    select member from v$logfile;

NAME

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

/u01/oradata/ora11gr2/datafiles/system01.dbf

/u01/oradata/ora11gr2/datafiles/sysaux01.dbf

/u01/oradata/ora11gr2/datafiles/undotbs01.dbf

/u01/oradata/ora11gr2/datafiles/users01.dbf

/u01/oradata/ora11gr2/datafiles/example01.dbf

/u01/oradata/ora11gr2/datafiles/secure1.dbf

/u01/oradata/ora11gr2/datafiles/insecure1.dbf

/u01/oradata/ora11gr2/datafiles/lobdata.dbf

/u01/oradata/ora11gr2/datafiles/dbfstbs.dbf

/u01/oradata/ora11gr2/datafiles/temp01.dbf

/u01/oradata/ora11gr2/datafiles/control01.ctl

/u01/oradata/ora11gr2/redologs/redo03a.log

/u01/oradata/ora11gr2/redologs/redo03b.log

/u01/oradata/ora11gr2/redologs/redo02a.log

/u01/oradata/ora11gr2/redologs/redo02b.log

/u01/oradata/ora11gr2/redologs/redo01a.log

/u01/oradata/ora11gr2/redologs/redo01b.log

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

ora11gr2