Skip navigation.

Oracle in Action

Syndicate content
Let's do it simply...
Updated: 15 hours 24 min ago

12c RAC: Map Instances Of Policy Managed Database To Nodes

Sat, 2014-07-19 04:34

RSS content

In contrast to admin-managed databases, in case of policy managed databases, there is no predefined mapping of an instance to a node. Hence any instance can run on any node. In case we need to connect to a specific instance using OS authentication, we need to

  •  find out the node where the instance is runnin
  •   set ORACLE_SID to the instance name
  •   Connect to the instance locally.

Now this problem can be resolved by mapping the instances to specific nodes.

Here is the demonstration :

– check that there is no mapping of instance names to hostnames

[oracle@host01 ~]$ srvctl status database -d cdb1
Instance cdb1_1 is running on node host01
Instance cdb1_2 is running on node host03
Instance cdb1_3 is running on node host02

– Configure instance cdb1_2 to run on host02 only

[oracle@host01 ~]$  srvctl modify instance -db cdb1 -instance cdb1_2  -node host02

– check that instance cdb1_2 has relocated to  host02

– The srvctl command reports the following :

  •   host01 is hosting cdb1_1 as earlier
  •   host02 is hosting 2 instances – cdb1_2 ( relocated), and cdb1_3 ( earlier)
  •   host03 which was hosting cdb1_2 does not host any instance presently
[oracle@host01 ~]$ srvctl status database -d cdb1

Instance cdb1_1 is running on node host01
Instance cdb1_2 is running on node host02
Instance cdb1_3 is running on node host02
Database cdb1 is not running on node host03

– Let’s verify if instance cdb1_2 has already stopped on host03

– Let’s check if service cdb1_2 is no longer registered with listener on host03
– But that is not so  : cdb1_2 is still registered with listener on host03

[oracle@host03 ~]$ lsnrctl stat

...

=(PROTOCOL=tcp)(HOST=192.9.201.241)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 2 handler(s) for this service...
Service "cdb1" has 1 instance(s).
  Instance "cdb1_2", status READY, has 1 handler(s) for this service...
Service "cdb1XDB" has 1 instance(s).
  Instance "cdb1_2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "cdb1_2", status READY, has 1 handler(s) for this service...
The command completed successfully

– Let’s check if there is any pmon process belonging to cdb1 running on host03
– Well there is still pmon process belonging to cdb1_2 running on host03

[oracle@host03 ~]$ ps -ef |grep pmon
oracle    1499     1  0 14:54 ?        00:00:00 ora_pmon_cdb1_2
oracle    2853  1261  0 15:18 pts/1    00:00:00 grep pmon
grid      6289     1  0 09:34 ?        00:00:04 asm_pmon_+ASM2

– Let’s try to connect to the instance cdb1_2 on host03 using OS authentication
– I am able to connect to cdb1_2 successfully

[oracle@host03 ~]$ export ORACLE_SID=cdb1_2

[oracle@host03 ~]$ sqlplus / as sysdba

SQL> sho parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      cdb1_2

This indicates that output of srvctl command is not reflecting the reality

— NOw let’s verify on host02 also

– Instance cdb1_3 is still registered with listener on host02

[oracle@host02 ~]$ lsnrctl stat

...

Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM3", status READY, has 2 handler(s) for this service...
Service "cdb1" has 1 instance(s).
  Instance "cdb1_3", status READY, has 1 handler(s) for this service...
Service "cdb1XDB" has 1 instance(s).
  Instance "cdb1_3", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "cdb1_3", status READY, has 1 handler(s) for this service...
The command completed successfully

– pmon process of instance cdb1_3 is still running on host02 as earlier

[oracle@host02 ~]$ ps -ef |grep pmon

oracle   13118     1  0 14:59 ?        00:00:00 ora_pmon_cdb1_3
oracle   15576 11818  0 15:23 pts/2    00:00:00 grep pmon
grid     16913     1  0 10:07 ?        00:00:04 asm_pmon_+ASM3

– Using OS authentication, I amable to connect to instance cdb1_3 as earlier

[oracle@host02 ~]$ export ORACLE_SID=cdb1_3

[oracle@host02 ~]$ sqlplus / as sysdba

SQL> sho parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      cdb1_3

– Let’s try to stop and restart database
– Instance cannot be started on host03

