Thinking Out Loud

April 29, 2020

AutoUpgrade What I Did Not Know

Filed under: upgrade — mdinh @ 5:51 pm

Deploy and Upgrade Postupgrade Checks and Fixes

After an upgrade completes with either Deploy or Upgrade modes, AutoUpgrade performs postupgrade checks.
It provides a process where you can enable your custom scripts to be run on each of the upgraded databases,
in accordance with the configuration instructions you provide in the AutoUpgrade configuration file,
and also can run automatic postupgrade fixups as part of the postupgrade process.

In Deploy mode, AutoUpgrade also confirms that the upgrade has succeeded,
and moves database files such as sqlnet.ora, tnsnames.ora, and listener.ora
from the source home to the target home.

After these actions are complete, the upgraded Oracle Database release is started in the new Oracle home.

Reference: About Oracle Database AutoUpgrade

Environments:

Source: Database Apr 2019 Release Update : 12.2.0.1.190416 (29314339)
Target: Database Release Update : 19.3.0.0.190416 (29517242)

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -version
build.hash 6010a62
build.version 19.8.1
build.date 2020/04/01 16:18:24
build.max_target_version 19
build.type production

Why is moving sqlnet.ora, tnsnames.ora, and listener.ora from the source home to the target home a problem?

There were multiple databases running from the same home and created application issues.

Ironically, I just recently had discussion about using TNS_ADMIN to store sqlnet.ora, tnsnames.ora, and listener.ora in central locations vs ORACLE_HOME.

Now curious if this will cause AutoUgrade to fail?

Overview of Local Naming Parameters

By default, the tnsnames.ora file is located in the ORACLE_HOME/network/admin directory.
Oracle Net will check the other directories for the configuration file.

For example, the order checking the tnsnames.ora file is as follows:

The directory specified by the TNS_ADMIN environment variable.
If the file is not found in the directory specified, then it is assumed that the file does not exist.
If the TNS_ADMIN environment variable is not set, then Oracle Net checks the ORACLE_HOME/network/admin directory.

Reference: 6.1 Overview of Local Naming Parameters

Thanks to a super smart team member (who wants to be anonymous) for sharing.

Playing With lsof

Filed under: linux — mdinh @ 12:53 pm

lsof – list open files

This is a continuation of How To Check ORACLE_HOME Is Being Used

Check for count of open files:

[root@db-fs-1 ~]# lsof +D /u01/app/oracle/11.2.0.4/db1|wc -l
550

[root@db-fs-1 ~]# lsof +D /u01/app/ggs|wc -l
56
[root@db-fs-1 ~]#

Shutdown GoldenGate and Kill pmon

[root@db-fs-1 ~]# . oraenv <<< hawk
ORACLE_SID = [root] ? The Oracle base has been set to /u01/app/oracle
[root@db-fs-1 ~]# cd /u01/app/ggs/
[root@db-fs-1 ggs]# ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.



