Cloning on different server using rman with same database name

TARGET DATABASE= prod on 10.131.100.148 (NOARCHIVELOG)

AUXILIARY DATABASE= prod on 10.131.100.37

ON TARGET (10.131.100.148)

C:\> rman

RMAN> connect target;

RMAN>backup database; (the location of my bakup files and controlfile is at C:\rman_prod_backup on target)

My oracle home on target machine is D:\oracle\product\10.2.0\db_1

ON AUXILIARY (10.131.100.37)

I install oracle 10g server with starter database

Then I create service(for database) i.e prod

C:\>oradim –new –sid prod –intpwd oracle –startmode auto

NOW I CONFIGURE listener.ora ant tnsnames.ora on auxiliary machine

Listener.ora on auxiliary machine is:-

# listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)

      (PROGRAM = extproc)

    )

        (SID_DESC =

      (GLOBAL_DBNAME = prod)

      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)

      (SID_NAME = prod)

    )

)

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

    )

    (DESCRIPTION =

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

    )

  )

tnsnames.ora on auxiliary machine is:-

# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

prod =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prod)

    )

  )

prod1 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prod)

    )

  )

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

NOW I COPY ALL MY BACKUP FROM C:\rman_prod_backup on target (10.131.100.148) to auxiliary (10.131.100.37) at C:\rman_prod_backup i.e same location (of backup) on both target and auxiliary.

-Produce a pfile for the new database

On target machine-

SQL> create pfile='C:\initprod.ora' from spfile;

File created.

Edit the parameter file accordingly:-

So ,my pfile i.e to be used for auxiliary database is:

prod.__db_cache_size=176160768

prod.__java_pool_size=4194304

prod.__large_pool_size=4194304

prod.__shared_pool_size=96468992

prod.__streams_pool_size=0

audit_file_dest='D:\oracle\product\10.2.0\admin\prod\adump'

background_dump_dest='D:\oracle\product\10.2.0\admin\prod\bdump'

user_dump_dest='D:\oracle\product\10.2.0\admin\prod\udump'

compatible='10.2.0.1.0'

control_files='D:\oracle\product\10.2.0\oradata\prod\control01.ctl','D:\oracle\product\10.2.0\oradata\prod\control02.ctl','D:\oracle\product\10.2.0\oradata\prod\control03.ctl'

core_dump_dest='D:\oracle\product\10.2.0\admin\prod\cdump'

db_block_size=8192

db_cache_size=176160768

db_domain=''

db_file_multiblock_read_count=16

DB_FILE_NAME_CONVERT='C:\oracle\product\10.2.0\oradata\prods\','D:\oracle\product\10.2.0\oradata\prod\'

db_name='prod'

db_recovery_file_dest='D:\oracle\product\10.2.0\flash_recovery_area'

db_recovery_file_dest_size=2147483648

DB_UNIQUE_NAME='prod'

#FAL_CLIENT='prod'

#FAL_SERVER='prods'

global_names=TRUE

INSTANCE_NAME='prod'

java_pool_size=4194304

job_queue_processes=10

large_pool_size=4194304

#LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,prods)'

#log_archive_dest_1='location=C:\prod_archives VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod'

#LOG_ARCHIVE_DEST_2='SERVICE=prods LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prods'

PLACE THE ABOVE PFILE AT D:\oracle\product\10.2.0\db_1\database\ on auxiliary machine (10.131.100.37)

Also, my oracle_home i.e locations of datafiles , controlfiles ,redolog files are same on both machine

i.e  D:\oracle\product\10.2.0\db_1

ON AUXILIARY DATABASE:

C:\Windows\system32>set oracle_sid=prod

C:\Windows\system32>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 9 13:53:00 2011

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

SQL> conn / as sysdba

Connected.

SQL> startup nomount pfile='D:\oracle\product\10.2.0\db_1\database\initprod.ora';

ORACLE instance started.

Total System Global Area  289406976 bytes

Fixed Size                  1248576 bytes

Variable Size             104858304 bytes

Database Buffers          176160768 bytes

Redo Buffers                7139328 bytes

SQL> DISCONNECT sys

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr

oduction

With the Partitioning, OLAP and Data Mining options

NOTE: AFTER STARTING MY AUXILIARY DATABASE  IN NOMOUNT STATE ,I DISCONNECT SYS AS NO SESSION MUST BE THERE WHILE DUPLICATING A DATABASE.

