Error to apply patch – OPATCHAUTO-72043

To solve the problem, change the permission/owner to your owner like oracle or grid.

See below the steps:

[[email protected] 30920127]# cat /etc/oratab
#
+ASM2:/u01/app/19.0.0/grid:N
teste:/u01/app/oracle/product/12.2.0/dbhome_1:N
[[email protected] ~]# . oraenv
ORACLE_SID = [root] ? teste
The Oracle base has been set to /u01/app/oracle
[[email protected] ~]# /u01/app/oracle/product/12.2.0/dbhome_1/OPatch/opatchauto apply /stage/midia/30920127 -oh $ORACLE_HOME

OPatchauto session is initiated at Mon Nov 7 13:50:27 2020

System initialization log file is /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatchautodb/systemconfig2020-11-07_02-50-29PM.log.

Session log file is /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatchauto/opatchauto2020-11-07_02-50-37PM.log
The id for this session is 3CTC
OPATCHAUTO-72043: Patch collection failed.
OPATCHAUTO-72043: Failed to create bundle patch object.
OPATCHAUTO-72043: Please verify the patch supplied.
OPatchAuto failed.

OPatchauto session completed at Mon Nov 7 13:50:38 2020
Time taken to complete the session 0 minute, 11 seconds

opatchauto failed with error code 42
[[email protected] ~]# pwd
/root
[[email protected] ~]# cd /stage/midia/30920127/
[[email protected] 30920127]# ls
26839277 30869447 30882603 30886680 30888810 automation bundle.xml README.html README.txt
[[email protected] 30920127]# chown oracle:oinstall * -Rf
[[email protected] 30920127]# /u01/app/oracle/product/12.2.0/dbhome_1/OPatch/opatchauto apply /stage/midia/30920127 -oh $ORACLE_HOME

OPatchauto session is initiated at Mon Nov 7 13:50:59 2020

System initialization log file is /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatchautodb/systemconfig2020-11-07_02-51-01PM.log.

Session log file is /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatchauto/opatchauto2020-11-07_02-51-09PM.log
The id for this session is RB4L
OPATCHAUTO-72043: Patch collection failed.
OPATCHAUTO-72043: Failed to create bundle patch object.
OPATCHAUTO-72043: Please verify the patch supplied.
OPatchAuto failed.

OPatchauto session completed at Mon Nov 7 13:51:10 2020
Time taken to complete the session 0 minute, 11 seconds

opatchauto failed with error code 42
[[email protected] 30920127]# chown oracle:oinstall ../* -Rf

or
chown oracle:oinstall /stage/midia/30920127 -Rf

##############################################################################
Problem solved !!!!
##############################################################################

[[email protected] 30920127]# /u01/app/oracle/product/12.2.0/dbhome_1/OPatch/opatchauto apply /stage/midia/30920127 -oh $ORACLE_HOME

OPatchauto session is initiated at Mon Nov 7 13:51:24 2020

System initialization log file is /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatchautodb/systemconfig2020-11-07_02-51-26PM.log.

Session log file is /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatchauto/opatchauto2020-11-07_02-51-33PM.log
The id for this session is WH1A

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/12.2.0/dbhome_1
Patch applicability verified successfully on home /u01/app/oracle/product/12.2.0/dbhome_1

Verifying SQL patch applicability on home /u01/app/oracle/product/12.2.0/dbhome_1
No step execution required………

Preparing to bring down database service on home /u01/app/oracle/product/12.2.0/dbhome_1
No step execution required………

Performing prepatch operation on home /u01/app/oracle/product/12.2.0/dbhome_1
Perpatch operation completed successfully on home /u01/app/oracle/product/12.2.0/dbhome_1

Start applying binary patch on home /u01/app/oracle/product/12.2.0/dbhome_1
Binary patch applied successfully on home /u01/app/oracle/product/12.2.0/dbhome_1

Performing postpatch operation on home /u01/app/oracle/product/12.2.0/dbhome_1
Postpatch operation completed successfully on home /u01/app/oracle/product/12.2.0/dbhome_1

Preparing home /u01/app/oracle/product/12.2.0/dbhome_1 after database service restarted
No step execution required………

Trying to apply SQL patch on home /u01/app/oracle/product/12.2.0/dbhome_1
No step execution required………

OPatchAuto successful.

——————————–Summary——————————–

Patching is completed successfully. Please find the summary as follows:

Host:machine01
RAC Home:/u01/app/oracle/product/12.2.0/dbhome_1
Version:12.2.0.1.0
Summary:

==Following patches were SKIPPED:

Patch: /stage/midia/30920127/30869447
Reason: This patch is not applicable to this specified target type – “rac_database”

Patch: /stage/midia/30920127/26839277
Reason: This patch is not applicable to this specified target type – “rac_database”

Patch: /stage/midia/30920127/30888810
Reason: This patch is not applicable to this specified target type – “rac_database”

==Following patches were SUCCESSFULLY applied:

Patch: /stage/midia/30920127/30882603
Log: /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2020-11-07_14-51-45PM_1.log

Patch: /stage/midia/30920127/30886680
Log: /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2020-11-07_14-51-45PM_1.log

OPatchauto session completed at Mon Nov 7 13:54:01 2020
Time taken to complete the session 2 minutes, 37 seconds

[[email protected] 30920127]#

Installing and running RDA

The rda can be downloaded from this page:
Remote Diagnostic Agent (RDA) – FAQ (Doc ID 330363.1)

Download it, and unzip on your preferred folder.

Checking current rda:

[[email protected] rda]# /opt/oracle.ahf/tfa/ext/darda/da/rda/rda.sh -cv
Loading the file list …
Checking the directory [D_RDA] . …
Checking the directory [D_RDA] engine …
Checking the directory [D_RDA_ADM] model …
Checking the directory [D_RDA_CHK] APPS …
Checking the directory [D_RDA_CHK] BI …
Checking the directory [D_RDA_CHK] CGBU …
Checking the directory [D_RDA_CHK] DB …
Checking the directory [D_RDA_CHK] EM …
Checking the directory [D_RDA_CHK] OFM …
Checking the directory [D_RDA_CHK] TEST …
Checking the directory [D_RDA_COL] APPS …
Checking the directory [D_RDA_COL] BI …
Checking the directory [D_RDA_COL] CGBU …
Checking the directory [D_RDA_COL] CLOUD …
Checking the directory [D_RDA_COL] DA …
Checking the directory [D_RDA_COL] DB …
Checking the directory [D_RDA_COL] EM …
Checking the directory [D_RDA_COL] EXPLORER …
Checking the directory [D_RDA_COL] OFM …
Checking the directory [D_RDA_COL] OS …
Checking the directory [D_RDA_COL] PGBU …
Checking the directory [D_RDA_COL] RDA …
Checking the directory [D_RDA_COL] SAMPLE …
Checking the directory [D_RDA_COL] TOOL …
Checking the directory [D_RDA_CSS] . …
Checking the directory [D_RDA_DAT] . …
Checking the directory [D_RDA_DFW] cv0200 …
Checking the directory [D_RDA_INC] Convert/Common …
Checking the directory [D_RDA_INC] Convert/DB/LOG …
Checking the directory [D_RDA_INC] Convert/OFM/OIM …
Checking the directory [D_RDA_INC] Convert/OS/INST …
Checking the directory [D_RDA_INC] Convert/OS/OS …
Checking the directory [D_RDA_INC] Convert/RDA/CONFIG …
Checking the directory [D_RDA_INC] Convert/TOOL/ALERT …
Checking the directory [D_RDA_INC] Convert/TOOL/COMPLY …
Checking the directory [D_RDA_INC] IRDA …
Checking the directory [D_RDA_INC] IRDA/CV0200 …
Checking the directory [D_RDA_INC] RDA …
Checking the directory [D_RDA_INC] RDA/Agent …
Checking the directory [D_RDA_INC] RDA/Driver …
Checking the directory [D_RDA_INC] RDA/Handle …
Checking the directory [D_RDA_INC] RDA/Library …
Checking the directory [D_RDA_INC] RDA/Limit …
Checking the directory [D_RDA_INC] RDA/Local …
Checking the directory [D_RDA_INC] RDA/Object …
Checking the directory [D_RDA_INC] RDA/Operator …
Checking the directory [D_RDA_INC] RDA/Request …
Checking the directory [D_RDA_INC] RDA/SDCL …
Checking the directory [D_RDA_INC] RDA/SDSL …
Checking the directory [D_RDA_INC] RDA/Target …
Checking the directory [D_RDA_INC] RDA/Token …
Checking the directory [D_RDA_INC] RDA/UI …
Checking the directory [D_RDA_INC] RDA/Value …
Checking the directory [D_RDA_INC] RDA/Web …
Checking the directory [D_RDA_MSG] charset …
Checking the directory [D_RDA_MSG] desc …
Checking the directory [D_RDA_MSG] en …
Checking the directory [D_RDA_MSG] fr …
Checking the directory [D_RDA_POD] en …

No issues found

Installing/setup.

[[email protected] rda]# chmod +x rda.sh rda.pl
[[email protected] rda]# chown oracle:oinstall /stage/rda -Rf
[[email protected] rda]# pwd
/stage/rda

Checking versions:

[[email protected] rda]# /opt/oracle.ahf/tfa/ext/darda/da/rda/rda.sh -vXRda check -A
Old build 20200121
[[email protected] rda]# ./rda.sh -vXRda check -A
Old build 20200421

[[email protected] rda]# ./rda.sh -S
——————————————————————————
RDA.BEGIN: Initializes the Data Collection
——————————————————————————
Enter the Oracle home to be used for data analysis

Follow all steps.

To run: Use oracle user – this is the recommendation from oracle.

[exadatavm01 [email protected]]/stage/rda> ./rda.sh
——————————————————————————
RDA Data Collection Started 29-Oct-2020 10:58:10
——————————————————————————
Processing RDA.BEGIN module …
Enter the password for “SYSTEM” at “ORCL1”:
Re-enter it to confirm:
Processing OS.PERF module …
Processing RDA.CONFIG module …
Processing SAMPLE.SAMPLE modu

Updating TFACTL and EXACHK in exadata at customer

Instaling latest version of tfa – in exadata at customer

Instalation used root user.

