RMAN-06023: no backup or copy of datafile 1 found to restore

RMAN> restore database;

RMAN-06023: no backup or copy of datafile 1 found to restore

RMAN> catalog recovery area;

searching for all files in the recovery area

List of Files Unknown to the Database

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

File Name: /ora/oracle/base/flash_recovery_area/AFISTND/backupset/2014_02_19/o1_mf_nnndf_TAG20140219T133441_9j9yb1sq_.bkp

File Name: /ora/oracle/base/flash_recovery_area/AFISTND/backupset/2014_02_19/o1_mf_annnn_TAG20140219T133522_9j9ycbjd_.bkp

File Name: /ora/oracle/base/flash_recovery_area/AFISTND/backupset/2014_02_19/o1_mf_ncsnf_TAG20140219T133441_9j9yc92d_.bkp

File Name: /ora/oracle/base/flash_recovery_area/AFISTND/backupset/2014_02_19/o1_mf_annnn_TAG20140219T133433_9j9y9t69_.bkp

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

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /ora/oracle/base/flash_recovery_area/AFISTND/backupset/2014_02_19/o1_mf_nnndf_TAG20140219T133441_9j9yb1sq_.bkp

File Name: /ora/oracle/base/flash_recovery_area/AFISTND/backupset/2014_02_19/o1_mf_annnn_TAG20140219T133522_9j9ycbjd_.bkp

File Name: /ora/oracle/base/flash_recovery_area/AFISTND/backupset/2014_02_19/o1_mf_ncsnf_TAG20140219T133441_9j9yc92d_.bkp

File Name: /ora/oracle/base/flash_recovery_area/AFISTND/backupset/2014_02_19/o1_mf_annnn_TAG20140219T133433_9j9y9t69_.bkp

RMAN> list backup;

List of Backup Sets

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

5       Full    963.23M    DISK        00:00:00     20-FEB-12    

        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20140219T133441

        Piece Name: /ora/oracle/base/flash_recovery_area/AFISTND/backupset/2014_02_19/o1_mf_nnndf_TAG20140219T133441_9j9yb1sq_.bkp

  List of Datafiles in backup set 5

  File LV Type Ckp SCN    Ckp Time  Name

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

  1       Full 1098780    19-FEB-14 /ora/oracle/base/oradata/afiprim/system01.dbf

  2       Full 1098780    19-FEB-14 /ora/oracle/base/oradata/afiprim/sysaux01.dbf

  3       Full 1098780    19-FEB-14 /ora/oracle/base/oradata/afiprim/undotbs01.dbf

  4       Full 1098780    19-FEB-14 /ora/oracle/base/oradata/afiprim/users01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time

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

6       32.00K     DISK        00:00:00     20-FEB-12  

        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20140219T133522

        Piece Name: /ora/oracle/base/flash_recovery_area/AFISTND/backupset/2014_02_19/o1_mf_annnn_TAG20140219T133522_9j9ycbjd_.bkp

  List of Archived Logs in backup set 6

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    11      1098761    20-FEB-12 1098832    20-FEB-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

7       Full    9.36M      DISK        00:00:00   20-FEB-12      

        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20140219T133441

        Piece Name: /ora/oracle/base/flash_recovery_area/AFISTND/backupset/2014_02_19/o1_mf_ncsnf_TAG20140219T133441_9j9yc92d_.bkp

  SPFILE Included: Modification time: 20-FEB-12

  SPFILE db_unique_name: AFIPRIM

  Control File Included: Ckp SCN: 1098820      Ckp time:20-FEB-12

BS Key  Size       Device Type Elapsed Time Completion Time

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

