Feed aggregator

PHP and Oracle DB: RPMs for Easy Install on Linux

Christopher Jones - Tue, 2018-02-06 22:05

The Oracle Linux team is now making updated PHP RPMs available on yum.oracle.com.  The good news: an RPM for the PHP OCI8 extension is included.  Yay!  My colleague, Sergio, has a blog post Connect PHP 7.2 to Oracle Database 12c using Oracle Linux Yum Server with the details.

JAN18: Database 11gR2 PSU, 12cR1 ProactiveBP, 12cR2 RU

Yann Neuhaus - Tue, 2018-02-06 15:32

If you want to apply the latest patches (and you should), you can go to the My Oracle Support Recommended Patch Advisor. But sometimes it is not up-todate. For example, for 12.1.0.2 only the PSU is displayed and not the Proactive Bundle Patch, which is highly recommended. And across releases, the names have changed and can be misleading: PSU for 11.2.0.4 (no Proactive Bundle Patch except for Engineered Systems). 12.1.0.2 can have SPU, PSU, or Proactive BP but the latest is highly recommended, especially now that it includes the adaptive statistics patches. 12.2.0.1 introduce the new RUR and RU, the latest one being the one recommended.

To get things clear, there’s also the Master Note for Database Proactive Patch Program, with reference to one note per release. This blog post is my master note to link directly to the recommended updates for Oracle Database.

Master Note for Database Proactive Patch Program (Doc ID 756671.1)
https://support.oracle.com/epmos/faces/DocContentDisplay?id=756671.1

11.2.0.4 – PSU

Database 11.2.0.4 Proactive Patch Information (Doc ID 2285559.1)
https://support.oracle.com/epmos/faces/DocContentDisplay?id=2285559.1
Paragraph -> 11.2.0.4 Database Patch Set Update

Latest as of Q1 2018 -> 16-Jan-2018 11.2.0.4.180116 (Jan 2018) Database Patch Set Update (DB PSU) 26925576 (Windows: 26925576)

12.1.0.2  – ProactiveBP

Database 12.1.0.2 Proactive Patch Information (Doc ID 2285558.1)
https://support.oracle.com/epmos/faces/DocContentDisplay?id=2285558.1
Paragraph -> 12.1.0.2 Database Proactive Bundle Patches (DBBP)

Latest as of Q1 2018 -> 16-Jan-2018 12.1.0.2.180116 Database Proactive Bundle Patch (Jan 2018) 12.1.0.2.180116 27010930

12.2.0.1 – RU

Database 12.2.0.1 Proactive Patch Information (Doc ID 2285557.1)
https://support.oracle.com/epmos/faces/DocContentDisplay?id=2285557.1
Paragraph -> 12.2.0.1 Database Release Update (Update)

Latest as of Q1 2018 -> 16-Jan-2018 12.2.0.1.180116 (Jan 2018) Database Release Update 27105253 (Windows: 12.2.0.1.180116 WIN DB BP 27162931)
 

Don’t forget SQL Developer

In the 12c Oracle Home SQL Developer is installed, but you should update it to the latest version.
Download the following from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
– The SQL Developer zip for ‘Other Platforms’, currently 17.4
– The SQLcl zip for ‘All Platforms’, currently 17.4

On the server, remove, or rename the original directory:
mv $ORACLE_HOME/sqldeveloper $ORACLE_HOME/sqldeveloper.orig

Unzip what you have downloaded:
unzip -d $ORACLE_HOME/ sqldeveloper-*-no-jre.zip
unzip -d $ORACLE_HOME/sqldeveloper sqlcl-*-no-jre.zip

I suggest to have a login.sql which sets the beautiful ansiconsole for SQLcl:

echo "set sqlformat ansiconsole" > $ORACLE_HOME/sqldeveloper/sqlcl/login.sql

On 12.2 you can run SQLcl just with ‘sql’ (and same arguments as sqlplus: / as sysdba or /nolog,…) because this is what is defined in $ORACLE_HOME/bin.
However, it sets the current working directory and i prefer to keep the current one as it is probably were I want to run scripts from.

Then I add the following aliases in .bashrc

alias sqlcl='JAVA_HOME=$ORACLE_HOME/jdk SQLPATH=$ORACLE_HOME/sqldeveloper/sqlcl bash $ORACLE_HOME/sqldeveloper/sqlcl/bin/sql'
alias sqldev='$ORACLE_HOME/sqldeveloper/sqldeveloper.sh'

