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;



rockdb scripts released!

By the first time, the release of these scripts I created to turn the job easier in the database.
This is an initial release of scripts – the main idea is – create scripts to turn – check trivial operations easier – like backup, expdp, – or creation of standby databases, duplicate, and get all details in case of export of database to another version/database. The main report can be sent by email in case of you use mutt like an email client and you can zip the results and send it.
They have inside scripts I created and collected from documentation, and we can see dashboards, and graphics to alert about issues.  This is the first release, I think will be changed a lot yet with some changes in menu and others.
You can get and download the script:
https://github.com/rochaandre/rockdb
Keep in touch and be happy.

 

DBMS_JOB move to DBMS_SCHEDULER

Nota

Easy way to migrate my jobs, to avoid issues for upgrade to 19c or other.

I created this query to help during the process.

WITH
queryintervaloriginal AS (
SELECT job, TRUNC(MONTHS_BETWEEN(next_DATE, LAST_DATE)) AS FREQ_MONTH
,(TRUNC(next_DATE) – TRUNC(LAST_DATE)) AS FREQ_DAY
, ROUND((next_DATE – LAST_DATE)* 24 * 60) AS FREQ_MINUTE
, TO_CHAR(next_DATE, ‘Dy’) AS WEEKDAY_VALUE
, TO_NUMBER(TO_CHAR(LAST_DATE, ‘DD’)) as DAY_VALUE
, TO_NUMBER(TO_CHAR(LAST_DATE, ‘HH24’)) as HOUR_VALUE
, TO_NUMBER(TO_CHAR(LAST_DATE, ‘MI’)) as MINUTE_VALUE
, INTERVAL
FROM dba_JOBS
),
queryinterval AS (
SELECT job, /* Created by Andre Rocha */
nvl(
CASE FREQ_MONTH
WHEN 1 THEN
‘FREQ=MONTHLY;’ || ‘BYMONTHDAY=’|| day_value || ‘;’ ||
‘BYHOUR=’|| hour_value || ‘;’ || ‘BYMINUTE=’ || minute_value || ‘;’
ELSE
CASE freq_day
WHEN 7 THEN
‘FREQ=WEEKLY;’ || ‘BYDAY=’|| weekday_value || ‘;’
|| ‘BYHOUR=’|| hour_value || ‘;’ || ‘BYMINUTE=’ || minute_value || ‘;’
WHEN 1 THEN
‘FREQ=DAILY;’ || ‘BYHOUR=’|| hour_value || ‘;’ || ‘BYMINUTE=’ || minute_value || ‘;’
ELSE
CASE
WHEN FREQ_MINUTE=60 THEN
‘FREQ=HOURLY;’ || ‘INTERVAL=1;’
WHEN FREQ_MINUTE<60 THEN ‘FREQ=MINUTELY;’ || ‘INTERVAL=’ || FREQ_MINUTE || ‘;’ END END END,INTERVAL) intervalvalue FROM queryintervaloriginal ), queryjobs AS ( select /* Created by Andre Rocha */ ‘dbms_scheduler.create_job( ‘ || chr(10) || ‘ job_name=>’||””||'”‘ || schema_user ||'”.”‘|| substr(upper(translate(what,’.(),;’,’_’)),1,20) ||’_JOB”‘ ||””||’, ‘ || chr(10)
|| ‘ job_type=> ‘||””||’PLSQL_BLOCK’||””||’,’ || chr(10)
|| ‘ job_action=> ‘||””||'”begin ‘ || what || ‘ end;”‘||””||’, ‘ || chr(10)
|| ‘ start_date => to_timestamp(‘||””|| to_char(next_date,’mm/dd/yyyy hh24:mi:ss’)
||””||’,’||””|| ‘mm/dd/yyyy hh24:mi:ss’ ||””||’), ‘ || chr(10)
|| DECODE (interval,’null’,NULL, ‘ repeat_interval => ‘ ||””||
(SELECT a.intervalvalue FROM queryinterval a WHERE a.job = dba_jobs.job)
|| ””||’, ‘ || chr(10) )
|| ‘ enabled => true, auto_drop=> false, ‘ || chr(10)
|| ‘ comments => ‘||””||’Converted from job ‘ || job || ”” || chr(10)
|| ‘);’ || chr(10)
from dba_jobs
where broken = ‘N’
)
SELECT * FROM queryjobs ;

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.

 

 

My prefered .bash_profile

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Check I am stopping the recovery processes first:

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

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

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

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

SQL> alter database open read only;

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

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

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

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

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

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

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

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

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

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

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

 

Duplicate rman from local backup

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

Source database – bdo

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

Target database – TESTE

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

Add database to /etc/oratab

Add this line to the end of /etc/oratab

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

Listener.ora

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

vi $ORACLE_HOME/network/admin/listener.ora


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

Password file creation

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

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

Create new file: initTESTE.ora

# Minimum Requirement.
DB_NAME='bdo'

Create new file: duplicateTESTE.sql

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

Create new file: duplicateTESTE.sh

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

Execute the rman restore in background

 nohup ./duplicateTESTE.sh &

Check the results

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

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

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

Errors and solutions when using duplicate with auxiliary and backup location

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

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

ORA-19504: failed to create file

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

ORA-09925: Unable to create audit trail file

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

ORA-01261: Parameter db_create_online_log_dest_1 destination string cannot be translated

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

RMAN-06403: could not obtain a fully authorized session

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

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

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

Use sqlplus to help you!

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

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

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