[[email protected] midia]#
[[email protected] midia]# /u02/opt/oracle.tfa/tfa/exadatavm01/tfa_home/bin/tfactl -version
TFA Version : 194400
TFA Build ID : 20200717142338
[[email protected] midia]# ./ahf_setup -ahf_loc /opt -silent -local -data_dir /u02

AHF Installer for Platform Linux Architecture x86_64

AHF Installation Log : /tmp/ahf_install_202300_235200_2020_10_28-15_59_01.log

Starting Autonomous Health Framework (AHF) Installation

AHF Version: 20.2.3 Build Date: 202010121848

TFA is already installed at : /u02/opt/oracle.tfa/tfa/exadatavm01/tfa_home

Installed TFA Version : 194400 Build ID : 20200717142338

AHF Location : /opt/oracle.ahf

AHF Data Directory : /u02/oracle.ahf/data

Shutting down TFA : /u02/opt/oracle.tfa/tfa/exadatavm01/tfa_home

Copying TFA Data Files from /u02/opt/oracle.tfa/tfa/exadatavm01/tfa_home

Uninstalling TFA : /u02/opt/oracle.tfa/tfa/exadatavm01/tfa_home

Extracting AHF to /opt/oracle.ahf

Configuring TFA Services

Copying TFA Data Files to AHF

Discovering Nodes and Oracle Resources

Starting TFA Services
Created symlink from /etc/systemd/system/multi-user.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.
Created symlink from /etc/systemd/system/graphical.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.

.———————————————————————————–.
| Host | Status of TFA | PID | Port | Version | Build ID |
+—————+—————+——–+——+————+———————-+
| exadatavm01 | RUNNING | 288357 | 5000 | 20.2.3.0.0 | 20230020201012184854 |
| exadatavm02 | RUNNING | 10598 | 5000 | 19.4.4.0.0 | 19440020200717142338 |
| exadatavm03 | RUNNING | 10301 | 5000 | 19.4.4.0.0 | 19440020200717142338 |
| exadatavm04 | RUNNING | 11142 | 5000 | 19.4.4.0.0 | 19440020200717142338 |
‘—————+—————+——–+——+————+———————-‘

Running TFA Inventory…

Adding default users to TFA Access list…

.———————————————————–.
| Summary of AHF Configuration |
+—————–+—————————————–+
| Parameter | Value |
+—————–+—————————————–+
| AHF Location | /opt/oracle.ahf |
| TFA Location | /opt/oracle.ahf/tfa |
| Exachk Location | /opt/oracle.ahf/exachk |
| Data Directory | /u02/oracle.ahf/data |
| Repository | /u02/oracle.ahf/data/repository |
| Diag Directory | /u02/oracle.ahf/data/exadatavm01/diag |
‘—————–+—————————————–‘

Starting exachk scheduler from AHF …

AHF binaries are available in /opt/oracle.ahf/bin

AHF is successfully installed

Moving /tmp/ahf_install_202300_235200_2020_10_28-15_59_01.log to /u02/oracle.ahf/data/exadatavm01/diag/ahf/

[[email protected] midia]#

After repeat the same in all nodes – final node:

[[email protected] midia]# scp exadatavm01:/stage/midia/AHF-LINUX_v20.2.3.zip .
AHF-LINUX_v20.2.3.zip 100% 389MB 204.5MB/s 00:01
[[email protected] midia]# unzip AHF-LINUX_v20.2.3.zip
Archive: AHF-LINUX_v20.2.3.zip
inflating: README.txt
inflating: ahf_setup
[[email protected] midia]# ./ahf_setup -ahf_loc /opt -silent -local -data_dir /u02

AHF Installer for Platform Linux Architecture x86_64

AHF Installation Log : /tmp/ahf_install_202300_183018_2020_10_28-17_19_47.log

Starting Autonomous Health Framework (AHF) Installation

AHF Version: 20.2.3 Build Date: 202010121848

TFA is already installed at : /u02/opt/oracle.tfa/tfa/exadatavm04/tfa_home

Installed TFA Version : 194400 Build ID : 20200717142338

AHF Location : /opt/oracle.ahf

AHF Data Directory : /u02/oracle.ahf/data

Shutting down TFA : /u02/opt/oracle.tfa/tfa/exadatavm04/tfa_home

Copying TFA Data Files from /u02/opt/oracle.tfa/tfa/exadatavm04/tfa_home

Uninstalling TFA : /u02/opt/oracle.tfa/tfa/exadatavm04/tfa_home

Extracting AHF to /opt/oracle.ahf

Configuring TFA Services

Copying TFA Data Files to AHF

Discovering Nodes and Oracle Resources

Starting TFA Services
Created symlink from /etc/systemd/system/multi-user.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.
Created symlink from /etc/systemd/system/graphical.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.

.———————————————————————————–.
| Host | Status of TFA | PID | Port | Version | Build ID |
+—————+—————+——–+——+————+———————-+
| exadatavm01 | RUNNING | 240533 | 5000 | 20.2.3.0.0 | 20230020201012184854 |
| exadatavm02 | RUNNING | 288357 | 5000 | 20.2.3.0.0 | 20230020201012184854 |
| exadatavm03 | RUNNING | 24967 | 5000 | 20.2.3.0.0 | 20230020201012184854 |
| exadatavm04 | RUNNING | 390067 | 5000 | 20.2.3.0.0 | 20230020201012184854 |
‘—————+—————+——–+——+————+———————-‘

Running TFA Inventory…

Adding default users to TFA Access list…

.———————————————————–.
| Summary of AHF Configuration |
+—————–+—————————————–+
| Parameter | Value |
+—————–+—————————————–+
| AHF Location | /opt/oracle.ahf |
| TFA Location | /opt/oracle.ahf/tfa |
| Exachk Location | /opt/oracle.ahf/exachk |
| Data Directory | /u02/oracle.ahf/data |
| Repository | /u02/oracle.ahf/data/repository |
| Diag Directory | /u02/oracle.ahf/data/exadatavm04/diag |
‘—————–+—————————————–‘

Starting exachk scheduler from AHF …

AHF binaries are available in /opt/oracle.ahf/bin

AHF is successfully installed

Moving /tmp/ahf_install_202300_183018_2020_10_28-17_19_47.log to /u02/oracle.ahf/data/exadatavm04/diag/ahf/

[[email protected] midia]#

Reference:

Autonomous Health Framework (AHF) – Including TFA and ORAchk/EXAChk (Doc ID 2550798.1)

https://docs.oracle.com/en/engineered-systems/health-diagnostics/autonomous-health-framework/ahfug/installing-and-upgrading-ahf.html#GUID-663F0836-A2A2-4EFB-B19E-EABF303739A9

Oracle Exadata Database Machine EXAchk (Doc ID 1070954.1) – updating for the latest tfa and exachk.

 

Upload last incident file:

adrci> set homepath diag/rdbms/emb11204/EMB11204
adrci> show incident

INCIDENT_ID PROBLEM_KEY CREATE_TIME
——————– —————————– ———————————
45854 ORA 7445 [kfkNotify()+25] 2014-03-05 19:11:23.914000 +00:00

adrci> ips pack incident 45854 in /tmp
Generated package 1 in file /tmp/ORA7445kf_20140327095418_COM_1.zip, mode complete

See Dcoument: 443529.1 for additonal details and instructions.

Collect and upload the incident zip file.

 

 

My prefered .bash_profile

export ORACLE_HOME
export ORACLE_HOME_LISTNER=$ORACLE_HOME
export ORACLE_HOSTNAME=`hostname`
export ORA_INVENTORY=/u01/app/oraInventory
export ORACLE_SID=CDB
export PDB_NAME=pdb1
export DATA_DIR=/u02/oradata

export ORACLE_SID
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1

export PATH=/usr/sbin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

