Trigger to update – TSTZ

This trigger below need be created before the impdp – execution. This will avoid ORA-39405 During Import – The insane way

Create the trigger below and replace the property according to your error. This will correct/update the timezone – but this will work only if your imported data will not have columns with timezone property. Make your adjusts – like the owner, the table name and property values.

CREATE OR REPLACE TRIGGER sys.create_table_trigger
  AFTER CREATE ON DATABASE
declare
  sql_text varchar2(200);
BEGIN
  IF DICTIONARY_OBJ_TYPE = 'TABLE' and ora_dict_obj_name = 'SYS_IMPORT_FULL_01' and ORA_SYSEVENT = 'CREATE' THEN
    sql_text := 'CREATE OR REPLACE TRIGGER update_timezone BEFORE INSERT OR UPDATE ON sys.SYS_IMPORT_FULL_01 ' ;
    sql_text := sql_text || ' FOR EACH ROW WHEN (new.property > 0) BEGIN ';
    sql_text := sql_text || ' if ' || ':' || 'new.property=32 then ';
    sql_text := sql_text  || ' :' || 'new.property:=42;';
    sql_text := sql_text  || ' end if;';
    sql_text := sql_text  || ' END; ';
    execute immediate sql_text ;
  end if;
END;



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:

[root@machine01 30920127]# cat /etc/oratab
#
+ASM2:/u01/app/19.0.0/grid:N
teste:/u01/app/oracle/product/12.2.0/dbhome_1:N
[root@machine01 ~]# . oraenv
ORACLE_SID = [root] ? teste
The Oracle base has been set to /u01/app/oracle
[root@machine01 ~]# /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
[root@machine01 ~]# pwd
/root
[root@machine01 ~]# cd /stage/midia/30920127/
[root@machine01 30920127]# ls
26839277 30869447 30882603 30886680 30888810 automation bundle.xml README.html README.txt
[root@machine01 30920127]# chown oracle:oinstall * -Rf
[root@machine01 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
[root@machine01 30920127]# chown oracle:oinstall ../* -Rf

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

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

[root@machine01 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

[root@machine01 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:

[root@exadatavm01 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.

[root@exadatavm01 rda]# chmod +x rda.sh rda.pl
[root@exadatavm01 rda]# chown oracle:oinstall /stage/rda -Rf
[root@exadatavm01 rda]# pwd
/stage/rda

Checking versions:

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

[root@exadatavm01 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 oracle@ORCL1]/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.

[root@exadatavm01 midia]#
[root@exadatavm01 midia]# /u02/opt/oracle.tfa/tfa/exadatavm01/tfa_home/bin/tfactl -version
TFA Version : 194400
TFA Build ID : 20200717142338
[root@exadatavm01 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/

[root@exadatavm01 midia]#

After repeat the same in all nodes – final node:

[root@exadatavm04 midia]# scp exadatavm01:/stage/midia/AHF-LINUX_v20.2.3.zip .
AHF-LINUX_v20.2.3.zip 100% 389MB 204.5MB/s 00:01
[root@exadatavm04 midia]# unzip AHF-LINUX_v20.2.3.zip
Archive: AHF-LINUX_v20.2.3.zip
inflating: README.txt
inflating: ahf_setup
[root@exadatavm04 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/

[root@exadatavm04 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.

 

 

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:

[root@techdb01 ~]# 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 <>
[root@techdb01 ~]# 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
[root@techdb01 ~]#
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][oracle@tech01:/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][oracle@tech01:/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][oracle@tech01:/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][oracle@tech01:/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][oracle@tech01:/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][oracle@tech01:/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:
[grid@tech01 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][oracle@tech01:/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][oracle@tech01:/home/oracle/working ] working$ srvctl status database -d london
Instance london1 is running on node tech01
[18.0.0.0 SID:boston1][oracle@tech01:/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][oracle@tech01:/home/oracle/working ] working$ source $HOME/london.env
[18.0.0.0 SID:london][oracle@tech01:/home/oracle/working ] working$ export ORACLE_SID=london1
[18.0.0.0 SID:london1][oracle@tech01:/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][oracle@tech01:/home/oracle/working ] working$
[18.0.0.0 SID:london1][oracle@tech01:/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][oracle@tech01:/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.

[grid@tech01 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][oracle@tech01:/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][oracle@tech01:/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..
[oracle@tech01 ~]$
srvctl status instance -d london -i london1
Instance london1 is running on node tech01
[oracle@tech01 ~]$
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/
[root@techdb01 ~]# su - grid
Last login: Thu Jun  4 11:48:21 -03 2020
[19.0.0.0 SID:+ASM1][grid@techdb01:/home/grid ] ~$ asmcmd mkdir +DATAC1/BOSTON/BROKER/
[19.0.0.0 SID:+ASM1][grid@techdb01:/home/grid ] ~$ asmcmd mkdir +DATAC1/LONDON/BROKER/
[19.0.0.0 SID:+ASM1][grid@techdb01:/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][oracle@tech01:/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][oracle@tech01:/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][oracle@exaccdb01:/home/oracle ] ~$ srvctl stop database -d london
[SID:london1][oracle@exaccdb01:/home/oracle ] ~$ srvctl start database -d london
[SID:london1][oracle@exaccdb01:/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][oracle@exaccdb01:/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][grid@techdb01:/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.
[oracle@techdb01 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)))
[oracle@techdb01 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
[oracle@techdb01 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.
[oracle@techdb01 london]$ cp /var/opt/oracle/dbaas_acfs/boston/db_wallet/* /var/opt/oracle/dbaas_acfs/london/db_wallet/
[oracle@techdb01 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][oracle@tech01:/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][oracle@tech01:/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]
[oracle@tech01:/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
[grid@tech01 ~]$ 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
[oracle@exaccdb01 ~]$ 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)
[grid@techdb01 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
[grid@techdb01 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
[grid@techdb01 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
[grid@techdb01 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][oracle@tech01:/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][oracle@tech01:/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][oracle@tech01:/u02/app/oracle/product/18.0.0.0/dbhome_2/network/admin/london ] london$
 scp sqlnet.ora oracle@tech02:`pwd`

ACE Be updated from the changes on your favorites documents on MOS check oracle database convert database snapshot DataGuard Exadata Dataguard RAC dbaascli DBMS_JOB 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 Move from DBMS_JOB to DBMS_SCHEDULER 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
[root@extechdb01 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:

[grid@exatechdb01 ~]$ 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:

[grid@exatechdb01 ~]$ crsctl check cluster -all

Linux Best Practices

Hello Guys,

Let’s investigate this issue on Exadata At Customer – or Exadata Machine:

[oracle@tech01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 18 16:15:50 2020
Version 19.7.0.0.0
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/init.ora';
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpbitsper
SQL> quit
Disconnected

The solutions is:

Reduce parameters processes, sessions, transactions and open_cursors for all databases. Follow the maximum used on each database to avoid to use much more than the database needs – and yes put some extra value since your database can grow. Update sysctl.conf parameters for your linux accommodate all databases. See the steps below:

=================================================================11) Check results for max limits on database used
=================================================================
column rname	format a30 	    heading 'Resource Name';
column curu	format 999,999,990	heading 'Current|Utilization' just c;
column maxu	format 999,999,990	heading 'Maximum|Utilization' just c;
column inita    format a10      heading 'Initial|Allocation'  just c;
column lim      format a10      heading 'Limit'               just r;
set lines 200
select inst_id, resource_name         rname
     , current_utilization   curu
     , max_utilization       maxu
     , initial_allocation    inita
     , limit_value           lim
from  gv$resource_limit  ;
select inst_id, resource_name         rname
     , current_utilization   curu
     , max_utilization       maxu
     , initial_allocation    inita
     , limit_value           lim
from  gv$resource_limit
WHERE resource_name in ('processes','sessions');
SQL> set lines 200
SQL> SQL> select inst_id, resource_name         rname
  2       , current_utilization   curu
  3       , max_utilization       maxu
  4       , initial_allocation    inita
  5       , limit_value           lim
  6  from  gv$resource_limit
  7
SQL> /
                                            Current      Maximum     Initial
   INST_ID Resource Name                  Utilization  Utilization  Allocation      Limit
---------- ------------------------------ ------------ ------------ ---------- ----------
         1 processes                               165          186       2048       2048
         1 sessions                                170          208       3100       3100
         1 enqueue_locks                            76           98      36542      36542
         1 enqueue_resources                        62           87      14684  UNLIMITED
         1 ges_procs                               153          173       2051       2051
         1 ges_ress                             11,663       87,988      69331  UNLIMITED
         1 ges_locks                               343          547      99571  UNLIMITED
         1 ges_cache_ress                       12,072       16,162          0  UNLIMITED
         1 ges_reg_msgs                            176        4,119       6126  UNLIMITED
         1 ges_big_msgs                             39          360       3826  UNLIMITED
                        0            0       1000       1000
         1 gcs_resources                        47,386       47,460     810054  UNLIMITED
         1 gcs_shadows                          33,188       34,349     810054  UNLIMITED
         1 smartio_overhead_memory             855,160    1,026,192          0  UNLIMITED
         1 smartio_buffer_memory               343,416    1,486,280          0  UNLIMITED
         1 smartio_metadata_memory                   0           56          0  UNLIMITED
         1 smartio_sessions                          3            4          0  UNLIMITED
         1 dml_locks                                 0           21      13640  UNLIMITED
         1 temporary_table_locks                     0            5  UNLIMITED  UNLIMITED
         1 transactions                              0            5       3410  UNLIMITED
.....
108 rows selected.
SQL>

Oracle suggested:

SEMMNI should be increased to accomodate more semaphores.
1. Query the current semaphore values in the kernel
     # /sbin/sysctl -a | grep sem
2. Modify SEMMNI value in the /etc/sysctl.conf.
From
kernel.sem = 250 32000 100 128
To
kernel.sem = 250 32000 100 200
3. # /sbin/sysctl -p

What worked for me:

### ANDRE ROCHA

# kernel.sem = 1024 60000 1024 256 -> original

kernel.sem = 1512 1200000 1512 1024

My original recipe:

=================================================================
List of best practices: ANDRE ROCHA
=================================================================1) Configure huge pages - Doc ID 361323.1  401749.1
2) Disable Transparent HugePages - Doc ID 1557478.1
3) Check if system using hugepage have this parameter USE_LARGE_PAGES enabled Doc ID 1392497.1
4) Check swap is set correct and swapiness - Doc ID 2240180.1 2404462.1  - vm.swappiness=10
5) Network Considerations - Doc ID 811306.1
6) Collect data for sar output and print graph to get swap memory results
7) About async IO, please check on aio.max-nr and fs.aio-nr.
8) Revise Kernel parameters with best practices/recommendations
9) Set/check kernel parameters can affect SGA size and shared pool
10) Check results of cluvfy for packages, kernel limits and general check results
14) Appendice of list of commands to be executed/get results
https://sarchart.weebly.com/howto.html
=================================================================Linux Kernel Lowmem Pressure Issues and Related Kernel Structures (Doc ID 452326.1)
ALERT: Disable Transparent HugePages on SLES11, RHEL6, RHEL7, OL6, OL7, and UEK2 and above (Doc ID 1557478.1)
HugePages on Linux: What It Is... and What It Is Not... (Doc ID 361323.1)
System Hangs Due To Process Kswapd0 (Doc ID 2240180.1)
Why is SWAP being used instead of available physical memory? (Doc ID 2404462.1)
https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/6/html/performance_tuning_guide/s-memory-tunables
A low swappiness value is recommended for database workloads. For example, for Oracle databases,
Red Hat recommends a swappiness value of 10. vm.swappiness=10
Top 5 Issues That Cause Node Reboots or Evictions or Unexpected Recycle of CRS (Doc ID 1367153.1)
If the platform is Linux, set up hugepages and set kernel parameter vm.min_free_kbytes to reserve 512MB.
Setting hugepages is probably the single most important thing to do on Linux. Note that memory_target can not
be set when using hugepages.
ALERT: Disable Transparent HugePages on SLES11, RHEL6, RHEL7, OL6, OL7, and UEK2 and above (Doc ID 1557478.1)
Because Transparent HugePages are known to cause unexpected node reboots and performance problems with RAC,
Oracle strongly advises to disable the use of Transparent HugePages. In addition, Transparent Hugepages may
cause problems even in a single-instance database environment with unexpected performance problems or delays.
As such, Oracle recommends disabling Transparent HugePages on all Database servers running Oracle.
Note: on UEK2 and above, check the existence of the /sys/kernel/mm/transparent_hugepage/ directory.
If this directory does not exist, then the transparent hugepage is removed from the kernel, so there is no need to
disable the transparent hugepage
For Oracle Linux 7, Oracle published MOS document 2066217.1
Oracle Linux 7 - How to disable Transparent HugePages for RHCK kernel?
Also, add the following line to /etc/default/grub file:
transparent_hugepage=never
cat /etc/grub.conf
title Oracle Linux Server (2.6.32-300.25.1.el6uek.x86_64)
        root (hd0,0)
        kernel /vmlinuz-2.6.32-300.25.1.el6uek.x86_64 ro root=LABEL=/ transparent_hugepage=never
        initrd /initramfs-2.6.32-300.25.1.el6uek.x86_64.img
=================================================================1) Configure huge pages - Doc ID 361323.1
=================================================================https://oracle-base.com/articles/linux/configuring-huge-pages-for-oracle-on-linux-64
Implement HugePages for the database instances.  HugePages provides a method to have larger page sizes and it will
lock the SGA into physical memory thus eliminating the need for system page table lookups for the SGA.
This is especially important on systems with high memory allocations due to eliminate the management overhead
associated with the such configurations.  HugePages is also recommended in environments where server stability
issues are evident – various spins/hangs, which are not attributed to Oracle Clusterware or other known OS issues.
To compute size, see Document 401749.1. For more information, see Document 361323.1. In 11gR1 and above, you must disable
AMM to use HugePages as directed in Document 749851.1.
$ grep Huge /proc/meminfo
AnonHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
$
Execute hugepages_setting script:
$ ./hugepages_setting.sh
Recommended setting: vm.nr_hugepages = 305
$
Edit file and add the result for the script hugepages_settings.sh: vm.nr_hugepages
/etc/sysctl
Run the following command as the "root" user.
# sysctl -p
To be permanet, edit the "/etc/grub.conf" file, adding "hugepages=306" to the end of the kernel
line for the default kernel and reboot.
You can now see the HugePages have been created, but are currently not being used.
Check the HugePages information again.
$ grep Huge /proc/meminfo
AnonHugePages:         0 kB
HugePages_Total:     306
HugePages_Free:       98
HugePages_Rsvd:       93
HugePages_Surp:
Hugepagesize:       2048 kB
$
$
If you prefer, you can set these parameters to a value just below the size of physical memory of
the server. This way you can forget about it, unless you add more physical memory.
Update Oracle database
ALTER SYSTEM SET use_large_pages=only SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
=================================================================2) Setting Shell Limits for the Oracle User
=================================================================
https://docs.oracle.com/cd/E51773_01/doc.12105/e51150/dbinstall.htm
To improve the performance of the software, you must increase the shell limits for the oracle user.
Add the following lines to the /etc/security/limits.conf file:
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
If not already present, add the following lines to the /etc/pam.d/login file:
session required /lib64/security/pam_limits.so
session required pam_limits.so
Depending on the oracle user's default shell, you need to make changes to it. For the Bourne, Bash, or Korn shell, add the following lines to the bottom of the /etc/profile file:
if [ $USER = "oracle" ]; then
    if [ $SHELL = "/bin/ksh" ]; then
        ulimit -p 16384
        ulimit -n 65536
    else
        ulimit -u 16384 -n 65536
    fi
fi
Add the following entries into the "/etc/security/limits.conf" script or
"/etc/security/limits.d/99-grid-oracle-limits.conf" script, where the setting is at least the size
of the HugePages allocation in KB (HugePages * Hugepagesize).
In this case the value is 306*2048=626688.
* soft memlock 626688
* hard memlock 626688
=================================================================2) Disable Transparent HugePages - Doc ID 1557478.1
=================================================================
If you are running RedHat/OEL 6/7, SLES 11/12 or UEK2 kernels, be sure to disable Transparent HugePages to prevent
performance problems and Node/Instance evictions.  Our long standing recomendation of using general HugePages still stands
(THP is a different implementation than general HugePages).  See Document 1557478.1 for additional details.
You can check the current setting using the following command, which is displaying the default value of "enabled=[always]".
# cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never
For Oracle Linux 6 the preferred method to disable Transparent HugePages is to add "transparent_hugepage=never" to the kernel boot line in the "/boot/grub/grub.conf" file.
title Oracle Linux Server (2.6.39-400.24.1.el6uek.x86_64)
        root (hd0,0)
        kernel /vmlinuz-2.6.39-400.24.1.el6uek.x86_64 ro root=/dev/mapper/vg_ol6112-lv_root rd_NO_LUKS  KEYBOARDTYPE=pc KEYTABLE=uk
LANG=en_US.UTF-8 rd_NO_MD SYSFONT=latarcyrheb-sun16  rd_NO_DM rd_LVM_LV=vg_ol6112/lv_swap rd_LVM_LV=vg_ol6112/lv_root rhgb quiet numa=off
transparent_hugepage=never
        initrd /initramfs-2.6.39-400.24.1.el6uek.x86_64.img
The server must be rebooted for this to take effect.
Alternatively, add the following lines into the "/etc/rc.local" file and reboot the server.
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
   echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
   echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
Whichever method you choose, remember to check the change has work after reboot.
# cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
#
=================================================================3) Check if system using hugepage have this parameter USE_LARGE_PAGES enabled Doc ID 1392497.1
=================================================================show parameter USE_LARGE_PAGES
=================================================================4) Check swap is set correct and swapiness - Doc ID 2240180.1 2404462.1  - vm.swappiness=10
=================================================================
Free -h
Take a backup of the current sysctl.conf file to be used in case of rollback
cp /etc/sysctl.conf /root/sysctl.conf_TICKETNUMBER
Review current swappiness configuration
cat /proc/sys/vm/swappiness
60
Change the current in-memory setting for vm.swappiness
echo 10 > /proc/sys/vm/swappiness
Making Permanent the mv.swappiness
# echo 'vm.swappiness=10' >> /etc/sysctl.conf
Confirm the new swappiness value
 # cat /proc/sys/vm/swappiness
---Result 10.
=================================================================5) Network Considerations - Doc ID 811306.1
=================================================================
RAC and Oracle Clusterware Best Practices and Starter Kit (Linux) (Doc ID 811306.1)
As workload dictates, rmem_max and wmem_max kernel parameters should be increased beyond the default 256kb.  These values determine how much kernel buffer memory is allocated per socket opened for network reads and writes:
net.core.rmem_default=262144
net.core.rmem_max=4194304 (for 11g and all RDS implementations)
net.core.rmem_max=2097152 (for 10g)
net.core.wmem_default=262144
net.core.wmem_max=1048576 (with RDS use at least 2097152)
Changing Kernel Parameter Values - sysctl.conf for 19c
https://docs.oracle.com/en/database/oracle/oracle-database/19/ladbi/changing-kernel-parameter-values.html#GUID-FB0CC366-61C9-4AA2-9BE7-233EB6810A31
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
=================================================================6) Collect data for sar output
=================================================================
### All Days available on SAR
DT=$(ls /var/log/sa/sa[0-9][0-9] | tr '\n' ' ' | sed 's/\/var\/log\/sa\/sa/ /g')
>/tmp/sar-$(hostname)-multiple.txt
for i in $DT; do
LC_ALL=C sar -A -f /var/log/sa/sa$i >> /tmp/sar-$(hostname)-multiple.txt
done
ls -l /tmp/sar-$(hostname)-multiple.txt
=================================================================7) About async IO, please check on aio.max-nr and fs.aio-nr
=================================================================
What value should kernel parameter AIO-MAX-NR be set to ? (Doc ID 2229798.1)
The minimum value suggested as per http://docs.oracle.com/database/121/LTDQI/toc.htm#BABFIFCGfor aio-max-nr is 1048576 .
the DOCS provide the minimum required for a good install and basic usage.
This is how we calculate aio-max-nr parameter value -
show parameter processes
aio-max-nr =no of process per DB * no of databases * 4096
After deriving the value using this formula , you should still monitor the /proc/sys/fs/aio-nr value and also monitor I/O
loading.
Points to know -
1) Number  of processes per database  is not background server process number, it's the number of background server
process + user process.
2) Parameter aio-max-nr is a limitation of aio-nr, no impact was reported if you set it too high. But we should still
monitor /proc/sys/fs/aio-nr value and tune this value.
cat /proc/sys/fs/aio-nr
=================================================================8) Revise Kernel parameters with best practices/recommendations
=================================================================https://docs.oracle.com/database/122/LADBI/app_manual.htm#LADBI7865
https://docs.oracle.com/en/database/oracle/oracle-database/19/cwlin/changing-kernel-parameter-values.html#GUID-FB0CC366-61C9-4AA2-9BE7-233EB6810A31
=================================================================
9) Set/check kernel parameters can affect SGA size
and shared pool
=================================================================
shmmax 64G 85% of 16G
kernel.shmmax 68719476736 kernel.shmmax=15032385536
need fit/affect SGA
kernel.shmall 4294967296=4G 40% 16G = 6,4G = 6871947673
need fit/affect Shared pool
=================================================================
10) Check results of cluvfy for packages, kernel limits and general check results
=================================================================
Download it
https://www.oracle.com/database/technologies/cvu-downloads.html
### All Days available on SAR
DT=$(ls /var/log/sa/sa[0-9][0-9] | tr '\n' ' ' | sed 's/\/var\/log\/sa\/sa/ /g')
>/tmp/sar-$(hostname)-multiple.txt
for i in $DT; do
LC_ALL=C sar -A -f /var/log/sa/sa$i >> /tmp/sar-$(hostname)-multiple.txt
done
ls -l /tmp/sar-$(hostname)-multiple.txt
=================================================================
General results/recommendations :
=================================================================
Kernel: Reduce shmmax to 85% of 16G
Kernel: increase shmall to 40% of 16G
Set hugepages: vm.nr_hugepages = 964
Swap is under the recommendation but sounds ok since was not using swap memory on it.
Next approach to continue to work on it: Investigate sga_target memory settings since
sga is using only 2G memory entire memory.
ex.
shmmax  64G 85% of 16G (total memoria)
kernel.shmmax 68719476736 kernel.shmmax=15032385536
need fit/affect SGA
kernel.shmall 4294967296=4G 40% 16G = 6,4G = 6871947673
need fit/affect Shared pool  (total memoria)
Oracle Exadata Database Machine Setup/Configuration Best Practices (Doc ID 1274318.1)
s.file-max = 6815744 512 x processes (for example 6815744 for 13312 processes)
kernel.shmmax = RAM times 0.5 (or higher at customer's discretion - see Note:567506.1)
Oracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Mac OS X,Solaris,Tru64 Unix Operating Systems
Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2) (Doc ID 169706.1)
Upon startup of Linux database get ORA-27102: out of memory Linux-X86_64 Error: 28:
No space left on device (Doc ID 301830.1)
What value should kernel parameter AIO-MAX-NR be set to ? (Doc ID 2229798.1)
TECH: Unix Semaphores and Shared Memory Explained (Doc ID 15566.1)
Exadata sysctl comments:
Maximum shared memory allowed is 85 percent of physical RAM
Maximum shared memory pages allowed are 85 percent of physical RAM divided by page size
kernel.sem = 1024 60000 1024 256
Check processes -> used to calculate aio-max-nr
show parameter memor
grep SwapTotal /proc/meminfo
=================================================================
11) Check results for max limits on database used
=================================================================
column rname	format a30 	    heading 'Resource Name';
column curu	    format 999,999,990	heading 'Current|Utilization' just c;
column maxu	    format 999,999,990	heading 'Maximum|Utilization' just c;
column inita    format a10      heading 'Initial|Allocation'  just c;
column lim      format a10      heading 'Limit'               just r;
column inst_id  format a10      heading 'Limit'               just r;
set lines 200
select inst_id, resource_name         rname
     , current_utilization   curu
     , max_utilization       maxu
     , initial_allocation    inita
     , limit_value           lim
from  gv$resource_limit  ;
select inst_id, resource_name         rname
     , current_utilization   curu
     , max_utilization       maxu
     , initial_allocation    inita
     , limit_value           lim
from  gv$resource_limit
WHERE resource_name in ('processes','sessions');
=================================================================
12) Check results for swap and compare with total of memory
=================================================================
Requirements for Installing Oracle Database 12.1 on RHEL5 or OL5 64-bit (x86-64) (Doc ID 1529433.1)
b.) Swap disk space proportional to the system's physical memory as follows:
RAM	Swap Space
Between 1 GB and 2 GB	1.5 times the size of RAM
Between 2 GB and 16 GB	Equal to the size of RAM
More than 16 GB	16 GB
NOTE: The above recommendations (from the Oracle® Database Installation Guide 12c Release 1
(12.1) for Linux) are MINIMUM recommendations for installations. Further RAM and swap space may be
required to tune/improve RDBMS performance.
grep SwapTotal /proc/meminfo
=================================================================
List of commands to be executed
=================================================================
Swap check and memory
grep SwapTotal /proc/meminfo
free -m
uname -r
cat /etc/sysctl.conf
cat /etc/security/limits.conf
cat /sys/kernel/mm/transparent_hugepage/enabled
cat /proc/sys/fs/aio-nr
cat /proc/sys/vm/swappiness
cat /etc/default/grub file:
cat /etc/grub.conf
cat /etc/security/limits.conf
show parameter memory
show parameter sga
show parameter processes
show parameter USE_LARGE_PAGES
NAME                                |Current/Acutal          |Suggested
------------------------------------|------------------------|------------------------------
HugePage                             not set                  vm.nr_hugepages = 964
shmmax                               64G                      85% of 16G
kernel.shmmax                        68719476736              kernel.shmmax=15032385536
kernel.shmall                        4294967296=4G            40% 16G = 6,4G = 6871947673
HugePage                             not set                  vm.nr_hugepages = 964
 = 64G
kernel.shmmax = 68719476736
### Exadata 202005 - standard
# kernel.sem = 1024 60000 1024 256
kernel.sem = 1512 1200000 1512 1024