[oracle@host01 ~]$ srvctl stop database -d cdb1

[oracle@host01 ~]$ srvctl start database -d cdb1

PRCR-1079 : Failed to start resource ora.cdb1.db
CRS-5017: The resource action "ora.cdb1.db start" encountered the following error: 
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0
. For details refer to "(:CLSN00107:)" in "/u01/app/12.1.0/grid/log/host03/agent/crsd/oraagent_oracle/oraagent_oracle.log".

CRS-2674: Start of 'ora.cdb1.db' on 'host03' failed
CRS-2632: There are no more servers to try to place resource 'ora.cdb1.db' on that would satisfy its placement policy
 
[oracle@host01 ~]$ srvctl status database -d cdb1

Instance cdb1_1 is running on node host01
Instance cdb1_2 is running on node host02
Database cdb1 is not running on node host03

– May be it is trying to start the same instance as earlier i.e. cdb1_2 on host03
– but since the instance cdb1_2 has already been started on host02, it is failing

– Let’s configure instance cdb1_3 to run on host03 and then attempt to   restart the instance on host03 – it works now

[oracle@host01 ~]$ srvctl modify instance -i cdb1_3 -d cdb1 -n host03
[oracle@host01 ~]$ srvctl start instance -i cdb1_3 -d cdb1 
[oracle@host01 ~]$ srvctl status database -d cdb1
Instance cdb1_1 is running on node host01
Instance cdb1_2 is running on node host02
Instance cdb1_3 is running on node host03

– Now let’s stop and restart the database once again and check the instance to node mapping

–Now it can be seen that instances cdb1_2 and cdb1_3 are running on the configured hosts only
i.e. host02 and host03 respectively

[oracle@host01 ~]$ srvctl status database -d cdb1

Instance cdb1_1 is running on node host01
Instance cdb1_2 is running on node host02
Instance cdb1_3 is running on node host03

Hence it can be inferred (my understanding) that after assigning instances to different hosts, we need to stop and restart the database for the mapping to actually be effective.
In the meanwhile, output of srvctl command may be misleading.

This mapping makes it very convenient to connect to the desired instance using OS authentication as we don’t need to check the instance currently runing on a host.

I hope this post was useful.

Your comments and suggestions are always welcome!

References:

http://docs.oracle.com/cd/E16655_01/rac.121/e17887/admin.htm#RACAD817
http://docs.oracle.com/cd/E16655_01/rac.121/e17887/srvctladmin.htm#RACAD7795

—————————————————————————————

Related Links:

Home

12c RAC Index

 

—————–



Tags:  

Del.icio.us
Digg

Comments:  2 comments on this itemYou might be interested in this:  
Copyright © ORACLE IN ACTION [12c RAC: Map Instances Of Policy Managed Database To Nodes], All Right Reserved. 2014.

The post 12c RAC: Map Instances Of Policy Managed Database To Nodes appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Oracle Critical Patch Update Advisory – July 2014

Wed, 2014-07-16 23:52

RSS content

Oracle has released July Critical Patch Update on 15 July 2014.

This Critical Patch Update provides 113 new security fixes across a wide range of product families including: Oracle Database, Oracle Fusion Middleware, Oracle Hyperion, Oracle Enterprise Manager Grid Control, Oracle E-Business Suite, Oracle PeopleSoft Enterprise, Oracle Siebel CRM, Oracle Industry Applications, Oracle Java SE, Oracle Linux and Virtualization, Oracle MySQL, and Oracle and Sun Systems Products Suite.

For more details, please click here.



Tags:  

Del.icio.us
Digg

Comments:  0 (Zero), Be the first to leave a reply!You might be interested in this:  
Copyright © ORACLE IN ACTION [Oracle Critical Patch Update Advisory - July 2014], All Right Reserved. 2014.

The post Oracle Critical Patch Update Advisory – July 2014 appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

ORA-09925: Unable to create audit trail file

Sat, 2014-07-12 03:33

RSS content

I received this error message when I started my virtual machine and tried to logon to my database as sysdba to startup the instance.
[oracle@node1 ~]$ sqlplus / as sysdba

ERROR:
ORA-09925: Unable to create audit trail file
Linux Error: 30: Read-only file system
Additional information: 9925
ORA-09925: Unable to create audit trail file
Linux Error: 30: Read-only file system
Additional information: 9925