ohvers ()
{
echo -n $ORACLE_HOME | sed -n ‘s/.*\/\([[:digit:].]\+\)\/.*/\1/p’
}
#export PS1=$’\\n# [ [email protected]\h:$PWD [\\t] [`ohvers` SID:${ORACLE_SID:-“no sid”}] ]\\n# ‘
PS1=’\[\e[31m\][`ohvers` SID:${ORACLE_SID:-“no sid”}][[email protected]\h:$PWD ]\[\e[0m\] \W\n\$ ‘
export JAVA_HOME=/u01/app/oracle/ODI_JAVA/jdk

echo “############################################### ”
echo “## Instances Currently Running in This Server”
echo “############################################### ”
ps -eaf | grep pmon | sed ‘s/^.*pmon_//g’ | egrep -v ‘//g|grep|+ASM|-MGMTDB’| sort

export NLS_DATE_FORMAT=’DD/MM/YYYY HH24:MI:SS’

How to solve – ORA-38788: More standby database recovery is needed

When doing a duplicate of database, restore, or simply convert to snapshot – we can get the error ORA-38788. See below:

SQL> ALTER DATABASE FLASHBACK ON;
ALTER DATABASE FLASHBACK ON
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38788: More standby database recovery is needed

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
*
ERROR at line 1:
ORA-38784: Cannot create restore point ‘SNAPSHOT_STANDBY_REQUIRED_10/17/2020 10:21:50’.
ORA-38788: More standby database recovery is needed

############################
Solution:
############################

If I recovered using rman -> recover database – will not stop until the last archivelog.
I tried to recover until sequence but the problem was the same. Requesting more recover.
This approach can be tried – but, if you start to recover database you will use the option REAL APPLY.
Please don’t use this approach if you are not totally fine with the terms used here.
Real apply is an option – and is very expensive. To use open database read only and start recover of archivelogs.
Only opening database for read only without recover process active, is fine.

Check I am stopping the recovery processes first:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.

My first shot: Tried to put flashback on: Not working.

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
*
ERROR at line 1:
ORA-38784: Cannot create restore point ‘SNAPSHOT_STANDBY_REQUIRED_10/17/2020 10:30:04’.
ORA-38788: More standby database recovery is needed

My solution: starting to open read only, database will apply all necessary logs and will open database read only.
This means, will not apply all archivelogs present in recovery area, will apply only necessary to the recover database.

SQL> alter database open read only;

##################################################
Alert log file:
##################################################
alter database open read only
2020-10-17T10:30:56.729708-03:00
Ping without log force is disabled:
instance mounted in exclusive mode.
Signalling error 1152 for datafile 1!
2020-10-17T10:30:56.804252-03:00
Beginning Standby Crash Recovery.
Started logmerger process
2020-10-17T10:30:57.262836-03:00
Managed Standby Recovery starting Real Time Apply
2020-10-17T10:31:00.947756-03:00
Parallel Media Recovery started with 8 slaves
2020-10-17T10:31:02.173885-03:00
Media Recovery Log +RECOC2/CDBX/ARCHIVELOG/2020_10_17/thread_1_seq_60765.508.1054028951
2020-10-17T10:31:02.355754-03:00
Media Recovery Log +RECOC2/CDBX/ARCHIVELOG/2020_10_17/thread_2_seq_57944.399.1054029017
2020-10-17T10:32:04.559638-03:00

And more archivelogs .. will stop when oracle database stay consistent.

Media Recovery Log +RECOC2/CDBX/ARCHIVELOG/2020_10_17/thread_2_seq_57979.366.1054029013
2020-10-17T10:44:11.183620-03:00
Incomplete Recovery applied until change 89911707313 time 10/16/2020 09:08:00
2020-10-17T10:44:12.665363-03:00
Completed Standby Crash Recovery.
2020-10-17T10:44:14.195065-03:00
Endian type of dictionary set to little
2020-10-17T10:44:21.001041-03:00
Undo initialization finished serial:0 start:2974475204 end:2974475204 diff:0 ms (0.0 seconds)
Dictionary check beginning
2020-10-17T10:44:23.588573-03:00

##################################################
Database was open
##################################################

Pluggable database XXXXX opened read only
2020-10-17T10:47:26.812990-03:00
Completed: ALTER PLUGGABLE DATABASE ALL OPEN
Completed: alter database open read only

DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE VERSION COMPATIBLE STATUS
—————- —————- ——————– ———- ———- ——–
CDBX PHYSICAL STANDBY READ ONLY 12.2.0.1.0 12.2.0.0.0 OPEN

HOST_NAME INSTANCE_NAME STATUS Startup time LOGINS CONNECTED
———————————- ————– ——– ————————————————————————— ——– ————
exacc CDBX OPEN 17-OCT-2020 08:03 ALLOWED CURRENT
oraclecloudatcustomer.com

##################################################
Some errors can be found in alert log and need be checked.
##################################################

cannot identify/lock data file 1100 – see DBWR trace file
ORA-01110: data file 1100: ‘+DATAC2’
2020-10-17T10:45:59.653763-03:00
File 1100 not verified due to error ORA-01157
2020-10-17T10:45:59.743584-03:00
XXXXXPDB(67):Pluggable Database XXXXXPDB Dictionary check complete
2020-10-17T10:45:59.859089-03:00
Errors in file /u02/app/oracle/diag/rdbms/CDBX/CDBX/trace/CDBX_dbw0_246152.trc:
ORA-01186: file 1062 failed

File 1094 not verified due to error ORA-01157
2020-10-17T10:46:17.516127-03:00
J5CEN1RMP(55):Re-creating tempfile +DATAC2 as +DATAC2/CDBX/999B4F7580E46340E053023013ACC277/TEMPFILE/rm_tmp.1263.1054032377

/u02/app/oracle/diag/rdbms/CDBX/CDBX/trace/CDBX_dbw0_246152.trc

 

Duplicate rman from local backup

We have a lot of posts todo the same – duplicate. But my post is complete steps to duplicate the database, and we need pay attention if the database will be duplicated on the same server. What we need take care with all attention on it? Redo log files, datafiles and controlfiles never be recreated/created at same source locations. All backups are under the location /backups/bkprman in my lab. Let’s start !

Source database – bdo

Check the locations from source database. Make sure when you are duplicating - any of these files will be replaced. Mistakes are done with redo logfiles members when we are duplicating. Take care to rename all files and check before open it. Take notes of the locations below.
Select name from v$datafile;
Select name from v$controlfile;
Select member from v$logfile;

Target database – TESTE

Before open the target database check same queries below.
Select name from v$datafile;
Select name from v$controlfile;
Select member from v$logfile;

Add database to /etc/oratab

Add this line to the end of /etc/oratab

TESTE:/u01/app/oracle/product/11.2.0/db_1:Y

Listener.ora

Add the listener.ora details below and reload listener – edit your listener.ora and lsnrctl reload it

vi $ORACLE_HOME/network/admin/listener.ora


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TESTE)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = TESTE)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srvoraclestby)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Password file creation

We need create the password file and use entries=5 sometimes we get error when connecting to auxiliary.

cd $ORACLE_HOME/dbs
orapwd file=orapwTESTE password=oracle ignorecase=y entries=5

Create new file: initTESTE.ora

# Minimum Requirement.
DB_NAME='bdo'

Create new file: duplicateTESTE.sql

DUPLICATE DATABASE TO TESTE
  SPFILE
    parameter_value_convert ('bdo','TESTE')
    set db_file_name_convert='+DATA/bdo/datafile/','/backups/oradata/TESTE/','+DATA/','/backups/oradata/TESTE/'
    set log_file_name_convert='+DATA/bdo/datafile/','/backups/oradata/TESTE/','+DATA/bdo/onlinelog/','/backups/oradata/TESTE/'
    set control_files='/backups/oradata/TESTE/control01.ctl'
    set db_name='TESTE'
    set log_archive_dest_1='location=/stage/archive'
    set log_archive_dest=''
    set DB_CREATE_ONLINE_LOG_DEST_1='/backups/oradata/TESTE/'
    set DB_CREATE_FILE_DEST='/backups/oradata/TESTE/'
    set audit_trail='none'
    set remote_login_passwordfile='EXCLUSIVE'
  BACKUP LOCATION '/backups/bkprman'
  NOFILENAMECHECK;

Create new file: duplicateTESTE.sh