Now , I run duplicate command on auxiliary machine(10.131.100.37) after connecting through rman at auxiliary machine:-

C:\Windows\system32>rman TARGET sys/oracle@prod AUXILIARY sys/oracle@prod1

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Nov 9 13:55:01 2011

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

connected to target database: PROD (DBID=174111093)

connected to auxiliary database: PROD (not mounted)

RMAN> duplicate TARGET database to 'prod' NOFILENAMECHECK;

Starting Duplicate Db at 09-NOV-11

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=155 devtype=DISK

contents of Memory Script:

{

   set newname for datafile  1 to

 "D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSTEM.DBF";

   set newname for datafile  2 to

 "D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\UNDOTBS1.DBF";

   set newname for datafile  3 to

 "D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSAUX.DBF";

   set newname for datafile  4 to

 "D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SAGARDATA01";

   set newname for datafile  5 to

 "D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\GGS_DATA01.DBF";

   restore

   check readonly

   clone database

   ;

}

executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 09-NOV-11

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSTEM.DBF

restoring datafile 00002 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\UNDOTBS1.DBF

restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSAUX.DBF

restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SAGARDATA01

restoring datafile 00005 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\GGS_DATA01.DBF

channel ORA_AUX_DISK_1: reading from backup piece C:\RMAN_PROD_BACKUP\BKS_PROD_1

5_1_20111108

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=C:\RMAN_PROD_BACKUP\BKS_PROD_15_1_20111108 tag=TAG20111108T124629

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25

Finished restore at 09-NOV-11

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS NOARCHIVEL

OG

  MAXLOGFILES     32

  MAXLOGMEMBERS      2

  MAXDATAFILES       32

  MAXINSTANCES    16

  MAXLOGHISTORY     1752

 LOGFILE

  GROUP  1  SIZE 10 M ,

  GROUP  2  SIZE 10 M ,

  GROUP  3  SIZE 10 M

 DATAFILE

  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSTEM.DBF'

 CHARACTER SET AL32UTF8

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

released channel: ORA_AUX_DISK_1

datafile 2 switched to datafile copy

input datafile copy recid=1 stamp=766763761 filename=D:\ORACLE\PRODUCT\10.2.0\OR

ADATA\PROD\UNDOTBS1.DBF

datafile 3 switched to datafile copy

input datafile copy recid=2 stamp=766763761 filename=D:\ORACLE\PRODUCT\10.2.0\OR

ADATA\PROD\SYSAUX.DBF

datafile 4 switched to datafile copy

input datafile copy recid=3 stamp=766763761 filename=D:\ORACLE\PRODUCT\10.2.0\OR

ADATA\PROD\SAGARDATA01

datafile 5 switched to datafile copy

input datafile copy recid=4 stamp=766763761 filename=D:\ORACLE\PRODUCT\10.2.0\OR

ADATA\PROD\GGS_DATA01.DBF

contents of Memory Script:

{

   recover

   clone database

   noredo

   ,

    delete archivelog

   ;

}

executing Memory Script

Starting recover at 09-NOV-11

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=155 devtype=DISK

Finished recover at 09-NOV-11

contents of Memory Script:

{

   shutdown clone;

   startup clone nomount ;

}

executing Memory Script

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     289406976 bytes

Fixed Size                     1248576 bytes

Variable Size                104858304 bytes

Database Buffers             176160768 bytes

Redo Buffers                   7139328 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS NOARCHIVEL

OG

  MAXLOGFILES     32

  MAXLOGMEMBERS      2

  MAXDATAFILES       32

  MAXINSTANCES    16

  MAXLOGHISTORY     1752

 LOGFILE

  GROUP  1  SIZE 10 M ,

  GROUP  2  SIZE 10 M ,

  GROUP  3  SIZE 10 M

 DATAFILE

  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSTEM.DBF'

 CHARACTER SET AL32UTF8

contents of Memory Script:

{

   set newname for tempfile  1 to

 "D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\TEMP.DBF";

   switch clone tempfile all;

   catalog clone datafilecopy  "D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\UNDOTBS1.D

BF";

   catalog clone datafilecopy  "D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSAUX.DBF

";

   catalog clone datafilecopy  "D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SAGARDATA0

1";

   catalog clone datafilecopy  "D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\GGS_DATA01

.DBF";

   switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\TEMP.DBF in co

ntrol file

cataloged datafile copy

datafile copy filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\UNDOTBS1.DBF recid=

1 stamp=766763785

cataloged datafile copy

datafile copy filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSAUX.DBF recid=2

stamp=766763786

cataloged datafile copy

datafile copy filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SAGARDATA01 recid=3

 stamp=766763786

cataloged datafile copy

datafile copy filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\GGS_DATA01.DBF reci

d=4 stamp=766763786

datafile 2 switched to datafile copy

input datafile copy recid=1 stamp=766763785 filename=D:\ORACLE\PRODUCT\10.2.0\OR

ADATA\PROD\UNDOTBS1.DBF

datafile 3 switched to datafile copy

input datafile copy recid=2 stamp=766763786 filename=D:\ORACLE\PRODUCT\10.2.0\OR

ADATA\PROD\SYSAUX.DBF

datafile 4 switched to datafile copy

input datafile copy recid=3 stamp=766763786 filename=D:\ORACLE\PRODUCT\10.2.0\OR

ADATA\PROD\SAGARDATA01

datafile 5 switched to datafile copy

input datafile copy recid=4 stamp=766763786 filename=D:\ORACLE\PRODUCT\10.2.0\OR

ADATA\PROD\GGS_DATA01.DBF

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 09-NOV-11

CROSS PLATFORM TRANSPORT DATABASE USING RMAN

SOURCE DATABASE: TEST ON WINDOWS 32-BIT

TARGET DATABASE:  TEST ON centOS 5.5 32-BIT

Open the source database in read-only mode:::::::

SQL>alter database open read only;

SQL>select * from v$transportable_platform order by 2;
SQL> desc v$db_transportable_platform

 Name                                      Null?    Type

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

 PLATFORM_ID                                        NUMBER

 PLATFORM_NAME                                      VARCHAR2(101)

 ENDIAN_FORMAT                                      VARCHAR2(14)

SQL> select * from v$db_transportable_platform;

PLATFORM_ID

-----------

PLATFORM_NAME

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

ENDIAN_FORMAT

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

          7

Microsoft Windows IA (32-bit)

Little

10

Linux IA (32-bit)

Little

PLATFORM_ID

-----------

PLATFORM_NAME

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

ENDIAN_FORMAT

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

          5

HP Tru64 UNIX

Little

         11

Linux IA (64-bit)

PLATFORM_ID

-----------

PLATFORM_NAME

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

ENDIAN_FORMAT

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

Little

         15

HP Open VMS

Little

          8

PLATFORM_ID

-----------

PLATFORM_NAME

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

ENDIAN_FORMAT

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

Microsoft Windows IA (64-bit)

Little

         13

Linux 64-bit for AMD

Little

PLATFORM_ID

-----------

PLATFORM_NAME

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

ENDIAN_FORMAT

         12

Microsoft Windows 64-bit for AMD

Little

         17

Solaris Operating System (x86)

Little

PLATFORM_ID

-----------

PLATFORM_NAME

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

ENDIAN_FORMAT

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

9 rows selected.

Start RMAN and connect to the source database as TARGET:::::

On another window:

C:\Documents and Settings\sagar.verma>set oracle_sid=test

C:\Documents and Settings\sagar.verma>rman

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Nov 4 12:13:10 2011

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

RMAN> connect target;

connected to target database: TEST (DBID=2060060426)

Run the CONVERT DATABASE command::::::::

RMAN> convert database

2> new database 'newdb'

3> transport script 'C:\convertdb\transportscript.sql'

4> to platform 'Linux IA (32-bit)'

5> DB_FILE_NAME_CONVERT'D:\oracle\product\10.2.0\oradata\test' 'C:\convertdb';

Starting convert at 04-NOV-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=153 devtype=DISK

Directory SYS.DATA_PUMP_DIR found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM.DBF

converted datafile=C:\CONVERTDB\SYSTEM.DBF

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX.DBF

converted datafile=C:\CONVERTDB\SYSAUX.DBF

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS1.DBF

converted datafile=C:\CONVERTDB\UNDOTBS1.DBF

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01

Run SQL script C:\CONVERTDB\TRANSPORTSCRIPT.SQL on the target platform to create

 database

Edit init.ora file D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00MQQCRM_1_0.ORA.

 This PFILE will be used to create the database on the target platform

To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target plat

form

To change the internal database identifier, use DBNEWID Utility

Finished backup at 04-NOV-11

RMAN>

1.     the command creates the files in the C:/convertdb/ directory on the source host. Move these files to the directory on the destination host that will contain the destination database files.

2.    If the path to the data files is different on the destination host, then edit the transport script to refer to the new data file locations.

3.    If necessary, edit the initialization parameter file to change any settings for the destination database.

These are the contents of my TRANSPORTSCRIPT.SQL

STARTUP NOMOUNT PFILE='/u01/oracle/product/10.2.0/db_1/dbs/INIT_00MQQCRM_1_0.ORA'

CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS FORCE LOGGING ARCHIVELOG

    MAXLOGFILES 32

    MAXLOGMEMBERS 2

    MAXDATAFILES 32

    MAXINSTANCES 16

    MAXLOGHISTORY 1752

LOGFILE

  GROUP 1 '/u01/oracle/oradata/test/ARCH_D-NEWDB_ID-2060060426_S-151_T-1_A-761913994_00MQQCRM'  SIZE 10M,

  GROUP 2 '/u01/oracle/oradata/test/ARCH_D-NEWDB_ID-2060060426_S-152_T-1_A-761913994_00MQQCRM'  SIZE 10M,

  GROUP 3 '/u01/oracle/oradata/test/ARCH_D-NEWDB_ID-2060060426_S-153_T-1_A-761913994_00MQQCRM'  SIZE 10M

DATAFILE

  '/u01/convertdb/SYSTEM.DBF',

  '/u01/ convertdb /UNDOTBS1.DBF',

  '/u01/ convertdb /SYSAUX.DBF'

CHARACTER SET AL32UTF8

;

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oracle/oradata/test/DATA_D-NEWDB_I-2060060426_TS-TEMP_FNO-1_00MQQCRM'

     SIZE 10485760  AUTOEXTEND OFF;

-- End of tempfile additions.

--

set echo off

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

prompt * Your database has been created successfully!

prompt * There are many things to think about for the new database. Here

prompt * is a checklist to help you stay on track:

prompt * 1. You may want to redefine the location of the directory objects.

prompt * 2. You may want to change the internal database identifier (DBID)

prompt *    or the global database name for this database. Use the

prompt *    NEWDBID Utility (nid).

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SHUTDOWN IMMEDIATE

STARTUP UPGRADE PFILE='/u01/oracle/product/10.2.0/db_1/dbs/INIT_00MQQCRM_1_0.ORA'

@@ ?/rdbms/admin/utlirp.sql

SHUTDOWN IMMEDIATE

STARTUP PFILE='/u01/oracle/product/10.2.0/db_1/dbs/INIT_00MQQCRM_1_0.ORA'

-- The following step will recompile all PL/SQL modules.

-- It may take serveral hours to complete.

@@ ?/rdbms/admin/utlrp.sql

set feedback 6;

              NOW I CREATE DESIRED FOLDERS ON NY CENTOS 5.5 MACHINE

              adump at /u01/oracle/admin/test/

              bdump at /u01/oracle/admin/test/

              cdump at /u01/oracle/admin/test/

              udump at /u01/oracle/admin/test/

             Also transferred my pfile i.e INIT_00MQQCRM_1_0.ORA from windows m/c to linux m/c (with name   

             inittest.ora at location /u01/oracle/product/10.2.0/db_1network/admin/ with command

             bash-3.2$ mv INIT_00MQQCRM_1_0.ORA inittest.ora)

             and have changed the locations within inittest.ora .

 

            This is a view of my inittest.ora on centOS m/c

  control_files            = "/u01/oracle/oradata/test/CF_D-NEWDB_ID-2060060426_00MQQCRM"

  db_recovery_file_dest    = "/u01/oracle/flash_recovery_area/test"

  db_recovery_file_dest_size= 2147483648

  instance_name            = "TEST"

  audit_file_dest          = "/u01/oracle/admin/test/adump"

  background_dump_dest     = "/u01/oracle/admin/test/bdump"

  user_dump_dest           = "/u01/oracle/admin/test/udump"

  core_dump_dest           = "/u01/oracle/admin/test/cdump"

  db_name                  = "TEST"

 db_unique_name           = "TEST

Also, don’t forget to update the tns entries within tnsnames.ora on CentOS m/c .

Now,

Bash-3.2$ export ORACLE_SID=test

Bash-3.2$ sqlplus /nolog as sysdba

Sql>@/u01/convertdb/TRANSPORTSCRIPT.SQL

After running this script, startup the database with

Sql>startup upgrade;

And then run scripts:

utlirp.sql and then utlrp.sql 

TNSnames and LISTENER.ora

Oracle Networking – TNSnames , Listener.ora

•        UNDERSTANDING NETWORK CONFIGURATION

Oracle Net is a software component that resides on the client and the Oracle database server Or Oracle DBA Machine. It is responsible for establishing and maintaining the connection between the client application and the server, as well as exchanging messages between them.

For the client application and a database to communicate, the client application must specify location details for the database it wants to connect to and the database must provide some sort of identification, or address.

•        WHAT IS AN ORACLE DATABASE LISTENER?

On the database server side,an Oracle Database Listener is an Oracle Database Process which "listen" for for users (clients) connecting to the database. The listener process, either creates a dedicated server process for each user or to a shared server process that handles many users.

It is configured in a file named listener.ora, with a protocol address that identifies the database.

(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=my-server) (PORT=1521)))

