Configure Multiple Listeners in the same Oracle Home

At times we may need to configure multiple listeners under same OH.

To do this, navigate to $OH/network/admin

vi listener.ora

Add these entries:

PRIM =


(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dba.asteroid.com)(PORT = 1521))

)

)


DR01 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dba.asteroid.com)(PORT = 1523))

)

)

SID_LIST_PRIM =(SID_LIST =(SID_DESC = (GLOBAL_DBNAME = PRIM) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = PRIM)))



SID_LIST_DR01 =(SID_LIST =(SID_DESC = (GLOBAL_DBNAME = DR01) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = DR01)))

 
 
In my case, i have two listeners: PRIM, DR01 runnng on two different ports: 1521, 1523
 
[oracle@dba admin]$ lsnrctl status PRIM




LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 27-JUL-2011 10:36:26



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



Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dba.asteroid.com)(PORT=1521)))

STATUS of the LISTENER

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

Alias PRIM

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 27-JUL-2011 10:33:20

Uptime 0 days 0 hr. 3 min. 6 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/dba/prim/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dba.asteroid.com)(PORT=1521)))

Services Summary...

Service "DR01" has 1 instance(s).

Instance "DR01", status BLOCKED, has 1 handler(s) for this service...

Service "PRIM" has 1 instance(s).

Instance "PRIM", status UNKNOWN, has 1 handler(s) for this service...

Service "prim.asteroid.com" has 1 instance(s).

Instance "prim", status READY, has 1 handler(s) for this service...

Service "primXDB.asteroid.com" has 1 instance(s).

Instance "prim", status READY, has 1 handler(s) for this service...

The command completed successfully

 
Cheers!

Change the OBIEE analytics keyword in the URL

Sometimes we may need to change the name of OBIEE(based on client's requirements)

By default we get analytics keyword at the end of the URL:

 http://pracdba.xp.com:9704/analytics

 We can change this to:

  http://pracdba.xp.com:9704/bi

To change the word "analytics" to BI in the URL, change the line indefault-web-site.xml in OracleBI\oc4j_bi\j2ee\home\config


from


web-app application="analytics" name="analytics" load-on-startup="true" root="/analytics"

to

web-app application="analytics" name="analytics" load-on-startup="true" root="/BI"

NOTE:

If port is changed to 80 (web port) in Linux only root user can run an application which binds to a port less than 1024.


After the changes restart OC4J

Clone ASM Database using RMAN

Pre-steps:


Backup target database

Make sure have enough memory, space in OS

Actual steps:

1. Create Directories needed for Auxiliary instance

[oracle@eprax01 rdbms]$ pwd

/u01/app/oracle/diag/rdbms

[oracle@eprax01 rdbms]$ mkdir dev

[oracle@eprax01 rdbms]$ ls

dev prd

[oracle@eprax01 rdbms]$ cd dev/

[oracle@eprax01 dev]$ mkdir bdump udump pfile trace

[oracle@eprax01 dev]$ ls

bdump pfile trace udump



2. Copy Target init.ora to the AUX instance location(dev in our case)

Note that we have spfile used and hence we first need to get pfile from spfile

SQL> sho parameter spfile;

NAME TYPE VALUE

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

spfile string +DATA/prd/spfileprd.ora

SQL> create pfile from spfile;

File created.

3. Copy the initprd.ora to initdev.ora and make necessary changes related to DEV instance.

[oracle@eprax01 dbs]$ cp -p initprd.ora initdev.ora

You can use different Diskgroup if you like (Which you should); I have used the same Disk group.

The init.ora may look like this:

*.audit_file_dest='/u01/app/oracle/admin/dev/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='+DATA/dev/controlfile/current.261.755015849','+DATA/dev/controlfile/current.260.755015851'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain=''

*.db_name='dev'

*.db_recovery_file_dest_size=4039114752

*.db_recovery_file_dest='+ARCH'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=devXDB)'

*.memory_target=364904448

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.db_file_name_convert=('+DATA/prd','+DATA/dev')  Use this most importantly


*.log_file_name_convert=('+DATA/prd','+DATA/dev')  Use this most importantly

*.instance_name='dev'

4. Build password file for DEV instance.

[oracle@eprax01 dbs]$ orapwd file=/u01/app/oracle/product/11.2.0/dbs/orapwdev password=dba123 entries=10

5. Start DEV(AUXILIARY instance) in nomount state

SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0/dbs/initdev.ora;

ORACLE instance started.

Total System Global Area 364081152 bytes

Fixed Size 1336568 bytes

Variable Size 289409800 bytes