- I rebooted my machine and got following messages which pointed to some errors encountered during filesystem check and instructed to run fsck manually.

[root@node1 ~]# init 6

Checking filesystems

/: UNEXPECTED INCONSISTENCY; RUN fsck MANUALLY.
(i.e., without -a or -p options)
*** An error occurred during the filesystem check.
*** Dropping you to a shell; the system will reboot
*** when you leave the shell.
Give root password for maintenance
(or type Control-D to continue):

– I entered password for root to initiate filesystem check. As a result I was prompted multiple no. of times to allow fixing of  various filesystem errors.

(Repair filesystem) 1 # fsck
Fix(y)?

- After all the errors had been fixed, filesystem check was restarted

Restarting e2fsck from the beginning...

/: ***** FILE SYSTEM WAS MODIFIED *****
/: ***** REBOOT LINUX *****

- After filesystem had been finally checked to be correct, I exited for reboot to continue.

(Repair filesystem) 2 # exit

– After the reboot, I could successfully connect to my database as sysdba .

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 12 09:21:52 2014

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

Connected to an idle instance.

SQL>

I hope this post was useful.

Your comments and suggestions are always welcome.

—————————————————————————————–

Related Links:

Home

Database Index

 

————-

 



Tags:  

Del.icio.us
Digg

Comments:  0 (Zero), Be the first to leave a reply!You might be interested in this:  
Copyright © ORACLE IN ACTION [ORA-09925: Unable to create audit trail file], All Right Reserved. 2014.

The post ORA-09925: Unable to create audit trail file appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

12c RAC: ORA-15477: cannot communicate with the volume driver

Fri, 2014-06-13 01:02

RSS content

I received ORA-15477 when I was trying to set attribute  compatible.advm  for DATA diskgroup to 12.1

– Using SQL –

SQL> alter diskgroup DATA set attribute 'compatible.advm'='12.1';

alter diskgroup DATA set attribute 'compatible.advm'='12.1'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15242: could not set attribute compatible.advm
ORA-15238: 12.1 is not a valid value for attribute compatible.advm
ORA-15477: cannot communicate with the volume driver

– using ASMCMD

ASMCMD> setattr -G DATA compatible.advm 12.1

ORA-15032: not all alterations performed
ORA-15242: could not set attribute compatible.advm
ORA-15238: 12.1 is not a valid value for attribute compatible.advm
ORA-15477: cannot communicate with the volume driver (DBD ERROR: OCIStmtExecute)
 A

– I tried to load advm drivers : again got error as version oracleasm rpm’s installed
did not match that of the OS

[root@host01 bin]# /u01/app/12.1.0/grid/bin/acfsload start

ACFS-9459: ADVM/ACFS is not supported on this OS version: '2.6.32-100.26.2.el5'

– checked version of oracleasm rpm’s installed – 2.6.18.238.el5

[grid@host01 bin]$ rpm -qa |grep oracleasm

oracleasm-2.6.18-238.el5-2.0.5-1.el5
oracleasmlib-2.0.4-1.el5
oracleasm-2.6.18-238.el5debug-2.0.5-1.el5
oracleasm-2.6.18-238.el5-debuginfo-2.0.5-1.el5
oracleasm-2.6.18-238.el5xen-2.0.5-1.el5
oracleasm-support-2.1.7-1.el5

– Checked kernel version : Kernel version = 2.6.32-100.26.2.el5 : different from oracleasm rpms (2.6.18-238.el5-2.0.5-1.el5)

[grid@host01 bin]$ uname -a
Linux host01.example.com 2.6.32-100.26.2.el5 #1 SMP Tue Jan 18 20:11:49 EST 2011 x86_64 x86_64 x86_64 GNU/Linux

– checked /etc/grub.conf : Two kernels were there and by default 2.6.32-100.26.2.el5 was getting loaded

[root@host01 bin]# cat /etc/grub.conf

# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /boot/, eg.
#          root (hd0,0)
#          kernel /vmlinuz-version ro root=/dev/sda2
#          initrd /initrd-version.img
#boot=/dev/sda
default=0
timeout=5
splashimage=(hd0,0)/grub/splash.xpm.gz
hiddenmenu
title Oracle Linux Server (2.6.32-100.26.2.el5uek)
root (hd0,0)
kernel /vmlinuz-2.6.32-100.26.2.el5 ro root=/dev/sda2 rhgb quiet
initrd /initrd-2.6.32-100.26.2.el5.img
title Oracle Linux Server-base (2.6.18-238.el5)
root (hd0,0)
kernel /vmlinuz-2.6.18-238.el5 ro root=LABEL=/ rhgb quiet
initrd /initrd-2.6.18-238.el5.img

– Edited /etc/grub.conf and modified default=1 so that kernel 2.6.18-238.el5 was loaded at boot time

# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /boot/, eg.
#          root (hd0,0)
#          kernel /vmlinuz-version ro root=/dev/sda2
#          initrd /initrd-version.img
#boot=/dev/sda

default=1

timeout=5
splashimage=(hd0,0)/grub/splash.xpm.gz
hiddenmenu
title Oracle Linux Server (2.6.32-100.26.2.el5uek)
root (hd0,0)
kernel /vmlinuz-2.6.32-100.26.2.el5 ro root=/dev/sda2 rhgb quiet
initrd /initrd-2.6.32-100.26.2.el5.img
title Oracle Linux Server-base (2.6.18-238.el5)
root (hd0,0)
kernel /vmlinuz-2.6.18-238.el5 ro root=LABEL=/ rhgb quiet
initrd /initrd-2.6.18-238.el5.img

– Rebooted and verified that loaded  kernel is 2.6.18-238.el5

[root@host01 ~]# init 6

uname -a
Linux host01.example.com 2.6.18-238.el5 #1 SMP Tue Jan 415:41:11 EST 2011 x86_64 x86_64 x86_64 GNU/Linux

- Checked that oracleacfs and oracleadvm modules were not loaded

[root@host01 ~]# lsmod |grep oracle
oracleasm              84136  1

– Tried to load ADVM modules – got error as ADVM/ACFS was not installed

[root@host01 ~]# $ORACLE_HOME/bin/acfsload start
ACFS-9391: Checking for existing ADVM/ACFS installation.
ACFS-9129: ADVM/ACFS not installed

– Used oerr to diagnose ACFS-9129

[root@host01 ~]# oerr ACFS 9129

09129, 0, "ADVM/ACFS not installed"
// *Cause:  No ADVM/ACFS drivers or commands have been found on the system.
//          All ADVM and ACFS actions will be disabled.
// *Action: Install components using 'acfsroot install <options>'.  No ACFS
//          file systems or ADVM volume devices will be available until
//          ADVM/ACFS has been installed.

– Installed ADVM/ACFS as advised by oerr

[root@host01 ~]# acfsroot install -h

ACFS-9161:  acfsroot install: Install ADVM/ACFS components.
ACFS-9185:  Usage: acfsroot install [-h] [-s | -v | -t <0,1,2>] [-l <directory>]
ACFS-9132:         [-h]             - print help/usage information
ACFS-9131:         [-s]             - silent mode (error messages only)
ACFS-9159:         [-v]             - verbose mode
ACFS-9332:         [-l <directory>] - location of the installation directory
ACFS-9189:         [-t <0,1,2> ]    - trace level
[root@host01 ~]# acfsroot install -v

