Run scripts for ORACLE12C container database (CDBs) and pluggable Database (PDBs) new features in the

For developers and DBAs, the impact of the Shell Script batch task is one of the biggest changes in multi-homed options. Because multihomed environments connect to pluggable databases through services, relying on cron and OS authentication becomes one of the biggest problems after switching to a multihomed environment. This article provides some ways to address the problem of a multi-homed environment before Shell scripting works.

1. Setting up the container

For DBA scripts that work at the container level, use "/as SYSDBA" to work as before. The problem occurs when you run a script within a pluggable database. The simplest way to solve this problem is to continue with the "/assysdba" connection, but set the container with the ALTER SESSION set container command in the script.

Sqlplus/as SYSDBA <<eof

ALTER SESSION SET CONTAINER = pdb1;

--run the task as before

SHOW Con_name;

EXIT;

Eof

To make the script more generic, use the PDB name as a parameter. Save the following script as "set_container_test.sh".

Sqlplus/as SYSDBA <<eof

ALTER SESSION SET CONTAINER = $;

-run the task as before

SHOW Con_name;

EXIT;

Eof

The PDB name is run as the first argument and the script is displayed, and the container is set to be the right one.

$ chmod u+x set_container_test.sh

$./set_container_test.sh PDB1

Sql*plus:release 12.1.0.1.0 Production ONFRI APR 18 16:48:51 2014

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

Connected to:

Oracle Database 12c Enterprise editionrelease 12.1.0.1.0-64bit Production

With the partitioning, OLAP, Advancedanalytics and Real application testing options

Sql> sql>

Session altered.

Sql> sql> sql>

Con_name

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

PDB1

Sql> sql> disconnected from oracledatabase 12c Enterprise Edition Release 12.1.0.1.0-64bit Production

With the partitioning, OLAP, Advancedanalytics and Real application testing options

$

2. Two_task method

Using the TWO_TASK environment variable is an obvious way to connect to a particular user, but unfortunately, the "/assysdba" connection method does not work.

$ Export TWO_TASK=PDB1

$ sqlplus/as SYSDBA

Sql*plus:release 12.1.0.1.0 Production ONFRI APR 18 16:54:34 2014

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

ERROR:

Ora-01017:invalid Username/password; Logondenied

Enter User-name:

With a definite username and password combined with the Two_task method, it can work as normal as before.

$ Export TWO_TASK=PDB1

$ sqlplus Test/test

Sql*plus:release 12.1.0.1.0 Production ONFRI APR 18 16:57:46 2014

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

Last successful login time:wed Apr 02 201410:05:22 +01:00

Connected to:

Oracle Database 12c Enterprise editionrelease 12.1.0.1.0-64bit Production

With the partitioning, OLAP, Advancedanalytics and Real application testing options

Sql> SHOW Con_name;

Con_name

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

PDB1

Sql>

You may not want to include a definite user name and password in the script, but if you add a service that points to the connection or use the TWO_TASK environment variable, you can connect to the determined PDB.

3. Secure external Password storage

Oracle 10g introduces the ability to connect to a database using a secure external password store instead of explicitly providing authentication. This service-based approach is actually good for using the PDB environment.

Put the following entry in the "$ORACLE _home/network/admin/sqlnet.ora" file and determine the required wallet directory.

Wallet_location =

(SOURCE =

(METHOD = FILE)

(Method_data =

(DIRECTORY =/u01/app/oracle/wallet)

)

)

Sqlnet. Wallet_override = TRUE

Ssl_client_authentication = FALSE

ssl_version = 0

Create a wallet to store authentication information. After Oracle11gr2, this function is well implemented by ORAPKI, and if the wallet is copied to another machine, it will prevent automatic login.

$ mkdir-p/u01/app/oracle/wallet

$ orapki Wallet Create-wallet "/u01/app/oracle/wallet"-pwd "MyPassword"-auto_login_local

Oracle Secret Store tool:version 12.1.0.1

Copyright (c) 2004, Oracle and/or Itsaffiliates. All rights reserved.

Enter Password:

Enter Password again:

$

Then, create a certification entry that is related to the TNS alias. The parameter is "Aliasusername password".

$ MKSTORE-WRL "/u01/app/oracle/wallet"-createcredential pdb1_test test test

Oracle Secret Store tool:version 12.1.0.1

Copyright (c) 2004, Oracle and/or Itsaffiliates. All rights reserved.

Enter Wallet Password:

Create credentialoracle.security.client.connect_string1

$

Create an alias in the "$ORACLE _home/network/admin/tnsnames.ora" file that matches the wallet.

Pdb1_test =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = Ol6-121.localdomain) (PORT = 1521))

(Connect_data =

(SERVER = dedicated)

(service_name = pdb1)

)

)

At this point, we can use the authentication in the wallet to connect to the identified database.

$ sqlplus/@pdb1_test

Sql*plus:release 12.1.0.1.0 Production Onsat APR 19 10:19:38 2014

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

Last successful login Time:sat Apr 19 201410:18:52 +01:00

Connected to:

Oracle Database 12c Enterprise Edition release12.1.0.1.0-64bit Production

With the partitioning, OLAP, Advancedanalytics and Real application testing options

Sql> SHOW USER

USER is "TEST"

Sql> SHOW Con_name

Con_name

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

PDB1

Sql>

4. Scheduler

The scheduler in ORACLE12C has been enhanced so that it can contain script-based tasks so that you can define inline scripts or invoke scripts on the file system. These are a variant of the external task, but the Sql_script and Backup_script task types make it easier to process authentication and multihomed environments.

catcon.pl

Another problem that DBAs encounter when running scripts in a multihomed environment is to run the same script in multiple PDBs. This can be done in the previous way, but it may be more convenient for Oracle to provide a Perl module called "catcon.pl".

In a multihomed environment, some Oracle-provided scripts must be executed in a specific order, first in the Cdb$root container. The "catcon.pl" module can complete it and provide a log of the container, so that you can easily check the completion of the task.

The complete syntax for the module is as follows, and running the module without parameters displays all usages.

$ Perl catcon.pl

Usage:catcon [-uusername[/password]] [-u Username[/password]]

[-D directory] [-L directory]

[{-c|-c} container] [-pdegree-of-parallelism]

[-E] [-S]

[-e {on |errorlogging-table-other-than-sperrorlog}]

[-G]

-B log-file-name-base

--

{sqlplus-script [arguments] |--x<sql-statement>} ...

Optional:

-uusername (Optional/password; otherwise prompts for password)

Used to connect to the database to run user-supplied scripts or

SQL statements

Defaults to "/as SYSDBA"

-uusername (Optional/password; otherwise prompts for password)

Used to connect to the database to perform internal tasks

Defaults to "/as SYSDBA"

-ddirectory containing the file to be run

-ldirectory to use for spool log files

-ccontainer (s) in which to run sqlplus scripts, i.e. skip all

Containers not named here; For example,

-C ' PDB1 PDB2 ',

-ccontainer (s) in which don't to run sqlplus scripts, i.e. skip all

Containers named here; For example,

-C ' CDB PDB3 '

Note:-c and-c is mutually exclusive

-pexpected number of concurrent invocations of this script on a given

Host

Note:this parameter rarely needs to be specified

-esets echo on while running sqlplus scripts

-soutput of running every script would be spooled into a file whose name

would be

<log-file-name-base>_<script_name_without_extension>_[<container_name_if_any>].<default_ Extension>

-esets errorlogging on; If on is specified, default error logging table

would be used, otherwise, specified error logging table (which must

has been created in every Container) would be used

-gturns on production of debugging info while running this script

Mandatory:

-bbase name (e.g. catcon_test) for log and spool file names

Sqlplus-script-sqlplus script to run OR

Sql-statement-a Statement Toexecute

NOTES:

-if--x<sql-statement> is the first non-option string, it needs to be

Preceeded with – To avoid confusing module parsing options into

Assuming that '-' was an option which that module was not expecting and

About which it would complain

-command line parameters to SQL scripts can be introduced using--p

Interactive (or secret) parameters to SQL scripts can be introduced

Using--p

For example,

Perl catcon.pl ... x.sql '--pjohn '--penter Password for John: ' ...

$

An example of running the "Catblock.sql" script in all containers is used in the manual for running Oracle-provided scripts.

$ . Oraenv

Oracle_sid = [CDB1]?

The Oracle base remains unchanged with value/u01/app/oracle

$ cd $ORACLE _home/rdbms/admin/

$ perl catcon.pl-d $ORACLE _home/rdbms/admin-b/tmp/catblock_output catblock.sql

$ ls/tmp/catblock_output*

Catblock_output0.log Catblock_output1.log Catblock_output2.log Catblock_output3.log

$

The first output file contains the output from the "Cdb$root" and "Pdb$seed" container. The last file contains the overall status output information for the task. The other files in the middle contain the output of the PDBs created by all users themselves.

The "catcon.pl" module can also be used to run queries in all containers in the CDB. The following command runs a query in all containers, and for each container, its information is output to a file named "/tmp/tbs_files_outputn.log".

$ cd $ORACLE _home/rdbms/admin/

$ perl catcon.pl-e-b/tmp/tbs_files_output----X "select Tablespace_name,file_name from Dba_data_files"

$ ls/tmp/tbs_files_output*

/tmp/tbs_files_output0.log/tmp/tbs_files_output1.log/tmp/tbs_files_output2.log/tmp/tbs_files_output3.log

$

With the "-C" option and the "-C" option, you can include and exclude specific PDBs. The following example runs a query in all user-defined containers by omitting the root and seed containers.

$ rm-f/tmp/tbs_files_output*

$ cd $ORACLE _home/rdbms/admin/

$ perl catcon.pl-e-C ' cdb$root pdb$seed '-b/tmp/tbs_files_output----X "select Tablespace_name,file_name from Dba_data_f Iles "

$

Run scripts for ORACLE12C container database (CDBs) and pluggable Database (PDBs) new features in the