When running SQL Developer for the first time you can create automatically a ‘/ as sysdba’ connection (but remember this is not a good practice to connect like this) and a connection for each user declared in the database: Right click on Connections and Create Local Connections

 

Cet article JAN18: Database 11gR2 PSU, 12cR1 ProactiveBP, 12cR2 RU est apparu en premier sur Blog dbi services.

New OA Framework 12.2.5 Update 19 Now Available

Steven Chan - Tue, 2018-02-06 11:46

Web-based content in Oracle E-Business Suite Release 12 runs on the Oracle Application Framework (also known as OA Framework, OAF, or FWK) user interface libraries and infrastructure. Since the initial release of Oracle E-Business Suite Release 12.2 in 2013, we have released a number of cumulative updates to Oracle Application Framework to fix performance, security, and stability issues.

These updates are provided in cumulative Release Update Packs, and cumulative Bundle Patches that can be applied on top of the Release Update Packs. In this context, cumulative means that the latest RUP or Bundle Patch contains everything released earlier.

The latest OAF update for Oracle E-Business Suite Release 12.2.5 is now available:

Where is this update documented?

Instructions for installing this OAF Release Update Pack are in the following My Oracle Support knowledge document:

Who should apply this patch?

All Oracle E-Business Suite Release 12.2.5 users should apply this patch.  Future OAF patches for EBS Release 12.2.5 will require this patch as a prerequisite. 

What's new in this update?

This bundle patch is cumulative: it includes (TBS) fixes in total, including all fixes released in previous EBS Release 12.2.5 bundle patches.

This latest bundle patch includes fixes for following bugs/issues:

  • Tip type is not displayed in advanced search region in 'Screen Reader Optimized' accessibility mode.

Related Articles

Categories: APPS Blogs

Podcast: Women in Technology: Motivation and Momentum

OTN TechBlog - Tue, 2018-02-06 10:39

According to the National Center for Women and Information Technology (NCWIT), while 57% of professional occupations in the US were held by women in 2016, women held only 26% of professional computing occupations. Correcting that imbalance is the right thing to do, of course. But there’s another dimension to the issue that raises the stakes for getting more women into IT jobs.

“We have 80,000 graduates every year coming out of college with computer science degrees,” says Kellyn Pot’Vin-Gorman, technical intelligence manager for the office of CTO at Delphix. But US colleges and universities can’t crank out computer science grads fast enough to meet demand. “Over a million technical jobs will be here by 2020, and we’ve got nobody to fill them,” Kellyn says.

Attracting more women into software development and other technical fields will help to fill the IT jobs that will otherwise go wanting. But, perhaps due to lingering gender bias, or simple oversight, effective communication of the opportunities doesn’t always happen. “No one told me that I could do this as a career,” says Michelle Malcher, a security architect at Extreme Scale Solutions in Chicago. “No one said, ‘you can have fun with code.’”

Now that Michelle is having fun with code, she, like Kellyn, puts significant time and effort into getting the word out about the opportunities and career potential for young women. But men also have a role in that mission. “Men need to be part of the conversation. It can’t just be women talking about women's issues,” says Natalie Delemar, a senior consultant with Ernst and Young and an active supporter of women in technology. “We need to have men at the table so that they understand the importance of these issues.”

Women and men can engage in mentoring and sponsorship activities that are important in getting more women into IT roles. Heli Helskyaho, CEO of Miracle Finland and a PhD student at the University of Helsinki, is one of two mentors recently elected by computer science students at that institution. “The faculty just decided that it's time to have mentorship in the university the first time after all these years.”

But while mentoring and sponsorship are important, there are key differences. And, as Natalie observes, “women in the workplace are actually over mentored and under sponsored.”

Natalie explains that while mentoring typically focuses on career guidance and advice on educational matters, “sponsorship is when somebody actually uses their political capital to put you into positions of power to give you experiences to get ahead.”

Getting ahead is what the latest Oracle Developer Community podcast is all about, as Kellyn Pot'Vin-Gorman, Michelle Malcher, Natalie Delemar, and Heli Helskyaho, along with panel organizer and moderator Laura Ramsey, share insight on what motivated them in their IT careers, and how they lend their expertise and energy to driving momentum in the effort to draw more women into technology.

This panel discussion took place at Oracle Openworld in San Francisco on September 18, 2016.

The Panelists

(Listed alphabetically)

