oracle database 12c Listener and tns configuration and connectivity

  • Existing number of PDBs 

     

    select b.con_id, a.pdb_name, a.status, b.open_mode,b.restricted
    from dba_pdbs a, v$pdbs b
    where a.pdb_name = b.name
    ORDER BY b. con_id

CON_ID PDB_NAME STATUS OPEN_MODE RES
———- ————— ——— ———- —
2 PDB$SEED NORMAL READ ONLY NO
3 PDB01 NORMAL READ WRITE NO
4 DBCAPDB03 NORMAL MOUNTED
5 SALESPDB UNPLUGGED MOUNTED
7 DBCAPDB02 NORMAL MOUNTED
8 DBCAPDB04 NORMAL MOUNTED
9 PDB_ARCH NORMAL MOUNTED
10 PDB001 NORMAL MOUNTED

8 rows selected.

  • Services which created automatically when a PDB get creates and some of the default services for root container(PDB=CDB$ROOT)

select con_id,name, pdb from v$services;

CON_ID NAME PDB
———- —————————————————————- ——————————
10 pdb001 PDB001
9 pdb_arch PDB_ARCH
8 dbcapdb04 DBCAPDB04
7 dbcapdb02 DBCAPDB02
5 salespdb SALESPDB
4 dbcapdb03 DBCAPDB03
3 pdb01 PDB01
1 TEST12CXDB CDB$ROOT
1 TEST12C CDB$ROOT
1 SYS$BACKGROUND CDB$ROOT
1 SYS$USERS CDB$ROOT

  • To find the number of services from os

 

[oracle@oel58-12c admin]$ lsnrctl status | grep instance
Service “TEST12C” has 1 instance(s).
Service “TEST12CXDB” has 1 instance(s).
Service “dbcapdb02” has 1 instance(s).
Service “dbcapdb03” has 1 instance(s).
Service “dbcapdb04” has 1 instance(s).
Service “pdb001” has 1 instance(s).
Service “pdb01” has 1 instance(s).
Service “pdb_arch” has 1 instance(s).
Service “salespdb” has 1 instance(s).

 

  • listener and tns configuration files

[oracle@oel58-12c admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.146.131)(PORT = 1523))
)
)

#1st Database
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
(SID_NAME = TEST12C)
)
)

# 2nd Database
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
(SID_NAME = FSDB12C)
)
)

# End OF listener.ora File

[oracle@oel58-12c admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

# Database TNS
TEST12C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel58-12c.local.com)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST12C)
)
)
# pdb01 TNS
pdb01_test12c =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel58-12c.local.com)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb01)
)
)

# salespdb TNS
salespdb_test12c =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel58-12c.local.com)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = salespdb)
)
)

# hrpdb TNS
hrpdb_test12c =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel58-12c.local.com)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hrpdb)
)
)
LISTENER_NCDBTEST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.146.131)(PORT = 1523))

# End OF tnsnames.ora File

  • The number of ways to connect to 12c Database and its Pluggable Database 

    hostname: oel58-12c.local.com

    IP Address: 192.168.146.131
    DB Name: TEST12C,FSDB12C
    Pluggable Databases list in master container(TEST12C): PDB$SEED ( Read only Seed PDB) ,PDB01 ,DBCAPDB03 ,SALESPDB ,DBCAPDB02 ,DBCAPDB04 ,PDB_ARCH ,PDB001

# How to connect
sqlplus user/password@//oel58-12c.local.com:1523/pdb01

sqlplus sys/ep1sodes@//192.168.146.131:1523/pdb01 as sysdba
sqlplus sys/ep1sodes@//oel58-12c.local.com:1523/pdb01 as sysdba

– connecting to comman user
sqlplus C##tuser/ep1sodes
sqlplus c##tuser/ep1sodes@//oel58-12c.local.com:1523/pdb01