. oraenv <<< TESTE
sqlplus / as sysdba <<EOF
shut abort
startup nomount pfile='/stage/scripts/initTESTE.ora';
exit
EOF
# rm -Rf $ORACLE_BASE/admin/$ORACLE_SID/adump
# mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
# Remove the old datafiles
rm -Rf /backups/oradata/TESTE/*
rman auxiliary sys/oracle cmdfile=/stage/scripts/duplicaTESTE.sql log=/stage/scripts/duplicaTESTE.log
sqlplus / as sysdba <<EOF
alter database open resetlogs;
exit
EOF

Execute the rman restore in background

 nohup ./duplicateTESTE.sh &

Check the results

tail -f duplicateTESTE.log
channel ORA_AUX_DISK_1: restoring datafile 00024 to /backups/oradata/TESTE/sankind07.dbf
channel ORA_AUX_DISK_1: restoring datafile 00025 to /backups/oradata/TESTE/sanlob08.dbf
channel ORA_AUX_DISK_1: restoring datafile 00026 to /backups/oradata/TESTE/sankhya07.dbf
channel ORA_AUX_DISK_1: restoring datafile 00027 to /backups/oradata/TESTE/sankhya08.dbf
channel ORA_AUX_DISK_1: restoring datafile 00028 to /backups/oradata/TESTE/sankind08.dbf
channel ORA_AUX_DISK_1: restoring datafile 00029 to /backups/oradata/TESTE/sanklob08.dbf
channel ORA_AUX_DISK_1: restoring datafile 00030 to /backups/oradata/TESTE/sankhya09.dbf
channel ORA_AUX_DISK_1: restoring datafile 00031 to /backups/oradata/TESTE/sanklob10.dbf
channel ORA_AUX_DISK_1: restoring datafile 00032 to /backups/oradata/TESTE/sankind09.dbf
channel ORA_AUX_DISK_1: reading from backup piece /backups/bkprman/bkpfull1042630360_s270188_s1

Before open the database, check if redo log groups are in the correct location

Check if the datafiles have been created in a different location from production database before you open the database with resetlogs option. If all is fine, open database with resetlogs option.
. oraenv <<< TESTE
sqlplus / as sysdba
Select name from v$datafile;
Select name from v$controlfile;
Select member from v$logfile;
alter database open resetlogs;
select status from v$instance;
exit

Errors and solutions when using duplicate with auxiliary and backup location

RMAN-06136: ORACLE error from auxiliary database: ORA-01507: database not mounted

Solution:
When connecting to auxiliary instance, password file is not working. Recreate password file or use this connection with the correct password like below:
rman auxiliary sys/oracle

ORA-19504: failed to create file

Solution:
Update/revise the parameters on file restoreTESTE.sql:
parameter_value_convert ('bdo','TESTE')
    set db_file_name_convert='+DATA/bdo/datafile/','/backups/oradata/TESTE/','+DATA/','/backups/oradata/TESTE/'
    set log_file_name_convert='+DATA/bdo/datafile/','/backups/oradata/TESTE/','+DATA/','/backups/oradata/TESTE/'
    set control_files='/backups/oradata/TESTE/control01.ctl'
    set log_archive_dest_1='location=/stage/archive'
    set log_archive_dest=''
    set DB_CREATE_ONLINE_LOG_DEST_1='/backups/oradata/TESTE/'
    set DB_CREATE_FILE_DEST='/backups/oradata/TESTE/'
    set audit_trail='none'

ORA-09925: Unable to create audit trail file

Solution:
update the parameter on file restoreTESTE.sql:
set audit_trail='none'

ORA-01261: Parameter db_create_online_log_dest_1 destination string cannot be translated

Solution:
Make sure the directory exists/update the location to one existent directory on file restoreTESTE.sql:
DB_CREATE_ONLINE_LOG_DEST_1='/backups/oradata/TESTE/'

RMAN-06403: could not obtain a fully authorized session

Solution:
Recreate the password file:
cd /u01/app/oracle/product/11.2.0/db_1/dbs
rm orapwTESTE
orapwd file=orapwTESTE password=oracle ignorecase=y entries=5

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Solution:
Update the listener.ora e try to reload the listener :
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TESTE)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = TESTE)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srvoraclestby)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
Error: Creation of redo logs - not necessary to restore database again - need recreate controlfile.
 GROUP  14 ( '/backups/oradata/TESTE/bdo/onlinelog/group_14.1974.947483695', '/backups/oradata/TESTE/bdo/onlinelog/group_14.2908.947483697' ) SIZE 120 M  REUSE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/10/2020 04:28:03
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-00301: erro ao incluir arquivo de log '/backups/oradata/TESTE/bdo/onlinelog/group_9.4980.947483691' - o arquivo n?o pode ser criado
ORA-27040: erro ao criar arquivo, n?o foi possivel criar o arquivo
Linux-x86_64 Error: 2: No such file or directory
shutdown database, create the controlfile pointing to all new datafiles and open database.
Add tempfile to temporary tablespaces.
move/remove controlfile.
mv /backups/oradata/TESTE/control01.ctl /backups/oradata/TESTE/co.old
mkdir -p /backups/oradata/TESTE/bdo/onlinelog/
sqlplus / as sysdba
shut abort
startup nomount
 CREATE CONTROLFILE reuse SET DATABASE "TESTE" RESETLOGS NOARCHIVELOG
  MAXLOGFILES    192
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY    11680
 LOGFILE
  GROUP   1 ( '/backups/oradata/TESTE/bdo/onlinelog/group_1.291.947518073', '/backups/oradata/TESTE/bdo/onlinelog/group_1.290.947518075' ) SIZE 120 M  REUSE,
  GROUP   2 ( '/backups/oradata/TESTE/bdo/onlinelog/group_2.289.947518075', '/backups/oradata/TESTE/bdo/onlinelog/group_2.288.947518075' ) SIZE 120 M  REUSE,
  GROUP   3 ( '/backups/oradata/TESTE/bdo/onlinelog/group_3.287.947518075', '/backups/oradata/TESTE/bdo/onlinelog/group_3.284.947518075' ) SIZE 120 M  REUSE
DATAFILE
'/backups/oradata/TESTE/system.278.853379527'
,'/backups/oradata/TESTE/users.277.853379111'
,'/backups/oradata/TESTE/undotbs2.5543.947483683'
,'/backups/oradata/TESTE/undotbs1.292.947518065'
,'/backups/oradata/TESTE/sysaux.275.853379001'
,'/backups/oradata/TESTE/sanklob06.dbf'
,'/backups/oradata/TESTE/sanklob05.dbf'
,'/backups/oradata/TESTE/sanklob04.dbf'
,'/backups/oradata/TESTE/sanklob03.dbf'
,'/backups/oradata/TESTE/sanklob02.dbf'
,'/backups/oradata/TESTE/sanklob01.dbf'
,'/backups/oradata/TESTE/sankind05.dbf'
,'/backups/oradata/TESTE/sankind03.dbf'
,'/backups/oradata/TESTE/sankind02.dbf'
,'/backups/oradata/TESTE/sankind01.dbf'
,'/backups/oradata/TESTE/sankhya05.dbf'
,'/backups/oradata/TESTE/sankhya04.dbf'
,'/backups/oradata/TESTE/sankhya03.dbf'
,'/backups/oradata/TESTE/sankhya02.dbf'
,'/backups/oradata/TESTE/sankhya01.dbf'
,'/backups/oradata/TESTE/sanlob08.dbf'
,'/backups/oradata/TESTE/sanklob10.dbf'
,'/backups/oradata/TESTE/sanklob08.dbf'
,'/backups/oradata/TESTE/sanklob07.dbf'
,'/backups/oradata/TESTE/sankind09.dbf'
,'/backups/oradata/TESTE/sankind08.dbf'
,'/backups/oradata/TESTE/sankind07.dbf'
,'/backups/oradata/TESTE/sankind06.dbf'
,'/backups/oradata/TESTE/sankhya09.dbf'
,'/backups/oradata/TESTE/sankhya08.dbf'
,'/backups/oradata/TESTE/sankhya07.dbf'
,'/backups/oradata/TESTE/sankhya06.dbf'
CHARACTER SET WE8ISO8859P1
;
SQL> alter database open resetlogs;
Banco de dados alterado.
Check the alert log for errors:
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
SQL> alter tablespace temp add tempfile '/backups/oradata/TESTE/temp02.dbf' size 10G autoextend on;
Tablespace alterado.
scripts$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 09:54:47 2020
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Conectado a:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
DB_UNIQUE_NAME   DATABASE_ROLE    OPEN_MODE            VERSION    COMPATIBLE STATUS
---------------- ---------------- -------------------- ---------- ---------- --------
TESTE            PRIMARY          READ WRITE           11.2.0.3.0 11.2.0.0.0 OPEN
HOST_NAME                          INSTANCE_NAME  STATUS   Startup time       LOGINS   CONNECTED
---------------------------------- -------------- -------- ------------------ -------- ------------
srvoraclestby                      TESTE          OPEN     10-JUN-2020 09:44  ALLOWED  CURRENT
SQL>

Use sqlplus to help you!

Each time we use sqlplus – we usually need check dataguard status, database role, and collect details on our system. This help us to be more proactive and collect data to leave the database in same status we started to work. A good approach is to get all details automatically without getting trash for our screen output. Same examples are below – I have been using this a couple of years and is very helpful in my day by day.

glogin.sql – update the file under $ORACLE_HOME/sqlplus/admin

set linesize 200
define _editor=vi
set serveroutput on size 1000000
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user)||'@'||instance_name||'('||host_name||')' global_name from v$instance;
--set sqlprompt '&gname> '
set termout on
column db_unique_name format a16
column database_role format a16
column db_version format a10
column db_status format a8
column db_compatible format a10
column db_version heading VERSION
column db_status heading STATUS
column db_compatible heading COMPATIBLE
select d.db_unique_name, d.database_role, d.open_mode, v.version db_version,
p.value db_compatible, v.status db_status
from gv$database d, gv$instance v, gv$parameter p
where p.name = 'compatible'
and d.inst_id=v.inst_id and v.inst_id = p.inst_id
;
col logins format a8
column instance_name format A14
column status format A8
column CONNECTED format A12
column host_name format A34
select host_name,instance_name,
status, to_char(startup_time,
'DD-MON-YYYY HH24:MI ') "Startup time",logins,
decode(instance_name,(select instance_name from v$instance) ,'CURRENT','NOT CURRENT') CONNECTED
from gv$instance
/

Upgrade your database using dbaascli in your exadata at customer

So easy to upgrade de dbhome and database. Let’s see the steps below:

[[email protected] ~]# dbaascli patch db list --oh `hostname`:/u02/app/oracle/product/12.2.0/dbhome_8
DBAAS CLI version 19.4.4.1.0
Executing command patch db list --oh techdb01.pbh:/u02/app/oracle/product/12.2.0/dbhome_8
INFO : EXACS patching
Available Patches
patchid :29708720 (DB 12.2.0.1.190716 DATABASE RELEASE UPDATE (Jul 2019))
patchid :30116802 (DB 12.2.0.1.191015 DATABASE RELEASE UPDATE (Oct 2019))
patchid :30501932 (DB 12.2.0.1.200114 DATABASE RELEASE UPDATE (Jan 2020))
patchid :30920127 (DB 12.2.0.1.200414 DATABASE RELEASE UPDATE (Apr 2020))
Install database patch using
dbaascli patch db apply --patchid 30920127 --dbnames <>
[[email protected] ~]# dbaascli patch db apply --patchid 30920127 --dbnames boston
DBAAS CLI version 19.4.4.1.0
Executing command patch db apply --patchid 30920127 --dbnames boston
INFO : EXACS patching
This might take some time, please take a look at file /var/opt/oracle/log/exadbcpatch/exadbcpatch.log for progress
Patch installation successful
[[email protected] ~]#
dbaascli patch db list --oh `hostname`:/u02/app/oracle/product/19.0.0.0/dbhome_4 --dbname boston
dbaascli patch db prereq --patchid 30899722 pdb01:/u02/app/oracle/product/19.0.0.0/dbhome_3 --dbnames boston
Please apply the apply with nohup clause and & at final this will take time:
nohup dbaascli patch db apply --patchid 30899722
pdb01:/u02/app/oracle/product/19.0.0.0/dbhome_3 --dbnames boston &
check logs for dbaascli - and outputs and ps -ef | grep dbaascli

Exadata Cloud at Customer gen2 Data Guard at same server/Grid – Explained version – EXACC

Dataguard setup for Exadata gen2 at Customer – detailed version.

For this post, I will present how I created the dataguard – duplicated database at same cluster. There is a couple of steps to be completed and – many errors can occur if the steps are not followed correctly. Let’s create a list with the brief of steps for each step to be completed to archive success for this configuration. Got some issues with # character in password.

SOURCE Database

[18.0.0.0 SID:boston1][[email protected]:/home/oracle ] ~$ srvctl config database -d boston
Database unique name: boston
Database name:
Oracle home: /u02/app/oracle/product/18.0.0.0/dbhome_2
Oracle user: oracle
Spfile: +DATA/boston/spfile.ora
Password file: +datac1/boston/password/passwd
Domain: techmax.domain
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATAC1
Mount point paths:
Services: boston1
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: racoper
Database instances: boston1,boston2,boston3,boston4
Configured nodes: tech01,tech02,tech03,tech04
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[18.0.0.0 SID:boston1][[email protected]:/home/oracle ] ~$
srvctl getenv database -d boston
boston:
TNS_ADMIN=/u02/app/oracle/product/18.0.0.0/dbhome_2/network/admin/boston

TARGET Database

[18.0.0.0 SID:boston1][[email protected]:/home/oracle ] ~$ srvctl config database -d london
Database unique name: london
Database name:
Oracle home: /u02/app/oracle/product/18.0.0.0/dbhome_2
Oracle user: oracle
Spfile: /u02/app/oracle/product/18.0.0.0/dbhome_2/dbs/spfilelondon1.ora
Password file: /u02/app/oracle/product/18.0.0.0/dbhome_2/dbs/orapwlondon
Domain: techmax.domain
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATAC1,RECOC1
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: racoper
Database instances: london1
Configured nodes: tech01
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[18.0.0.0 SID:boston1][[email protected]:/home/oracle ] ~$
srvctl getenv database -d london
london:
TNS_ADMIN=/u02/app/oracle/product/18.0.0.0/dbhome_2/network/admin/london

To create source – boston database

create json file: boston.json
{
"object": "db",
"action": "start",
"operation": "createdb",
"params": {
"nodelist": "",
"dbname": "boston",
"edition": "EE_EP",
"version": "12.2.0.0",
"ohome_name": "OraHome3",
"adminPassword": " WElc0me__0",
"sid": "boston",
"pdbName": "PDB1",
"charset": "WE8MSWIN1252",
"ncharset": "AL16UTF16",
"backupDestination": "NONE" },
"outputfile": "/home/oracle/createdbboston.out",
"FLAGS": ""
}
Create jason to check status: checkstatusboston.json
{
  "object": "db",
  "action": "status",
  "operation": "createdb",
  "id": 170,
  "params": {
    "dbname": "boston"
  },
  "outputfile": "/home/oracle/createdb.out",
  "FLAGS": ""
}
/var/opt/oracle/dbaasapi/dbaasapi -i boston.json

Archivelog and Force Logging

[18.0.0.0 SID:boston1][[email protected]:/home/oracle ] ~$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Wed May 27 20:55:06 2020
Version 18.6.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Connected to:
Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
Version 18.6.0.0.0
DB_UNIQUE_NAME       DATABASE_ROLE    VERSION    COMPATIBLE STATUS
-------------------- ---------------- ---------- ---------- --------
boston               PRIMARY          18.0.0.0.0 12.2.0.1.0 MOUNTED
HOST_NAME                          INSTANCE_NAME  STATUS   Startup time       LOGINS   CONNECTED
---------------------------------- -------------- -------- ------------------ -------- --------------
tech01.techmax.domain              boston1        MOUNTED  27-MAY-2020 20:47  ALLOWED  CURRENT
SQL> alter database archivelog ;
Database altered.
SQL> ALTER DATABASE  force logging;
Database altered.
SQL> SELECT force_logging FROM v$database;
FORCE_LOGGING
---------------------------------------
YES
SQL> quit
Disconnected from Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
Version 18.6.0.0.0
[18.0.0.0 SID:boston1][[email protected]:/home/oracle ] ~$

Create standby redo logs – Need create one more than redo groups on boston.

SQL> select group#, thread#, bytes/1024/1024  from v$log order by thread#,  group#;
    GROUP#    THREAD# BYTES/1024/1024
---------- ---------- ---------------
         1          1            4000
         2          1            4000
         3          1            4000
         4          1            4000
         9          2            4000
        10          2            4000
        11          2            4000
        12          2            4000
        13          3            4000
        14          3            4000
        15          3            4000
        16          3            4000
        17          4            4000
        18          4            4000
        19          4            4000
        20          4            4000
16 rows selected.
SQL>
SQL> select group#, thread#, bytes/1024/1024  from v$standby_log   order by thread#,  group#;
    GROUP#    THREAD# BYTES/1024/1024
---------- ---------- ---------------
         5          1            4000
         6          1            4000
         7          1            4000
         8          1            4000
        21          1            4000
        22          2            4000
        23          2            4000
        24          2            4000
        25          2            4000
        26          2            4000
        27          3            4000
        28          3            4000
        29          3            4000
        30          3            4000
        31          3            4000
        32          4            4000
        33          4            4000
        34          4            4000
        35          4            4000
        36          4            4000
20 rows selected.
alter database add logfile thread 1 group 1 '+DATAC1' size 4000m;
alter database add logfile thread 1 group 2 '+DATAC1' size 4000m;
alter database add logfile thread 1 group 3 '+DATAC1' size 4000m;
alter database add logfile thread 1 group 4 '+DATAC1' size 4000m;
alter database add logfile thread 1 group 5 '+DATAC1' size 4000m;
alter database add logfile thread 2 group 6 '+DATAC1' size 4000m;
alter database add logfile thread 2 group 7 '+DATAC1' size 4000m;
alter database add logfile thread 2 group 8 '+DATAC1' size 4000m;
alter database add logfile thread 2 group 9 '+DATAC1' size 4000m;
alter database add logfile thread 2 group 10 '+DATAC1' size 4000m;
...
OR
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('+DATAC1') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('+DATAC1') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('+DATAC1') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('+DATAC1') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('+DATAC1') SIZE 4G;
-- for each instance -  repeat change the thread number
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('+DATAC1') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('+DATAC1') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('+DATAC1') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('+DATAC1') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('+DATAC1') SIZE 4G;

Update password file

orapwd file=/u02/app/oracle/product/18.0.0.0/dbhome_2/dbs/orapwlondon password="TechMaster1_" force=y entries=10
cp /u02/app/oracle/product/18.0.0.0/dbhome_2/dbs/orapwlondon /u02/app/oracle/product/18.0.0.0/dbhome_2/dbs/orapwboston
or try to get a copy from boston database:
ASMCMD> cp pwdboston.674.1043766629 /u02/app/oracle/product/18.0.0.0/dbhome_2/dbs/orapwlondon
copying +datac1/boston/password/pwdboston.674.1043766629 -> /u02/app/oracle/product/18.0.0.0/dbhome_2/dbs/orapwlondon
ASMCMD>
Go to grid and execute the copy of password for boston and london:
[[email protected] admin]$ asmcmd
ASMCMD> cd +datac1/london/password
ASMCMD>  cp /u02/app/oracle/product/18.0.0.0/dbhome_2/dbs/orapwlondon passwd
copying /u02/app/oracle/product/18.0.0.0/dbhome_2/dbs/orapwlondon -> +datac1/london/password/passwd
ASMCMD> ls
orapwlondon
passwd
pwdlondon.1244.1040809123

Target – starting london1 instance

export ORACLE_SID=london1
[18.0.0.0 SID:london1][[email protected]:/home/oracle/working ] working$ cat startuplondon.sql
shut abort
host srvctl stop instance -d london -i london1
startup nomount pfile='/home/oracle/working/london.ora';
host rm -f /u02/app/oracle/product/18.0.0.0/dbhome_2/dbs/spfilelondon.ora
create spfile from pfile='/home/oracle/working/london.ora';
host srvctl start instance -d london -i london1 -o nomount
host srvctl status database -d london -v
exit
...
[18.0.0.0 SID:london1][[email protected]:/home/oracle/working ] working$ srvctl status database -d london
Instance london1 is running on node tech01
[18.0.0.0 SID:boston1][[email protected]:/home/oracle/working ] working$ cat /home/oracle/working/london.ora
DB_NAME=boston
DB_UNIQUE_NAME=london
sga_target=8G
*.db_domain='techmax.domain'
*.compatible='12.2.0.1.0'
#*.local_listener='LISTENER'
#*.remote_listener='p-scan.techmax.domain:1521'
*.db_recovery_file_dest='+RECOC1'
*.db_recovery_file_dest_size=10737418240
*.dg_broker_config_file1='+DATAC1/london/BROKER/dr1london.dat'
*.dg_broker_config_file2='+DATAC1/london/BROKER/dr2london.dat'
*.cluster_database=FALSE
*.instance_number=1
*.undo_tablespace='UNDOTBS1'
*.standby_file_management='AUTO'
*.dg_broker_start=FALSE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.db_create_file_dest='+DATAC1'
*.db_create_online_log_dest_1='+DATAC1'
*.enable_pluggable_database=true
[18.0.0.0 SID:boston1][[email protected]:/home/oracle/working ] working$ source $HOME/london.env
[18.0.0.0 SID:london][[email protected]:/home/oracle/working ] working$ export ORACLE_SID=london1
[18.0.0.0 SID:london1][[email protected]:/home/oracle/working ] working$ sqlplus / as sysdba @startuplondon.sql
SQL*Plus: Release 18.0.0.0.0 - Production on Wed May 27 21:11:37 2020
Version 18.6.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Connected to:
Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
Version 18.6.0.0.0
DB_UNIQUE_NAME       DATABASE_ROLE    VERSION    COMPATIBLE STATUS
-------------------- ---------------- ---------- ---------- --------
london               PHYSICAL STANDBY 18.0.0.0.0 12.2.0.1.0 MOUNTED
HOST_NAME                          INSTANCE_NAME  STATUS   Startup time       LOGINS   CONNECTED
---------------------------------- -------------- -------- ------------------ -------- --------------
tech01.techmax.domain                     london1        MOUNTED  27-MAY-2020 17:17  ALLOWED  CURRENT
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 8578638416 bytes
Fixed Size                 12456528 bytes
Variable Size            1392508928 bytes
Database Buffers         7130316800 bytes
Redo Buffers               43356160 bytes
File created.
Instance london1 is running on node tech01
Disconnected from Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
Version 18.6.0.0.0
[18.0.0.0 SID:london1][[email protected]:/home/oracle/working ] working$
[18.0.0.0 SID:london1][[email protected]:/home/oracle/working ] working$
Please add the london instance to the oracle cluster.
srvctl remove database -d london -noprompt
srvctl add database -d london -oraclehome $ORACLE_HOME
srvctl setenv database -d london -T "TNS_ADMIN=/u02/app/oracle/product/19.0.0.0/dbhome_4/network/admin/london"
srvctl modify database -d london -pwfile +DATAC1/london/PASSWORD/orapwlondon
srvctl modify database -d london -spfile +DATAC1/london/spfilelondon.ora
srvctl modify database -d london -domain pbh
srvctl modify database -d london -diskgroup DATAC1,RECOC1
srvctl modify database -d london -startoption mount
srvctl add instance -db london  -instance london1  -node techcdb01
srvctl add instance -db london  -instance london2  -node techcdb02
After the initial load you can test the start of instance and database.
srvctl start database -d london -v
srvctl status instance -d london -node exaccdb01
srvctl status instance -d london -node exaccdb02
srvctl getenv database -d london
srvctl config database -d london
srvctl status database -d london -v
Instance london1 is running on node tech01. Instance status: .

Source database – Boston status

18.0.0.0 SID:london1][[email protected]:/home/oracle/working ] working$ srvctl status database -d boston -v
Instance boston1 is running on node tech01. Instance status: Open.
Instance boston2 is running on node tech02. Instance status: Open.
Instance boston3 is running on node tech03. Instance status: Open.
Instance boston4 is running on node tech04. Instance status: Open.

Listener reload (update all nodes)

We need update again at final to finish configuration if target have instances running on all nodes. We need start with london1 running on host01 and boston running on all nodes.

[[email protected] admin]$ cat listener.ora (add this to your listener)
# DG
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=boston.techmax.domain)
      (ORACLE_HOME=/u02/app/oracle/product/18.0.0.0/dbhome_2)
      (SID_NAME=boston1)
      (ENVS="TNS_ADMIN=/u02/app/oracle/product/18.0.0.0/dbhome_2/network/admin/boston")
      (ENVS="ORACLE_UNQNAME=boston"))
    (SID_DESC=
      (GLOBAL_DBNAME=london.techmax.domain)
      (ORACLE_HOME=/u02/app/oracle/product/18.0.0.0/dbhome_2)
      (SID_NAME=london1)
      (ENVS="TNS_ADMIN=/u02/app/oracle/product/18.0.0.0/dbhome_2/network/admin/london")
      (ENVS="ORACLE_UNQNAME=london"))
    (SID_DESC=
      (GLOBAL_DBNAME=boston_DGMGRL.techmax.domain)
      (ORACLE_HOME=/u02/app/oracle/product/18.0.0.0/dbhome_2)
      (SID_NAME=boston1)
      (ENVS="TNS_ADMIN=/u02/app/oracle/product/18.0.0.0/dbhome_2/network/admin/boston")
      (ENVS="ORACLE_UNQNAME=boston"))
    (SID_DESC=
      (GLOBAL_DBNAME=london_DGMGRL.techmax.domain)
      (ORACLE_HOME=/u02/app/oracle/product/18.0.0.0/dbhome_2)
      (SID_NAME=london1)
      (ENVS="TNS_ADMIN=/u02/app/oracle/product/18.0.0.0/dbhome_2/network/admin/london")
      (ENVS="ORACLE_UNQNAME=london"))
  )

Reload listener and force register for more static services

Grid user:
lsnrctl reload
lsnrctl status
Oracle user:
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 4 09:49:54 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter system register;
System altered.

Update tnsnames.ora and listener.ora

tnsnames.ora will be present for each TNS_ADMIN location for each database. In this case we need update both boston and london locations. The *(UR=A)* clause is intended to work with a dynamically registered handler so the use of SERVICE_NAME versus SID is preferred, check the MOS doc ID 362656.1 to get some background about it. For tnsnames.ora consider update all locations returned in srctl getenv - TNS_ADMIN - add the entries below. Copy the files for all nodes, and restart listener. All hosts - are tech01.techmax.domain and at final we need change to scan name instead of host01 (tech01.techmax.domain).
Create same entries in $GRID_HOME/network/admin/tnsnames.ora for all nodes. Can be removed after the duplicate.
Same tnsnames.ora need be copied to london in all nodes.
[18.0.0.0 SID:boston1][[email protected]:/u02/app/oracle/product/18.0.0.0/dbhome_2/network/admin/london ] london$
cat tnsnames.ora (add this to your tnsnames.ora for both london and boston)
BOSTON =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = p-scan.techmax.domain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = boston.techmax.domain)
         (UR=A)
    )
  )
BOSTON_DGMGRL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = p-scan.techmax.domain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = boston_DGMGRL.techmax.domain)
      (FAILOVER_MODE =
        (TYPE = select)
        (METHOD = basic)
      )
    )
  )
LONDON_DGMGRL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tech01.techmax.domain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = london_DGMGRL.techmax.domain)
    )
  )
LONDON =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tech01.techmax.domain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = london.techmax.domain)
         (UR=A)
    )
  )
LONDON1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tech01.techmax.domain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = london1.techmax.domain)
         (UR=A)
    )
  )
#optional for tests
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = p-scan.techmax.domain)(PORT = 1521))
  )

Test Connectivity – boston and london – TEST IT in all NODES !!!!

[12.2.0 SID:boston1][[email protected]:/home/oracle/working ] working$
sqlplus  sys/"xxx"@london as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed May 27 21:34:50 2020
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Last Successful login time: Wed May 27 2020 17:25:09 -03:00
Connected to:
Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
SQL> quit
Disconnected from Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
sqlplus  sys/"xxx"@boston as sysdba

Copy wallet files from BOSTON to LONDON

Make sure the wallet from BOSTON is same on LONDON. Make sure you have a copy from wallet before start to deal with it.

Check sqlnet.ora the location and make properly copy of files:

cat $TNS_ADMIN/sqlnet.ora
(DIRECTORY=/var/opt/oracle/dbaas_acfs/boston/tde_wallet))
(DIRECTORY=/var/opt/oracle/dbaas_acfs/boston/db_wallet)))
Make a backup before start to deal with it.
cp -r /var/opt/oracle/dbaas_acfs/boston/* /backup/
cp -r /var/opt/oracle/dbaas_acfs/london/* /backup/
cp /var/opt/oracle/dbaas_acfs/boston/tde_wallet /var/opt/oracle/dbaas_acfs/london/tde_wallet
cp /var/opt/oracle/dbaas_acfs/boston/db_wallet /var/opt/oracle/dbaas_acfs/london/db_wallet

Duplicate database

Before start the duplicate, need confirm if london1 instance is running even for srvctl status instance -d london -i london1 (grid). Duplicate below will get some issues see last section. London needs only one instance = cluster =false..
[[email protected] ~]$
srvctl status instance -d london -i london1
Instance london1 is running on node tech01
[[email protected] ~]$
srvctl status database -d london
Instance london1 is running on node tech01
Instance london2 is not running on node tech02
rman target sys/"TechMaster1_"@boston  auxiliary sys/"TechMaster1_"@london
DUPLICATE target database for standby from active database
  SPFILE
    parameter_value_convert ('boston','LONDON','BOSTON','LONDON')
    set db_unique_name='london'
    set db_name='boston'
    set cluster_database='FALSE'
    set db_file_name_convert='boston','london','BOSTON','LONDON'
    set log_file_name_convert='boston','london','BOSTON','LONDON'
    set pdb_file_name_convert='boston','london','BOSTON','LONDON'
    set db_create_file_dest='+DATAC1'
    set db_create_online_log_dest_1='+DATAC1'
    set audit_trail='none'
    set standby_file_management='AUTO'
    set log_archive_dest_1='location=+RECOC1'
    set LOG_ARCHIVE_DEST_10='location=+DATAC1'
    set remote_listener=''
    set enable_pluggable_database='TRUE'
    SET LISTENER_NETWORKS ''
    SET CLUSTER_INTERCONNECTS ''
    set core_dump_dest '/u02/app/oracle/diag/rdbms/LONDON/london1/cdump'
    set instance_name 'london1'
  NOFILENAMECHECK
  DORECOVER;

Configure Dataguard – Activate Dataguard Broker on london and boston

Connected to BOSTON:
alter system set dg_broker_start=FALSE sid='*' scope=both;
alter system set dg_broker_config_file1='+DATAC1/BOSTON/BROKER/dr1boston.dat' sid='*' scope=both;
alter system set dg_broker_config_file2='+DATAC1/BOSTON/BROKER/dr2boston.dat' sid='*' scope=both;
alter system set dg_broker_start=TRUE sid='*' scope=both;
Connected to LONDON:
alter system set dg_broker_start=FALSE sid='*' scope=both;
alter system set dg_broker_config_file1='+DATAC1/LONDON/BROKER/dr1london.dat' sid='*' scope=both;
alter system set dg_broker_config_file2='+DATAC1/LONDON/BROKER/dr2london.dat' sid='*' scope=both;
alter system set dg_broker_start=TRUE sid='*' scope=both;

Create asm directory to Dataguard Broker configuration files

Create directory for dataguard broker files inside of asm.
Connected with grid user:
asmcmd mkdir +DATAC1/BOSTON/BROKER/
asmcmd mkdir +DATAC1/LONDON/BROKER/
[[email protected] ~]# su - grid
Last login: Thu Jun  4 11:48:21 -03 2020
[19.0.0.0 SID:+ASM1][[email protected]:/home/grid ] ~$ asmcmd mkdir +DATAC1/BOSTON/BROKER/
[19.0.0.0 SID:+ASM1][[email protected]:/home/grid ] ~$ asmcmd mkdir +DATAC1/LONDON/BROKER/
[19.0.0.0 SID:+ASM1][[email protected]:/home/grid ] ~$

Check alert log of Dataguard Broker

Check errors on dataguard broker:
ln -s /u02/app/oracle/diag/rdbms/boston/boston1/trace/drcboston1.log drcboston1.log
tail -f drcboston1.log

Configure Flashback Database in London – In case of failover – reinstate database will not work if this not configured properly in London ( standby )

SQL> alter database flashback on;

Configure Dataguard Broker – Create configuration

Resume of the configuration to be done:
CREATE CONFIGURATION DGBOSTON as
 PRIMARY DATABASE IS boston
 CONNECT IDENTIFIER IS boston;
show configuration;
add database london as connect identifier is london;
enable configuration;
show database london;
show configuration;
All steps below:
dgmgrl sys/"TechMaster1_"@boston
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Wed May 27 17:21:40 2020
Version 18.6.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "boston"
Connected as SYSDBA.
DGMGRL>  CREATE CONFIGURATION DGBOSTON as
>  PRIMARY DATABASE IS boston
>  CONNECT IDENTIFIER IS boston;
Configuration "dgboston" created with primary database "boston"
DGMGRL> show configuration
Configuration - dgboston
  Protection Mode: MaxPerformance
  Members:
  boston - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> add database london as connect identifier is london;
Database "london" added
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration
Configuration - dgboston
  Protection Mode: MaxPerformance
  Members:
  boston - Primary database
    Warning: ORA-16809: multiple warnings detected for the member
    london - Physical standby database
      Warning: ORA-16854: apply lag could not be determined
Fast-Start Failover: DISABLED
Configuration Status:
WARNING   (status updated 31 seconds ago)
DGMGRL> show database london
Database - london
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 17.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    london1
Database Status:
SUCCESS
DGMGRL> quit
[18.0.0.0 SID:london1][[email protected]:/home/oracle/working ] working$ dgmgrl sys/"TechMaster1_"@boston               DGMGRL for Linux: Release 18.0.0.0.0 - Production on Wed May 27 17:25:08 2020
Version 18.6.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "boston"
Connected as SYSDBA.
DGMGRL> show configuration
Configuration - dgboston
  Protection Mode: MaxPerformance
  Members:
  boston - Primary database
    Warning: ORA-16809: multiple warnings detected for the member
    london - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
WARNING   (status updated 22 seconds ago)
DGMGRL> show database boston
Database - boston
  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    boston1
    boston2
    boston3
    boston4
Database Status:
SUCCESS
DGMGRL> show database london
Database - london
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 6.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    london1
Database Status:
SUCCESS
DGMGRL> quit

Final checks

select * from v$encryption_wallet;
select time,  name, message, action FROM pdb_plug_in_violations;
dbaascli tde status --dbname london
dbaascli tde status --dbname boston
Drop london:
export ORACLE_SID=london
[ SID:no sid][[email protected]:/home/oracle/working ] working$ cat droplondon.sql
shut abort
startup mount exclusive restrict;
drop database;
shut abort
host srvctl stop instance -d london -i london1
startup mount exclusive restrict;
drop database;
host srvctl status instance -d london -i london1

Errors and solutions

ERROR: London running only one instance. Put running all instances.
alter system set cluster_database=true scope=spfile sid='*';
alter system set instance_number=1  scope=spfile sid='london1';
alter system set instance_number=2  scope=spfile sid='london2';
alter system set undo_tablespace='UNDOTBS1'  scope=spfile sid='london1';
alter system set undo_tablespace='UNDOTBS2'  scope=spfile sid='london2';
alter system set thread=1  scope=spfile sid='london1';
alter system set thread=2  scope=spfile sid='london2';
[SID:london1][[email protected]:/home/oracle ] ~$ srvctl stop database -d london
[SID:london1][[email protected]:/home/oracle ] ~$ srvctl start database -d london
[SID:london1][[email protected]:/home/oracle ] ~$ srvctl status database -d london -v
Instance london1 is running on node exaccdb01. Instance status: Mounted (Closed).
Instance london2 is running on node exaccdb02. Instance status: Mounted (Closed).
[SID:london1][[email protected]:/home/oracle ] ~$
ERROR:
connected to auxiliary database (not started)
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/27/2020 15:38:50
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06403: could not obtain a fully authorized session
RMAN-04006: error from auxiliary database: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4150
Additional information: 14021371
RMAN>
SOLUTION:
1) Start london instance using srvctl
2) Reload listener - check listener.ora
3) Test password
4) boston and london - entries in tnsnames.ora need point to IP host01.
ERROR:
Starting restore at 27-MAY-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=6 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/27/2020 16:11:10
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: Oracle error from auxiliary database: ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server
Solution:
1) Reload listener
2) Check london1 status
3) Test connectivity to boston - need point to host01 in all node
ERROR - Duplicate abort
Added - the entries on grid network/admin/tnsnames.ora
[19.0.0.0 SID:+ASM1][[email protected]:/u01/app/19.0.0.0/grid/network/admin ] admin$ ls -ltr
-rw-r--r-- 1 grid oinstall 1101 Jun  4 10:32 tnsnames.ora
Without this - will cause this error:
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: Oracle error from auxiliary database: ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server
Solution: Add entries in tnsnames.ora to $GRID_HOME/network/admin
Make sure all wallets are same from source database - Copy all from Boston to Lodon
Check the sqlnet.ora file to make sure you are copying from BOSTON to LONDON wallet files
Please make copy before update it.
[[email protected] london]$ cat sqlnet.ora
SQLNET.ENCRYPTION_SERVER = required
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA1)
SQLNET.CRYPTO_CHECKSUM_SERVER = required
ENCRYPTION_WALLET_LOCATION =
 (SOURCE=
  (METHOD=FILE)
   (METHOD_DATA=
    (DIRECTORY=/var/opt/oracle/dbaas_acfs/london/tde_wallet)))
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)
SQLNET.IGNORE_ANO_ENCRYPTION_FOR_TCPS = TRUE
HTTPS_SSL_VERSION = 1.2
SQLNET.EXPIRE_TIME = 10
SQLNET.WALLET_OVERRIDE = FALSE
SSL_VERSION = 1.2
WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/var/opt/oracle/dbaas_acfs/london/db_wallet)))
[[email protected] london]$ ls -ltr /var/opt/oracle/dbaas_acfs/london/tde_wallet
total 88
drwxrwx--- 2 oracle oinstall 20480 Jun  2 13:33 tde_seps
-rw------- 1 oracle oinstall  2555 Jun  2 13:34 ewallet_2020060216341943.p12
-rw------- 1 oracle oinstall     0 Jun  2 15:12 ewallet.p12.lck
-rw------- 1 oracle oinstall     0 Jun  2 15:12 cwallet.sso.lck
-rw------- 1 oracle asmadmin  5467 Jun  2 15:13 ewallet_2020060218133052.p12
-rw------- 1 oracle oinstall  8011 Jun  2 15:13 ewallet.p12
-rw------- 1 oracle oinstall  8056 Jun  2 15:13 cwallet.sso
[[email protected] london]$ ls -ltr /var/opt/oracle/dbaas_acfs/london/db_wallet
total 4
-rw------- 1 oracle oinstall   0 Jun  2 12:49 cwallet.sso.lck
-rw------- 1 oracle oinstall 813 Jun  2 12:50 cwallet.sso
Copy in all nodes - be sure you have a bkp before update it.
[[email protected] london]$ cp /var/opt/oracle/dbaas_acfs/boston/db_wallet/* /var/opt/oracle/dbaas_acfs/london/db_wallet/
[[email protected] london]$ cp -r /var/opt/oracle/dbaas_acfs/boston/tde_wallet/* /var/opt/oracle/dbaas_acfs/london/tde_wallet/
ERROR:
This happened because we are using same machine/server to duplicate boston database to london. If you are using a database to duplicate in another grid/server this need to follow the default duplicate.
sql statement: alter database mount standby database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/29/2020 10:12:14
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 05/29/2020 10:12:14
RMAN-11003: failure during parse/execution of SQL statement: alter database mount standby database
ORA-01103: database name 'BOSTON' in control file is not 'LONDON'
RMAN>
Solution:
Duplicate with this:
    set db_unique_name='london'
    set db_name='boston'
rmanduplicate.sql
[18.0.0.0 SID:london1][[email protected]:/home/oracle/working ] working$ cat rmanduplicate.sql
DUPLICATE target database for standby from active database
  SPFILE
    parameter_value_convert ('boston','london','BOSTON','london')
    set db_unique_name='london'
    set db_name='boston'
    set cluster_database='FALSE'
    set db_file_name_convert='boston','london','BOSTON','london'
    set log_file_name_convert='boston','london','BOSTON','london'
    set db_create_file_dest='+DATAC1'
    set db_create_online_log_dest_1='+DATAC1'
    set audit_trail='none'
    set log_archive_dest_1='location=+RECOC1'
    set LOG_ARCHIVE_DEST_10='location=+DATAC1'
    set remote_listener=''
    set enable_pluggable_database='TRUE'
  NOFILENAMECHECK
  DORECOVER;
ERROR:
create configuration hang on dataguard DG BROKER
Using old configuration for boston database.
DGMGRL> CREATE CONFIGURATION DGBOSTON PRIMARY DATABASE IS boston CONNECT IDENTIFIER IS boston;
Error: ORA-16571: Oracle Data Guard configuration file creation failure
Failed.
[18.0.0.0 SID:london1][[email protected]:/u02/app/oracle/product/18.0.0.0/dbhome_2/network/admin/london ] london$ locate drcboston                     /u02/app/oracle/diag/rdbms/boston/boston/trace/drcboston.log
/u02/app/oracle/diag/rdbms/boston/boston1/trace/drcboston1.log
/u02/app/oracle/diag/rdbms/london/boston1/trace/drcboston1.log
[18.0.0.0 SID:london1]
[[email protected]:/u02/app/oracle/product/18.0.0.0/dbhome_2/network/admin/london ] london$ tail -f /u02/app/oracle/diag/rdbms/boston/boston1/trace/drcboston1.log
Configuration file disk update failed for
Broker operation, error is ORA-16571
  ORA-17503: ksfdopn:2 Failed to open file +DATAC1/boston/BROKER/dr1london.dat
  ORA-15173: entry 'BROKER' does not exist in directory 'boston'
2020-05-29T10:52:34.760-03:00
  ORA-17503: ksfdopn:2 Failed to open file +DATAC1/boston/BROKER/dr2london.dat
  ORA-15173: entry 'BROKER' does not exist in directory 'boston'
cannot open configuration file "+DATAC1/boston/BROKER/dr2london.dat"
  ORA-17503: ksfdopn:2 Failed to open file +DATAC1/boston/BROKER/dr2london.dat
  ORA-15173: entry 'BROKER' does not exist in directory 'boston'
Broker operation completed with error ORA-16571
Failed to get the ConfigurationWideServiceName property value, status=ORA-16532.
REMOVE CONFIGURATION completed successfully
2020-05-29T10:55:42.532-03:00
Broker operation
No database profile named boston found in configuration
Broker operation completed successfully
ADD DATABASE
Published configuration wide service boston_CFG
INTERNAL ERROR: Unexpected dynamic default property HostName.
ADD DATABASE completed successfully
SOLUTION:
Recreate the dataguard broker configuration files.
Restart boston database for all nodes
srvctl stop database -d boston
srvctl start database -d boston
srvctl stop database -d london
srvctl start database -d london
[[email protected] ~]$ asmcmd
ASMCMD>
ASMCMD>
ASMCMD> mkdir +DATAC1/boston/BROKER/
ASMCMD> mkdir +DATAC1/london/BROKER/
ASMCMD> exit
Error:
DGMGRL> add database london as connect identifier is london;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set
Solution connected to london1:
SQL> alter system set log_archive_dest_2='' scope=both sid='*';
System altered.
Error: Cannot drop london database
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 3 11:11:46 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show parameter instance
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
active_instance_count                integer
cluster_database_instances           integer     2
instance_abort_delay_time            integer     0
instance_groups                      string
instance_mode                        string      READ-WRITE
instance_name                        string      london1
instance_number                      integer     1
instance_type                        string      RDBMS
open_links_per_instance              integer     4
parallel_instance_group              string
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation
Solution: Update cluster parameter to FALSE
SQL> shut abort
ORACLE instance shut down.
SQL> startup mount exclusive restrict;
ORACLE instance started.
Total System Global Area 7963757000 bytes
Fixed Size                  9157064 bytes
Variable Size            1677721600 bytes
Database Buffers         4613734400 bytes
Redo Buffers               69308416 bytes
In-Memory Area           1593835520 bytes
Database mounted.
SQL> show parameter cluster
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster                          boolean     FALSE
cdb_cluster_name                     string
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string      100.107.2.1:100.107.2.2
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation
SQL>  alter system set cluster_database=false scope=spfile sid='*';
System altered.
SQL> create pfile='$HOME/initlondon.ora' from spfile;
File created.
SQL> shut abort
ORACLE instance shut down.
SQL>  startup mount exclusive restrict;
ORACLE instance started.
Total System Global Area 7963757000 bytes
Fixed Size                  9157064 bytes
Variable Size            1677721600 bytes
Database Buffers         4613734400 bytes
Redo Buffers               69308416 bytes
In-Memory Area           1593835520 bytes
Database mounted.
SQL> show parameter instance
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
active_instance_count                integer
cluster_database_instances           integer     1
instance_abort_delay_time            integer     0
instance_groups                      string
instance_mode                        string      READ-WRITE
instance_name                        string      london1
instance_number                      integer     1
instance_type                        string      RDBMS
open_links_per_instance              integer     4
parallel_instance_group              string
SQL>  drop database;
Revise files under ASM after remove london database:
Is possible to remain some files under ASM disgroup RECOC1 for archivelogs and controlfiles autobackup for london temporary database (used only to be present in control plane)
[[email protected] scripts]$ ./asmdu.sh -d recoc1/london -g
Instances running on techdb01 : +APX1, +ASM1, boston1, CDBD021, CDBH011, CDBH021, CDBIT1, CDBP011, CDBP021, CDBP051, CDBP081, CDBP211
                DiskGroup      Redundancy        Total GB       Usable GB        % Free
                ---------     -----------        --------       ---------        ------
                   recoc1            HIGH        30648.00        26862.23          87
                       recoc1/london subdirectories size
                   Subdir         Used GB     Raw Used GB
                   ------         -------     -----------
              ARCHIVELOG/            0.61            1.83
              AUTOBACKUP/            0.17            0.50
             CONTROLFILE/            0.00            0.00
                   ------         -------       ---------
                    Total            0.78            2.33
[[email protected] scripts]$ asmcmd
ASMCMD> cd recoc1
ASMCMD> cd london
ASMCMD> cd archivelog
ASMCMD> ls
2020_06_02/
2020_06_03/
ASMCMD> pwd
+recoc1/london/archivelog
ASMCMD> rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ORA-15032: not all alterations performed
ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)
ASMCMD> ls
2020_06_02/
2020_06_03/
ASMCMD> cd 2020_06_02/
ASMCMD> ls
thread_1_seq_1.386.1042032297
thread_1_seq_2.319.1042032327
thread_1_seq_3.507.1042032359
thread_1_seq_4.618.1042032497
thread_1_seq_5.682.1042032589
thread_1_seq_6.622.1042032861
thread_1_seq_7.617.1042032925
thread_1_seq_8.608.1042033179
thread_1_seq_9.607.1042033179
thread_2_seq_1.623.1042032465
thread_2_seq_2.662.1042032589
thread_2_seq_3.638.1042032861
thread_2_seq_4.613.1042033043
thread_2_seq_5.528.1042033115
ASMCMD> rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD> cd ..
ASMCMD> ls
2020_06_03/
ASMCMD> cd 20*
ASMCMD> ls
thread_1_seq_10.487.1042105389
thread_2_seq_6.1088.1042105389
ASMCMD> rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD> cd ..
ASMCMD> ls
ASMCMD> pwd
+recoc1/london/archivelog
ASMCMD> cd ..
ASMCMD> ls
ARCHIVELOG/
AUTOBACKUP/
CONTROLFILE/
ASMCMD> cd autobackup
ASMCMD> ls
2020_06_02/
ASMCMD> cd 20*
ASMCMD> rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD> cd ..
ASMCMD> quit
[[email protected] scripts]$ ./asmdu.sh -d recoc1/london -g
Instances running on techdb01 : +APX1, +ASM1, boston1, CDBD021, CDBH011, CDBH021, CDBIT1, CDBP011, CDBP021, CDBP051, CDBP081, CDBP211
                DiskGroup      Redundancy        Total GB       Usable GB        % Free
                ---------     -----------        --------       ---------        ------
                   recoc1            HIGH        30648.00        26863.00          87
                       recoc1/london subdirectories size
                   Subdir         Used GB     Raw Used GB
                   ------         -------     -----------
              ARCHIVELOG/            0.00            0.00
              AUTOBACKUP/            0.00            0.00
             CONTROLFILE/            0.00            0.00
                   ------         -------       ---------
                    Total            0.00            0.00
[[email protected] scripts]$

Some usefull commands to be used on Dataguard Broker dgmgrl


EDIT DATABASE 'london' SET STATE='APPLY-OFF';
EDIT DATABASE 'london' SET STATE='APPLY-ON';
EDIT DATABASE 'london' SET property delaymins=1440;
EDIT DATABASE 'london' SET property applyparallel=4;
convert DATABASE 'london' to snapshot standby;
convert DATABASE 'london' to physical standby;
Real apply SQL - physical standby
EDIT DATABASE 'london' SET STATE='APPLY-OFF';
shut immediate
startup
alter database recover managed standby database using current logfile disconnect;
alter database recover managed standby database cancel;
EDIT DATABASE 'london' SET STATE='APPLY-ON';

glogin update

[18.0.0.0 SID:london1][[email protected]:/u02/app/oracle/product/18.0.0.0/dbhome_2/sqlplus/admin ] admin$ cat glogin.sql
set linesize 200
define _editor=vi
set serveroutput on size 1000000
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user)||'@'||instance_name||'('||host_name||')' global_name from v$instance;
--set sqlprompt '&gname> '
set termout on
column db_unique_name format a20
column database_role format a16
column db_version format a10
column db_status format a8
column db_compatible format a10
column db_version heading VERSION
column db_status heading STATUS
column db_compatible heading COMPATIBLE
select d.db_unique_name, d.database_role, d.open_mode, v.version db_version,
p.value db_compatible, v.status db_status
from gv$database d, gv$instance v, gv$parameter p
where p.name = 'compatible'
and d.inst_id=v.inst_id and v.inst_id = p.inst_id
;
col logins format a8
column instance_name format A12
column status format A8
column CONNECTED format A12
column host_name format A32
select host_name,instance_name,
status, to_char(startup_time,
'DD-MON-YYYY HH24:MI ') "Startup time",logins,
decode(instance_name,(select instance_name from v$instance) ,'CURRENT','NOT CURRENT') CONNECTED
from gv$instance
/

Check if wallet is in use on boston and london

[18.0.0.0 SID:boston1][[email protected]:/u02/app/oracle/product/18.0.0.0/dbhome_2/network/admin/boston ] boston$ cat sqlnet.ora
SQLNET.ENCRYPTION_SERVER = required
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA1)
SQLNET.CRYPTO_CHECKSUM_SERVER = required
ENCRYPTION_WALLET_LOCATION =
 (SOURCE=
  (METHOD=FILE)
   (METHOD_DATA=
    (DIRECTORY=/var/opt/oracle/dbaas_acfs/boston/tde_wallet)))
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)
SQLNET.IGNORE_ANO_ENCRYPTION_FOR_TCPS = TRUE
HTTPS_SSL_VERSION = 1.2
SQLNET.EXPIRE_TIME = 10
SQLNET.WALLET_OVERRIDE = FALSE
SSL_VERSION = 1.2
WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/var/opt/oracle/dbaas_acfs/boston/db_wallet)))
Error:
SQL> set lines 200 pages 9999
cSQL> ol WRL_PARAMETER format a50
SQL> select * from V_$ENCRYPTION_WALLET;
WRL_TYPE             WRL_PARAMETER                                      STATUS   WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
-------------------- -------------------------------------------------- -------- -------------------- --------- -------- --------- ----------
FILE                 /u02/app/oracle/admin/boston/wallet                NOT_AVAI UNKNOWN              SINGLE    NONE     UNDEFINED          1
                                                                        LABLE
FILE                                                                    NOT_AVAI UNKNOWN              SINGLE    UNITED   UNDEFINED          2
                                                                        LABLE
FILE                                                                    NOT_AVAI UNKNOWN              SINGLE    UNITED   UNDEFINED          3
                                                                        LABLE
SQL> quit
Need be to correct location - restart boston and check in london - copy sqlnet.ora to update in all nodes. Repeat london and boston
[18.0.0.0 SID:boston1][[email protected]:/u02/app/oracle/product/18.0.0.0/dbhome_2/network/admin/london ] london$
 scp sqlnet.ora [email protected]:`pwd`

ACE Be updated from the changes on your favorites documents on MOS convert database snapshot DataGuard Exadata Dataguard RAC dbaascli duplicate auxiliary DUPLICATE fail with ORA-38788 duplicate using backup location EM_LOADERJOB EM_LOADERJOB.unlock_target EXACC exachk Exadata Cloud at customer Gen2 EXADATA GEN2 Exadata Patch 19c linux kernel best practices linux kernel parameters Metalink update you Missing UNLOCK_TARGET OEM alert log growing opatch OPATCHAUTO-72043 ORA-01261 ORA-09925 ORA-12514 ORA-27300 ORA-27301 ORA-27302 ORA-38784 ORA-38788 ORA-38788: More standby database recovery is needed oracle duplicate failure rda Receive updates from MOS recover database RMAN-04014 RMAN-06403. tablespace size tablespace sysaux tfa WARNING: too many parse errors

Exadata Cloud at Customer GEN2 – patch

DEPLOY BUNDLE PATCHES

GRID

Discover the home of grid :

ps -eaf | grep crs
[[email protected] dbinput]# dbaascli patch db list --oh exatechdb01:/u01/app/19.0.0.0/grid/

Check if the cluster is running in all nodes:

Check the cluster version:

[[email protected] ~]$ crsctl query crs activeversion

Oracle Clusterware active version on the cluster is [19.0.0.0.0]

We need to execute prereq before apply the patch:

dbaascli patch db prereq –patchid 30501910-GI –dbnames grid

Deploy the patch:

dbaascli patch db apply –patchid 30501910-GI –dbnames grid:

Verify if cluster is back and online:

[[email protected] ~]$ crsctl check cluster -all