DBFS filesystem Write error

I was trying to use the DBFS file system on test environment to take rman backup(Although it is not one of the recommended filesystem for keeping the backups).DBFS is basically used to store LOB data which is referred a securefiles in 11g.The LOB data of format pre 11g version is referred as basicfiles. After configuration of DBFS I used the most basic method to mount the file system


dbfs_client dbfs_user@migrate  /mnt/dbfs.

My dbfs filesystem got mounted and I was able to create the files manually there but I had issue while invoking the RMAN backup


[oracle@localhost ~]$ df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      37295632  27205460   8165052  77% /
/dev/sda1               101086     43302     52565  46% /boot
tmpfs                   765228    158800    606428  21% /dev/shm
dbfs                     19648       184     19464   1% /mnt/dbfs

RMAN BACKUP was throwing following error

RMAN> backup as copy datafile 2 format '/mnt/dbfs/staging_area/file2.bkp';

Starting backup at 27-DEC-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=49 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=+DATA/migrate/datafile/sysaux.259.793193181

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

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

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

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/27/2012 08:24:22

ORA-19504: failed to create file "/mnt/dbfs/staging_area/file2.bkp"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 13: Permission denied

Additional information: 1

The cause of the problem could be following.The staging area directory is owned by root user.

[oracle@localhost dbfs]$ ls -lrt
total 0
drwxrwxrwx 3 root root 0 Dec 26 15:44 staging_area

So I mounted again the filesystem with the following option (allow_other).


 dbfs_client dbfs_user@migrate --pass-through-fuse-options -o allow_other,direct_io  /mnt/dbfs

Still it was not working.Then I got the following error.Note that it was not letting me to use allow_other and allow_root options together. 

RMAN>  backup as copy datafile 2 format '/mnt/dbfs/staging_area/file2.bkp';

Starting backup at 27-DEC-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=38 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=+DATA/migrate/datafile/sysaux.259.793193181

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

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

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

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/27/2012 08:26:40

ORA-19502: write error on file "/mnt/dbfs/staging_area/file2.bkp", block number 13568 (block size=8192)

ORA-27072: File I/O error

Additional information: 4

Additional information: 13568

Additional information: 917504

ORA-19502: write error on file "/mnt/dbfs/staging_area/file2.bkp", block number 13568 (block size=8192)

As per the oracle documentation we have following.

• allow_root allows the root user to access the filesystem. This option requires setting the user_allow_other parameter in the /etc/fuse.confconfiguration file.
• allow_other allows other users to access the filesystem. This option requires setting the user_allow_other parameter in the /etc/fuse.confconfiguration file.

I had mounted the FS again with the allow_root option and it worked !!!!!!!!
I used the following option (allow_root) this time.

[oracle@localhost staging_area]$ nohup dbfs_client dbfs_user@migrate -o allow_other,direct_io /mnt/dbfs  < .passwd_dbfs &

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Feb 7 10:10:27 2013

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

connected to target database: MIGRATE (DBID=3570484994)

RMAN> backup as copy datafile 2 format '/mnt/dbfs/staging_area/file2.bkp';

Starting backup at 07-FEB-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/migrate/datafile/sysaux.259.793193181
output file name=/mnt/dbfs/staging_area/file2.bkp tag=TAG20130207T101052 RECID=17 STAMP=806753697
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:04:12
Finished backup at 07-FEB-13

Starting Control File and SPFILE Autobackup at 07-FEB-13
piece handle=/u01/app/oracle/flash_recovery_area/MIGRATE/autobackup/2013_02_07/o1_mf_s_806753705_8k7r8xln_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 07-FEB-13