8       215.23M    DISK        00:00:00     20-FEB-12      

        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20140219T133433

        Piece Name: /ora/oracle/base/flash_recovery_area/AFISTND/backupset/2014_02_19/o1_mf_annnn_TAG20140219T133433_9j9y9t69_.bkp

  List of Archived Logs in backup set 8

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    2       949180     20-FEB-12 959894     20-FEB-12

  1    3       959894     20-FEB-12 970908     20-FEB-12

  1    4       970908     20-FEB-12 970945     20-FEB-12

  1    5       970945     20-FEB-12 973569     20-FEB-12

  1    6       973569     20-FEB-12 973650     20-FEB-12

  1    7       973650     20-FEB-12 998626     20-FEB-12

  1    8       998626    20-FEB-12 1038882    20-FEB-12

  1    9       1038882    20-FEB-12 1079885    20-FEB-12

  1    10      1079885    20-FEB-12 1098761    20-FEB-12

Oracle 11g Configure Data Guard

Step by Step, document for creating Physical Standby Database, 11g DATA GUARD

Creating a Data Guard Physical Standby environment, General Review.

Manually setting up a Physical standby database is a simple task when all prerequisites and setup
steps are carefully met and executed.

In this example I did use 2 Virtual Machine hosts, that host a RAC1 database. All RAC2 preinstall requisites are then in place and no additional configuration was necessary to implement Data Guard Physical Standby manually.

The Enviroment
2 Linux servers, Oracle Linux 6
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
ssh is configured for user oracle on both nodes
Oracle Home is on identical path on both nodes
Implementation notes:

Once you have your primary database up and running these are the steps to follow:
1. Enable Forced Logging
2. Create a Password File
3. Configure a Standby Redo Log
4. Enable Archiving
5. Set Primary Database Initialization Parameters

Having followed these steps to implement the Physical Standby you need to follow these steps:

1. Create a Control File for the Standby Database
2. Backup the Primary Database and transfer a copy to the Standby node.
3. Prepare an Initialization Parameter File for the Standby Database
4. Configure the listener and tnsnames to support the database on both nodes
5. Set Up the Environment to Support the Standby Database on the standby node.
6. Start the Physical Standby Database
7. Verify the Physical Standby Database Is Performing Properly
Step by Step Implementation of a Physical Standby Environment
Primary Database Steps
Primary Database General View

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Current log sequence 1
SQL> select name from v$database;
NAME
---------
WHITE
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/ora/oracle/ora11g/base/oradata/white/system01.dbf
/ora/oracle/ora11g/base/oradata/white/undotbs01.dbf
/ora/oracle/ora11g/base/oradata/white/sysaux01.dbf
/ora/oracle/ora11g/base/oradatawhite/users01.dbf

SQL> show parameters unique

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string white


SQL>
Enable Forced Logging
In order to implement Standby Database we enable 'Forced Logging'.
This option ensures that even in the event that a 'nologging' operation is done, force logging
takes precedence and all operations are logged
into the redo logs.



SQL> ALTER DATABASE FORCE LOGGING;
Database altered.


Create a Password File
A password file must be created on the Primary and copied over to the Standby site. The sys
password must be identical on both sites. This is
a key pre requisite in order to be able to ship and apply archived logs from Primary to Standby.


[oracle@rac2 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac2 dbs]$ orapwd file=orapwwhite password=oracle force=y
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE



Configure a Standby Redo Log
A Standby Redo log is added to enable Data Guard Maximum Availability and Maximum
Protection modes. It is important to configure the
Standby Redo Logs (SRL) with the same size as the online redo logs.
In this example I'm using Oracle Managed Files, that's why I don't need to provide the SRL
path and file name. If you are not using OMF's
you then must pass the full qualified name.
SQL> select group#,type,member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
3 /ora/oracle/ora11g/base/oradata/white/redo03.log
2 /ora/oracle/ora11g/base/oradata/white/redo02.log
1 /ora/oracle/ora11g/base/oradata/whitee/redo01.log
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
2 '/ora/oracle/ora11g/base/oradata/white/stby04.log' size 50m;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
2 '/ora/oracle/ora11g/base/oradata/white/stby05.log' size 50m;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
2 /ora/oracle/ora11g/base/oradata/white/stby06.log' size 50m;
Database altered.
SQL> SELECT GROUP#,TYPE,MEMBER FROM V$LOGFILE;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
3 ONLINE Configure a Standby Redo Log
A Standby Redo log is added to enable Data Guard Maximum Availability and Maximum
Protection modes. It is important to configure the
Standby Redo Logs (SRL) with the same size as the online redo logs.
In this example I'm using Oracle Managed Files, that's why I don't need to provide the SRL
path and file name. If you are not using OMF's
you then must pass the full qualified name.
SQL> select group#,type,member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
3 ONLINE/ora/oracle/ora11g/base/oradata/white/redo03.log
2 ONLINE/ora/oracle/ora11g/base/oradata/white/redo02.log
1 ONLINE/ora/oracle/ora11g/base/oradata/white/redo01.log
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
2 '/u01/app/oracle/oradata/white/stby04.log' size 50m;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
2 '/u01/app/oracle/oradata/white/stby05.log' size 50m;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
2 '/u01/app/oracle/oradata/white/stby06.log' size 50m;
Database altered.
SQL> SELECT GROUP#,TYPE,MEMBER FROM V$LOGFILE;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
Configure a Standby Redo Log
A Standby Redo log is added to enable Data Guard Maximum Availability and Maximum
Protection modes. It is important to configure the
Standby Redo Logs (SRL) with the same size as the online redo logs.
In this example I'm using Oracle Managed Files, that's why I don't need to provide the SRL
path and file name. If you are not using OMF's
you then must pass the full qualified name.
SQL> select group#,type,member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
3 ONLINE/ora/oracle/ora11g/base/oradata/white/redo03.log
2 ONLINE/ora/oracle/ora11g/base/oradata/white/redo02.log
1 ONLINE /ora/oracle/ora11g/base/oradata/white/redo01.log
SQL> select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
2 '/u01/app/oracle/oradata/white/stby04.log' size 50m;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
2 '/u01/app/oracle/oradata/white/stby05.log' size 50m;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
2 '/u01/app/oracle/oradata/white/stby06.log' size 50m;
Database altered.

SQL> SELECT GROUP#,TYPE,MEMBER FROM V$LOGFILE;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
3 ONLINE     /ora/oracle/ora11g/base/oradata/white/redo03.log
2 ONLINE     /ora/oracle/ora11g/base/oradata/white/redo02.log
1 ONLINE     /ora/oracle/ora11g/base/oradata/white/redo01.log
4 STANDBY /ora/oracle/ora11g/base/oradata/white/stby04.log
5 STANDBY /ora/oracle/ora11g/base/oradata/white/stby05.log
6 STANDBY /ora/oracle/ora11g/base/oradata/whitestby06.log

6 rows selected.



Set Primary Database Initialization Parameters

Data Guard must use spfile, in order to configure it we create and configure the standby
parameters on a regular pfile, and once it is ready we convert it to an spfile.
Several init.ora parameters control the behavior of a Data Guard environment. In this example
the Primary database init.ora is configured so
that it can hold both roles, as Primary or Standby.

SQL> CREATE PFILE FROM SPFILE;
File created.

(or)

SQL> CREATE PFILE='/tmp/initwhite.ora' from spfile;
Created.


Edit the pfile to add the standby parameters, here shown highlighted:

white.__db_cache_size=331350016
white.__java_pool_size=4194304
white.__large_pool_size=4194304
white.__oracle_base='/ora/oracle/ora11g/base'#ORACLE_BASE set from environment
white.__pga_aggregate_target=301989888
white.__sga_target=536870912
white.__shared_io_pool_size=0
white.__shared_pool_size=184549376
white.__streams_pool_size=4194304
*.audit_file_dest='/ora/oracle/ora11g/base/admin/white/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/ora/oracle/ora11g/base/oradata/white/control01.ctl','/ora/oracle/ora11g/base/flash_recovery_area/white/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='white'
*.db_unique_name='white'
*.db_recovery_file_dest='/ora/oracle/ora11g/base/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/ora/oracle/ora11g/base'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=whiteXDB)'
*.local_listener='LISTENER_WHITE'
*.log_archive_config='DG_CONFIG=(white,black)'
*.log_archive_dest_1='LOCATION=/ora/oracle/ora11g/base/flash_recovery_area/white/onlinelog1/ valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=white'
*.log_archive_dest_2='service=black LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=black'
*.fal_server=black
*.fal_client=white
*.db_file_name_convert='/ora/oracle/ora11g/base/oradata/black/','/ora/oracle/ora11g/base/oradata/white/'
*.log_file_name_convert='/ora/oracle/ora11g/base/oradata/black/','/ora/oracle/ora11g/base/oradata/white/'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_max_processes=30
*.standby_file_management=auto
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=301989888
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912
*.undo_tablespace='UNDOTBS1'

Once the new parameter file is ready we create from it the spfile:

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount pfile=/ora/oracle/orace11g/dbs/initwhite.ora

ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated

ORA-07286: sksagdi: cannot obtain device information.

Linux Error: 2: No such file or directory

note: create a archive log destination(location) folder as per in parameter file and then startup

the database.

SQL> startup nomount pfile=/ora/oracle/orace11g/dbs/initwhite.ora

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 96470608 bytes

Database Buffers 184549376 bytes

Redo Buffers 2973696 bytes

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
Enable Archiving
On 10g you can enable archive log mode by mounting the database and executing the
archivelog command:
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ora/oracle/ora11g/base/flash_back_recovery/white/archive1
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2


SQL>
Standby Database Steps
Here, i am going to create standby database using backup of the primary database
datafiles,redologs, controlfile by rman. compare with user managed backup, rman is
comfortable and flexible method.

