OCI: Connection manager 18c setup for ATP/ADW

This note is about how to configure Connection manager 18c to be used as a front end proxy for both the autonomous database ATP or data warehouse ADW.

 

#1 First provision a VM running a basic shape, running OL7

 

#2 Login to the connection manager VM and configure the oracle user as detailed in this note

 

#3 Configure the XDisplay as detailed in this note

 

#4 Add the following prereq for the 12c client runinstaller not to fail because of missing prerequisites

yum install compat-libcap1 libstdc++-devel ksh glibc-devel libaio-devel psmisc

 

#5 Open the port tcp/1950 for the connection manager as detailed in this note

 

#6 Transfer the 18c Database client on the VM and unzip it as the oracle user.

 

#7 Login with the oracle user and run the installer. Ignore the warning about the swap size. Install the connection manager component

 

#8 Create the directory /u01/app/oracle/admin/wallet/atp_wallet and transfer into this location the ATP or ADP wallet files.

This directory should list the following files:

ls -l /u01/app/oracle/admin/wallet/atp_wallet
total 44
-rw-r--r--. 1 oracle oinstall 10613 Dec 16 13:10 cwallet.sso
-rw-------. 1 oracle oinstall     0 Dec 16 13:10 cwallet.sso.lck
-rw-r--r--. 1 oracle oinstall 10568 Dec 16 13:10 ewallet.p12
-rw-------. 1 oracle oinstall     0 Dec 16 13:10 ewallet.p12.lck

 

#9 Configure the cman.ora file in $ORACLE_HOME/network/admin

Specify the private IP and a port. Other parameters are only relevant for more complex configurations.

Add the wallet_location parameter

cman_[vm].[sub].[vcn].oraclevcn.com=   
  (configuration=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.14)(PORT=1950))
    )
    (RULE_LIST=
      (RULE=(SRC=*)(DST=*)(SRV=*)(ACT=accept))
    )
    (parameter_list=
      (log_level=user)
      (max_connections=256)
      (idle_timeout=0)
      (inbound_connect_timeout=0)
      (session_timeout=0)
      (outbound_connect_timeout=0)
      (max_gateway_processes=8)
      (min_gateway_processes=3)
      (remote_admin=on)
      (trace_directory=/tmp)
      (trace_level=user)
      (trace_timestamp=on)
      (trace_filelen=1000)
      (trace_fileno=1)
      (max_cmctl_sessions=4)
      (event_group=init_and_term,memory_ops)
    )
  )

wallet_location =
  (SOURCE=
    (METHOD=File)
    (METHOD_DATA=
      (DIRECTORY=/u01/app/oracle/admin/wallet/atp_wallet)
    )
  )

 

#10 Start the connection manager

$ cmctl startup 
CMCTL for Linux: Version 18.0.0.0.0 - Production on 16-Dec-2018 13:35:42
Copyright (c) 1996, 2018, Oracle. All rights reserved.
Current instance ... is not yet started
Connecting to (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.14)(PORT=1950)))
Starting Oracle Connection Manager instance ...
Please wait...
CMAN for Linux: Version 18.0.0.0.0 - Production
Status of the Instance
----------------------
Instance name cman...oraclevcn.com
Version CMAN for Linux: Version 18.0.0.0.0 - Production
Start date 16-Dec-2018 13:35:42
Uptime 0 days 0 hr. 0 min. 9 sec
Num of gateways started 3
Average Load level 0
Log Level USER
Trace Level USER
Instance Config file /u01/app/oracle/product/18.0.0/client_1/network/admin/cman.ora
Instance Log directory /u01/app/oracle/diag/netcman/.../alert
Instance Trace directory /u01/app/oracle/diag/netcman/.../trace
The command completed successfully.
$

 

 

#11 Test the connection to the ATP

Revise the TNS aliases to reference first the connection manager host and port:

atp_cman=
  (DESCRIPTION =
    (SOURCE_ROUTE=YES)
    (ADDRESS =
       (PROTOCOL = TCP)
       (HOST = 10.0.0.14)
       (PORT = 1950))
    (ADDRESS =
       (protocol=tcps)
       (port=1522)
       (host=adb.eu-frankfurt-1.oraclecloud.com)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (service_name=[atp service].atp.oraclecloud.com)
    )
    (security=(ssl_server_cert_dn=
        "CN=adwc.eucom-central-1.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US"))   )
    )
  )

 

then connect from the ATP database using the new alias:

$ sqlplus user/pwd@atp_cman

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Dec 16 13:47:30 2019 Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Last Successful login time: Fri Dec 16 2018 13:46:49 +00:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0
SQL>
Advertisements

OCI: DBsystems manual standby setup

This note describes how to create, manually, a standby database for a 12cR2 primary database running on a DBsystem on Oracle Cloud Infrastructure.

 

#1 Provision two DBsystems with preferably the same database name, both with Entreprise Edition to benefits from the DG broker feature. At the end of the provisioning, put down their unique database name. For this note, the primary is db01_fra3bs running from the db01p host, and the secondary is db01_fra34d, running from the db01s host.