Database Buffers 67108864 bytes

Redo Buffers 6225920 bytes


6. Add entries in Listener and TNSNAMES .ora

DEV =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = eprax01)(PORT = 1523))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dev)

)

)


7. Connect to target database and auxiliary

[oracle@eprax01 admin]$ rman target sys/XXXXX@prd auxiliary / - Please connect Auxiliary database locally and connect to Target database remotely using services. Otherwise we end-up getting the below error:

RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

To avoid this, Set Oracle SID as AUX database(in our case DEV) and then logon to RMAN as shown below:

[oracle@eprax01 ~]$ rman target sys/XXXXX@prd auxiliary /


Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jul 6 15:22:05 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRD (DBID=1818679464)
connected to auxiliary database: DEV (not mounted)

RMAN> duplicate target database to dev;
Starting Duplicate Db at 06-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK

contents of Memory Script:

{

sql clone "create spfile from memory";

}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:

{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 364081152 bytes
Fixed Size 1336568 bytes
Variable Size 293604104 bytes
Database Buffers 62914560 bytes
Redo Buffers 6225920 bytes

contents of Memory Script:
{
sql clone "alter system set control_files =

''+DATA/dev/controlfile/current.283.755796269'', ''+DATA/dev/controlfile/current.284.755796269'' comment=

''Set by RMAN'' scope=spfile";

sql clone "alter system set db_name =

''PRD'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''DEV'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

restore clone primary controlfile;

alter clone database mount;

}

executing Memory Script

sql statement: alter system set control_files = ''+DATA/dev/controlfile/current.283.755796269'', ''+DATA/dev/controlfile/current.284.755796269'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''PRD'' comment= ''Modified by RMAN duplicate'' scope=spfile

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

Oracle instance shut down

Oracle instance started

Total System Global Area 364081152 bytes
Fixed Size 1336568 bytes
Variable Size 293604104 bytes
Database Buffers 62914560 bytes
Redo Buffers 6225920 bytes

Starting restore at 06-JUL-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=22 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece +ARCH/prd/autobackup/2011_07_06/s_755788909.275.755788911

channel ORA_AUX_DISK_1: piece handle=+ARCH/prd/autobackup/2011_07_06/s_755788909.275.755788911 tag=TAG20110706T132149

channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
output file name=+DATA/dev/controlfile/current.283.755796269
output file name=+DATA/dev/controlfile/current.284.755796269

Finished restore at 06-JUL-11
database mounted

RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:

{

set until scn 1258875;

set newname for datafile 1 to

"+data";

set newname for datafile 2 to

"+data";

set newname for datafile 3 to

"+data";

set newname for datafile 4 to

"+data";

set newname for datafile 5 to

"+data";

restore

clone database

;

}

executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting restore at 06-JUL-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

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

channel ORA_AUX_DISK_1: restoring datafile 00001 to +data

channel ORA_AUX_DISK_1: restoring datafile 00002 to +data

channel ORA_AUX_DISK_1: restoring datafile 00003 to +data

channel ORA_AUX_DISK_1: restoring datafile 00004 to +data

channel ORA_AUX_DISK_1: restoring datafile 00005 to +data

channel ORA_AUX_DISK_1: reading from backup piece +ARCH/prd/backupset/2011_07_06/nnndf0_tag20110706t132104_0.272.755788867

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:16

Finished restore at 06-JUL-11

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=7 STAMP=755796410 file name=+DATA/dev/datafile/system.285.755796333

datafile 2 switched to datafile copy

input datafile copy RECID=8 STAMP=755796410 file name=+DATA/dev/datafile/sysaux.286.755796337

datafile 3 switched to datafile copy

input datafile copy RECID=9 STAMP=755796410 file name=+DATA/dev/datafile/undotbs1.288.755796343

datafile 4 switched to datafile copy

input datafile copy RECID=10 STAMP=755796410 file name=+DATA/dev/datafile/users.289.755796343

datafile 5 switched to datafile copy

input datafile copy RECID=11 STAMP=755796411 file name=+DATA/dev/datafile/example.287.755796341

contents of Memory Script:

{

set until scn 1258875;

recover

clone database

delete archivelog

;

}

executing Memory Script
executing command: SET until clause

Starting recover at 06-JUL-11

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 23 is already on disk as file +ARCH/prd/archivelog/2011_07_06/thread_1_seq_23.273.755788903

archived log for thread 1 with sequence 24 is already on disk as file +ARCH/prd/archivelog/2011_07_06/thread_1_seq_24.276.755791611

archived log for thread 1 with sequence 25 is already on disk as file +ARCH/prd/archivelog/2011_07_06/thread_1_seq_25.277.755795985

archived log file name=+ARCH/prd/archivelog/2011_07_06/thread_1_seq_23.273.755788903 thread=1 sequence=23

archived log file name=+ARCH/prd/archivelog/2011_07_06/thread_1_seq_24.276.755791611 thread=1 sequence=24

archived log file name=+ARCH/prd/archivelog/2011_07_06/thread_1_seq_25.277.755795985 thread=1 sequence=25

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

Finished recover at 06-JUL-11

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set db_name =

''DEV'' comment=

''Reset to original value by RMAN'' scope=spfile";

sql clone "alter system reset db_unique_name scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script


database dismounted

Oracle instance shut down



connected to auxiliary database (not started)

Oracle instance started


Total System Global Area 364081152 bytes

Fixed Size 1336568 bytes

Variable Size 293604104 bytes

Database Buffers 62914560 bytes

Redo Buffers 6225920 bytes

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

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 364081152 bytes
Fixed Size 1336568 bytes
Variable Size 293604104 bytes
Database Buffers 62914560 bytes
Redo Buffers 6225920 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DEV" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ( '+data', '+data' ) SIZE 50 M REUSE,

GROUP 2 ( '+data', '+data' ) SIZE 50 M REUSE,

GROUP 3 ( '+data', '+data' ) SIZE 50 M REUSE

DATAFILE

'+DATA/dev/datafile/system.285.755796333'

CHARACTER SET AL32UTF8

contents of Memory Script:

{

set newname for tempfile 1 to

"+data";

switch clone tempfile all;

catalog clone datafilecopy "+DATA/dev/datafile/sysaux.286.755796337",

"+DATA/dev/datafile/undotbs1.288.755796343",

"+DATA/dev/datafile/users.289.755796343",

"+DATA/dev/datafile/example.287.755796341";

switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data in control file

cataloged datafile copy

datafile copy file name=+DATA/dev/datafile/sysaux.286.755796337 RECID=1 STAMP=755796530

cataloged datafile copy

datafile copy file name=+DATA/dev/datafile/undotbs1.288.755796343 RECID=2 STAMP=755796530

cataloged datafile copy

datafile copy file name=+DATA/dev/datafile/users.289.755796343 RECID=3 STAMP=755796531

cataloged datafile copy

datafile copy file name=+DATA/dev/datafile/example.287.755796341 RECID=4 STAMP=755796531



datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=755796530 file name=+DATA/dev/datafile/sysaux.286.755796337

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=755796530 file name=+DATA/dev/datafile/undotbs1.288.755796343

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=755796531 file name=+DATA/dev/datafile/users.289.755796343

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=755796531 file name=+DATA/dev/datafile/example.287.755796341
contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 06-JUL-11

Confirmation:

SQL> select instance_name, status from v$instance;


INSTANCE_NAME STATUS

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

dev OPEN

Backup ASM database using RMAN

Backup ASM database using RMAN:



I have used FRA for storing all the backups. You may use the regular method to store the backup using log_archive_dest



SQL> sho parameter db_recovery_file_dest



NAME TYPE VALUE

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

db_recovery_file_dest string +DATA

db_recovery_file_dest_size big integer 3852M



Change the FRA destination to some other diskgroup.

Create a new Disk Group.



I have already created ASM Disks: ARCH01 and ARCH02 using OracleASM as shown above.

[root@eprax01 ~]# /etc/init.d/oracleasm listdisks

ARCH01

ARCH02

DATA01

DATA02



Identify the PATH from v$asm_disk



SQL> select NAME, LABEL, PATH from v$asm_disk where LABEL like ‘%ARC%’;



NAME LABEL PATH

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

ARCH01 ORCL:ARCH01

ARCH02 ORCL:ARCH02


Create a new Diskgroup for FRA:



SQL> CREATE DISKGROUP ARCH EXTERNAL REDUNDANCY DISK 'ORCL:ARCH01', 'ORCL:ARCH02';



Diskgroup created.



SQL> select NAME, STATE from v$asm_diskgroup;

NAME STATE

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

DATA MOUNTED

ARCH MOUNTED



Change the FRA location:

SQL> alter system set db_recovery_file_dest='+ARCH';

System altered.



Setup RMAN for backing up database:

1. Configure RMAN for Retention policy

Window based:

Specifying a recovery window by using the CONFIGURE RETENTION POLICY TO RECOVERY WINDOW command: A recovery window is a period of time that begins with the current time and extends backward in time to the point of recoverability. The point of recoverability is the earliest time for a hypothetical point-in-time recovery (the earliest point to which a database can be recovered following a media failure).



For example, when a recovery window of five days is implemented, the window of time must extend back exactly five days from the present, so that a backup can be restored and recovered to this point. This retention policy can be implemented by executing the following RMAN command:



CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;



Redundancy based:

The REDUNDANCY parameter specifies that any number of backups or copies beyond a specified number does not need to be retained. For example, the CONFIGURE RETENTION POLICY TO REDUNDANCY 3; command specifies that a maximum of three backups or copies of a data file or control file can be retained. Redundancy is the default type of retention policy.



By default, the retention policy is configured to REDUNDANCY = 1 to maintain compatibility with the behavior of the REPORT OBSOLETE command in earlier RMAN releases. The CONFIGURE RETENTION POLICY CLEAR; command can be executed to return the retention policy to the default value of one.



2. Configure RMAN for autobackup



RMAN can automatically back up the control file and server parameter file (SPFILE) in situations in which the RMAN repository data for your database has been updated in a way that affects RMAN's ability to restore your database.



With a control file autobackup, RMAN can recover the database even if the current control file, recovery catalog, and server parameter file are inaccessible. Because the path used to store the autobackup follows a well-known format, RMAN can search for and restore the server parameter file from that autobackup.

The first channel allocated during the backup job creates the autobackup and places it into its own backup set; for autobackups after database structural changes, the default disk channel makes the backup. If a server parameter file is used, it is backed up in the same backup set as the control file during a control file autobackup.



After the control file autobackup completes, the database writes a message containing the complete path of the backup piece and the device type to the alert log.



The RMAN behavior when the BACKUP command includes datafile 1 depends on the CONFIGURE CONTROLFILE AUTOBACKUP setting. If control file autobackups are ON and the backup includes datafile 1, RMAN writes the control file and SPFILE to a separate autobackup backup set. If control file autobackups are OFF and the backup includes datafile 1, then RMAN includes the current control file and SPFILE in the same backup set as the datafiles.



RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;



new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored



CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+ARCH';



3. Configure RMAN for backup optimization.

Optimization makes sure that it skips already backed up copies/backup sets

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;



new RMAN configuration parameters:

CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters are successfully stored





Backup database online:

RMAN> backup database plus archivelog;





Starting backup at 04-JUL-11

current log archived

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=44 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=9 RECID=1 STAMP=755195883

input archived log thread=1 sequence=10 RECID=2 STAMP=755627785

input archived log thread=1 sequence=11 RECID=3 STAMP=755636559

input archived log thread=1 sequence=12 RECID=4 STAMP=755645793

channel ORA_DISK_1: starting piece 1 at 04-JUL-11

channel ORA_DISK_1: finished piece 1 at 04-JUL-11

piece handle=+ARCH/prd/backupset/2011_07_04/annnf0_tag20110704t213635_0.257.755645797 tag=TAG20110704T213635 comment=NONE

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

Finished backup at 04-JUL-11



Starting backup at 04-JUL-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=+DATA/prd/datafile/system.256.755015605

input datafile file number=00002 name=+DATA/prd/datafile/sysaux.257.755015611

input datafile file number=00005 name=+DATA/prd/datafile/example.269.755015953

input datafile file number=00003 name=+DATA/prd/datafile/undotbs1.258.755015615

input datafile file number=00004 name=+DATA/prd/datafile/users.259.755015615

channel ORA_DISK_1: starting piece 1 at 04-JUL-11

channel ORA_DISK_1: starting piece 1 at 04-JUL-11


channel ORA_DISK_1: finished piece 1 at 04-JUL-11

piece handle=+ARCH/prd/backupset/2011_07_04/nnndf0_tag20110704t213644_0.258.755645807 tag=TAG20110704T213644 comment=NONE

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

Finished backup at 04-JUL-11



Starting backup at 04-JUL-11

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=13 RECID=5 STAMP=755645864

channel ORA_DISK_1: starting piece 1 at 04-JUL-11

channel ORA_DISK_1: finished piece 1 at 04-JUL-11

piece handle=+ARCH/prd/backupset/2011_07_04/annnf0_tag20110704t213745_0.260.755645865 tag=TAG20110704T213745 comment=NONE

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

Finished backup at 04-JUL-11



Starting Control File and SPFILE Autobackup at 04-JUL-11

piece handle=+ARCH/prd/autobackup/2011_07_04/s_755645867.261.755645869 comment=NONE

Finished Control File and SPFILE Autobackup at 04-JUL-11 ---> Spfile and control files are backed up because AUTOBACKUP is on