This example shows a TCP/IP protocol address that specifies the host machine of the listener and a port number.

Here is an example of $ORACLE_HOME/network/admin/listener.ora:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = C:\Oracle_10g)

(PROGRAM = extproc)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

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

)

)

LISTENER2 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP)(HOST = cata1)(PORT = 1522))

)

)

Here 2 listeners are configured for this server.

•        CONFIGURE THE CLIENT

When a client configured with the same protocol address broadcasts a request for a connection, the listener on the server machine or Oracle DBA machine brokers the request and forwards it to the Oracle database.

The client uses a CONNECT DESCRIPTOR to specify the database it wants to connect to. This connect descriptor contains-- a protocol address and --a database service name.

A database can have multiple services defined, so a specific one must be specified for the connection. In the case of the preconfigured database that you installed, there is only one service, the name of which defaults to the global database name.

The following example shows a CONNECT DESCRIPTOR that enables client to connect to a database service called live.

SCOTT =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = live)

    )

  )

Connection Requests:-

Users initiate a connection request by providing a connect string. A CONNECT STRING includes a username and password, along with a connect identifier.

This connect identifier can be the connect descriptor itself, or a name that resolves to the connect descriptor. One of the most common connect identifiers is a net service name, a simple name for a service.

The following example shows a connect string that uses net service name live as the connect identifier