ACFS-9500: Location of Oracle Home is '/u01/app/12.1.0/grid' as determined from the internal configuration data
ACFS-9505: Using acfsutil executable from location: '/u01/app/12.1.0/grid/usm/install/cmds/bin/acfsutil'
ACFS-9300: ADVM/ACFS distribution files found.
ACFS-9155: Checking for existing 'oracleoks.ko' driver installation.
ACFS-9155: Checking for existing 'oracleadvm.ko' driver installation.
ACFS-9155: Checking for existing 'oracleacfs.ko' driver installation.
ACFS-9307: Installing requested ADVM/ACFS software.
ACFS-9503: ADVM and ACFS driver media location is '/u01/app/12.1.0/grid/usm/install/Oracle/EL5/x86_64/2.6.18-8/2.6.18-8.el5-x86_64/bin'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/Oracle/EL5/x86_64/2.6.18-8/2.6.18-8.el5-x86_64/bin/oracleadvm.ko' to the path '/lib/modules/2.6.18-8.el5/extra/usm/oracleadvm.ko'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/Oracle/EL5/x86_64/2.6.18-8/2.6.18-8.el5-x86_64/bin/oracleoks.ko' to the path '/lib/modules/2.6.18-8.el5/extra/usm/oracleoks.ko'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/Oracle/EL5/x86_64/2.6.18-8/2.6.18-8.el5-x86_64/bin/oracleacfs.ko' to the path '/lib/modules/2.6.18-8.el5/extra/usm/oracleacfs.ko'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/advmutil' to the path '/sbin/advmutil'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/advmutil.bin' to the path '/sbin/advmutil.bin'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/fsck.acfs' to the path '/sbin/fsck.acfs'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/fsck.acfs.bin' to the path '/sbin/fsck.acfs.bin'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/mkfs.acfs' to the path '/sbin/mkfs.acfs'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/mkfs.acfs.bin' to the path '/sbin/mkfs.acfs.bin'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/mount.acfs' to the path '/sbin/mount.acfs'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/mount.acfs.bin' to the path '/sbin/mount.acfs.bin'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/acfsdbg' to the path '/sbin/acfsdbg'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/acfsdbg.bin' to the path '/sbin/acfsdbg.bin'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/acfsutil' to the path '/sbin/acfsutil'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/acfsutil.bin' to the path '/sbin/acfsutil.bin'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/umount.acfs' to the path '/sbin/umount.acfs'
ACFS-9504: Copying file '/u01/app/12.1.0/grid/usm/install/cmds/bin/umount.acfs.bin' to the path '/sbin/umount.acfs.bin'
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9321: Creating udev for ADVM/ACFS.
ACFS-9323: Creating module dependencies - this may take some time.

- Checked that  ACFS/ADVM modules have been loaded

[root@host01 ~]# lsmod |grep oracle

 oracleacfs           2837904  0
oracleadvm            342512  2
oracleoks             409560  2 oracleacfs,oracleadvm
oracleasm              84136  1

– Now I could change the attribute compaible.advm for diskgroup DATA to 12.1 successfully

SQL> alter diskgroup DATA set attribute 'compatible.advm'='12.1';

Diskgroup altered.

I hope this post was useful.

Your comments and suggestions are always welcome.

————————————————————————————————————————

Related Links:

Home

12c RAC Index

 

————————–



Tags:  

Del.icio.us
Digg

Comments:  0 (Zero), Be the first to leave a reply!You might be interested in this:  
Copyright © ORACLE IN ACTION [12c RAC: ORA-15477: cannot communicate with the volume driver], All Right Reserved. 2014.

The post 12c RAC: ORA-15477: cannot communicate with the volume driver appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Oracle Enterprise Manager Cloud Control 12c Release 4 Released

Tue, 2014-06-03 08:39

RSS content

The long awaited OEM 12c R4 has finally been released ….

You can download it here and experience a whole lot of its new features.

To find out about its new features , click here for oracle documentation.

Click here for the video Introducing  OEM 12c Release 4.

References:

https://blogs.oracle.com/partnernews/entry/introducing_oracle_enterprise_manager_12c?utm_source=feedburner&utm_medium=email&utm_campaign=Feed%3A+orana+%28OraNA%29

http://oramanageability.wordpress.com/2014/06/03/whats-new-in-oem-12c-release-4-12-1-0-4/?utm_source=feedburner&utm_medium=email&utm_campaign=Feed%3A+orana+%28OraNA%29

———————————————————————————————–

Related links :

Home

OraNews

 



Tags:  

Del.icio.us
Digg

Comments:  0 (Zero), Be the first to leave a reply!You might be interested in this:  
Copyright © ORACLE IN ACTION [Oracle Enterprise Manager Cloud Control 12c Release 4 Released], All Right Reserved. 2014.

The post Oracle Enterprise Manager Cloud Control 12c Release 4 Released appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

12c: USE_SID_AS_SERVICE_listener_name

Fri, 2014-05-30 03:58

RSS content

In an Oracle 12c container database, the client must specify a service name in order to connect to the PDB. Many  Database clients with earlier releases of Oracle Database might have hard-coded connect descriptors to use SID to connect to the database. Now if they choose to plug in their non-CDB’s into a CDB, they will have to modify the connect descriptors in tnsnames.ora on all the client sites.