sqlplus user/password@pdb01_test12c
or
sqlplus user/password@//oel58-12c.local.com:1523/pdb01
or
sqlplus user/password@oel58-12c.local.com:1523/pdb01
or
sqlplus user/password@//192.168.146.131:1523/pdb01
or
sqlplus user/password@192.168.146.131:1523/pdb01
or
conn user/password@oel58-12c.local.com:1523/pdb01
or
conn user/password@oel58-12c.local.com:1523/pdb01
or
conn user/password@pdb01_test12c
or
conn user/password@//oel58-12c.local.com:1523/pdb01
or
conn user/password@oel58-12c.local.com:1523/pdb01
or
conn user/password@//192.168.146.131:1523/pdb01
or
conn user/password@192.168.146.131:1523/pdb01
or
conn user/password@oel58-12c.local.com:1523/pdb01

Note: If you see any discipancy please send me your feedback

Posted in 12c Database | Leave a comment

DBCA error for Listener issue during 12c database creation

While using DBCA to create a new 12c database, always DBCA was failing with below error /warning message.

DBCA error for Listener issue

Solution:

I have skipped the listener check(not new listener nor existing selection), didn’t select any listener in DBCA. Database creation completed successfully.

Once Database creation completed, listener was configured separately.

Posted in 12c Database | Leave a comment

[INS-20802] Automatic Storage Management Configuration Assistant failed

[INS-20802] Automatic Storage Management Configuration Assistant failed

—————————

Error Synmtoms

—————————

Error Descriptor: During 12c Grid Installer,

  1. At 96% installation failed with error “[INS-20802] Automatic Storage Management Configuration Assistant failed”
  2. trace file shows(GIM-00090 OS-dependent operation:open failed with status: 2)

log file shows(INFO: Read: ORA-00845: MEMORY_TARGET not supported on this system)

/u01/app/oraInventory/logs/installActions2014-12-28_03-40-26PM.log

2014-12-28 16:47:35.881939 :CLSDYNAM:1109064000: [ ora.asm]{0:0:219} [clean] Gimh::check OH /u01/app/12.1.0/grid SID +ASM

2014-12-28 16:47:35.882630 :CLSDYNAM:1109064000: [ ora.asm]{0:0:219} [clean] GIMH: GIM-00104: Health check failed to connect to instance.

GIM-00090: OS-dependent operation:open failed with status: 2

GIM-00091: OS failure message: No such file or directory

GIM-00092: OS failure occurred at: sskgmsmr_7

2014-12-28 16:47:35.882679 :CLSDYNAM:1109064000: [ ora.asm]{0:0:219} [clean] (:CLSN00006:)AsmAgent::gimhChecks failed gimh state 0

2014-12-28 16:47:35.883113 :CLSDYNAM:1109064000: [ ora.asm]{0:0:219} [clean] (:CLSN00006:)InstAgent::checkState return unplanned offline

2014-12-28 16:47:35.883241 :CLSDYNAM:1109064000: [ ora.asm]{0:0:219} [clean] ConnectionPool::stopConnection

2014-12-28 16:47:35.883293 :CLSDYNAM:1109064000: [ ora.asm]{0:0:219} [clean] ConnectionPool::removeConnection connection count 0

2014-12-28 16:47:35.883322 :CLSDYNAM:1109064000: [ ora.asm]{0:0:219} [clean] ConnectionPool::removeConnection freed 0

2014-12-28 16:47:35.883519 :CLSDYNAM:1109064000: [ ora.asm]{0:0:219} [clean] ConnectionPool::stopConnection sid +ASM status  1

2014-12-28 16:47:35.883795 :CLSDYNAM:1109064000: [ ora.asm]{0:0:219} [clean] InstAgent::stop  shutdown mode: 4

2014-12-28 16:47:35.883902 :CLSDYNAM:1109064000: [ ora.asm]{0:0:219} [clean] ConnectionPool::getConnection 160 ERROR sid +ASM pool status 1

2014-12-28 16:47:35.884054 :CLSDYNAM:1109064000: [ ora.asm]{0:0:219} [clean] InstAgent::stop pool pConnxn 00000000

2014-12-28 16:47:35.885044 :CLSDYNAM:1109064000: [ ora.asm]{0:0:219} [clean] InstConnection:InstConnection: init:01b39df0 oracleHome:/u01/app/12.1.0/grid oracleSid:+ASM instanceType:2