CONNECT sagar/verma@live

•        SOME QUESTIONS:-

Q) If I configured many listeners on my server, how could I see which is used ?

Ans)Using LSNRCTL> show current listener command.

       Other useful commands:-

       lsnrctl status

       lsnrctl start

       lsnrctl stop

Q)How could I see if the listener is running ?

SWITCHOVER AND FAILOVER

SWITCHOVER/FAILOVER

·     

           Switchover   -   Role reversal between primary and standby databases. Old primary is now a standby. Used for hardware upgrades, OS upgrades, etc.

·        Failover   -   Primary is down with little hope of quick recovery (ie. hardware failure on primary machine).Once failover has been initiated , the old primary database is no longer useful.

STEPS OF SWITCHOVER

On primary database:

Sql> select switchover_status from v$database ;

Initiate switchover on primary database :

Sql> alter database commit to switchover to physical standby;

After the above statement completes the old primary is now a standby database.

While still on old primary shutdown database and restart as standby:

Sql>shu immediate;

Sql>startup nomount;

Sql>alter database mount standby database;

On the old standby system (new primary)

Sql> select switchover_status from v$database;

On new primary complete switchover then restart database:

Sql>alter database commit to switchover to primary;

Sql>shu immediate;

Sql>startup;

On new standby start managed recovery:

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

On new primary begin redo transport:

Sql>alter system archive log current;

Switchover complete.

STEPS OF FAILOVER

Manually register any redo logs with the standby that are available and that have not been applied.

If using standby redo logs, initiate failover with:

Sql> alter database managed standby database finish;

If not using standby redo logs:

Sql> Alter database recover managed standby database finish skip standby logfile;

Convert the Physical Standby to Primary (modify pfile before restart):

Initialization Parameters:

...

log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=prods'

log_archive_dest_2='service=prod valid_for=(online_logfiles,primary_role) db_unique_name=prod'

log_archive_dest_state_1='ENABLE'

log_archive_dest_state_2='DEFER'

fal_server='prod','prods'

fal_client='prods'

...

        

Sql>alter database commit to switchover to primary;

Sql>shu immediate;

Sql>startup;

Failover complete.