This problem can be resolved by the use of listener.ora parameter USE_SID_AS_SERVICE_listener_name.  Setting this parameter to on instructs the listener to use the SID in the connect descriptor as a service name and connect the client to the specified database. The default value for this parameter is off and it has to be explicitly set to on to use this functionality.

Here is the demonstration :

Presently, I have a CDB called CDB1 into which I have plugged in a non-CDB (ncdb).

SQL> sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb1
SQL> sho pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED                       READ ONLY  NO
3 NCDB                           READ WRITE NO

Currently connect descriptor in tnsnames.ora entry for ncdb points to SID as follows:

NCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ncdb)
)
)

In listener.ora , since there is no entry for USE_SID_AS_SERVICE_listener_name, it defaults to off.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

– If I try to connect to NCDB, I get following error :

[oracle@host01 ~]$ export ORACLE_SID=ncdb
[oracle@host01 ~]$ sqlplus system/oracle@ncdb

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 30 15:24:00 2014

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

ERROR:
 ORA-12505: TNS:listener does not currently know of SID given in connect
 descriptor

– I  edited  listener.ora to set USE_SID_AS_SERVICE_LISTENER to on

USE_SID_AS_SERVICE_LISTENER=on
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

[oracle@host01 admin]$ lsnrctl reload

[oracle@host01 admin]$ lsnrctl stat

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 30-MAY-2014 15:24:55

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host01.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                29-MAY-2014 11:03:14
Uptime                    1 days 4 hr. 21 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/host01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host01.example.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=host01.example.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "cdb1" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "em12rep" has 1 instance(s).
Instance "em12rep", status READY, has 1 handler(s) for this service...
Service "em12repXDB" has 1 instance(s).
Instance "em12rep", status READY, has 1 handler(s) for this service...
Service "ncdb" has 1 instance(s).
 Instance "cdb1", status READY, has 1 handler(s) for this service...
The command completed successfully

– Now I can connect  to NCDB easily ..

[oracle@host01 ~]$ sqlplus system/oracle@ncdb

SQL> sho con_name

CON_NAME
------------------------------
NCDB

Hope this post was useful.

Your comments and suggestions are always welcome!!

References:

http://docs.oracle.com/cd/E16655_01/network.121/e17611/listener.htm#NETRF2090

http://www.freelists.org/post/oracle-l/DB12c-in-Production,21

——————————————————————————————

Related Links:

Home

Database 12c Index

12c : Connecting to CDB/PDB – Set Container vs Connect
12c : Connecting To PDB’s With Same Name

—————————————————————————

 

 

 



Tags:  

Del.icio.us
Digg

Comments:  0 (Zero), Be the first to leave a reply!You might be interested in this:  
Copyright © ORACLE IN ACTION [12c: USE_SID_AS_SERVICE_listener_name], All Right Reserved. 2014.

The post 12c: USE_SID_AS_SERVICE_listener_name appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

12c : PDB cannot share CDB’s temporary tablespace

Tue, 2014-04-29 00:19

RSS content

As per Oracle 12c documentation,  a PDB can

- have its own local temporary tablespace, or

- if it does not have its own  temporary tablespace, it can share the temporary tablespace with the CDB.

To demonstrate a PDB sharing the temporary tablespace of CDB,  the  first step is to create a PDB without a temporary tablespace. By default when a PDB is created from seed, it is created with its local temporary tablespace TEMP and it cannot be dropped as it is the default temporary tablespace of the PDB.

So, the only options I could think of were to

Method – I

  • Create a non-CDB without temporary tablespace and the plug it into a CDB

Method – II

  • Create a non-CDB / PDB with temporary tablespace,
  • Generate its xml file using dbms_PDB
  •  Edit the xml file to remove the entry for temporary tablespace
  •   Plug in the non-CDB into a CDB

I will demonstrate the second method. For this purpose, I created a non-CDB orcl2 using DBCA so that it contained default temporary tablespace temp.

– Open the non-CDB in read only mode

ORCL2> shu immediate;
             startup mount;
             alter database open read only;

– Generate an XML metadata file for the non-CDB

ORCL2>exec dbms_pdb.describe ('/u01/app/oracle/oradata/orcl2/orcl2.xml');

– Edit the xml file to remove the entry for temp tablespace

[oracle@em12 ~]$ vi /u01/app/oracle/oradata/orcl2/orcl2.xml