Natalie Delemar
Senior Consultant, Ernst and Young
President, ODTUG Board of Directors
Twitter LinkedIn Facebook Heli Helskyaho Heli Helskyaho
CEO, Miracle Finland
Oracle ACE Director
Ambassador, EMEA Oracle Usergroups Community
Twitter LinkedIn Facebook Michelle Malcher
Security Architect, Extreme Scale Solutions
Oracle ACE Director
Twitter LinkedIn Facebook Kellyn Pot'Vin-Gorman
Technical Intelligence Manager, Office of CTO, Delphix
President, Board Of Directors, Denver SQL Server User Group
Twitter LinkedIn Facebook Laura Ramsey
Manager, Database Technology and Developer Communities
Oracle America
Twitter LinkedIn Facebook   Additional Resources Coming Soon
  • DevOps: Can This Marriage be Saved? (Feb 21)
    What is the biggest threat to successful DevOps? What’s the most common DevOps mistake? Experts Nicole Forsgen, Leonid Igolnik, Alaina Prokharchyk, Baruch Sadogursky, Shay Shmeltzer, and Kelly Shortridge discuss what it takes to make DevOps work.
  • Combating Complexity
    An article in the September 2017 edition of the Atlantic warned of The Coming Software Apocalypse. Oracle's Chris Newcombe was interviewed for that article. In this podcast Chris joins Chris Richardson, Adam Bien, and Lucas Jellema to discuss heading off catastophic software failures.
Subscribe

Never miss an episode! The Oracle Developer Community Podcast is available via:

:

Materialized views and Synonyms

Tom Kyte - Tue, 2018-02-06 09:06
Good day. I need help with refresh materialized view. I created synonym MySynonym: <code>CREATE OR REPLACE SYNONYM "MyScheme"."MySynonym" FOR "MyScheme2"."SomeTable";</code> I created materialized view: <code>CREATE MATERIALIZED VIEW MyMView T...
Categories: DBA Blogs

Cast to varchar2 with utl_raw

Tom Kyte - Tue, 2018-02-06 09:06
Hi Below query converts the string into lower case. i would want to keep the case as it is .. Can you please suggest a solution for this. <code>select utl_raw.cast_to_varchar2(nlssort('PRAshantE', 'nls_sort=binary_ai')) from dual; </code> ...
Categories: DBA Blogs

Function based unique index is not used in select

Tom Kyte - Tue, 2018-02-06 09:06
Hi guys, I'm curious why the function based index is not used in the following SELECT statement. <code> SELECT USERNAME FROM USERS WHERE USERNAME = 'MyUser' AND IS_DELETED = 0; </code> Or <code> SELECT USERNAME FROM USERS WHERE USERNAME = 'M...
Categories: DBA Blogs

Accessing dynamically generated pivot columns through a cursor