Create an RMAN backup which we will use later to create the standby:
[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [oracle] ? white
[oracle@rac2 ~]$ rman target=/



Recovery Manager: Release 11.2.0.2.0 - Production on Wed Jan 04 18:41:51 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: WHITE (DBID=3603807872)

RMAN> backup full database format '/u01/app/oracle/backup/%d_%U.bckp' plus archivelog
format '/u01/app/oracle/backup/%d_%U.bckp';
Next, create a standby controlfile backup via RMAN:

RMAN> configure channel device type disk format '/u01/app/oracle/backup/%U';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/backup/%U';
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;

RMAN> BACKUP ARCHIVELOG ALL;
In this simple example, I am backing up the primary database to disk; therefore, I must make the
backupsets available to the standby host if I want to use them as the basis for my duplicate
operation:
[oracle@rac2 ~]$ cd /u01/app/oracle/backup

[oracle@rac2 backup]$ ls -lart
total 636080
drwxrwxr-x 9 oracle oinstall 4096 Jan 20 18:42 ..
-rw-r----- 1 oracle oinstall 50418176 Jan 20 18:43 WHITE_01l3v1uv_1_1.bckp
-rw-r----- 1 oracle oinstall 531472384 Jan 20 18:54 WHITE_02l3v203_1_1.bckp
-rw-r----- 1 oracle oinstall 7143424 Jan 20 18:54 WHITE_03l3v2jf_1_1.bckp
-rw-r----- 1 oracle oinstall 1346560 Jan 20 18:54 WHITE_04l3v2jv_1_1.bckp
-rw-r----- 1 oracle oinstall 7110656 Jan 20 19:19 05l3v41r_1_1
drwxr-xr-x 2 oracle oinstall 4096 Jan 20 19:20 .
-rw-r----- 1 oracle oinstall 53174272 Jan 20 19:21 06l3v448_1_1
[oracle@rac2 backup]$ scp * oracle@rac1:/u01/app/oracle/backup/
05l3v41r_1_1 100% 6944KB 6.8MB/s 00:00
06l3v448_1_1 100% 51MB 16.9MB/s 00:03
WHITE_01l3v1uv_1_1.bckp 100% 48MB 2.7MB/s 00:18
WHITE_02l3v203_1_1.bckp 100% 507MB 1.5MB/s 05:47
WHITE_03l3v2jf_1_1.bckp 100% 6976KB 996.6KB/s 00:07
WHITE_04l3v2jv_1_1.bckp 100% 1315KB 1.3MB/s 00:01
NOTE:
The primary and standby database location for backup folder must be same.
for eg: /u01/app/oracle/backup folder

On the standby node create the required directories to get the datafiles
mkdir -p /ora/oracle/ora11g/base/oradata/black
mkdir -p /ora/oracle/ora11g/base/oradata/arch
mkdir -p  /ora/oracle/ora11g/base/oradata/black
mkdir -p  /ora/oracle/ora11g/base/oradata/black/adump
mkdir -p  /ora/oracle/ora11g/base/oradata/black/bdump
mkdir -p  /ora/oracle/ora11g/base/oradata/black/udump
mkdir -p  /ora/oracle/ora11g/base/flash_recovery_area/WHITE
mkdir -p /ora/oracle/ora11g/base/flash_recovery_area/WHITE/onlinelog


Prepare an Initialization Parameter File for the Standby Database
Copy from the primary pfile to the standby destination
[oracle@rac2 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/
[oracle@rac2 dbs]$ scp initwhite.ora oracle@rac1:/tmp/initblack.ora
initwhite.ora 100% 1704 1.7KB/s 00:00


Copy and edit the primary init.ora to set it up for the standby role,as here shown highlighted:



black.__db_cache_size=331350016
black.__java_pool_size=4194304
black.__large_pool_size=4194304
black.__oracle_base='/ora/oracle/ora11g/base'#ORACLE_BASE set from environment
black.__pga_aggregate_target=301989888
black.__sga_target=536870912
black.__shared_io_pool_size=0
black.__shared_pool_size=184549376
black.__streams_pool_size=4194304
*.audit_file_dest='/ora/oracle/ora11g/base/admin/black/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/ora/oracle/ora11g/base/oradata/black/control01.ctl','/ora/oracle/ora11g/base/flash_recovery_area/black/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='white'
*.db_unique_name='black'
*.log_archive_config='DG_CONFIG=(white,black)'
*.db_recovery_file_dest='/ora/oracle/ora11g/base/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/ora/oracle/ora11g/base'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=whiteXDB)'
#*.local_listener='LISTENER'
*.log_archive_dest_1='LOCATION=/ora/oracle/ora11g/base/flash_recovery_area/black/onlinelog1/ valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=black'
*.log_archive_dest_2='service=white LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=white'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_max_processes=30
*.fal_server='white'
*.fal_client='black'
*.standby_file_management=auto
#*.db_file_name_convert='/ora/oracle/ora11g/base/oradata/black/','/ora/oracle/ora11g/base/oradata/white/'
#*.log_file_name_convert='/ora/oracle/ora11g/base/oradata/black/','/ora/oracle/ora11g/base/oradata/white/'
*.db_file_name_convert='white','black'
*.log_file_name_convert='white','black'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=301989888
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912
*.undo_tablespace='UNDOTBS1'

Configure the listener and tnsnames to support the database on both nodes
Configure listener.

# tnsnames.ora Network Configuration File: /ora/oracle/ora11g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

Primary DB Listener.ora file contents.

# listener.ora Network Configuration File: /ora/oracle/ora11g/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = black)
      (ORACLE_HOME = /ora/oracle/ora11g)
      (SID_NAME = black)
    ))
    (SID_DESC =
      (GLOBAL_DBNAME = white)
      (ORACLE_HOME = /ora/oracle/ora11g)
      (SID_NAME = white)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.24)(PORT = 1521))
  )

