Configure the Network Environment to Allow Connections to Multiple Databases

1. Documentation in Tahiti -> Masters Book List -> Net Services Administrator’s Guide -> 9 Configuring and Administering Oracle Net Listener

Documentation Tahiti -> Masters Book List -> Administrator’s Guide -> 2 Creating and Configuring an Oracle Database ->
Creating a Database With the CREATE DATABASE Statement

2. In this objective, basically are not asking you configure the LISTENER to redirect requests to several different DB.
To test we will create a DB manually (which is also interesting to do this exercise) and see how the LISTENER we have created can serve requests for the two databases (OCM vs OCMTEST).

. OCMTEST create the database.

# We define session variables
export ORACLE_HOME = / u01 / app / oracle / product / 11.2.0 / dbhome_1
export ORACLE_SID = OCMTEST

# Create the configuration file from the example of the documentation
# · Http://docs.oracle.com/cd/E11882_01/server.112/e25494/create005.htm#CIAGFFIE
vi $ ORACLE_HOME / dbs / initOCMTEST.ora
# We introduce these lines
db_name = 'OCMTEST'
MEMORY_TARGET = 300M
processes = 150
audit_file_dest = '/ u01 / app / oracle / admin / OCMTEST / adump'
audit_trail = 'db'
db_block_size = 8192
db_domain = ''
db_recovery_file_dest = '/ u01 / app / oracle / flash_recovery_area'
DB_RECOVERY_FILE_DEST_SIZE = 2G
diagnostic_dest = '/ u01 / app / oracle'
dispatchers = '(PROTOCOL = TCP) (SERVICE = ORCLXDB)'
open_cursors = 300
REMOTE_LOGIN_PASSWORDFILE = 'EXCLUSIVE'
undo_tablespace = 'UNDOTBS1'
control_files ='/u01/app/oracle/oradata/OCMTEST/control01.ctl','/u01/app/oracle/flash_recovery_area/OCMTEST/control02.ctl'
Compatible = '11 .2.0 '
-- Nos conectamos a la Instancia con SQL*Plus "sqlplus / as sysdba" para crear el SPFILE
CREATE SPFILE FROM PFILE;

# Creamos los directorios que hemos configurado en el SPFILE y otros que necesitaremos más adelante
mkdir -p /u01/app/oracle/admin/OCMTEST/adump
mkdir -p /u01/app/oracle/oradata/OCMTEST
mkdir -p /u02/app/oracle/oradata/OCMTEST
mkdir -p /u01/app/oracle/flash_recovery_area/OCMTEST/

# Levantamos la instancia en modo NOMOUNT
# Este comando nos va a dar un error (ORA-845) ya que tenemos que aumentar el FS temporal (tmpfs)
# Para ampliarlo temporalmente (para este ejercicio) debemos ejecutar como root el siguiente comando:
mount -o remount,size=1G /dev/shm

-- Ahora ya podemos levantar la instancia
STARTUP NOMOUNT

-- Creamos la BD
-- Para ello, adaptamos el ejemplo de la documentación
-- Modificamos Passwords, Rutas, Juego de Caracteres y TBS de UNDO
CREATE DATABASE OCMTEST
   USER SYS IDENTIFIED BY *************
   USER SYSTEM IDENTIFIED BY *************
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/OCMTEST/redo101.log','/u02/app/oracle/oradata/OCMTEST/redo102.log') SIZE 100M BLOCKSIZE 512,
           GROUP 2 ('/u01/app/oracle/oradata/OCMTEST/redo201.log','/u02/app/oracle/oradata/OCMTEST/redo202.log') SIZE 100M BLOCKSIZE 512,
           GROUP 3 ('/u01/app/oracle/oradata/OCMTEST/redo301.log','/u02/app/oracle/oradata/OCMTEST/redo302.log') SIZE 100M BLOCKSIZE 512
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/u01/app/oracle/oradata/OCMTEST/system01.dbf' SIZE 325M REUSE
   SYSAUX DATAFILE '/u01/app/oracle/oradata/OCMTEST/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TABLESPACE users
      DATAFILE '/u01/app/oracle/oradata/OCMTEST/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/oradata/OCMTEST/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs1
      DATAFILE '/u01/app/oracle/oradata/OCMTEST/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

-- Creamos el Diccionario de Datos
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
CONNECT SYSTEM
@?/sqlplus/admin/pupbld.sql
-- Estamos haciendo una instalación mínima de Oracle. Si comparamos con la instalación con DBCA, se instalan entre otros estos paquetes:
-- Tenemos una descripción de que hace cada uno en la documentación (http://docs.oracle.com/cd/E11882_01/server.112/e25513/scripts.htm#REFRN005)
-- @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catblock.sql;
-- @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catoctk.sql;
-- @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/owminst.plb;
-- connect "SYSTEM"/"systemPassword"
-- @/u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/help/hlpbld.sql helpus.sql;

-- Activaremos los modos ARCHIVELOG y FLASHBACK
CONN / AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG
ALTER DATABASE FLASHBACK ON
ALTER DATABASE OPEN

4. Without making any changes, the PMON process of the new BD (OCMTEST) register dynamically in the LISTENER services we already have.
This is because we have the LISTENER in the default port on the interface associated with the hostname of the machine. If we changed some of these parameters, we have to set an entry in the tnsnames.ora file that points to that particular LISTENER entry and add the parameter LOCAL_LISTENER OCMTEST instance.

# Check that after a minutes instance have raised OCMTEST register with LISTENER
lsnrctl services
# We’ll see how the service has OCMTEST READY status.

# We can add service statically in the listener.ora configuration (but not necessary)
# This SID_LIST_LISTENER modify the input file $ ORACLE_HOME / network / admin / listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = OCM)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = OCM)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = OCMTEST)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = OCMTEST)
    )
  )
# Recargamos LISTENER settings
lsnrctl reload

5. Now, to complete the network configuration, it is good practice to add the entry OCMTEST service in the tnsnames.ora file.

# Add the following lines to the file $ ORACLE_HOME / network / admin / tnsnames.ora

OCMTEST =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP) (HOST = ocm.dbajunior.com) (PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = OCMTEST)
     )
   )
Tried configuration #
sqlplus system @ OCMTEST

6. Now undo all changes and delete the database.

# Remove the file tnsnames.ora entry OCMTEST
OCMTEST =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP) (HOST = ocm.dbajunior.com) (PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = OCMTEST)
     )
   )

# We edit the entry SID_LIST_LISTENER the listener.ora file as follows
SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (GLOBAL_DBNAME = CMO)
       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
       (SID_NAME = CMO)
     )
   )
# Recargamos LISTENER settings
lsnrctl reload

# Remove the BD "carefully" <img src = "http: //sworg/images/core/emoji/72x72/1f609.png" alt = "

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s