GGSCI (db-fs-1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:06


GGSCI (db-fs-1) 2> stop *

Sending STOP request to EXTRACT EXT1 ...
Request processed.


GGSCI (db-fs-1) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT1        00:00:00      00:00:05


GGSCI (db-fs-1) 4> stop mgr !

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.


GGSCI (db-fs-1) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
EXTRACT     STOPPED     EXT1        00:00:00      00:00:17


GGSCI (db-fs-1) 6> exit

[root@db-fs-1 ggs]# ps -ef|grep [p]mon
oracle   17864     1  0 Apr28 ?        00:00:08 ora_pmon_hawk
[root@db-fs-1 ggs]# kill -9 17864

Check for count of open files:

[root@db-fs-1 ggs]# lsof +D /u01/app/oracle/11.2.0.4/db1|wc -l
15

[root@db-fs-1 ggs]# lsof +D /u01/app/ggs|wc -l
6
[root@db-fs-1 ggs]#

Check for open files:
cwd – some process is in directory /u01/app/ggs
DUH! That would be me.

[root@db-fs-1 ggs]# lsof +D /u01/app/ggs
COMMAND   PID USER   FD   TYPE DEVICE SIZE/OFF    NODE NAME
bash    29106 root  cwd    DIR  249,2     4096 1443287 /u01/app/ggs
lsof    29913 root  cwd    DIR  249,2     4096 1443287 /u01/app/ggs
lsof    29914 root  cwd    DIR  249,2     4096 1443287 /u01/app/ggs

[root@db-fs-1 ggs]# lsof +D /u01/app/oracle/11.2.0.4/db1
COMMAND PID   USER   FD   TYPE DEVICE SIZE/OFF    NODE NAME
tnslsnr 876 oracle  txt    REG  249,2   972792  918631 /u01/app/oracle/11.2.0.4/db1/bin/tnslsnr
tnslsnr 876 oracle  mem    REG  249,2   241443  923131 /u01/app/oracle/11.2.0.4/db1/lib/libclsra11.so
tnslsnr 876 oracle  mem    REG  249,2 17647384  923129 /u01/app/oracle/11.2.0.4/db1/lib/libhasgen11.so
tnslsnr 876 oracle  mem    REG  249,2  3354840  923133 /u01/app/oracle/11.2.0.4/db1/lib/libocrb11.so
tnslsnr 876 oracle  mem    REG  249,2  1612720  923132 /u01/app/oracle/11.2.0.4/db1/lib/libocr11.so
tnslsnr 876 oracle  mem    REG  249,2   156012  923134 /u01/app/oracle/11.2.0.4/db1/lib/libocrutl11.so
tnslsnr 876 oracle  mem    REG  249,2    12787  919774 /u01/app/oracle/11.2.0.4/db1/lib/libskgxn2.so
tnslsnr 876 oracle  mem    REG  249,2    59021  920648 /u01/app/oracle/11.2.0.4/db1/lib/libnque11.so
tnslsnr 876 oracle  mem    REG  249,2  7996693  923239 /u01/app/oracle/11.2.0.4/db1/lib/libnnz11.so
tnslsnr 876 oracle  mem    REG  249,2 53775272  920645 /u01/app/oracle/11.2.0.4/db1/lib/libclntsh.so.11.1
tnslsnr 876 oracle  mem    REG  249,2   148728  919817 /u01/app/oracle/11.2.0.4/db1/lib/libons.so
tnslsnr 876 oracle    3r   REG  249,2    52224 1057224 /u01/app/oracle/11.2.0.4/db1/rdbms/mesg/diaus.msb
tnslsnr 876 oracle    5r   REG  249,2    12288  920670 /u01/app/oracle/11.2.0.4/db1/network/mesg/nlus.msb
tnslsnr 876 oracle    7r   REG  249,2    47104  919729 /u01/app/oracle/11.2.0.4/db1/network/mesg/tnsus.msb
[root@db-fs-1 ggs]#

Kill tns, change directory, and check for open files:

[root@db-fs-1 ggs]# ps -ef|grep tns
root        22     2  0 Apr28 ?        00:00:00 [netns]
oracle     876     1  0 Apr28 ?        00:00:03 /u01/app/oracle/11.2.0.4/db1/bin/tnslsnr LISTENER -inherit
root     29944 29106  0 14:36 pts/0    00:00:00 grep --color=auto tns

[root@db-fs-1 ggs]# kill -9 876

[root@db-fs-1 ggs]# lsof +D /u01/app/oracle/11.2.0.4/db1
[root@db-fs-1 ggs]# cd

[root@db-fs-1 ~]# lsof +D /u01/app/ggs|wc -l
0

[root@db-fs-1 ~]# lsof +D /u01/app/oracle/11.2.0.4/db1|wc -l
0
[root@db-fs-1 ~]#

Copy Or Move File And Append Date Using Bash

Filed under: linux,shell scripting — mdinh @ 4:15 am

bash version 4.2.46(2)-release

[ggs@db-fs-1 ~]$ echo "$SHELL"
/bin/bash
[ggs@db-fs-1 ~]$

[ggs@db-fs-1 ~]$ echo $BASH_VERSION
4.2.46(2)-release
[ggs@db-fs-1 ~]$

Demo 1:

[ggs@db-fs-1 ~]$ touch foo.log
[ggs@db-fs-1 ~]$ file=foo.log
[ggs@db-fs-1 ~]$
[ggs@db-fs-1 ~]$ ls -l $file*; cp -fv ${file%%.*}.{${file#*.},${file#*.}."$(date +%Y%m%d)"}; ls -l $file*
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:49 foo.log
‘foo.log’ -> ‘foo.log.20200429’
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:49 foo.log
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:50 foo.log.20200429
[ggs@db-fs-1 ~]$

Demo 2:

[ggs@db-fs-1 ~]$ touch archive.tar.gz
[ggs@db-fs-1 ~]$ file=archive.tar.gz
[ggs@db-fs-1 ~]$
[ggs@db-fs-1 ~]$ ls -l $file*; cp -fv ${file%%.*}.{${file#*.},${file#*.}."$(date +%Y%m%d)"}; ls -l $file*
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:48 archive.tar.gz
‘archive.tar.gz’ -> ‘archive.tar.gz.20200429’
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:48 archive.tar.gz
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:49 archive.tar.gz.20200429
[ggs@db-fs-1 ~]$

Demo 3: Rotate ggserr.log (only required because logrotate is not configured)

ggs@db-fs-1 ggs]$ file=ggserr.log
[ggs@db-fs-1 ggs]$ ls -l $file*; cp -fv ${file%%.*}.{${file#*.},${file#*.}."$(date +%Y%m%d)"}; ls -l $file*
-rw-r----- 1 ggs oinstall 534 Apr 29 06:04 ggserr.log
‘ggserr.log’ -> ‘ggserr.log.20200429’
-rw-r----- 1 ggs oinstall 534 Apr 29 06:04 ggserr.log
-rw-r----- 1 ggs oinstall 534 Apr 29 06:05 ggserr.log.20200429

[ggs@db-fs-1 ggs]$ cat /dev/null > ggserr.log
[ggs@db-fs-1 ggs]$ ls -l $file*
-rw-r----- 1 ggs oinstall   0 Apr 29 06:05 ggserr.log
-rw-r----- 1 ggs oinstall 534 Apr 29 06:05 ggserr.log.20200429
[ggs@db-fs-1 ggs]$

Demo 4:

[ggs@db-fs-1 ~]$ file=archive.tar.gz
[ggs@db-fs-1 ~]$ ls -l $file*; mv -fv ${file%%.*}.{${file#*.},${file#*.}."$(date +%Y%m%d)"}; ls -l $file*
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:48 archive.tar.gz
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:49 archive.tar.gz.20200429
‘archive.tar.gz’ -> ‘archive.tar.gz.20200429’
-rw-r--r-- 1 ggs oinstall 0 Apr 29 05:48 archive.tar.gz.20200429
[ggs@db-fs-1 ~]$

Why is this even important?

Consistent and mindless effort but need to remember syntax.

April 25, 2020

GoldenGate 19.1.0.0.4 Silent Install

Filed under: 19c,GoldenGate,runInstaller — mdinh @ 12:22 am

GoldenGate install is so easy; don’t bother with GUI install.

Download Oracle GoldenGate 19.1.0.0.4 for Oracle on Linux x86-64 : 191004_fbo_ggs_Linux_x64_shiphome.zip

Create copy of response file:

[ggs@db-fs-1 response]$ pwd
/u01/app/oracle/fbo_ggs_Linux_x64_shiphome/Disk1/response

[ggs@db-fs-1 response]$ ls -l
total 12
-rwxr-xr-x 1 ggs oinstall  209 Apr 24 14:07 oggcore_11g_db.rsp
-rwxrwxr-x 1 ggs oinstall 4439 Jan 23  2019 oggcore.rsp
[ggs@db-fs-1 response]$

Create response file for install: just 5 lines and doesn’t get any easier than this.

[ggs@db-fs-1 response]$ cat oggcore_11g_db.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v19_1_0
INSTALL_OPTION=ORA11g
SOFTWARE_LOCATION=/u01/app/ggs
INVENTORY_LOCATION=/u01/app/oraInventory
UNIX_GROUP_NAME=oinstall
[ggs@db-fs-1 response]$

Install Goldengate: (Note: relative path is not supported for response file]

[ggs@db-fs-1 response]$ cd ../
[ggs@db-fs-1 Disk1]$ ls
install  response  runInstaller  stage

[ggs@db-fs-1 Disk1]$ ./runInstaller -silent -showProgress -waitforcompletion -responseFile response/oggcore_11g_db.rsp
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 44288 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 2047 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-04-24_02-08-58PM. Please wait ...
[FATAL] [INS-10101] The given response file response/oggcore_11g_db.rsp is not found.
CAUSE: The given response file is either not accessible or do not exists.
ACTION: Give a correct response file location. (Note: relative path is not supported)
[ggs@db-fs-1 Disk1]$

[ggs@db-fs-1 Disk1]$ ./runInstaller -silent -showProgress -waitforcompletion -responseFile /tmp/oggcore_11g_db.rsp
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 28928 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 17407 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-04-24_02-23-29PM. Please wait ...

You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2020-04-24_02-23-29PM.log

Prepare in progress.
..................................................   10% Done.

Prepare successful.

Copy files in progress.
..................................................   36% Done.
..................................................   54% Done.
..................................................   77% Done.
..................................................   82% Done.
..................................................   88% Done.
....................
Copy files successful.

Link binaries in progress.
..........
Link binaries successful.

Setup files in progress.
..................................................   93% Done.
..................................................   95% Done.
..................................................   96% Done.
..................................................   98% Done.
..................................................   99% Done.

Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.
..................................................   95% Done.
Successfully Setup Software.
..................................................   100% Done.

Finish Setup successful.
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2020-04-24_02-23-29PM.log' for more details.
[ggs@db-fs-1 Disk1]$

April 24, 2020

GoldenGate Monitoring With User Separation

Filed under: 19c,GoldenGate — mdinh @ 10:10 pm

Typically, GoldenGate monitoring is performed by GoldenGate user.

However, there may be circumstances where monitoring is performed by another user and this is test case for such scenario.

I did not want to create yet another user; hence, GoldenGate will be monitored by user oracle.

GoldenGate 19.1.0.0.4 with Database 11.2.0.4.

Both users (ggs and oracle) belong to the same primary group oinstall:

[ggs@db-fs-1 ggs]$ id ggs
uid=54322(ggs) gid=54321(oinstall) groups=54321(oinstall),54322(dba)

[ggs@db-fs-1 ggs]$ id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54318(asmdba),54320(asmadmin),54322(dba),54323(backupdba),54324(oper),54325(dgdba),54326(kmdba)
[ggs@db-fs-1 ggs]$

Permission for GoldenGate directories are 755:

[ggs@db-fs-1 ggs]$ ls -ld dir*
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:21 dirchk
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:12 dircrd
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirdat
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirdef
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:13 dirdmp
drwxr-xr-x 3 ggs oinstall 4096 Apr 24 14:25 diretc
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:25 dirout
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirpcs
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:20 dirprm
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirrpt
drwxr-xr-x 4 ggs oinstall 4096 Apr 24 14:25 dirsca
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirsql
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirtmp
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirwlt
[ggs@db-fs-1 ggs]$

Check lag as user ggs – no issues:

[ggs@db-fs-1 ggs]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.



GGSCI (db-fs-1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:02


GGSCI (db-fs-1) 2> info ext1

EXTRACT    EXT1      Last Started 2020-04-24 16:24   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Process ID           18886
Log Read Checkpoint  Oracle Redo Logs
                     2020-04-24 17:12:08  Seqno 374, RBA 14901248
                     SCN 0.1622637 (1622637)


GGSCI (db-fs-1) 3> lag ext1

2020-04-24 17:12:26  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to EXTRACT EXT1 ...
Last record lag 2 seconds.
At EOF, no more records to process


GGSCI (db-fs-1) 4> dblogin useridalias gguser
Successfully logged into database.

GGSCI (db-fs-1 as gguser@hawk) 5> lag ext1

Sending GETLAG request to EXTRACT EXT1 ...
Last record lag 2 seconds.
At EOF, no more records to process


GGSCI (db-fs-1 as gguser@hawk) 6> exit
[ggs@db-fs-1 ggs]$

Check lag as user oracle – issue with permissions and extract is not visible:

[oracle@db-fs-1 ggs]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.



GGSCI (db-fs-1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (db-fs-1) 2> sh ls -l /u01/app/ggs/dirchk/*

-rwxr-xr-x 1 ggs oinstall  2048 Apr 24 15:21 /u01/app/ggs/dirchk/EXT1.cpb
-rwxr-xr-x 1 ggs oinstall 20480 Apr 24 17:14 /u01/app/ggs/dirchk/EXT1.cpe


GGSCI (db-fs-1) 3> sh chmod 775 /u01/app/ggs/dirchk/*

chmod: changing permissions of ‘/u01/app/ggs/dirchk/EXT1.cpb’: Operation not permitted
chmod: changing permissions of ‘/u01/app/ggs/dirchk/EXT1.cpe’: Operation not permitted


GGSCI (db-fs-1) 4> sh ls -l /u01/app/ggs/dirchk/*

-rwxrwxr-x 1 ggs oinstall  2048 Apr 24 15:21 /u01/app/ggs/dirchk/EXT1.cpb
-rwxrwxr-x 1 ggs oinstall 20480 Apr 24 17:14 /u01/app/ggs/dirchk/EXT1.cpe

GGSCI (db-fs-1) 5>

Change permission for dirchk to 775:

ggs@db-fs-1 ggs]$ chmod 775 /u01/app/ggs/dirchk/*

Check lag as user oracle – issue with permissions, extract appears, lag check failed:

GGSCI (db-fs-1) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:07


GGSCI (db-fs-1) 6> lag ext1

2020-04-24 17:14:58  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to EXTRACT EXT1 ...

2020-04-24 17:14:58  ERROR   OGG-15161  Could not initialize the connection with EXTRACT EXT1 (Permission denied).


GGSCI (db-fs-1) 7> dblogin useridalias gguser
Successfully logged into database.

GGSCI (db-fs-1 as gguser@hawk) 8> lag ext1

Sending GETLAG request to EXTRACT EXT1 ...

******************************************************************************************************************************
***** 2020-04-24 17:16:34  ERROR   OGG-15161  Could not initialize the connection with EXTRACT EXT1 (Permission denied). *****
******************************************************************************************************************************

GGSCI (db-fs-1 as gguser@hawk) 9>

Change permissions for all GoldenGate directories to 775:

[ggs@db-fs-1 ggs]$ chmod 775 -R dir*

Check lag using as user oracle – works perfectly:

GGSCI (db-fs-1 as gguser@hawk) 10> lag ext1

Sending GETLAG request to EXTRACT EXT1 ...
Last record lag 2 seconds.
At EOF, no more records to process


GGSCI (db-fs-1 as gguser@hawk) 11>

You might be thinking about least privileges principle.

Change permissions for GoldenGate directories (dirchk and dirtmp) to 775:

[ggs@db-fs-1 ggs]$ chmod 775 -R /u01/app/ggs/dirchk
[ggs@db-fs-1 ggs]$ chmod 775 -R /u01/app/ggs/dirtmp

[ggs@db-fs-1 ggs]$ ls -ld dir*
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:21 dirchk
drwxrwxr-x 2 ggs oinstall 4096 Apr 24 15:12 dircrd
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirdat
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirdef
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:13 dirdmp
drwxr-xr-x 3 ggs oinstall 4096 Apr 24 14:25 diretc
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:25 dirout
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirpcs
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 15:20 dirprm
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 16:24 dirrpt
drwxr-xr-x 4 ggs oinstall 4096 Apr 24 14:25 dirsca
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirsql
drwxrwxr-x 2 ggs oinstall 4096 Apr 24 16:24 dirtmp
drwxr-xr-x 2 ggs oinstall 4096 Apr 24 14:52 dirwlt
[ggs@db-fs-1 ggs]$

Check lag as user oracle – SUCCESS:

[oracle@db-fs-1 ggs]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.



GGSCI (db-fs-1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:02


GGSCI (db-fs-1) 2> lag *

2020-04-25 00:02:13  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to EXTRACT EXT1 ...
Last record lag 1 seconds.
At EOF, no more records to process


GGSCI (db-fs-1) 3> sh ls -l /u01/app/ggs/dirchk

total 28
-rwxrwxr-x 1 ggs oinstall  4096 Apr 24 20:24 EXT1.cpb
-rwxrwxr-x 1 ggs oinstall 20480 Apr 25 00:02 EXT1.cpe


GGSCI (db-fs-1) 4> sh ls -l /u01/app/ggs/dirtmp

total 0
srwxrwxr-x 1 ggs oinstall 0 Apr 24 16:24 EXT1.s


GGSCI (db-fs-1) 5> exit
[oracle@db-fs-1 ggs]$

Q.E.D.

April 17, 2020

How To Create New Standby Using Active Standby Duplication From Existing Standby

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 8:46 pm

Basically, my insanity is from my experience as Senior Tax Advisor and investment background.

Planning and organization will simplify processes in the long run.

I will share my thoughts for implementing Data Guard having two standby databases.

Data Guard configurations will be for the following hosts: LAX, PHX, SLC

  1. ORACLE_SID and db_name will be the same value.
  2. db_unique_name and service_names will be the same value.
  3. Listener will be LISTENER.
  4. TNS entries (alias) will be created for service_names and listener.
  5. Data Guard Broker is being used with Oracle Restart

Hostname: LAX (PRIMARY) : OEL6

Option #1: Using alphabet for configurations

ORACLE_SID(db_name)/db_unique_name(service_names): HAWK/HAWKA
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKA

dg_config=(HAWKA,HAWKB,HAWKC)
fal_server='HAWKB,HAWKC'

Option #2: Using hostname for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/LAX
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_LAX

dg_config=(LAX,PHX,SLC)
fal_server='PHX,SLC'

Hostname: PHX (CURRENT STANDBY) : OEL6

Option #1: Using alphabet for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/HAWKB
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKB

dg_config=(HAWKA,HAWKB,HAWKC)
fal_server='HAWKA,HAWKC'

Option #2: Using hostname for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/PHX
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_PHX

dg_config=(LAX,PHX,SLC)
fal_server='LAX,SLC'

Hostname: SLC (NEW STANDBY) : OEL7

Option #1: Using alphabet for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/HAWKC
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKC

dg_config=(HAWKA,HAWKB,HAWKC)
fal_server='HAWKB,HAWKC'

Option #2: Using hostname for configurations

ORACLE_SID(db_name)/db_unique_name: HAWK/SLC
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_SLC

dg_config=(LAX,PHX,SLC)
fal_server='LAX,PHX'

I know what you are thinking. Why did I name local_listener when I specifically blogged Why Name Listener?!

I did so for consistency and to avoid having to make too many changes.

As stated from blog post, local_listener=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)) would be better since the only change required would be tns entry for listener alias.

Semi off topic: I still remembered from my martial art teacher, “Expected the unexpected.”

How does this relate to Data Guard and creating standby databases?

Always create database having db_name different from db_unique_name.

One day when there is a requirement to create standby database, environment will be configured with good naming convention. “Expect the unexpected.”

The demonstration will be using Option #2.

One downside, what happens when there is a new host added for the same location?
Use PHX2?
Me being as anal as I am, that’s going to be disturbing.
May be suffix all locations with numeric values?
You decide.

CONFIGURE ARCHIVELOG DELETION TO NONE

There are 1059 datafiles and 5 tempfiles for database duplication and don’t want archivelog to be deleted before second standby is duplicated.

Recovery Manager: Release 12.1.0.2.0 – Production on Mon Apr 13 19:06:26 2020
Finished Duplicate Db at 2020-APR-14 03:42:51

LAX: CONFIGURE ARCHIVELOG DELETION AND DB_UNIQUE_NAME

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
RMAN> CONFIGURE DB_UNIQUE_NAME 'SLC' CONNECT IDENTIFIER 'SLC';

PHX: CONFIGURE ARCHIVELOG DELETION

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

SLC : BUG 19504946 – FLASH CACHE DOESN’T WORK IN OEL7

Apply patch as required.

SLC : Create pfile and startup nomount database

Since Database Smart Flash Cache was configured at PHX (CURRENT STANDBY) and not at SLC (NEW STANDBY),
need to disable db_flash_cache_size and created dummy db_flash_cache_file for the time being until configured.

$ cat initHAWK.ora
*.db_name='HAWK'
*.db_flash_cache_size='0'
*.db_flash_cache_file='+DATA/flashfile.dat'

FOR ALL HOSTS : Verify TNS entries using tnsping from DB_HOME

tnsping LAX; tnsping PHX; tnsping SLC
tnsping LISTENER_LAX; tnsping LISTENER_PHX; tnsping LISTENER_SLC

FOR ALL HOSTS : Verify connect using TNS as SYSDBA

sqlplus sys@LAX as sysdba
sqlplus sys@PHX as sysdba
sqlplus sys@SLC as sysdba

PHX : Perform RMAN Active DB Duplication

$ rman checksyntax @ duplicate.rman

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Apr 13 19:06:15 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

RMAN> set echo on
2> connect target *
3> connect auxiliary *
4> run {
5> allocate channel c1 type disk maxopenfiles 1;
6> allocate channel c2 type disk maxopenfiles 1;
7> allocate channel c3 type disk maxopenfiles 1;
8> allocate auxiliary channel a1 type disk;
9> allocate auxiliary channel a2 type disk;
10> allocate auxiliary channel a3 type disk;
11> duplicate target database for standby from active database
12> spfile
13> parameter_value_convert 'phx','slc','PHX','SLC'
14> set db_unique_name='SLC'
15> set db_flash_cache_size='0'
16> set db_flash_cache_file='+DATA/flashfile.dat'
17> set pga_aggregate_limit='24159191040'
18> set pga_aggregate_target='11596411699'
19> set sga_max_size='32G'
20> set fal_server='LAX,PHX'
21> set local_listener='LISTENER_SLC'
22> nofilenamecheck;
23> }
24> exit
The cmdfile has no syntax errors

Recovery Manager complete.

$ nohup rman @ duplicate.rman > /tmp/duplicate_rman.log 2>&1 &

PHX : Add 2nd Standby (SLC) to DG Broker Configuration

Better not to use dgmgrl / as it may be problematic.

$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@PHX
DGMGRL> add database SLC as connect identifier is SLC;
DGMGRL> enable configuration
DGMGRL> show configuration
DGMGRL> show database SLC
DGMGRL> exit

SLC : Move spfile to ASM

$ echo "+DATA/spfile$ORACLE_SID.ora" > init$ORACLE_SID.ora

SQL> create pfile='/tmp/init@.ora' from spfile;
File created.

SQL> ! ls -l /tmp/init*
-rw-r--r-- 1 oracle oinstall 9511 Apr 14 17:33 /tmp/init$ORACLE_SID.ora

SQL> create spfile='+DATA/spfile$ORACLE_SID.ora' from pfile='/tmp/init$ORACLE_SID.ora';
File created.

SQL>

SLC : Add database to Oracle Oracle Restart

$ export ORACLE_SID=HAWK
$ export ORACLE_UNQNAME=SLC

$ srvctl add database -dbname $ORACLE_SID -db $ORACLE_UNQNAME -instance $ORACLE_SID -oraclehome $ORACLE_HOME \
  -spfile +DATA/spfile$ORACLE_SID.ora -pwfile $ORACLE_HOME/dbs/orapw$ORACLE_SID \
  -role PHYSICAL_STANDBY -diskgroup "FRA,DATA" -startoption MOUNT -stopoption IMMEDIATE

$ srvctl config database -d $ORACLE_UNQNAME
$ srvctl start database -d $ORACLE_UNQNAME
$ srvctl status database -d $ORACLE_UNQNAME -v

$ ps -ef|grep [p]mon

PHX/SLC : Review resource

crsctl stat res -t -w "TYPE = ora.database.type"
crsctl stat res -t -w "TYPE = ora.service.type"

LAX: CONFIGURE ARCHIVELOG DELETION

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

PHX/SLC: CONFIGURE ARCHIVELOG DELETION

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

REFERENCES:

What’s New in Oracle Data Guard?

New 11.2 Features Common to Redo Apply and SQL Apply
The FAL_CLIENT database initialization parameter is no longer required.

Data Guard Broker 12c Release 1 (12.1.0.2)

The following database initialization parameters are controlled by broker configurable properties.
Therefore, you should not set these parameters manually:

ARCHIVE_LAG_TARGET
DB_FILE_NAME_CONVERT
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_STATE_n
LOG_ARCHIVE_FORMAT
LOG_ARCHIVE_MAX_PROCESSES
LOG_ARCHIVE_MIN_SUCCEED_DEST
LOG_ARCHIVE_TRACE
LOG_FILE_NAME_CONVERT
STANDBY_FILE_MANAGEMENT

Configuring RMAN in a Data Guard Environment

CONFIGURE DB_UNIQUE_NAME defines a connection to a physical standby database and implicitly registers the new database.

  New standby databases are also automatically registered when RMAN connects as TARGET to a standby database for the first time.

CONFIGURE FOR DB_UNIQUE_NAME configures settings for a database in the Data Guard environment.

UPDATES:

The only consistency with Oracle is that it’s inconsistent.

*** Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)
Note: Static “_DGMGRL” entries are no longer needed as of Oracle Database 12.1.0.2 in Oracle Data Guard Broker configurations
that are managed by Oracle Restart, RAC On Node or RAC as the Broker will use the clusterware to restart an instance.

*** How to Configure Static Listener Registration for Standalone,RAC and Data Guard setup (Doc ID 2312510.1)

*** Connection to Auxiliary using connect string failed with ORA-12528 (Doc ID 419440.1)
If “Duplicate from Active Database” is used , you must connect using ‘TNS alias’ to both, Target and AUXiliary Instance.
Therefore, the AUXiliary instance should have a static listener configured like:
$ rman target sys/@ auxiliary sys/@

To test, sqlplus (sysdba user), and rman must be able to connect to AUXiliary Instance, even if AUX instance is down/nomount.

For this recommendation is to create a ‘Static Listener’ entry for the AUX instance.

The cause of the problem is that the Auxiliary Instance is started in NOMOUNT status, so it’s not registered with listener.
PMON process registers the instances with the listener when they are in MOUNT status, while instances in NOMOUNT status appear in listener as BLOCKED.

In addition, RMAN will shutdown the auxiliary instance several times during the execution.
Therefore, the connection may succeed initially, then fail with the error later in the execution.

*** Performing RMAN duplicate from standby to create a new clone (Doc ID 1665784.1)

*** Bug 11715084 : ACTIVE DUPLICATE SHOULD WORK WHEN CONNECTED TO STANDBY AS SOURCE DB
Updated : 25-Feb-2020. Fixed in Product Version 12.1

*** Data Guard Physical Standby Changing the SYS password when a broker configuration exists (Doc ID 1199943.1)

Q.E.D.

April 16, 2020

How To Check ORACLE_HOME Is Being Used

Filed under: 18c,Grid Infrastructure,Oracle_Cloud — mdinh @ 1:08 pm

At a high level, seems pretty simple doesn’t it?

Investigating usage for /u01 to free space:

$ df -h /u01
Filesystem                    Size  Used Avail Use% Mounted on
/dev/mapper/VGExaDb-LVDbOra1   20G   15G  4.3G  77% /u01

Find five largest directories for u01:

# du -a /u01 | sort -n -r | head -n 5
61379684        /u01
61379660        /u01/app
30698332        /u01/app/oracle
30698308        /u01/app/oracle/product
16096196        /u01/app/18.1.0.0

# du -a /u01/app/oracle/product | sort -n -r | head -n 5
30698308        /u01/app/oracle/product
8913108 /u01/app/oracle/product/12.1.0.2
8913104 /u01/app/oracle/product/12.1.0.2/dbhome_1
8569260 /u01/app/oracle/product/12.2.0.1
8569256 /u01/app/oracle/product/12.2.0.1/dbhome_1

Different versions of unused ORACLE_HOME from u01:

$ ls -l /u01/app/oracle/product
drwxrwxr-x 3 grid   oinstall 4096 Apr  2  2019 11.2.0.4
drwxrwxr-x 3 grid   oinstall 4096 Apr  2  2019 12.1.0.2
drwxrwxr-x 3 grid   oinstall 4096 Apr  2  2019 12.2.0.1
drwxrwxr-x 3 grid   oinstall 4096 Apr  2  2019 18.1.0.0
drwxr-xr-x 3 oracle oinstall 4096 Apr  3  2019 java

Check inventory and oratab for any reference to u01:

$ grep u01 /u01/app/oraInventory/ContentsXML/inventory.xml
home crs='true' /='' loc='/u01/app/18.1.0.0/grid' name='OraGI18Home1' idx='1' type='O'

$ grep u01 /etc/oratab
+ASM1:/u01/app/18.1.0.0/grid:N          # line added by Agent

Check for processes running from specific home:
Note: 11.2.0.4 show process because currently in the directory.

$ pwd
/u01/app/oracle/product/11.2.0.4

$ lsof +D /u01/app/oracle/product/11.2.0.4
COMMAND    PID   USER   FD   TYPE DEVICE SIZE/OFF   NODE NAME
bash    115430 oracle  cwd    DIR  249,2     4096 524298 /u01/app/oracle/product/11.2.0.4
lsof    172485 oracle  cwd    DIR  249,2     4096 524298 /u01/app/oracle/product/11.2.0.4
lsof    172488 oracle  cwd    DIR  249,2     4096 524298 /u01/app/oracle/product/11.2.0.4

$ lsof +D /u02/app/oracle/product/12.2.0/dbhome_5
$ lsof +D /u02/app/oracle/product/12.2.0/dbhome_17
COMMAND      PID   USER   FD   TYPE  DEVICE SIZE/OFF     NODE NAME
oraagent. 273460 oracle  mem    REG 202,112     1544 25693540 /u02/app/oracle/product/12.2.0/dbhome_17/dbs/hc_DATABASE52.dat
oraagent. 273460 oracle  209r   REG 202,112     1544 25693540 /u02/app/oracle/product/12.2.0/dbhome_17/dbs/hc_DATABASE52.dat

$ cd
$ lsof +D /u01/app/oracle/product/11.2.0.4
$

Suddenly, I remembered srvctl config all which I had blogged about.

$ . oraenv <<< +ASM1
ORACLE_SID = [+ASM1] ? The Oracle base has been set to /u01/app/grid

$ srvctl config all

Oracle Clusterware configuration details
========================================

Oracle Clusterware basic information
------------------------------------
  Operating         Linux
  system
  Name              EXAPHXAD218-039
  Class             STANDALONE
  Cluster nodes     hostname-node1, hostname-node2
  Version           18.0.0.0.0
  Groups            SYSOPER:asmoper SYSASM:asmadmin SYSRAC:asmadmin SYSDBA:asmdba
  Cluster home      /u01/app/18.1.0.0/grid
  OCR locations     +DATAC1
  Voting disk       /dev/exadata_quorum/QD_DATAC1_EF42DA09,
  locations         /dev/exadata_quorum/QD_DATAC1_A4242108, o, o, o

Cluster network configuration details
-------------------------------------
  Interface name  Type  Subnet            Classification
  bondeth0        IPV4  10.157.39.0/26    PUBLIC
  clib0           IPV4  192.168.132.0/22  PRIVATE, ASM
  clib1           IPV4  192.168.132.0/22  PRIVATE, ASM

SCAN configuration details
--------------------------

SCAN "hostname-node-scan.oraclevcn.com" details
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  Name                 hostname-node-scan.oraclevcn.com
  IPv4 subnet          10.157.39.0/26
  DHCP server type     static
  End points           TCP:1521

  SCAN listeners
  --------------
  Name        VIP address
  LISTENER    10.157.39.15


ASM configuration details
-------------------------
  Mode             remote
  Password file    +DATAC1
  SPFILE           +DATAC1

  ASM disk group details
  ++++++++++++++++++++++
  Name    Redundancy
  DATAC1  HIGH
  RECOC1  HIGH

Database configuration details
==============================

Database "ora.database3_pho.db" details
--------------------------------------
  Name               ora.database3_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_21

Database "ora.database2_pho.db" details
-------------------------------------
  Name               ora.database2_pho.db
  Type               RAC
  Version            18.0.0.0.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/18.0.0.0/dbhome_4

Database "ora.database3_pho.db" details
-------------------------------------
  Name               ora.database3_pho.db
  Type               RAC
  Version            18.0.0.0.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/18.0.0.0/dbhome_2

Database "ora.database4_pho.db" details
-------------------------------------
  Name               ora.database4_pho.db
  Type               RAC
  Version            18.0.0.0.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/18.0.0.0/dbhome_3

Database "ora.database5_pho.db" details
-------------------------------------
  Name               ora.database5_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_17

Database "ora.database6_pho.db" details
-------------------------------------
  Name               ora.database6_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_25

Database "ora.database7_pho.db" details
--------------------------------------
  Name               ora.database7_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_19

Database "ora.database8_pho.db" details
-----------------------------------
  Name               ora.database8_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_3

Database "ora.database9_pho.db" details
-----------------------------------
  Name               ora.database9_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_20

Database "ora.databasea_pho.db" details
-----------------------------------
  Name               ora.databasea_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_26

Database "ora.databaseb_pho.db" details
--------------------------------------
  Name               ora.databaseb_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_18

Database "ora.databasec_pho.db" details
-----------------------------------
  Name               ora.databasec_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_4

Database "ora.databased_pho.db" details
-----------------------------------
  Name               ora.databased_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_22

Database "ora.databasec_pho.db" details
-----------------------------------
  Name               ora.wus3p_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_23

Database "ora.databased_pho.db" details
--------------------------------------
  Name               ora.databased_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_8

Database "ora.databasee_pho.db" details
-----------------------------------
  Name               ora.databasee_pho.db
  Type               RAC
  Version            12.2.0.1.0
  Role               PHYSICAL_STANDBY
  Management         AUTOMATIC
  policy
  SPFILE             +DATAC1
  Password file      +DATAC1
  Groups             OSDBA:dba OSOPER:racoper OSBACKUP:dba OSDG:dba OSKM:dba
                     OSRAC:dba
  Oracle home        /u02/app/oracle/product/12.2.0/dbhome_24
$

By the way, this is Oracle Database Exadata Cloud Service from what I was informed.

These are actual number of databases.

Looks to be in Phoenix region base on pho suffix in the name?

How does one tell if on cloud or on premise?

My guess is oraclevcn.com from hostname.

April 5, 2020

Using sshUserSetup.sh for Passwordless ssh

Filed under: Dataguard — mdinh @ 12:03 pm

Quick short and sweet. I am creating POC for Dataguard with multiple standby configuration using/hacking vagrant virtual box.

Being as lazy as I am and not liking to have to enter password, use sshUserSetup.sh

[oracle@ol7-121-dg1 ~]$ cd /u01/software/database/sshsetup/
[oracle@ol7-121-dg1 sshsetup]$


[oracle@ol7-121-dg1 sshsetup]$ ./sshUserSetup.sh -h
Please specify a valid and existing cluster configuration file.
Either user name or host information is missing
Usage ./sshUserSetup.sh -user <user name> [ -hosts "<space separated hostlist>" | -hostfile <absolute path of cluster configuration file> ] [ -advanced ]  [ -verify] [ -exverify ] [ -logfile <desired absolute path of logfile> ] [-confirm] [-shared] [-help] [-usePassphrase] [-noPromptPassphrase]
[oracle@ol7-121-dg1 sshsetup]$


[oracle@ol7-121-dg1 sshsetup]$ ./sshUserSetup.sh -user oracle -hosts "ol7-121-dg1 ol7-121-dg2 ol7-121-dg3" -noPromptPassphrase
The output of this script is also logged into /tmp/sshUserSetup_2020-04-05-11-53-56.log
Hosts are ol7-121-dg1 ol7-121-dg2 ol7-121-dg3
user is oracle
Platform:- Linux
Checking if the remote hosts are reachable
PING ol7-121-dg1.localdomain (192.168.56.101) 56(84) bytes of data.
64 bytes from ol7-121-dg1.localdomain (192.168.56.101): icmp_seq=1 ttl=64 time=0.016 ms
64 bytes from ol7-121-dg1.localdomain (192.168.56.101): icmp_seq=2 ttl=64 time=0.019 ms
64 bytes from ol7-121-dg1.localdomain (192.168.56.101): icmp_seq=3 ttl=64 time=0.036 ms
64 bytes from ol7-121-dg1.localdomain (192.168.56.101): icmp_seq=4 ttl=64 time=0.045 ms
64 bytes from ol7-121-dg1.localdomain (192.168.56.101): icmp_seq=5 ttl=64 time=0.041 ms

--- ol7-121-dg1.localdomain ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4293ms
rtt min/avg/max/mdev = 0.016/0.031/0.045/0.012 ms
PING ol7-121-dg2.localdomain (192.168.56.102) 56(84) bytes of data.
64 bytes from ol7-121-dg2.localdomain (192.168.56.102): icmp_seq=1 ttl=64 time=0.333 ms
64 bytes from ol7-121-dg2.localdomain (192.168.56.102): icmp_seq=2 ttl=64 time=0.657 ms
64 bytes from ol7-121-dg2.localdomain (192.168.56.102): icmp_seq=3 ttl=64 time=0.547 ms
64 bytes from ol7-121-dg2.localdomain (192.168.56.102): icmp_seq=4 ttl=64 time=0.539 ms
64 bytes from ol7-121-dg2.localdomain (192.168.56.102): icmp_seq=5 ttl=64 time=0.514 ms

--- ol7-121-dg2.localdomain ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4310ms
rtt min/avg/max/mdev = 0.333/0.518/0.657/0.104 ms
PING ol7-121-dg3.localdomain (192.168.56.103) 56(84) bytes of data.
64 bytes from ol7-121-dg3.localdomain (192.168.56.103): icmp_seq=1 ttl=64 time=0.356 ms
64 bytes from ol7-121-dg3.localdomain (192.168.56.103): icmp_seq=2 ttl=64 time=0.554 ms
64 bytes from ol7-121-dg3.localdomain (192.168.56.103): icmp_seq=3 ttl=64 time=0.463 ms
64 bytes from ol7-121-dg3.localdomain (192.168.56.103): icmp_seq=4 ttl=64 time=0.362 ms
64 bytes from ol7-121-dg3.localdomain (192.168.56.103): icmp_seq=5 ttl=64 time=0.472 ms

--- ol7-121-dg3.localdomain ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4517ms
rtt min/avg/max/mdev = 0.356/0.441/0.554/0.076 ms
Remote host reachability check succeeded.
The following hosts are reachable: ol7-121-dg1 ol7-121-dg2 ol7-121-dg3.
The following hosts are not reachable: .
All hosts are reachable. Proceeding further...
firsthost ol7-121-dg1
numhosts 3
The script will setup SSH connectivity from the host ol7-121-dg1.localdomain to all
the remote hosts. After the script is executed, the user can use SSH to run
commands on the remote hosts or copy files between this host ol7-121-dg1.localdomain
and the remote hosts without being prompted for passwords or confirmations.

NOTE 1:
As part of the setup procedure, this script will use ssh and scp to copy
files between the local host and the remote hosts. Since the script does not
store passwords, you may be prompted for the passwords during the execution of
the script whenever ssh or scp is invoked.

NOTE 2:
AS PER SSH REQUIREMENTS, THIS SCRIPT WILL SECURE THE USER HOME DIRECTORY
AND THE .ssh DIRECTORY BY REVOKING GROUP AND WORLD WRITE PRIVILEDGES TO THESE
directories.

Do you want to continue and let the script make the above mentioned changes (yes/no)?
yes

The user chose yes
User chose to skip passphrase related questions.
Creating .ssh directory on local host, if not present already
Creating authorized_keys file on local host
Changing permissions on authorized_keys to 644 on local host
Creating known_hosts file on local host
Changing permissions on known_hosts to 644 on local host
Creating config file on local host
If a config file exists already at /home/oracle/.ssh/config, it would be backed up to /home/oracle/.ssh/config.backup.
Creating .ssh directory and setting permissions on remote host ol7-121-dg1
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR oracle. THIS IS AN SSH REQUIREMENT.
The script would create ~oracle/.ssh/config file on remote host ol7-121-dg1. If a config file exists already at ~oracle/.ssh/config, it would be backed up to ~oracle/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host ol7-121-dg1.
Warning: Permanently added 'ol7-121-dg1,192.168.56.101' (ECDSA) to the list of known hosts.
oracle@ol7-121-dg1's password:
Done with creating .ssh directory and setting permissions on remote host ol7-121-dg1.
Creating .ssh directory and setting permissions on remote host ol7-121-dg2
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR oracle. THIS IS AN SSH REQUIREMENT.
The script would create ~oracle/.ssh/config file on remote host ol7-121-dg2. If a config file exists already at ~oracle/.ssh/config, it would be backed up to ~oracle/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host ol7-121-dg2.
Warning: Permanently added 'ol7-121-dg2,192.168.56.102' (ECDSA) to the list of known hosts.
oracle@ol7-121-dg2's password:
Done with creating .ssh directory and setting permissions on remote host ol7-121-dg2.
Creating .ssh directory and setting permissions on remote host ol7-121-dg3
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR oracle. THIS IS AN SSH REQUIREMENT.
The script would create ~oracle/.ssh/config file on remote host ol7-121-dg3. If a config file exists already at ~oracle/.ssh/config, it would be backed up to ~oracle/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host ol7-121-dg3.
Warning: Permanently added 'ol7-121-dg3,192.168.56.103' (ECDSA) to the list of known hosts.
oracle@ol7-121-dg3's password:
Done with creating .ssh directory and setting permissions on remote host ol7-121-dg3.
Copying local host public key to the remote host ol7-121-dg1
The user may be prompted for a password or passphrase here since the script would be using SCP for host ol7-121-dg1.
oracle@ol7-121-dg1's password:
Done copying local host public key to the remote host ol7-121-dg1
Copying local host public key to the remote host ol7-121-dg2
The user may be prompted for a password or passphrase here since the script would be using SCP for host ol7-121-dg2.
oracle@ol7-121-dg2's password:
Done copying local host public key to the remote host ol7-121-dg2
Copying local host public key to the remote host ol7-121-dg3
The user may be prompted for a password or passphrase here since the script would be using SCP for host ol7-121-dg3.
oracle@ol7-121-dg3's password:
Done copying local host public key to the remote host ol7-121-dg3
cat: /home/oracle/.ssh/known_hosts.tmp: No such file or directory
cat: /home/oracle/.ssh/authorized_keys.tmp: No such file or directory
SSH setup is complete.

------------------------------------------------------------------------
Verifying SSH setup
===================
The script will now run the date command on the remote nodes using ssh
to verify if ssh is setup correctly. IF THE SETUP IS CORRECTLY SETUP,
THERE SHOULD BE NO OUTPUT OTHER THAN THE DATE AND SSH SHOULD NOT ASK FOR
PASSWORDS. If you see any output other than date or are prompted for the
password, ssh is not setup correctly and you will need to resolve the
issue and set up ssh again.
The possible causes for failure could be:
1. The server settings in /etc/ssh/sshd_config file do not allow ssh
for user oracle.
2. The server may have disabled public key based authentication.
3. The client public key on the server may be outdated.
4. ~oracle or ~oracle/.ssh on the remote host may not be owned by oracle.
5. User may not have passed -shared option for shared remote users or
may be passing the -shared option for non-shared remote users.
6. If there is output in addition to the date, but no password is asked,
it may be a security alert shown as part of company policy. Append the
additional text to the <OMS HOME>/sysman/prov/resources/ignoreMessages.txt file.
------------------------------------------------------------------------
--ol7-121-dg1:--
Running /usr/bin/ssh -x -l oracle ol7-121-dg1 date to verify SSH connectivity has been setup from local host to ol7-121-dg1.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR.
Sun Apr  5 11:54:28 UTC 2020
------------------------------------------------------------------------
--ol7-121-dg2:--
Running /usr/bin/ssh -x -l oracle ol7-121-dg2 date to verify SSH connectivity has been setup from local host to ol7-121-dg2.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR.
Sun Apr  5 11:54:28 UTC 2020
------------------------------------------------------------------------
--ol7-121-dg3:--
Running /usr/bin/ssh -x -l oracle ol7-121-dg3 date to verify SSH connectivity has been setup from local host to ol7-121-dg3.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR.
Sun Apr  5 11:54:28 UTC 2020
------------------------------------------------------------------------
SSH verification complete.
[oracle@ol7-121-dg1 sshsetup]$

I wonder if Tim reads my blog?

Why Name Listener?!

Filed under: Dataguard — mdinh @ 11:48 am

May be I am too naive to know, If you have reason, then please share.

With the following configuration, environment can be easily migrated/duplicated with minimal or no change.
The only change would be port for local_listener if port# changed.

Also, easier to use and maintain.

local_listener=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1591))
lsnrctl status
tnsnames.ora entry ***not*** required

With the following configuration, environment can be migrated/duplicated requiring multiple changes.

local_listener=LISTENER_NAME
lsnrctl status LISTENER_NAME
tnsnames.ora entry required for LISTENER_NAME

If listener is named, then does every environment has different names?

Security will mostly likely be used for justification but I don’t see it.

DATAGUARD sqlnet.ora NAMES.DEFAULT_DOMAIN

Filed under: 12c,Dataguard — mdinh @ 5:20 am

If you just want the solution, then read Database Startup Fails With ORA-00119 (Doc ID 471767.1)

From standby database, startup mount resulted in the following errors:

ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER'

When setting local_listener, my preference is:

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))' scope=spfile sid='*';

However, some implementations will use the following:

alter system set local_listener=LISTENER scope=spfile sid='*';

There are pros and cons to both.

When using LISTENER with tnsnames, modifications can be performed from tnsnames.ora without having to modify database parameters.

However, it’s not forgiving when there are misconfiguration.

Demo 1:
Modify local_listener and restart DB.

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))' scope=spfile sid='*';

System altered.

SQL> shu abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2924928 bytes
Variable Size             520097408 bytes
Database Buffers         1073741824 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------------------------
listener_networks                    string
local_listener                       string      (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))
remote_listener                      string
SQL>

Demo 2:
Modify local_listener and restart DB failed. Modify sqlnet.ora (delete NAMES.DEFAULT_DOMAIN=world).

### This is a bad omen and no changes should be made to DB until tnsping is resolved.
[oracle@ol7-121-dg2 ~]$ tnsping LISTENER

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-APR-2020 04:32:05

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name
[oracle@ol7-121-dg2 ~]$

### Restart DB FAILED
SQL> alter system set local_listener=LISTENER scope=spfile sid='*';

System altered.

SQL> shu abort
ORACLE instance shut down.
SQL> startup mount
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER'
SQL>

That’s not good!

Check sqlnet.ora

[oracle@ol7-121-dg2 ~]$ cat /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=400
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)

SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256)

SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256)
NAMES.DEFAULT_DOMAIN=world

[oracle@ol7-121-dg2 ~]$ vi /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

[oracle@ol7-121-dg2 ~]$ cat /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=400
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)

SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256)

SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256)
[oracle@ol7-121-dg2 ~]$

Did you see the problem? Not sure why NAMES.DEFAULT_DOMAIN=world was set and remove solved the issue.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2924928 bytes
Variable Size             520097408 bytes
Database Buffers         1073741824 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      LISTENER
remote_listener                      string
SQL>

Demo 3:
Rollback sqlnet.ora (add NAMES.DEFAULT_DOMAIN=world), modify tnsnames.ora (LISTENER.world), and restart DB.

[oracle@ol7-121-dg2 ~]$ cat /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=400
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)

SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256)

SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256)

NAMES.DEFAULT_DOMAIN=world

[oracle@ol7-121-dg2 ~]$ tnsping LISTENER

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-APR-2020 04:54:52

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name
[oracle@ol7-121-dg2 ~]$

[oracle@ol7-121-dg2 ~]$ cat /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/tnsnames.ora
LISTENER.world = (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg2.localdomain)(PORT = 1521))

hawka.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg1.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = hawk)
    )
  )

hawkb.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg2.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = hawk)
    )
  )

[oracle@ol7-121-dg2 ~]$ vi /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/tnsnames.ora

[oracle@ol7-121-dg2 ~]$ tnsping LISTENER

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-APR-2020 05:04:18

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg2.localdomain)(PORT = 1521))
OK (0 msec)

[oracle@ol7-121-dg2 ~]$

SQL> shu abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2924928 bytes
Variable Size             520097408 bytes
Database Buffers         1073741824 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      LISTENER
remote_listener                      string
SQL>

As demonstrated, having more options is not always good as it can lead to more likelihood for errors. Chose your evil wisely.

Next Page »

Create a free website or blog at WordPress.com.