– Use the xml file to plug in the non-CDB into  CDB2 as PDB_ORCL2

CDB$ROOT@CDB2>create pluggable database pdb_orcl2 using '/u01/app/oracle/oradata/orcl2/orcl2.xml'  nocopy;

– Connect to PDB_ORCL2 and run the “$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql” script to clean up the new PDB, removing any items that should not be present in a PDB.

CDB$ROOT@CDB2>connect sys/oracle@pdb_orcl2 as sysdba

PDB_ORCL2@CDB2> @?/rdbms/admin/noncdb_to_pdb

PDB_ORCL2@CDB2> alter pluggable database open;

– Check that data from non-CDB is available in the new PDB

PDB_ORCL2@CDB2>select count(empno) from scott.emp;

COUNT(EMPNO)
------------
14

– Verify that temporary tablespace has not been created in PDB_ORCL2

PDB_ORCL2@CDB2> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
USERS
EXAMPLE

– Verify that tempfile has not been created in PDB_ORCL2

PDB_ORCL2@CDB2> select name from v$tempfile;

no rows selected

So, I was able to create a PDB without temporary tablespace. Now I wanted to check if PDB_ORCL2 uses the temp tablespace of the CDB.

– First check that default temporary tablespace TEMP exists for the CDB

CDB$ROOT@CDB2> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

CDB$ROOT@CDB2>  select PROPERTY_NAME, PROPERTY_VALUE

from database_properties
where upper(PROPERTY_NAME) like '%TEMP%';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ---------------
DEFAULT_TEMP_TABLESPACE        TEMP

– Set pga_aggregate_target to its lowest allowed value of 10m to force a sort to spill to the temporary tablespace.

 CDB$ROOT@CDB2> alter system set pga_aggregate_target=10m;

– Issue a query in PDB_ORCL2 which will spill to temporary tablespace

PDB_ORCL2@CDB2> select * from dba_objects order by 1,2,3,4,5,6,7;
select * from dba_objects order by 1,2,3,4,5,6,7
*
ERROR at line 1:
ORA-00959: tablespace 'TEMP' does not exist

I expected the query to use temporary tablespace TEMP of CDB but it failed as it expected temp tablespace to exist locally for PDB as its default temporary tablespace is set to TEMP (as was in non-CDB)

PDB_ORCL2@CDB2> col property_name for a30
PDB_ORCL2@CDB2> col property_value for a15
PDB_ORCL2@CDB2> l
1  select PROPERTY_NAME, PROPERTY_VALUE from database_properties
2*  where upper(PROPERTY_NAME) like '%TEMP%'
PDB_ORCL2@CDB2> /

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ---------------
DEFAULT_TEMP_TABLESPACE        TEMP

 – I tried to modify the default temporary tablespace to cdb$root:temp but that attempt also failed.

PDB_ORCL2@CDB2> alter database default temporary tablespace cdb$root:temp;
alter database default temporary tablespace cdb$root:temp
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

Hence, I was able to create a PDB without temporary tablespace but could not make it use the temporary tablespace of CDB.

References:

Oracle documentation

———————————————————————————————————————–

Related Links:

Home

Database 12c Index

——————————————————–—————————

 



Tags:  

Del.icio.us
Digg