[oracle@localhost ~]$ cd /mnt/dbfs/*/
[oracle@localhost staging_area]$ ls -lrt
total 552968
-rw-r----- 1 oracle asmadmin 566239232 Feb  7 10:14 file2.bkp

My backup file was listed in the metadata of DBFS filesystem as well.

SQL> select pathname from t_staging_area;

PATHNAME

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

/

/.sfs

/.sfs/RECYCLE

/.sfs/attributes

/.sfs/content

/.sfs/snapshots

/.sfs/tools

/file2.bkp

8 rows selected.

Shared NFS mount point for Real application testing (RAT) on RAC

Hi All,

I was trying to test the DB replay process from my 2 node RAC (11gR2) test machine set up on Oracle virtualbox environment . I faced the issue that I don't have a shared mount point to store the capture files.Following points are to be noted.

1) You can't use ASM for storing the DB capture files.ASM was the only shared directory I had at that point.

2) If you run capture from one instance it will capture the workload of all the instances.

So the shared file system has to be on ext3 file system shared by both the instances.Fo this I did following.

Create the disk and associate it with VirtualBox machines rac1 and rac2.

$ VBoxManage createhd --filename nfs.vdi --size 5120 --format VDI --variant Fixed

# Connect it to the rac1 vm.

$ VBoxManage storageattach rac1 --storagectl "SATA Controller" --port 7 --device 0 --type hdd --medium nfs.vdi --mtype shareable

$ # Make shareable.

$ VBoxManage modifyhd nfs.vdi --type shareable

# Connect it to the rac2 vm.

VBoxManage storageattach rac2 --storagectl "SATA Controller" --port 7 --device 0 --type hdd --medium nfs.vdi --mtype shareable

You will see the new disk as /dev/sd* on rac1>i had formatted it wih ext3 format on rac1

1)Format the /dev/sdg using the ext3 format on rac1 .So you will get the /dev/sdg1

2)service nfs start on both the nodes

3)Create a directory to mount the newly created disk.For me it is /nfs on both the nodes.Add following line to the /etc/fstab on node 1

/dev/sdg1               /nfs                    ext3    defaults        1 1

 for the first time mount -t nfs  /dev/sdg1               /nfs 

4)Now you need to mount the disk from node 2 as well .for that  do the following.

mount 192.168.56.101:/nfs /nfs -o rsize=8192,wsize=8192

where 192.168.56.101 is the ip address of rac1 machine accessible from rac2.

Now if you create a directory with ownership oracle:oinstall in /nfs it should be visible from bot the nodes

[root@rac2 RAT]# cd /nfs

[root@rac2 nfs]# ls -lrt

total 20

drwx------ 2 root   root     16384 Nov 16 23:00 lost+found

-rw-r--r-- 1 root   root         0 Nov 17 03:19 1

drwxr-xr-x 4 oracle oinstall  4096 Nov 19 06:54 RAT

Now you can create a database directory on the shared nfs filesystem and  you are good to run the capture process .

DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'capture01',dir => 'RAT')

Index issue core dumping

I had seen the following issue in the trace files while it was core dumping every few secs.The offending statement was a delete statement with where clause.On examining the trace files it looked like an issue of key mismatch.Note that this is key mismatch issue not a corrupt index issue so all the tools used for finding the corrupt blocks will not return anything.eg-database Validate,dbverify and analyze on index will come clean since the logical structure of the index is valid.To identify the key mismatch issue proceed as follwing.

From the trace files see following error

oer 8102.2 - obj# 155099, rdba: 0x53037ad2(afn 332, blk# 363218)
kdk key 8102.2:
  ncol: 2, len: 14
  key: (14):  XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
  mask: (4096)

Clearly the delete staement was not able to identify a key which was there in the table but not in the index.Next find the offending index using the object id reported in the trace file


--> select name from obj$ where obj# = 155099 

To identify the keys that are missing from the index we ran the following queries.

Following query will give the keys which are there in the table but not in the index.

SELECT /*+ FULL(t1) */ rowid,ORDER_ID
FROM scott.Task_activity t1
MINUS
SELECT /*+ index(t XIE4TASK_ACTIVITY$C) */ rowid,ORDER_ID
FROM scott.Task_activity t;

Following query will give the keys which are there in the index but not in the table.

SELECT /*+ index(t XIE4TASK_ACTIVITY$C) */ rowid,ORDER_ID
FROM scott.Task_activity t
MINUS
SELECT /*+ FULL(t1) */ rowid,ORDER_ID
FROM scott.Task_activity t1;

The resolution is to drop and recreate the index don't use the online rebuild of the index.

ORA-03113: end-of-file on communication channel

After doing a package install the 2 node RAC database running on 11.1.0.7.0 was brought up .One instance came up fine but the other instance was not coming up and throwing the following error.

ORA-03113: end-of-file on communication channel
Process ID: 20194
Session ID: 1655 Serial number: 5

WARNING: The 'LOG_ARCHIVE_CONFIG' init.ora parameter settings
are inconsistent with another started instance. This may be
caused by the 'DB_UNIQUE_NAME' init.ora parameter being specified
differently on one or more of the other RAC instances; the
DB_UNIQUE_NAME parameter value MUST be identical for all
instances of the database.
Errors in file /u02/diag/rdbms/shas/shas2/trace/swprd2_lgwr_5292.trc:



--After looking at the paratemet log_archive_config it was null and also the DB_UNIQUE_NAME was same across 2 nodes.The we checked the view "Select * from v$dataguard_config" and identified that it has non identical entry from the other node.I used following steps to resolve the issue:


you can reset this parameter and bring the instance.

Just mount the instance - problematic one

alter system set log_archive_config='dg_config=(nodg_config)' scope=both sid='*';

Temporary tablespace tempfile moved accidently at OS level

I saw this issue when the tempfile was moved accidently at the operating system level.It was throwing following error in the alert log.

data file 202: '/udb/u02/ORACLE/shas/temp2.dbf'
ORA-27041: unable to open file
ORA-12012: error on auto execute of job 449748
ORA-01116: error in opening database file ORA-01116: error in opening database file 202
ORA-01110: data file 202: '/udb/u02/ORACLE/shas/temp2.dbf'
ORA-27041: unable to open file
ORA-06512:

The resolution is to drop and add the tempfile but when I tried to drop I recieved the  following error.

ORA-25152: TEMPFILE cannot be dropped at this time

I used the following steps to identify the SID using the tempfile .After killing the SID I was able to drop and recreate the tempfile.

1)  SELECT s.sid, s.username, s.status, u.tablespace, u.segfile#, u.contents, u.extents, u.blocks  
FROM v$session s, v$sort_usage u  
WHERE s.saddr=u.session_addr  
ORDER BY u.tablespace, u.segfile#, u.segblk#, u.blocks;  