Tom Kyte - Tue, 2018-02-06 09:06
Hi Tom, I have a PL/SQL report wherein the columns change dynamically (refer sample SQL: https://livesql.oracle.com/apex/livesql/s/f8gbm32e2acju7jzvuzjjh3a1) based on the parameter. Suppose if the report is run for the Q1 2018, then it will have...
Categories: DBA Blogs

how to create VIEW in a schema, have to retrieve LOB data over db_link

Tom Kyte - Tue, 2018-02-06 09:06
ORA-22992: cannot use LOB locators selected from remote tables 22992. 00000 - "cannot use LOB locators selected from remote tables" *Cause: A remote LOB column cannot be referenced. *Action: Remove references to LOBs in remote tables. ...
Categories: DBA Blogs

Deploying Oracle OEM agents 13c on windows targets (2008 R2) while OMS is on Linux

Amis Blog - Tue, 2018-02-06 09:00

Situation at customer’s site: OMS 13.2 on Oracle Linux, targets are Windows machines, and a bit ancient: Windows 2008 R2. How to deploy agents on those targets? Several methods are possible in theory. In this blog I’ll describe my efforts to determine what is really possible and what is efficient.

When using Linux targets, there’s no question how to deploy agents, there’s a wunderfull mechanism to push agents to the targets. No pain (mostly). Even to targets in the Oracle Cloud.

Using the same mechanism for Windows, we need cywin to emulate a Linux connection. That’s my first attempt, but there are other possilities to explore:

1. The above named cygwin option. Install cygwin on every Windows target host. When that’s done (automated perhaps), it should be easy to push agents.

2. Using a Windows staging server to push agents to a Windows target, from a Windows machine. Should be easier, and less labour at the target-site.

3. A silent install at every Windows target of the agent.

 

1. Cygwin option.

There is quite a bit documentation to be found and blogposts about this subject, mentioned in the resources below this post. Most striking details of the configurartion:

– you need to edit the file \oui\prov\resources\ssPaths_msplats.properties on the OMS server. Change properties like SCP_PATH, SH_PATH, CHMOD_PATH, LS_PATH, SSH_PATH, MKDIR_PATH. Small but minor detail, not mentioned in the documentation: use forward slashes instead of backslash !! D:/ instead of D:\.

– put the user of the target and password in the password file on the target:

$ /bin/mkpasswd -l -u oracle >> /etc/passwd

When Cygwin has been installed, push the agents through Setup —> add target –> manually.

image

And installling:

image

And in my case it fails unfortunately:

Execution of command d:/oracle/agent13c/ADATMP_2018-01-19_15-17-49-PM/agentDeploy.bat -ignorePrereqs ORACLE_HOSTNAME=<hostname> AGENT_BASE_DIR=d:/oracle/agent13c OMS_HOST=<hostname> EM_UPLOAD_PORT=4903 AGENT_INSTANCE_HOME=d:/oracle/agent13c/agent_inst b_doDiscovery=false START_AGENT=false b_forceInstCheck=true -force AGENT_PORT=3872 on host <hostname> Failed

At this time I did not know what went wrong, thought I ran into bug 23499235 : 13c Cloud Control Agent not Deploying on Microsoft Windows x64 2008 R2.  To investigate what went wrong, it’s best to to run the command manual at the target. But that’s what I’m basically doing in the third option, so I stopped this action of pushing the agents for the moment.

2. Using a Windows staging server

This should be the most promising option, a centralised staging server, pushing the agents to the targets. Oracle has documented this by an example:

Example Deployment of an Enterprise Manager 12.1.0.5/13c Cloud Control Agent on an MS Windows Host with the PsExec Method from an MS Windows Staging Server when the OMS is on Unix (Doc ID 2304834.1)

But there are several exellent blogs about this configuration, like here.

In general:

Choose and configure a Windows server to act as a ‘pushing’ agents server. Copy the agent software, install psexec tools, test connection to the target host, create a property file and push the agent.

  • How to obtain the agent has been excellent described by dbakevlar in this blog.
  • How to install psexec tools and test the connection has been described here.
  • The property file I used:

HOST_NAMES=<hostname>
USER_NAME=<windows domain>\oracle
PASSWORD=<password>
AGENT_IMAGE_PATH=D:\tools\staging\13.2.0.0.0_AgentCore_233.zip
AGENT_BASE_DIR=D:\oracle\agent13c
OMS_HOST=<Linux-OMS>
EM_UPLOAD_PORT=4903
AGENT_REGISTRATION_PASSWORD=<agent_passw>
PSEXEC_DIR=D:\tools\pstools
REMOTE_SYS_DIR=C:\Windows

And off we go, pushing the agent to a Windows target:

agentDeployPSExec.bat PROPERTIES_FILE=d:\tools\staging\<property_file>

But then, the following error:

Error copying D:\tools\staging\\unzip_tmp.exe to remote system:

D:\tools\pstools\psexec.exe \\<targethostname> -u <windows-domainname>\oracletab -p ****** cmd.exe /c move C:\Windows\unzip_tmp.exe D:\oracle\agent13c\ADATMP_–_–

PsExec v2.2 – Execute processes remotely

Copyright (C) 2001-2016 Mark Russinovich

Sysinternals – www.sysinternals.com

Access is denied.

And.. there’s a note for this:

EM 13c: PsExec.exe Access Is Denied When Attempting To Move C:\Windows\unzip_tmp.exe On Target Host (Doc ID 2201143.1)

Solution: Turn off UAC at the target Windows server. This is not an option for the organization , so I’ll skip this alternative….

3. A silent install at every Windows target of the agent

At first, this seemed not to be the fastest way, but after the experiences of the other options, it could be the best option.

There’s a good description how to do this  at DBAkevlar’s site, won’t repeat this in this blog.

After some struggle with missing dll’s (Windows 2008 R2), read a lot of notes about this (see below this post), got the job done.

The trick for me: add the following dll’s to a directory which is in the PATH variable:

  • perl58.dll
  • msvcp71.dll
  • msvcr71.dll
  • msvr100.dll

Conclusion: first do a silent install to ensure the installation works. After that you may decide to choose one of the other options. I decided to go for the silent install by the way.

 

Resources:

Install cygwin on Windows servers: https://docs.oracle.com/cd/E73210_01/EMBSC/GUID-B8ED3864-2CCC-4508-9E98-73E79E0E852D.htm#EMBSC152

Download cygwin: https://cygwin.com/install.html

Cygwin installation blogpost: http://www.carajandb.com/en/blogs/blog-swinkler-en/190-oem-12c-agent-deploy-on-windows-no-problem-with-cygwin

Another cygwin installation blogpost: http://www.carajandb.com/en/blogs/blog-swinkler-en/190-oem-12c-agent-deploy-on-windows-no-problem-with-cygwin

Doc ID 2304834.1: Example Deployment of an Enterprise Manager 12.1.0.5/13c Cloud Control Agent on an MS Windows Host with the PsExec Method from an MS Windows Staging Server when the OMS is on Unix

Toadword about staging server:

https://www.toadworld.com/platforms/oracle/b/weblog/archive/2014/07/04/enterprise-manager-agent-deployment-gets-better-on-windows

Download psexec tool: http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

dbakevlar, deploying standalone agents: http://dbakevlar.com/2013/10/em12c-agent-deployment-on-windows/

Access denied with psexec: EM 13c: PsExec.exe Access Is Denied When Attempting To Move C:\Windows\unzip_tmp.exe On Target Host (Doc ID 2201143.1)

The post Deploying Oracle OEM agents 13c on windows targets (2008 R2) while OMS is on Linux appeared first on AMIS Oracle and Java Blog.

Partner Webcast – Database Security Assessment Tool and GDPR: Uncover new opportunities

As part of Oracle’s defense in depth capabilities, the Oracle Database Security Assessment Tool (DBSAT) helps identify areas where your database configuration, operation, or implementation...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Multitenant, PDB, ‘save state’, services and standby databases

Yann Neuhaus - Mon, 2018-02-05 15:12

Creating – and using – your own services has always been the recommendation. You can connect to a database without a service name, though the instance SID, but this is not what you should do. Each database registers its db_unique_name as a service, and you can use it to connect, but it is always better to create your own application service(s). In multitenant, each PDB registers its name as a service, but the recommendation is still there: create your own services, and connect with your services.
I’ll show in this blog post what happens if you use the PDB name as a service and the standby database registers to the same listener as the primary database. Of course, you can workaround the non-unique service names by registering to different listeners. But this just hides the problem. The main reason to use services is to be independent from physical attributes, so being forced to assign a specific TCP/IP port is not better than using an instance SID.

I have the primary (CDB1) and standby (CDB2) databases registered to the default local listener:

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 03-FEB-2018 23:11:23
 
Copyright (c) 1991, 2016, Oracle. All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 02-FEB-2018 09:32:30
Uptime 1 days 13 hr. 38 min. 52 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/VM122/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM122)(PORT=5501))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "627f7512a0452fd4e0537a38a8c055c0" has 2 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Instance "CDB2", status READY, has 1 handler(s) for this service...
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 "CDB1_CFG" has 2 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "CDB1_DGB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1_DGMGRL" has 1 instance(s).
Instance "CDB1", status UNKNOWN, has 1 handler(s) for this service...
Service "CDB2" has 1 instance(s).
Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "CDB2XDB" has 1 instance(s).
Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "CDB2_DGB" has 1 instance(s).
Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "CDB2_DGMGRL" has 1 instance(s).
Instance "CDB2", status UNKNOWN, has 1 handler(s) for this service...
Service "pdb1" has 2 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Instance "CDB2", status READY, has 1 handler(s) for this service...
The command completed successfully