ADR_BASE_LISTENER = /ora/oracle/ora11g/base



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



Primary DB TNSNAME.ORA file contents.

BLACK =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.23)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = black)
    )
  )

LISTENER_WHITE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.24)(PORT = 1521))




WHITE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.24)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = white)
    )
  )


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

# listener.ora Network Configuration File: /ora/oracle/ora11g/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = white)
      (ORACLE_HOME = /ora/oracle/ora11g)
      (SID_NAME = white)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = black)
      (ORACLE_HOME = /ora/oracle/ora11g)
      (SID_NAME = black)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.23)(PORT = 1521))
  )

ADR_BASE_LISTENER = /ora/oracle/ora11g/base




Listener.ora File Contents Standy DB

# listener.ora Network Configuration File: /ora/oracle/ora11g/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
       (SID_DESC =
      (GLOBAL_DBNAME = white)
      (ORACLE_HOME = /ora/oracle/ora11g)
      (SID_NAME = white)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = black)
      (ORACLE_HOME = /ora/oracle/ora11g)
      (SID_NAME = black)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.23)(PORT = 1521))
  )

ADR_BASE_LISTENER = /ora/oracle/ora11g/base


TNSNAMES.ORA Standby DB



# tnsnames.ora Network Configuration File: /ora/oracle/ora11g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.


BLACK =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.23)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = black)
    )
  )


WHITE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.24)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = white)
    )
  )

Start the listener and check tnsping on both nodes to both services

$ lsnrctl start   (Primary DB)
$ lsnrctl start   (Standby DB)
$ tnsping white
$ tnsping black



Set Up the Environment to Support the Standby Database on the standby node.
Create a passwordfile for the standby:
[oracle@rac1 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwblack password=oracle
note: sys password must be identical for both primary and standby database
Append an entry to oratab:
[oracle@rac1 ~]$ echo "black:/u01/app/oracle/product/10.2.0/db_1:N" >> /etc/oratab
Startup nomount the Standby database
Nomount the standby instance in preparation ffor the duplicate operation:
Startup nomount the Standby database and generate an spfile
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [whiteowl] ? black
[oracle@rac1 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 21 00:38:03 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/tmp/initblack.ora'
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
SQL> create spfile from pfile='/tmp/initblack.ora';
File created.


SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Create the standby database using rman:
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? black
[oracle@rac1 ~]$ rman target=sys/oracle@white auxiliary=/
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jan 21 00:43:11 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: WHITE (DBID=3603807872)
connected to auxiliary database: WHITE (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;
Start the redo apply:
SQL> alter database recover managed standby database disconnect from session;
Test the configuration by generating archive logs from the primary and then querying the
standby to see if the logs are being successfully applied.
On the Primary:
SQL> alter system switch logfile;
SQL> alter system archive log current;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/white/arch/
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
On the Standby:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/black/arch/
Oldest online log sequence 8
Next log sequence to archive 0
Current log sequence 10

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
2 ORDER BY SEQUENCE#;


Stop the managed recovery process on the standby:

SQL> alter database recover managed standby database cancel;

Database Switchover

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements.

Convert Primary DB to standby DB

$ sqlplus /nolog
SQL> connect / as sysdba
SQL> alter database commit to switchover to standby;

Shutdown Primary DB

SQL>shutdown immediate;

Mount old primary DB as standby DB

SQL> Startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;

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

On the original standby database issue the following commands.
Convert standby DB to Primary DB
SQL> Connect / as sysdba

SQL>alter database commit to switchover to primary;

Shutdown Standby Database

SQL> Shutdown immediate;

Open old standby database as primary

SQL> Startup

Once this is complete, test the log transport as before. If everything is working fine, switch the primary database back to the original server by doing another switchover. This is known as a switchback.