2)  select sid, username, osuser, program, machine from v$session where sid in   
();  

Local file system to ASM migration

Incrementally updating rman backups

Incrementally updating backups is an important feature of Oracle database available from oracle 10g onwards.I am going to test some of the scenarios related to this backup strategy.We need to have enough space on the disk to keep the backups for this strategy.

• First day of the backup will be similar to L0 backup.We run 2 commands daily.First command will take the rman incremental backup and second will rollforward our existing backup with the backup we have just taken.

1. BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE;
2. RECOVER COPY OF DATABASE WITH TAG WEEKLY;

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE;

Starting backup at 03-SEP-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/test/system01.dbf
output file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_system_84bmsbx4_.dbf tag=WEEKLY RECID=1 STAMP=793046771
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/test/sysaux01.dbf
output file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_sysaux_84bmts04_.dbf tag=WEEKLY RECID=2 STAMP=793046805
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/test/undotbs01.dbf
output file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_84bmvw32_.dbf tag=WEEKLY RECID=3 STAMP=793046815
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 03-SEP-12
channel ORA_DISK_1: finished piece 1 at 03-SEP-12
piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_ncsn1_WEEKLY_84bmw4gy_.bkp tag=WEEKLY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/test/users01.dbf
output file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_users_84bmw5hk_.dbf tag=WEEKLY RECID=4 STAMP=793046822
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 03-SEP-12

RMAN> RECOVER COPY OF DATABASE WITH TAG WEEKLY;

Starting recover at 03-SEP-12

using channel ORA_DISK_1

no copy of datafile 1 found to recover

no copy of datafile 2 found to recover

no copy of datafile 3 found to recover

no copy of datafile 4 found to recover

Finished recover at 03-SEP-12

RMAN> list backup;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

1       Incr 1  9.36M      DISK        00:00:02     03-SEP-12

        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: WEEKLY

        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_ncsn1_WEEKLY_84bmw4gy_.bkp

  SPFILE Included: Modification time: 03-SEP-12

  SPFILE db_unique_name: TEST

  Control File Included: Ckp SCN: 954398       Ckp time: 03-SEP-12

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

/

System altered.

SQL> /

System altered.

SQL> /

System altered.

• On the second day it will rollforward the changes to existing database backup

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE;

Starting backup at 03-SEP-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/test/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/test/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/test/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/test/users01.dbf

channel ORA_DISK_1: starting piece 1 at 03-SEP-12

channel ORA_DISK_1: finished piece 1 at 03-SEP-12

piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnfhol_.bkp tag=WEEKLY comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 03-SEP-12

channel ORA_DISK_1: finished piece 1 at 03-SEP-12

piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_ncsn1_WEEKLY_84bngxyo_.bkp tag=WEEKLY comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 03-SEP-12

RMAN> RECOVER COPY OF DATABASE WITH TAG WEEKLY;

Starting recover at 03-SEP-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile copies to recover

recovering datafile copy file number=00001 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_system_84bmsbx4_.dbf

recovering datafile copy file number=00002 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_sysaux_84bmts04_.dbf

recovering datafile copy file number=00003 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_84bmvw32_.dbf

recovering datafile copy file number=00004 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_users_84bmw5hk_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnfhol_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnfhol_.bkp tag=WEEKLY

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 03-SEP-12

• If we add a tablespace to the database ,next rman backup will acknowledge the change and it will take the backup of new datafile.

SQL> create tablespace test1 datafile '/u01/app/oracle/oradata/test/test1.dbf' size 50m;

Tablespace created.

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE;

Starting backup at 03-SEP-12

using channel ORA_DISK_1

no parent backup or copy of datafile 5 found

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/test/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/test/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/test/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/test/users01.dbf

channel ORA_DISK_1: starting piece 1 at 03-SEP-12

channel ORA_DISK_1: finished piece 1 at 03-SEP-12

piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnlr6s_.bkp tag=WEEKLY comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/test/test1.dbf

output file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_test1_84bnmk9h_.dbf tag=WEEKLY RECID=9 STAMP=793047571

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 03-SEP-12

channel ORA_DISK_1: finished piece 1 at 03-SEP-12

piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_ncsn1_WEEKLY_84bnmoh4_.bkp tag=WEEKLY comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 03-SEP-12

RMAN>

RECOVER COPY OF DATABASE WITH TAG WEEKLY;

RMAN>

Starting recover at 03-SEP-12

using channel ORA_DISK_1

no copy of datafile 5 found to recover

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile copies to recover

recovering datafile copy file number=00001 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_system_84bmsbx4_.dbf

recovering datafile copy file number=00002 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_sysaux_84bmts04_.dbf

recovering datafile copy file number=00003 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_84bmvw32_.dbf

recovering datafile copy file number=00004 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_users_84bmw5hk_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnlr6s_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnlr6s_.bkp tag=WEEKLY

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 03-SEP-12

• Lets check the recovery.I am going to delete the users01 datafile belonging to the users tablespace.

SQL> !rm -rf /u01/app/oracle/oradata/test/users01.dbf

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  622149632 bytes

Fixed Size                  2215904 bytes

Variable Size             377487392 bytes

Database Buffers          239075328 bytes

