ODAVP: Re-IP

This note is about changing the IP/netmask and gateway settings for a virtualized ODA.

These steps are detailed in the MOS 1504734.1 [How to Change Oracle Database Appliance IP Addresses Post Deployment], but mostly focusing on baremetal ODA.

 

#1 Boot the dom0 nodes

 

#2 Login to the first dom0 and reconfigure the 1 and optionally additional network

oakcli configure firstnet
oakcli configure additionalnet

If the second command show “no more interfaces to configure”, manually change the file /opt/oracle/oak/conf/dom0.xml

 

#3 Login with ‘grid’ to each dom0 systems and change the network settings in the clusterware, for example:

oifcfg delif -global eth1/128.3.10.0                <= old
oifcfg setif -global eth1/100.188.10.0:public <= new
oifcfg getif

 

#4 Login with root from the first dom0 node and bounce the clusterware to check for anomalies

crsctl stop cluster -all
crsctl start cluster -all

 

#5 Login with root from the first dom0 node and update the onecommand config file with the new parameter settings

/opt/oracle/oak/onecmd/onecommand.params 

 

#6 Stop the clusterware again

crsctl stop cluster -all

 

#7 Login with root from the first dom0 node and run through the wizatd, to check for anomalies

cd /opt/oracle/oak/onecmd
oakcli deploy -conf onecommand.params config

Warning this run with X, so it may be necessary to establish first, manually, the IP configuration of the eth1 network on Dom0

Make sure the root password does NOT include special characters (& or %)

Save the config at the end and compare with the previous version.

 

#8 Still from the first node, reconfigure the cluster with the new parameters

Run all steps that apply for the change

# cd /opt/oracle/oak/onecmd/
# ./GridInst.pl -o -l      <= list the step
# ./GridInst.pl -o -s 1     <= example for step1, here for Setup Network
# ./GridInst.pl -o -s 6     <= example for step6, here for Setup /etc/hosts

Check that the ifcfg-ethX files have been changed on both nodes

 

#9 Restart the clusterware, still from the first node 

# crsctl start cluster -all

 

#10 To change the VIP settings, as it turns that the above guideline could not complete this task, from each node:

# srvctl stop nodeapps -f
# srvctl modify vip -n <node> -netnum <num> -address <vip>/<gateway>/<interface>

 

#11 To change the SCAN VIP settings:

Check the SCAN settings with the following commands:

# srvctl config scan

If these settings are incorrect (ORA-12543 would eventually be returned from various TNS commands), then the scan vip need to be updated as well:

# srvctl stop scan_listener
# srvctl stop scan
# srvctl modify scan -n <scan>

Check that the changed has completed with the following command:

# srvctl config scan

 

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;

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 and unzip apex 18.3 in the n/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/
EOF

 

#3 Change the images symbolic links (quite convenient to handle periodic upgrades)

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

 

#4 Download and unzip the ORDS zip file in the newly created directory

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