Look at service ‘pdb1′, which is the name for my PDB. Connecting to //localhost:1521/PDB1 can connect you randomly to CDB1 (the primary database) or CDB2 (the standby database).

Here is an example, connecting several times to the PDB1 service:

[oracle@VM122 ~]$ for i in {1..5} ; do sqlplus -L -s sys/oracle@//localhost/pdb1 as sysdba <<< 'select name,open_mode,instance_name from v$instance , v$database;'; done
 
NAME OPEN_MODE INSTANCE_NAME
--------- -------------------- ----------------
CDB1 READ WRITE CDB1
 
NAME OPEN_MODE INSTANCE_NAME
--------- -------------------- ----------------
CDB1 READ ONLY WITH APPLY CDB2
 
NAME OPEN_MODE INSTANCE_NAME
--------- -------------------- ----------------
CDB1 READ WRITE CDB1
 
NAME OPEN_MODE INSTANCE_NAME
--------- -------------------- ----------------
CDB1 READ ONLY WITH APPLY CDB2
 
NAME OPEN_MODE INSTANCE_NAME
--------- -------------------- ----------------
CDB1 READ WRITE CDB1

I was connected at random to CDB1 or CDB2.

As an administrator, you know the instance names and you can connect to the one you want with: //localhost:1521/PDB1/CDB1 or //localhost:1521/PDB1/CDB2:

[oracle@VM122 ~]$ for i in {1..3} ; do sqlplus -L -s sys/oracle@//localhost/pdb1/CDB1 as sysdba <<< 'select name,open_mode,instance_name from v$instance , v$database;'; done
 
NAME OPEN_MODE INSTANCE_NAME
--------- -------------------- ----------------
CDB1 READ WRITE CDB1
 
NAME OPEN_MODE INSTANCE_NAME
--------- -------------------- ----------------
CDB1 READ WRITE CDB1
 
NAME OPEN_MODE INSTANCE_NAME
--------- -------------------- ----------------
CDB1 READ WRITE CDB1
 
[oracle@VM122 ~]$ for i in {1..3} ; do sqlplus -L -s sys/oracle@//localhost/pdb1/CDB2 as sysdba <<< 'select name,open_mode,instance_name from v$instance , v$database;'; done
 
NAME OPEN_MODE INSTANCE_NAME
--------- -------------------- ----------------
CDB1 READ ONLY WITH APPLY CDB2
 
NAME OPEN_MODE INSTANCE_NAME
--------- -------------------- ----------------
CDB1 READ ONLY WITH APPLY CDB2
 
NAME OPEN_MODE INSTANCE_NAME
--------- -------------------- ----------------
CDB1 READ ONLY WITH APPLY CDB2

Of course this is not what you want. And we must not start or stop the default services. For the application, the best you can do is to create your service. And if you want to be able to connect to the Active Data Guard standby, which is opened in read-only, then you can create a ‘read-write’ service and a ‘read-only’ service that you start depending on the role.

Create and Start a read-write service on the primary

This example supposes that you have only Oracle Database software installed. If you are in RAC, with the resources managed by Grid Infrastructure, or simply with Oracle Restart, creating a service is easy with srvctl, and you add it to a PDB with ‘-pdb’ and also with a role to start it automatically in the primary or in the standby. But without it, you use dbms_service:

SQL> connect /@CDB1 as sysdba
Connected.
 
SQL> alter session set container=pdb1;
Session altered.
 
SQL> exec dbms_service.create_service(service_name=>'pdb1_RW',network_name=>'pdb1_RW');
PL/SQL procedure successfully completed.
 
SQL> exec dbms_service.start_service(service_name=>'pdb1_RW');
PL/SQL procedure successfully completed.
 
SQL> alter session set container=cdb$root;
Session altered.

The service is created, stored in SERVICE$ visible with DBA_SERVICES:

SQL> select name,name_hash,network_name,creation_date,pdb from cdb_services order by con_id,service_id;
NAME NAME_HASH NETWORK_NAME CREATION_DATE PDB
---- --------- ------------ ------------- ---
pdb1_RW 3128030313 pdb1_RW 03-FEB-18 PDB1
pdb1 1888881990 pdb1 11-JAN-18 PDB1

Save state

I have created and started the PDB1_RW service. However, if I restart the database, the service will not start automatically. How do you ensure that the PDB1 pluggable database starts automatically when you open the CDB? You ‘save state’ when it is opened. It is the same for the services you create. You need to ‘save state’ when they are opened.


SQL> alter pluggable database all save state;
Pluggable database ALL altered.

The information is stored in PDB_SVC_STATE$, and I’m not aware of a dictionary view on it:

SQL> select name,name_hash,network_name,creation_date,con_id from v$active_services order by con_id,service_id;
 
NAME NAME_HASH NETWORK_NAME CREATION_DATE CON_ID
---- --------- ------------ ------------- ------
pdb1_RW 3128030313 pdb1_RW 03-FEB-18 4
pdb1 1888881990 pdb1 11-JAN-18 4
 
SQL> select * from containers(pdb_svc_state$);
 
INST_ID INST_NAME PDB_GUID PDB_UID SVC_HASH SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6 CON_ID
------- --------- -------- ------- -------- ------ ------ ------ ------ ------ ------ ------
1 CDB1 627F7512A0452FD4E0537A38A8C055C0 2872139986 3128030313 1

The name is not in this table, you have to join with v$services using(name_hash):

SQL> select name,name_hash,network_name,creation_date,con_id from v$active_services order by con_id,service_id;
 