Redo Buffers                3371008 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/u01/app/oracle/oradata/test/users01.dbf'

• To recover I have to restore the backup image copies and apply the archive log changes.

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Sep 3 19:03:31 2012

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

connected to target database: TEST (DBID=2091192679, not open)

RMAN> restore database;

Starting restore at 03-SEP-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring datafile 00001

input datafile copy RECID=13 STAMP=793047598 file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_system_84bmsbx4_.dbf

destination for restore of datafile 00001: /u01/app/oracle/oradata/test/system01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00001

output file name=/u01/app/oracle/oradata/test/system01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00002

input datafile copy RECID=12 STAMP=793047598 file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_sysaux_84bmts04_.dbf

destination for restore of datafile 00002: /u01/app/oracle/oradata/test/sysaux01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00002

output file name=/u01/app/oracle/oradata/test/sysaux01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00003

input datafile copy RECID=11 STAMP=793047598 file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_84bmvw32_.dbf

destination for restore of datafile 00003: /u01/app/oracle/oradata/test/undotbs01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00003

output file name=/u01/app/oracle/oradata/test/undotbs01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00004

input datafile copy RECID=10 STAMP=793047598 file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_users_84bmw5hk_.dbf

destination for restore of datafile 00004: /u01/app/oracle/oradata/test/users01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00004

output file name=/u01/app/oracle/oradata/test/users01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00005

input datafile copy RECID=9 STAMP=793047571 file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_test1_84bnmk9h_.dbf

destination for restore of datafile 00005: /u01/app/oracle/oradata/test/test1.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00005

output file name=/u01/app/oracle/oradata/test/test1.dbf RECID=0 STAMP=0

Finished restore at 03-SEP-12

RMAN> recover database;

Starting recover at 03-SEP-12

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_7_84bnpcd5_.arc

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_8_84bnpdcg_.arc

archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_9_84bnphy5_.arc

archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_10_84bnpjwf_.arc

archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_11_84bnpkpo_.arc

archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_12_84bnpld7_.arc

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_7_84bnpcd5_.arc thread=1 sequence=7

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_8_84bnpdcg_.arc thread=1 sequence=8

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_9_84bnphy5_.arc thread=1 sequence=9

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_10_84bnpjwf_.arc thread=1 sequence=10

media recovery complete, elapsed time: 00:00:01

Finished recover at 03-SEP-12

• As an alternate and fast process we can switch the datafile to the backup copy

SQL> !rm /u01/app/oracle/oradata/test/system01.dbf

SQL> startup

ORACLE instance started.

Total System Global Area  622149632 bytes

Fixed Size                  2215904 bytes

Variable Size             377487392 bytes

Database Buffers          239075328 bytes

Redo Buffers                3371008 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/test/system01.dbf'

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Sep 3 19:10:25 2012

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

connected to target database: TEST (DBID=2091192679, not open)

RMAN> switch database to copy;

using target database control file instead of recovery catalog

datafile 1 switched to datafile copy "/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_system_84bmsbx4_.dbf"

datafile 2 switched to datafile copy "/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_sysaux_84bmts04_.dbf"

datafile 3 switched to datafile copy "/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_84bmvw32_.dbf"

datafile 4 switched to datafile copy "/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_users_84bmw5hk_.dbf"

datafile 5 switched to datafile copy "/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_test1_84bnmk9h_.dbf"

RMAN> recover database;

Starting recover at 03-SEP-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_7_84bnpcd5_.arc

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_8_84bnpdcg_.arc

archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_9_84bnphy5_.arc

archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_10_84bnpjwf_.arc

archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_11_84bnpkpo_.arc

archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_12_84bnpld7_.arc

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_7_84bnpcd5_.arc thread=1 sequence=7

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_8_84bnpdcg_.arc thread=1 sequence=8

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_9_84bnphy5_.arc thread=1 sequence=9

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_10_84bnpjwf_.arc thread=1 sequence=10

media recovery complete, elapsed time: 00:00:01

Finished recover at 03-SEP-12

SQL>  alter database open;

Database altered.

11g Standby creation on the same host with Flashback enabled at both the sites

This topic is not something new but still I have seen some of the issues while creating a standby on the same host.so I have copied all the issues in this post while creating the standby.Following conventions are followed

Primary:orcl
Standby :stby

We are going to keep the DB_name for both the databases same and the db_unique name different.

• To start with lets make an entry of the standby database in the /etc/oratab

[oracle@localhost ~]$ tail -f /etc/oratab
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
orcl:/u01/app/oracle/product/11.1.0/db_1:Y
stby:/u01/app/oracle/product/11.1.0/db_1:Y

• I have found that the database is in no archivelog mode.So lets put it in archivelog mode

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> shutdown immediate;

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

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.

SQL>

SQL> alter database archivelog;

Database altered.

SQL> alter database open ;

Database altered.

SQL> select log_mode from v$database;

LOG_MODE

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

ARCHIVELOG

• Lets add standby redo logfiles to primary.The idea is to add n+1 redo log files where n stands for the primary redo log files.It will be usefull at the time of switcover as well .Take care of the size of the redo log files.

SQL> select GROUP#,TYPE,MEMBER from v$logfile;

    GROUP# TYPE

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

