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

Advertisements

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>)))'
/
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.

Apex: upgrading Apex to 18.2, Ords to 18.3

Apex 18.2 and Ords 18.3 are now available.

To upgrade these versions in place, for example on an OCI DBsystem, from respectively Apex 18.1 and Ords 18.2:

#1 Download apex 18.3 under /u01/app/oracle/product/apex/18.3

 

#2 Upgrade apex

cd /u01/app/oracle/product/apex/18.3
sqlplus / as sysdba <<EOF
ALTER SESSION SET CONTAINER=<mycontainer>
@apexins SYSAUX SYSAUX TEMP /i/

 

#3 Change the images symbolic links

cd /u01/app/oracle/product/apex/latest
rm images
ln -s ../18.3/images images

 

#4 Download ORDS in /u01/app/oracle/product/ords/18.3

 

#5 Upgrade the params files

cd /u01/app/oracle/product/ords/18.3/params
cp /u01/app/oracle/product/ords/18.2/params/* .

 

#6 Upgrade the config directory

cd /u01/app/oracle/product/ords/18.3
java -jar ords.war configdir /u01/app/oracle/product/ords/config

 

#7 Upgrade the ORDS schema

cd /u01/app/oracle/product/ords/18.3/params
java -jar ords.war

The end of the process should start the ords server

SQL Developer: Rest Development connection setup to DBCS and https

There are two possible hiccups with the setup of “Rest Development” with SQL Developer 18c, when trying to connect to some https respoint, for example running on a DBcs on OCI.

#1 PKIX path building failed trying to connect after https is specified, basically getting the the error below trying to connect to a RestData service:

Cannot connect to <XX>.
sun.security.validator.ValidatorException: PKIX path building failed: 
sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

 

The Solution is to add the destination server certificate to the cacerts file from the Java JDK embedded with SQL Developer, for example:

C:\Oracle\SQLDev\182\jdk\lib\security\cacerts
or
C:\Oracle\SQLDev\183\jdk\jre\lib\security\cacerts

 

At this point, the easiest  is to transfer that certificate file on Linux and run the following command before transferring it back to Windows.

$ORACLE_HOME/jdk/jre/bin/keytool -storepass changeit -import -trustcacerts -keystore cacerts -file <mycertificate> -alias "myalias"

 

#2 Invalid resource owner credentials during login

The username to be specified is a special user that one can create from the compute instance running ORDS:

cd /u01/app/oracle/product/ords/18.2
or
cd /u01/app/oracle/product/ords/18.3

then

java -jar ords.war user ords_dev   "SQL Developer"
and/or
java -jar ords.war user ords_admin "Listener Administrator"

The command above will store these credentials in the ORDS configuration directory, where the user_name will be specified in clear, for example

/u01/app/oracle/product/ords/config/ords/credentials

Then use ords_dev or ords_admin to connect to the restful admin service to either develop new services or administer the service

 

Note that when upgrading from Apex 18.1 to 18.2, this is a matter to copy the cacerts file into the new SQLdev directory.

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-c: Project Fn setup

Basically following the instructions posted in the fn project page:

 

#1 Setup a VM running OL7.2 with 50GB

Preferably pick OL72 over OL68 as fn require docker > 17.5

 

#2 Setup security policy (OL7)

# setenforce permission

 

#3 Install docker (root)

Follow the instructions posted in the documentation.

Update /etc/yum.repos.d/public-yum-ol7.repo to enable the ol7_addons then

# yum install docker-engine wget tree
# chkconfig docker on
# yum update
# service docker start

 

#4 Install fn (as root)

curl -LSs https://raw.githubusercontent.com/fnproject/cli/master/install | sh

 

 

#5 Create a runtime user and add it to the docker group

# useradd -g 500 -d /home/oracle oracle
# sudo usermod -a -G docker oracle

 

#6 Login as oracle and start the fn server

$ fn start

 

#5 Login again as oracle in a second window and configure a demo project

$ mkdir project
$ cd project
$ fn init --runtime go hello
$ cd hello
$ fn run
$ fn deploy --app myapp --local
$ fn invoke myapp hello

Update the file func.go, changing Hello with bonjour, deploy and test again

$ fn deploy --app myapp --local
$ fn invoke myapp hello
{"message":"Bonjour World"}