NAME NAME_HASH NETWORK_NAME CREATION_DATE CON_ID
---- --------- ------------ ------------- ------
SYS$BACKGROUND 165959219 26-JAN-17 1
SYS$USERS 3427055676 26-JAN-17 1
CDB1_CFG 1053205690 CDB1_CFG 24-JAN-18 1
CDB1_DGB 184049617 CDB1_DGB 24-JAN-18 1
CDB1XDB 1202503288 CDB1XDB 11-JAN-18 1
CDB1 1837598021 CDB1 11-JAN-18 1
pdb1 1888881990 pdb1 11-JAN-18 4
pdb1_RW 3128030313 pdb1_RW 03-FEB-18 4

So, in addition to storing the PDB state in PDBSTATE$, visible with dba_pdb_saved_states, the service state is also stored. Note that they are at different level. PDBSTATE$ is a data link: stored on CDB$ROOT only (because the data must be read before opening the PDB) but PDB_SVC_STATE$ is a local table in the PDB as the services can be started only when the PDB is opened.

This new service is immediately registered on CDB1:

Service "pdb1" has 2 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb1_RW" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
The command completed successfully

Create and Start a read-only service for the standby

If you try to do the same on the standby for a PDB1_RO service, you cannot because service information has to be stored in the dictionary:

SQL> exec dbms_service.create_service(service_name=>'pdb1_RO',network_name=>'pdb1_RO');
 
Error starting at line : 56 File @ /media/sf_share/122/blogs/pdb_svc_standby.sql
In command -
BEGIN dbms_service.create_service(service_name=>'pdb1_RO',network_name=>'pdb1_RO'); END;
Error report -
ORA-16000: database or pluggable database open for read-only access

So, the read-only service has to be created on the primary:

SQL> connect /@CDB1 as sysdba
Connected.
SQL> alter session set container=pdb1;
Session altered.
 
SQL> exec dbms_service.create_service(service_name=>'pdb1_RO',network_name=>'pdb1_RO');
 
SQL> select name,name_hash,network_name,creation_date,pdb from cdb_services order by con_id,service_id;
NAME NAME_HASH NETWORK_NAME CREATION_DATE PDB
---- --------- ------------ ------------- ---
pdb1_RW 3128030313 pdb1_RW 03-FEB-18 PDB1
pdb1_RO 1562179816 pdb1_RO 03-FEB-18 PDB1
pdb1 1888881990 pdb1 11-JAN-18 PDB1

The SERVICE$ dictionary table is replicated to the standby, so I can I can start it on the standby:

SQL> connect /@CDB2 as sysdba
Connected.
SQL> alter session set container=pdb1;
Session altered.
 
SQL> exec dbms_service.start_service(service_name=>'pdb1_RO');
PL/SQL procedure successfully completed.

Here is what is registered to the listener:

Service "pdb1" has 2 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb1_RO" has 1 instance(s).
Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb1_RW" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
The command completed successfully

Now, the PDB_RO connects to the standby and PDB_RW to the primary. Perfect.

No ‘save state’ on the standby

At this point, you would like to have the PDB_RO started when PDB1 is opened on the standby, but ‘save state’ is impossible on a read-only database:

SQL> alter session set container=cdb$root;
Session altered.
 
SQL> alter pluggable database all save state;
 
Error starting at line : 84 File @ /media/sf_share/122/blogs/pdb_svc_standby.sql
In command -
alter pluggable database all save state
Error report -
ORA-16000: database or pluggable database open for read-only access

You can’t manage the state (open the PDB, start the services) in the standby database.

The primary ‘save state’ is replicated in standby

For the moment, everything is ok with my services:

Service "pdb1_RO" has 1 instance(s).
Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb1_RW" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...

If I restart the primary CDB1, everything is ok again because I saved the state of the PDB and the service. But what happens when the standby CDB2 restarts?


SQL> connect /@CDB2 as sysdba
Connected.
SQL> startup force;
...
SQL> show pdbs
 
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDB1 MOUNTED

The PDB is not opened: the ‘saved state’ for PDB is not read in the standby.
However, when I open the PDB, it seems that the ‘saved state’ for service is applied, and this one is replicated from the primary:

SQL> alter pluggable database PDB1 open;
Pluggable database altered.
SQL> host lsnrctl status
...
Service "pdb1" has 2 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb1_RW" has 2 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Instance "CDB2", status READY, has 1 handler(s) for this service...
The command completed successfully

My PDB1_RW is registered for both, connections will connect at random to the primary or the standby, and then the transactions will fail half of the times. It will be the same in case of switchover. This is not correct.

Save state instances=()