MEMBER

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

         3 ONLINE

/u01/app/oracle/oradata/orcl/redo03.log

         2 ONLINE

/u01/app/oracle/oradata/orcl/redo02.log

         1 ONLINE

/u01/app/oracle/oradata/orcl/redo01.log

SQL> select bytes/1024/1024 from v$log;

BYTES/1024/1024

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

             50

             50

             50

SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stby_redo01' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stby_redo02' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stby_redo03' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stby_redo04' size 50m;

Database altered.

• Lets add the few of the mandatory parameters for the creation of the standby

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,stby)';

System altered.

SQL> Alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=stby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;

System altered.

SQL> alter system set FAL_SERVER=stby;

System altered.

SQL> alter system set FAL_CLIENT=orcl;

System altered.

SQL> alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/stby' scope=spfile;

System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/stby' scope=spfile;

System altered.

SQL>

• Please mind this step.It is very important.If you won't add a static entry to the listener file of the database it will be impossible to connect to our auxiliary database which is stby in our case.

[oracle@localhost admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =(SID_DESC =(GLOBAL_DBNAME = stby.localdomain)(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)(SID_NAME = stby)))

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

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

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@localhost admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl.localdomain)

    )

  )

stby =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = stby.localdomain)

    )

  )

[oracle@localhost admin]$ tnsping stby

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 31-AUG-2012 16:06:22

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

Used parameter files:

/u01/app/oracle/product/11.1.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stby.localdomain)))

OK (10 msec)

• In 11g it is important to copy the password file following the naming convention.Dont try to recreate the password file ,it doesn't work sometimes.In our case we are creating the standby on the same host so we just copy and rename the password file.

[oracle@localhost admin]$ cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ ls -lrt

total 24

-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora

-rw-r----- 1 oracle oinstall   24 Aug 24 16:32 lkORCL

-rw-r--r-- 1 oracle oinstall  916 Aug 25 12:05 initorcl.ora

-rw-r----- 1 oracle oinstall 1536 Aug 29 15:05 orapworcl

-rw-rw---- 1 oracle oinstall 1544 Aug 31 15:28 hc_orcl.dat

-rw-r----- 1 oracle oinstall 3584 Aug 31 15:51 spfileorcl.ora

[oracle@localhost dbs]$ cp orapworcl orapwstby

Database opened.
SQL> SQL> SQL> create pfile from spfile;

File created.

• This step didn't work for me don't know why.I tried to copy the parameter file,renamed and modified few of the parameters .After that I was not able to make the remote connection to the stby database.So I created the pfile again with only 3 parameters (db_name,db_unique_name and db_block_size) and it worked.

[oracle@localhost dbs]$ cp initorcl.ora initstby.ora

[oracle@localhost dbs]$ export ORACLE_SID=stby

[oracle@localhost dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 1 20:29:40 2012

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

Connected to an idle instance.

SQL> startup nomount

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 1 20:29:40 2012

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

Connected to an idle instance.

SQL> startup nomount

ORA-09925: Unable to create audit trail file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 9925

[oracle@localhost dbs]$ cd /u01/app/oracle/admin/

[oracle@localhost admin]$

[oracle@localhost admin]$

[oracle@localhost admin]$

[oracle@localhost admin]$ ls

orcl

[oracle@localhost admin]$ mkdir stby

[oracle@localhost admin]$ cd orcl

[oracle@localhost orcl]$ ls -lrt

total 20

drwxr-x--- 2 oracle oinstall  4096 Aug 24 16:35 pfile

drwxr-x--- 2 oracle oinstall  4096 Aug 26 17:45 dpdump

drwxr-x--- 2 oracle oinstall 12288 Sep  1 20:23 adump

[oracle@localhost orcl]$ cd ../stby

[oracle@localhost stby]$ mkdir pfile dpdump adump

[oracle@localhost dbs]$ sqlplus sys/oracle@stby as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 1 20:34:15 2012

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

ERROR:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

[oracle@localhost dbs]$ tnsping stby

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 01-SEP-2012 20:35:07

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

Used parameter files:

/u01/app/oracle/product/11.1.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stby.localdomain)))

OK (0 msec)

• As a workaround I created the initstby.ora with only 3 parameters and my remote connection to standby worked.

[oracle@localhost dbs]$ cat initstby.ora

db_name='orcl'

db_unique_name='stby'

db_block_size=8192

• This is most easy step now.We just have to start the rman session and start the script

[oracle@localhost dbs]$ rman target sys/oracle@orcl auxiliary sys/oracle@stby

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 1 21:01:25 2012

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

connected to target database: ORCL (DBID=1319955483)

connected to auxiliary database: ORCL (not mounted)

run {

allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate auxiliary channel stby type disk;

duplicate target database for standby from active database

spfile

  parameter_value_convert 'orcl','stby'

  set db_unique_name='stby'

  set db_file_name_convert='/orcl/','/stby/'

  set log_file_name_convert='/orcl/','/stby/'

  set control_files='/u01/app/oracle/oradata/stby/control01.ctl'

  set log_archive_max_processes='5'

  set fal_client='stby'

  set fal_server='orcl'

  set standby_file_management='AUTO'

  set log_archive_config='dg_config=(orcl,stby)'

  set log_archive_dest_1='service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'

;

}