Comments:  3 comments on this itemYou might be interested in this:  
Copyright © ORACLE IN ACTION [12c : PDB cannot share CDB's temporary tablespace], All Right Reserved. 2014.

The post 12c : PDB cannot share CDB’s temporary tablespace appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

12c: Connecting to CDB/PDB – Set Container Vs Connect

Thu, 2014-04-24 03:29

RSS content

In Oracle 12c, you can connect to a PDB using two methods :

- Switch the container using Alter system set container …

- Use connect command to connect to PDB using network alias

Let’s compare the two methods :

The use of SET CONTAINER avoids the need to create a new connection from scratch.

If there is an existing connection to a PDB / CDB$root, the same connection can be used to connect to desired PDB / CDB$root.

– Connect to CDB

[oracle@em12 ~]$ sqlplus system/oracle@cdb1

CDB$ROOT@CDB1> sho con_name

CON_NAME
------------------------------
CDB$ROOT

– Check the PID for the process created on the operating system

[oracle@em12 ~]$ ps -ef |grep LOCAL |grep -v grep

oracle 23271 1 0 10:23 ? 00:00:00 oraclecdb1 (LOCAL=NO)

– Change the container to PDB1 using Set container

CDB$ROOT@CDB1> alter session set container=pdb1;

sho con_name

CON_NAME
------------------------------
PDB1

– Check that the operating system PID remains the same as earlier connection is reused and a new connection has not been created

[oracle@em12 ~]$ ps -ef |grep LOCAL |grep -v grep

oracle 23271 1 0 10:23 ? 00:00:00 oraclecdb1 (LOCAL=NO)

– Switch the container back to cdb$root using connect

CDB$ROOT@CDB1> conn system/oracle@cdb1
sho con_name

CON_NAME
------------------------------
CDB$ROOT

– Check that a new operating system PID has been created as a new  connection  has been created

[oracle@em12 ~]$ ps -ef |grep LOCAL |grep -v grep

oracle 23409 1 0 10:29 ? 00:00:00 oraclecdb1 (LOCAL=NO)
glogin.sql is not executed when Alter session set container is used

To demonstrate it, I have added following lines to my glogin.sql to display CDB/PDB name in SQL prompt:

define gname=idle
column global_name new_value gname
set heading off
set termout off
col global_name noprint
select upper(sys_context ('userenv', 'con_name') || '@' || sys_context('userenv', 'db_name')) global_name from dual;
set sqlprompt '&gname> '
set heading on
set termout on

- Let’s connect to PDB1 using “Connect” and verify that glogin.sql is executed and prompt displays CDB/PDB name

 

SQL> conn sys/oracle@pdb1 as sysdba
PDB1@CDB1> 

- Verify that the prompt displays current container (PDB1) and container database (CDB1)

PDB1@CDB1> sho con_name
PDB1

PDB1@CDB1> sho parameter db_name
db_name                              string      cdb1

– Now let’s connect to PDB2 using Alter session set container and verify that glogin.sql is not executed and the same prompt as earlier is displayed

PDB1@CDB1>  alter session set container=pdb2;

 Session altered.
PDB1@CDB1> sho con_name
CON_NAME

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

 PDB2  
-- Let's connect to PDB2 using connect and verify that glogin.sql is executed as the prompt displays the PDB name PDB2

 PDB1@CDB1> connect sys/oracle@pdb2 as sysdba

 PDB2@CDB1> 
Pending transactions are not committed when Alter system set container is used

– Let’s start a transaction in PDB1

PDB1@CDB1> create table pdb1_tab(x number);
Table created.
PDB1@CDB1> insert into pdb1_tab values (1);
1 row created.

– Switch the container to PDB2

PDB1@CDB1> alter session set container=pdb2;

– Try to start another transaction on PDB2 – does not allow as an active transaction exists in the parent container PDB1

PDB1@CDB1> create table pdb2_tab (x number);

 create table pdb2_tab (x number)

 *

 ERROR at line 1:

 ORA-65023: active transaction exists in container PDB1

– In another session check that the transaction was not committed and no rows are visible in table pdb1_tab

CDB$ROOT@CDB1> conn system/oracle@pdb1

 PDB1@CDB1> select * from pdb1_tab;
no rows selected
 Alter session set container cannot be used by local users

 

– Try to give set container privilege to a local user HR in PDB2 – fails as common privilege cannot be granted to a local user and hence a local user cannot user alter session set container to connect to another PDB

PDB2@CDB1> connect system/oracle@pdb2

 PDB2@CDB1> grant set container to hr container=all;

 grant set container to hr container=all

 *

 ERROR at line 1:

 ORA-65030: one may not grant a Common Privilege to a Local User or Role

I hope this post was useful.

Your comments and suggestions are always welcome.

References :

http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_admin.htm#ADMIN13970

———————————————————————————-

Related Links:

Home

Database 12c Index

——————————————————–

 



Tags:  

Del.icio.us
Digg

Comments:  0 (Zero), Be the first to leave a reply!You might be interested in this:  
Copyright © ORACLE IN ACTION [12c: Connecting to CDB/PDB - Set Container Vs Connect], All Right Reserved. 2014.

The post 12c: Connecting to CDB/PDB – Set Container Vs Connect appeared first on ORACLE IN ACTION.

Categories: DBA Blogs