What I would like is the possibility to save state for a specific DB_UNIQUE_NAME, like with pluggable ‘spfile’ parameters. But this is not possible. What is possible is to mention an instance but you can use it only for the primary instance where you save the state (or you get ORA-65110: Invalid instance name specified) and anyway, this will not be correct after a switchover.

So what?

Be careful, with services and ensure that the services used by the application are registered only for the correct instance. Be sure that this persists when the instances are restarted. For this you must link a service name to a database role. This cannot be done correctly with ‘save state’. You can use startup triggers, or better, Grid Infrastructure service resources.

Do not connect to the default service with the PDB name, you cannot remove it and cannot stop it, so you may have the same name for different instances in a Data Guard configuration. You can register the standby instances to different local listeners, to avoid the confusion, but you may still register to the same SCAN listener.

Create your own services, start them depending on the database role, and do not use ‘save state’ in a physical standby configuration.

 

Cet article Multitenant, PDB, ‘save state’, services and standby databases est apparu en premier sur Blog dbi services.

Constraint to allow either NULL or distinct values

Tom Kyte - Mon, 2018-02-05 15:06
Hi guys, I wonder if it is possible to create a constraint on a table which does either allow NULL in a specified column for a group of items *or* a unique value. I set up an example. Imagine you can either set a jedi-unit to manage themselves...
Categories: DBA Blogs

Avoiding multiple json traversals

Tom Kyte - Mon, 2018-02-05 15:06
Good morning folks I'm trying to consume JSON documents that basically follow this structure: <code> { "items": [ { "id": "111A", "someAttributes": [ { "name": "anAttribute", "value": "A Value" }, { "nam...
Categories: DBA Blogs

Error: "Specified partition does not exist" when creating multi-group XML Index on partitioned table

Tom Kyte - Mon, 2018-02-05 15:06
Hi TOM We are trying to create a multi-group XML Index on a partitioned table. Table has no sub-partitions as they are not yet supported by XML Indexes, but we need the XML Index to have more than one group. This is the table: <code>CREAT...
Categories: DBA Blogs

Insert date from Oracle to PostgreSQL

Tom Kyte - Mon, 2018-02-05 15:06
I try to insert date form oracle to postgresql my database date format is dd/mm/yyyy postgresql date format yyyy/mm/dd <code>insert into "public"."test"@PG_LINK select 'test' name, TO_DATE (TO_CHAR(SU.DATE_OF_BIRTH, 'yyyy/mm/dd'),'yyyy/mm/dd...
Categories: DBA Blogs

Do something when the transaction ends

Tom Kyte - Mon, 2018-02-05 15:06
I work with Delphi and Oracle, and what I'm trying to do is change the value of a field by informing a user by mail. This is being done wrongly with a trigger. I thought that in the compound trigger in the after statement the transaction was over (co...
Categories: DBA Blogs

extract an xml node

Tom Kyte - Mon, 2018-02-05 15:06
I have some xml that has a namespace defined in a node within a namespace node ( see Ah:AppHdr ), I would like to extract the value of the element Ah:MsgRef .Can I extract it, if so how? When I try I get an XPATH error. declare l_req xmlType := x...
Categories: DBA Blogs

Can not make read only tablespace which has offline datafile

Tom Kyte - Mon, 2018-02-05 15:06
Hello Dears, A couple of months ago, we had problem: we lost most recent added disk on the server. Which contained latest 4 datafiles. As we had no database/archivelog backup, we had to mark those datafiles offline drop and open the instance. W...
Categories: DBA Blogs

Join Us for the OAUG AppsTech 2018 eLearning Series

Steven Chan - Mon, 2018-02-05 12:15

Mark your calendar and join us for the upcoming OAUG AppsTech eLearning Series.  As part of this OAUG sponsored series, members of Oracle E-Business Suite Development and Product Management will be providing the following webinars:

Technical Essentials for Running Oracle E-Business Suite on Oracle Cloud
Speaker:  Santiago Bastidas,
Date & Time: Tuesday, February 13, 1:00 PM EST

Faster and Better:  Oracle E-Business Suite Desktop Integration
Speaker:  Senthilkumar Ramalingam
Date & Time: Wednesday, February 21, 2018, 1:00 p.m. EST

Oracle E-Business Suite 12.2:  Fusion Middleware (WebLogic Server) Administration
Speakers:  Kevin Hudson and Elke Phelps
Date & Time: Tuesday, February 27, 2018, 1:00 p.m. EST

A complete listing of sessions for the AppsTech eLearning Series is available on the OAUG website.

References

 

Categories: APPS Blogs

Pages

Subscribe to Oracle FAQ aggregator