10>   parameter_value_convert 'orcl','stby'

11>   set db_unique_name='stby'

12>   set db_file_name_convert='/orcl/','/stby/'

13>   set log_file_name_convert='/orcl/','/stby/'

14>   set control_files='/u01/app/oracle/oradata/stby/control01.ctl'

15>   set log_archive_max_processes='5'

16>   set fal_client='stby'

  set fal_server='orcl'

17> 18>   set standby_file_management='AUTO'

19>   set log_archive_config='dg_config=(orcl,stby)'

20>   set log_archive_dest_1='service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'

21> ;

22> }

using target database control file instead of recovery catalog

allocated channel: prmy1

channel prmy1: SID=57 device type=DISK

allocated channel: prmy2

channel prmy2: SID=52 device type=DISK

allocated channel: prmy3

channel prmy3: SID=47 device type=DISK

allocated channel: prmy4

channel prmy4: SID=58 device type=DISK

allocated channel: stby

channel stby: SID=19 device type=DISK

Starting Duplicate Db at 01-SEP-12

contents of Memory Script:

{

   backup as copy reuse

   targetfile  '/u01/app/oracle/product/11.1.0/db_1/dbs/orapworcl' auxiliary format

 '/u01/app/oracle/product/11.1.0/db_1/dbs/orapwstby'   targetfile

 '/u01/app/oracle/product/11.1.0/db_1/dbs/spfileorcl.ora' auxiliary format

 '/u01/app/oracle/product/11.1.0/db_1/dbs/spfilestby.ora'   ;

   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.1.0/db_1/dbs/spfilestby.ora''";

}

executing Memory Script

Starting backup at 01-SEP-12

Finished backup at 01-SEP-12

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.1.0/db_1/dbs/spfilestby.ora''

contents of Memory Script:

{

   sql clone "alter system set  audit_file_dest =

 ''/u01/app/oracle/admin/stby/adump'' comment=

 '''' scope=spfile";

   sql clone "alter system set  dispatchers =

 ''(PROTOCOL=TCP) (SERVICE=stbyXDB)'' comment=

 '''' scope=spfile";

   sql clone "alter system set  db_unique_name =

 ''stby'' comment=

 '''' scope=spfile";

   sql clone "alter system set  db_file_name_convert =

 ''/orcl/'', ''/stby/'' comment=

 '''' scope=spfile";

   sql clone "alter system set  log_file_name_convert =

 ''/orcl/'', ''/stby/'' comment=

 '''' scope=spfile";

   sql clone "alter system set  control_files =

 ''/u01/app/oracle/oradata/stby/control01.ctl'' comment=

 '''' scope=spfile";

   sql clone "alter system set  log_archive_max_processes =

 5 comment=

 '''' scope=spfile";

   sql clone "alter system set  fal_client =

 ''stby'' comment=

 '''' scope=spfile";

   sql clone "alter system set  fal_server =

 ''orcl'' comment=

 '''' scope=spfile";

   sql clone "alter system set  standby_file_management =

 ''AUTO'' comment=

 '''' scope=spfile";

   sql clone "alter system set  log_archive_config =

 ''dg_config=(orcl,stby)'' comment=

 '''' scope=spfile";

   sql clone "alter system set  log_archive_dest_1 =

 ''service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'' comment=

 '''' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/stby/adump'' comment= '''' scope=spfile

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=stbyXDB)'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''stby'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/orcl/'', ''/stby/'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/orcl/'', ''/stby/'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''/u01/app/oracle/oradata/stby/control01.ctl'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_max_processes =  5 comment= '''' scope=spfile

sql statement: alter system set  fal_client =  ''stby'' comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''orcl'' comment= '''' scope=spfile

sql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_config =  ''dg_config=(orcl,stby)'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

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

allocated channel: stby

channel stby: SID=18 device type=DISK

contents of Memory Script:

{

   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/stby/control01.ctl';

}

executing Memory Script

Starting backup at 01-SEP-12

channel prmy1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.1.0/db_1/dbs/snapcf_orcl.f tag=TAG20120901T210539 RECID=2 STAMP=792882341

channel prmy1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 01-SEP-12

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/u01/app/oracle/oradata/stby/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/u01/app/oracle/oradata/stby/system01.dbf";

   set newname for datafile  2 to

 "/u01/app/oracle/oradata/stby/sysaux01.dbf";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/stby/undotbs01.dbf";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/stby/users01.dbf";

   set newname for datafile  5 to

 "/u01/app/oracle/oradata/stby/example01.dbf";

   backup as copy reuse

   datafile  1 auxiliary format

 "/u01/app/oracle/oradata/stby/system01.dbf"   datafile

 2 auxiliary format

 "/u01/app/oracle/oradata/stby/sysaux01.dbf"   datafile

 3 auxiliary format

 "/u01/app/oracle/oradata/stby/undotbs01.dbf"   datafile

 4 auxiliary format

 "/u01/app/oracle/oradata/stby/users01.dbf"   datafile

 5 auxiliary format

 "/u01/app/oracle/oradata/stby/example01.dbf"   ;

   sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/stby/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 01-SEP-12

channel prmy1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf

channel prmy2: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

channel prmy3: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

channel prmy4: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf

output file name=/u01/app/oracle/oradata/stby/example01.dbf tag=TAG20120901T210550

channel prmy4: datafile copy complete, elapsed time: 00:00:57

channel prmy4: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf

output file name=/u01/app/oracle/oradata/stby/undotbs01.dbf tag=TAG20120901T210550

channel prmy3: datafile copy complete, elapsed time: 00:01:18

output file name=/u01/app/oracle/oradata/stby/users01.dbf tag=TAG20120901T210550

channel prmy4: datafile copy complete, elapsed time: 00:00:35

output file name=/u01/app/oracle/oradata/stby/sysaux01.dbf tag=TAG20120901T210550

channel prmy2: datafile copy complete, elapsed time: 00:02:38

output file name=/u01/app/oracle/oradata/stby/system01.dbf tag=TAG20120901T210550

channel prmy1: datafile copy complete, elapsed time: 00:02:49

Finished backup at 01-SEP-12

sql statement: alter system archive log current

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=2 STAMP=792882523 file name=/u01/app/oracle/oradata/stby/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=3 STAMP=792882524 file name=/u01/app/oracle/oradata/stby/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=4 STAMP=792882524 file name=/u01/app/oracle/oradata/stby/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=5 STAMP=792882524 file name=/u01/app/oracle/oradata/stby/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=6 STAMP=792882524 file name=/u01/app/oracle/oradata/stby/example01.dbf

Finished Duplicate Db at 01-SEP-12

released channel: prmy1

released channel: prmy2

released channel: prmy3

released channel: prmy4

released channel: stby

RMAN>

• So the standby creation has been successful .Let's see if recovery is working

SQL> select recovery_mode from v$archive_dest_status;

RECOVERY_MODE
-----------------------
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE

On the standby database-

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

On primary database switch logfile

Log of the standby database 


Sat Sep 01 21:16:00 2012
Media Recovery Waiting for thread 1 sequence 45 (in transit)
Sat Sep 01 21:16:00 2012
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance stby - Archival Error
ORA-16014: log 5 sequence# 43 not archived, no available destinations
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/stby/stby_redo02'
Errors in file /u01/app/oracle/diag/rdbms/stby/stby/trace/stby_arc4_9364.trc:
ORA-16014: log 5 sequence# 43 not archived, no available destinations
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/stby/stby_redo02'
Sat Sep 01 21:16:00 2012
RFS[4]: Selected log 6 for thread 1 sequence 45 dbid 1319955483 branch 792174751
Recovery of Online Redo Log: Thread 1 Group 6 Seq 45 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/stby/stby_redo03
Sat Sep 01 21:16:04 2012
Archiver process freed from errors. No longer stopped
Media Recovery Waiting for thread 1 sequence 46
RFS[4]: Selected log 7 for thread 1 sequence 46 dbid 1319955483 branch 792174751
Recovery of Online Redo Log: Thread 1 Group 7 Seq 46 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/stby/stby_redo04

• See the error above .It is because I made a mistake in setting the archive log location So I set it again.

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stby';

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     45
Next log sequence to archive   0
Current log sequence           47
SQL> alter system set log_archive_dest_2='service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'
  2  ;

System altered.

alert logfile of standby database


RFS[7]: Assigned to RFS process 11388
RFS[7]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 3425
Primary database is in MAXIMUM PERFORMANCE mode
RFS[7]: No standby redo logfiles of size 102400 blocks available
RFS[7]: Opened log for thread 1 sequence 48 dbid 1319955483 branch 792174751
Archived Log entry 4 added for thread 1 sequence 48 rlc 792174751 ID 0x4ead2e1b dest 2:
RFS[7]: No standby redo logfiles of size 102400 blocks available
RFS[7]: Opened log for thread 1 sequence 49 dbid 1319955483 branch 792174751
Sat Sep 01 21:24:04 2012
Media Recovery Log /u01/app/oracle/flash_recovery_area/STBY/archivelog/2012_09_01/o1_mf_1_48_845nbm58_.arc
Media Recovery Waiting for thread 1 sequence 49 (in transit)

Now it's using the correct logfile location

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     52

Next log sequence to archive   54

Current log sequence           54

SQL> select thread#,max(sequence#),applied from v$archived_log group by thread#,applied;

   THREAD# MAX(SEQUENCE#) APPLIED

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

         1             52 YES

         1             53 IN-MEMORY

• For rest of the steps I am going to follow  support Doc ID 316392.1 to configure the dataguard broker and test the switchover scenario

On primary database 

SQL> alter system set dg_broker_start=false ;

System altered.

SQL> alter system set DG_BROKER_CONFIG_FILE1='/u01/app/oracle/oradata/orcl/dr1_orcl.dat';

System altered.

SQL> alter system set DG_BROKER_CONFIG_FILE2='/u01/app/oracle/oradata/orcl/dr2_orcl.dat';

System altered.

SQL> alter system set dg_broker_start=true ;

System altered.

SQL> exit

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

With the Partitioning, OLAP and Data Mining options

[oracle@localhost ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle@orcl

Connected.

DGMGRL> create configuration orcl_config as primary database is orcl connect identifier is orcl;

Configuration "orcl_config" created with primary database "orcl"

On Standby database 

SQL> alter system set dg_broker_start=false ;

System altered.

SQL> alter system set DG_BROKER_CONFIG_FILE1='/u01/app/oracle/oradata/orcl/dr1_stby.dat';

System altered.

SQL> alter system set DG_BROKER_CONFIG_FILE2='/u01/app/oracle/oradata/orcl/dr2_stby.dat'

  2  ;

System altered.

SQL> alter system set dg_broker_start=true ;

System altered.

[oracle@localhost ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle@orcl

Connected.

DGMGRL> add database stby  as connect identifier is stby maintained as physical;

Database "stby" added

DGMGRL> show configuration

Configuration - orcl_config

  Protection Mode: MaxPerformance

  Databases:

    orcl - Primary database

    stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration

Configuration - orcl_config

  Protection Mode: MaxPerformance

  Databases:

    orcl - Primary database

    stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> show database stby

Database - stby

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       0 seconds

  Real Time Query: OFF

  Instance(s):

    stby

Database Status:

SUCCESS

• Lets perform the switchover to the standby database stby using DGMGRL

[oracle@localhost ~]$ dgmgrl /

DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL> connect sys/oracle@orcl

Connected.

DGMGRL> switchover to stby;

Performing switchover NOW, please wait...

New primary database "stby" is opening...

Operation requires shutdown of instance "orcl" on database "orcl"

Shutting down instance "orcl"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "orcl" on database "orcl"

Starting instance "orcl"...

Unable to connect to database

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.

Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:

        start up and mount instance "orcl" of database "orcl"

• After this we have to start the standby database in mount state and start the recovery

[oracle@localhost ~]$ export ORACLE_SID=stby

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 3 14:14:59 2012

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

Connected to:

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

With the Partitioning, OLAP and Data Mining options

SQL> select database_role from v$database;

DATABASE_ROLE

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

PRIMARY

[oracle@localhost ~]$ export ORACLE_SID=orcl

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 3 14:15:38 2012

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

Connected to an idle instance.

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.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL>  alter database recover managed standby database using current logfile disconnect;

Database altered.

• To confirm if the standby is applying the redo logs lets make some logfile switches on the new primary(stby).

[oracle@localhost ~]$ export ORACLE_SID=stby

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 3 14:19:03 2012

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

Connected to:

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

With the Partitioning, OLAP and Data Mining options

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

Contents of alert logfile of new standby (orcl)

Thread 1 advanced to log sequence 73 (LGWR switch)

  Current log# 1 seq# 73 mem# 0: /u01/app/oracle/oradata/stby/redo01.log

Mon Sep 03 14:19:17 2012

Archived Log entry 37 added for thread 1 sequence 72 ID 0x4eb99875 dest 1:

Mon Sep 03 14:19:17 2012

LNS: Standby redo logfile selected for thread 1 sequence 73 for destination LOG_ARCHIVE_DEST_2

Thread 1 advanced to log sequence 74 (LGWR switch)

  Current log# 2 seq# 74 mem# 0: /u01/app/oracle/oradata/stby/redo02.log

Mon Sep 03 14:19:18 2012

Archived Log entry 39 added for thread 1 sequence 73 ID 0x4eb99875 dest 1:

LNS: Standby redo logfile selected for thread 1 sequence 74 for destination LOG_ARCHIVE_DEST_2

Mon Sep 03 14:19:25 2012

Thread 1 cannot allocate new log, sequence 75

Checkpoint not complete

  Current log# 2 seq# 74 mem# 0: /u01/app/oracle/oradata/stby/redo02.log

Thread 1 advanced to log sequence 75 (LGWR switch)

  Current log# 3 seq# 75 mem# 0: /u01/app/oracle/oradata/stby/redo03.log

Mon Sep 03 14:19:26 2012

Archived Log entry 42 added for thread 1 sequence 74 ID 0x4eb99875 dest 1:

LNS: Standby redo logfile selected for thread 1 sequence 75 for destination LOG_ARCHIVE_DEST_2

• Now switchover testing is complete so we can do switchover again to make the orcl primary and stby as standby.

[oracle@localhost trace]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle@stby

Connected.

DGMGRL> show configuration

Configuration - orcl_config

  Protection Mode: MaxPerformance

  Databases:

    stby - Primary database

    orcl - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> switchover to orcl

Performing switchover NOW, please wait...

New primary database "orcl" is opening...

Operation requires shutdown of instance "stby" on database "stby"

Shutting down instance "stby"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "stby" on database "stby"

Starting instance "stby"...

Unable to connect to database

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.

Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:

        start up and mount instance "stby" of database "stby"

DGMGRL>

[oracle@localhost trace]$ export ORACLE_SID=orcl

[oracle@localhost trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 3 14:22:33 2012

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

Connected to:

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

With the Partitioning, OLAP and Data Mining options

SQL> select database_role from v$database;

DATABASE_ROLE

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

PRIMARY

I am not going to perform failover deliberaltely because I'll have to rebuild the primary after a failover .

To get over the ORA-12514 error static entry should be added for db_unique_name_DGMGRL.db_domain

http://docs.oracle.com/cd/B19306_01/server.102/b14230/install.htm#BABECEJC