instanceVersion:12.1.0.2.0

*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-

/u01/app/grid/diag/crs/oel58-12c/crs/trace/ohasd_oraagent_grid.trc

WARNING: Skipping line:

INFO: Exceeded the number of arguments passed to stdin. CurrentCount:3 Total args:2

INFO: Read: Configuring ASM failed with the following message:

WARNING: Skipping line: Configuring ASM failed with the following message:

INFO: Exceeded the number of arguments passed to stdin. CurrentCount:3 Total args:2

INFO: Read: Configuring HA resource failed. The following error occurred:

WARNING: Skipping line: Configuring HA resource failed. The following error occurred:

INFO: Exceeded the number of arguments passed to stdin. CurrentCount:3 Total args:2

INFO: Read: PRCR-1079 : Failed to start resource ora.asm

WARNING: Skipping line: PRCR-1079 : Failed to start resource ora.asm

INFO: Exceeded the number of arguments passed to stdin. CurrentCount:3 Total args:2

INFO: Read: CRS-5017: The resource action “ora.asm start” encountered the following error:

WARNING: Skipping line: CRS-5017: The resource action “ora.asm start” encountered the following error:

INFO: Exceeded the number of arguments passed to stdin. CurrentCount:3 Total args:2

INFO: Read: ORA-00845: MEMORY_TARGET not supported on this system

WARNING: Skipping line: ORA-00845: MEMORY_TARGET not supported on this system

INFO: Exceeded the number of arguments passed to stdin. CurrentCount:3 Total args:2

INFO: Read: . For details refer to “(:CLSN00107:)” in “/u01/app/grid/diag/crs/oel58-12c/crs/trace/ohasd_oraagent_grid.trc”.

WARNING: Skipping line: . For details refer to “(:CLSN00107:)” in “/u01/app/grid/diag/crs/oel58-12c/crs/trace/ohasd_oraagent_grid.trc”.

INFO: Exceeded the number of arguments passed to stdin. CurrentCount:3 Total args:2

INFO: Read:

WARNING: Skipping line:

INFO: Exceeded the number of arguments passed to stdin. CurrentCount:3 Total args:2

INFO: Read: CRS-2674: Start of ‘ora.asm’ on ‘oel58-12c’ failed

WARNING: Skipping line: CRS-2674: Start of ‘ora.asm’ on ‘oel58-12c’ failed

INFO: Exceeded the number of arguments passed to stdin. CurrentCount:3 Total args:2

INFO: Read:

WARNING: Skipping line:

INFO: Exceeded the number of arguments passed to stdin. CurrentCount:3 Total args:2

INFO: Completed Plugin named: Automatic Storage Management Configuration Assistant

————————

 Solution:

————————

First of all, login as root and have a look at the filesystem:

[grid@oel58-12c logs]$ df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/mapper/VolGroup00-LogVol00

35G   15G   19G  45% /

/dev/sda1              99M   24M   71M  25% /boot

tmpfs                 1002M  0  1002M   1% /dev/shm

So we can see that tmpfs has a size of 1002M. We can change the size of that filesystem by issuing the following command (where “2g” is the size I want for my MEMORY_TARGET):

[root@oel58-12c]# mount -t tmpfs shmfs -o size=2g /dev/shm

This command (re)mounts the shmfs filesystem (check this post for more information about shmfs) with the option “size=2g“.

The shared memory file system should be big enough to accommodate the MEMORY_TARGET and MEMORY_MAX_TARGET values, or Oracle will throw the ORA-00845 error. Note that when changing

something with the mount command, the changes are not permanent.

To make the change persistent, edit your /etc/fstab file to include the option you specified above:

[root@oel58-12c]# cat /etc/fstab

[..]

tmpfs                   /dev/shm                tmpfs   size=2g        0 0

[..]

In my case, I replaced the “defaults” option with the size=2g option. After saving the file, the changes should be permanent. now…

Once the above changes done, retry the Universaler installer installation which stoped at 96%

Posted in Uncategorized | Leave a comment

Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!

Posted in Uncategorized | 1 Comment