Note that the database name could be different, say chicago and boston, for the same db_name being network, but it is probably more elegant and more intuitive to keep the same prefix.

 

#2 Drop the second database

sqlplus -s / as sysdba <<EOF
SHUTDOWN IMMEDIATE;
STARTUP MOUNT RESTRICT EXCLUSIVE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
DROP DATABASE;
EOF

 

#3 Create a new init.ora file initdb01_init for the secondary node, here db01_fra34d

db_name=db01
db_unique_name=db01_fra34d
enable_pluggable_database=true
db_create_file_dest='+DATA'
db_create_online_log_dest_1='+DATA'
db_recovery_file_dest='+RECO'

 

#4 Start the secondary database in nomount, using the spfile

sqlplus / as sysdba <<EOF
SHUTDOWN IMMEDIATE
create spfile='?/dbs/spfiledb01.ora' from pfile='?/dbs/initdb01_init.ora';
startup nomount;
EOF

 

#5 Copy the password file from the primary

cd $ORACLE_HOME/dbs 10.x.x.x:/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwdb01 .

 

#6 Copy the walletfile from the primary

cd /opt/oracle/dcs/commonstore/wallets/tde
mv db01_fra34d db01_fra34d.old
mkdir db01_fra34d
cd db01_fra34d
scp 10.x.x.x:/opt/oracle/dcs/commonstore/wallets/tde/db01_fra3bs/* .

 

#7 Add a first static TNS entry in the grid listener.ora file on the stanmdby server for the duplicate command to work and a second for the DG broker

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = db01_fra34d.subxxxxxxxxxxx.vcnxxxxxxxxxx.oraclevcn.com)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/dbhome_1)
      (SID_NAME = db01) 
    )
    (SID_DESC = 
      (GLOBAL_DBNAME = db01_fra34d_dgmgrl.subxxxxxxxxxxx.vcnxxxxxxxxxx.oraclevcn.com) 
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/dbhome_1) 
      (SID_NAME = db01) ) 
  )

 

#8 Add a static on the primary server for the DG broker

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = db01_fra3bs_dgmgrl.subxxxxxxxxxxx.vcnxxxxxxxxxx.oraclevcn.com) 
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/dbhome_1) 
      (SID_NAME = db01) 
    ) 
  )

 

#9 Reload the grid listener on both the primary and standby server

lnrctl reload

 

#10 Add the following entries on the primary TNS tnsnames.ora file

db01_fra34d =
  (DESCRIPTION =
    (ADDRESS = 
      (PROTOCOL = TCP)
      (HOST = db01s.subxxxxxxxxxxx.vcnxxxxxxxxxx.oraclevcn.com)
      (PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db01_fra34d.subxxxxxxxxxxx.vcnxxxxxxxxxx.oraclevcn.com)
    )
  )

 

#11 Add the following entries in the standby TNS tnsnames.ora file

db01_fra3ds =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)
    (HOST = db01p.subxxxxxxxxxxx.vcnxxxxxxxxxx.oraclevcn.com)
    (PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db01_fra3ds.subxxxxxxxxxxx.vcnxxxxxxxxxx.oraclevcn.com)
    )
  )

 

#12 From  the primary, check that both connection are working properly:

sqlplus / AS SYSDBA
connect sys/<pwd>@db01_fra3bs as sysdba
connect sys/<pwd>@db01_fra34d as sysdba
EOF

 

#13 Add standby logfilles on the primary

sqlplus / as sysdba <<EOF
alter database add standby logfile thread 1 '+RECO' size 1024M;
alter database add standby logfile thread 1 '+RECO' size 1024M;
alter database add standby logfile thread 1 '+RECO' size 1024M;
alter database add standby logfile thread 1 '+RECO' size 1024M;
EOF

 

#14 Start the duplicate command

rman target sys/<pwd>@db01_fra3bs auxiliary sys/<pwd>@db01_fra34d <<EOF

run {
allocate channel prim1 type disk;
allocate channel prim2 type disk;
allocate channel prim3 type disk;
allocate channel prim4 type disk;
allocate auxiliary channel stdby1 type disk;

duplicate target database for standby from active database dorecover;
sql channel stdby1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel stdby1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel stdby1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel stdby1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
} 
EOF

 

#15 Alternate duplicate command

The following command may also consolidate the 3 latest commands into a single one

rman target sys/<pwd>@db01_fra3bs auxiliary sys/<pwd>@db01_fra34d <<EOF 

run {
allocate channel prim1 type disk;
allocate channel prim2 type disk;
allocate channel prim3 type disk;
allocate channel prim4 type disk;
allocate auxiliary channel stdby1 type disk;

sql channel prim1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel prim1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel prim1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel prim1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";

duplicate target database for standby from active database dorecover;

sql channel stdby1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel stdby1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel stdby1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel stdby1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel prim1 "alter system archive log current";
sql channel stdby1 "alter database recover managed standby database disconnect";
}
EOF

 

#16 Restart the standby in active dg mode

sqlplus / as sysdba <<EOF
shutdown immediate;
startup mount;
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;
EOF

 

#17 Setup DG

sqlplus / AS SYSDBA <<EOF
connect sys/<pwd>@db01_fra3bs as sysdba
ALTER SYSTEM SET log_archive_config='dg_config=(db01_fra3bs,db01_fra34d)' SCOPE=BOTH;
connect sys/<pwd>@db01_fra34d as sysdba
ALTER SYSTEM SET log_archive_config='dg_config=(db01_fra3bs,db01_fra34d)' SCOPE=BOTH;
connect sys/<pwd>@db01_fra3bs as sysdba
ALTER SYSTEM SET log_archive_dest_2='service=db01_fra34d async valid_for=(all_logfiles,primary_role) db_unique_name=db01_fra34d' SCOPE=BOTH;
connect sys/<pwd>@db01_fra34d as sysdba
ALTER SYSTEM SET log_archive_dest_5='service=db01_fra3bs async valid_for=(all_logfiles,primary_role) db_unique_name=db01_fra3bs' SCOPE=BOTH;
EOF

The specific order above is to avoid occasional ORA-16047 warnings.

 

#18 Check the DG status

dgmgrl <<EOF
connect sys/<pwd>
show configuration
validate database db01_fra3bs;
validate database db01_fra34d;
EOF

Make sure there is no error or warning.

 

#19 Test a switchover

dgmgrl 
DGMGRL> connect sys/<pwd>
DGMGRL> switchover to db01_fra34d

OCI: Cloning a remote-PDB as a PDB

This note describes how to clone a remote PDB as a new located on a OCI dbsystem.

 

#1 From the source CDB

CONNECT / AS SYSDBA
ALTER SESSION SET CONTAINER=<source pdb>;
CREATE USER dbclone IDENTIFIED BY 'password';
GRANT CREATE SESSION TO dbclone;
GRANT CREATE PLUGGABLE DATABASE TO dbclone;
CONNECT / AS SYSDBA
ALTER PLUGGABLE DATABASE <source pdb> CLOSE;
ALTER PLUGGABLE DATABASE <source pdb> OPEN READ ONLY;

 

#2 From the target CDB:

DROP DATABASE LINK dblink;
CREATE DATABASE LINK dblink
CONNECT TO dbclone IDENTIFIED BY <password> 
USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <private ip of the dbsystem>)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = <source pdb>.<domain>)))';
ALTER SESSION SET global_name=FALSE;    <= Optional if getting ORA-02085 error
SELECT COUNT(1) FROM dual@dblink;

 

#3 Submit the clone (started from the target CDB)

ALTER PLUGGABLE DATABASE <target pdb> CLOSE;
DROP PLUGGABLE DATABASE <target pdb> INCLUDING DATAFILES;
CREATE PLUGGABLE DATABASE <target pdb> FROM <source pdb>@dblink
KEYSTORE IDENTIFIED BY "<target cdb wallet password>";

Note: the last command above may return the error ORA-28382: Global wallet operation in RAC failed. This error means that the ORACLE_UNQNAME variable has probably not been properly set.

 

#4 In case of plugging violation of that kind, run database to rollback patches not available in the target db

$ORACLE_HOME/OPatch/datapatch


#5 Then reopen the PDB
ALTER PLUGGABLE DATABASE <target pdb> CLOSE;
ALTER PLUGGABLE DATABASE <target pdb> OPEN;

ADW: dbms_cloud.copy_data and record delimiter

To import via dbms_cloud.copy_data a windows file that include window CR+LF at the end of each record, specify the record delimiter as follow:

BEGIN
DBMS_CLOUD.COPY_DATA
(table_name      => '<table>'
,credential_name => '<credential_name>'
,file_uri_list   => 'https://<restpoint>/<file>'
,schema_name     => '<schema>'
,format           => json_object('recorddelimiter'      value '''\\r\\n''')
);
END;
/

 

An invalid specification of this delimiter would return the error below:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 801
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 782
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "xxx": expecting one of: "double-quoted-string, hexprefix, newline, single-quoted-string"
KUP-01007: at line 2 column 2
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 757
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 819
ORA-06512: at line 2

OCI: ORDS 18.x redirection and logging

The tips below are applicable for ORDS 18.x standalone, for example after a deployment on an OCI dbsystem.

#1 To configure the access.log, update the file standalone.properties available from the /u01/app/oracle/product/ords/config/ords/standalone/standalone.properties configuration file to add the following property

standalone.access.log=/tmp/ords

Where /tmp/ords is going to be a directory that will hold the daily logfiles. Then restart the ords standalone process.

 

#2 To implement some redirect for the internal ords port to be accessible from the standard https port 443, instead of the native port 8443, create the file https (for example) in /etc/xinetd.d, with the following content:

service jetty-https
{
disable = no
type = UNLISTED
socket_type = stream
protocol = tcp
wait = no
redirect = localhost 8443
port = 443
user = nobody
}

Then restart the xinetd.d service

service xinetd restart

Check that the firewall and the ingress rules allow inbound access to 443.