Soumya's Database Blog : January 2023

ORA-01017: invalid username/password during convert database to physical standby

Convert database to physical standby command errored with ORA-01017: invalid username/password; logon denied

So during execution of convert to physical standby using dgbroker utility , an error of ORA-01017: invalid username/password; logon denied is observed.

[oracle@server1]$ dgmgrl /

DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 19 02:11:48 2023

Version 19.16.0.0.0

Welcome to DGMGRL, type "help" for information.

Connected to "PROD"

Connected as SYSDG.

DGMGRL> connect sys@PROD

Password:

Connected to "PROD"

Connected as SYSDBA.

DGMGRL> CONVERT DATABASE sbdb to PHYSICAL STANDBY;

Converting database "sbdb" to a Physical Standby database, please wait...

Operation requires shut down of instance "PROD" on database "sbdb"

Shutting down instance "PROD"...

ORA-01017: invalid username/password; logon denied

Solution:-

There could be a few possibilities.

1. Make sure the sys user password you are using is correct.

2. The password file in both primary and standby database is same and valid.

3. Sometimes error can be observed if case sensitive parameter is different between primary and standby

However after making sure all the above points , we were still failing to convert the snapshot standby database into physical standby.

To resolve this, we need to connect to dgmgrl utility as sys user and then perform the conversion.

[oracle@az-pspddb2 flashback]$ dgmgrl /

DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 19 02:11:48 2023

Version 19.16.0.0.0

Welcome to DGMGRL, type "help" for information.

Connected to "PROD"

Connected as SYSDG.

DGMGRL> connect sys@PROD

Password:<Provide sys password>

Connected to "PROD"

Connected as SYSDBA.

DGMGRL> CONVERT DATABASE sbdb to PHYSICAL STANDBY;

Converting database "sbdb" to a Physical Standby database, please wait...

Operation requires shut down of instance "PROD" on database "sbdb"

Shutting down instance "PROD"...

Connected to "SBDB"

Database closed.

Database dismounted.

ORACLE instance shut down.

Operation requires start up of instance "PROD" on database "sbdb"

Starting instance "PROD"...

Connected to an idle instance.

ORA-01034: ORACLE not available

Process ID: 0

Session ID: 0 Serial number: 0

ORACLE instance started.

Connected to "SBDB"

Database mounted.

How to fix ORA-16853: apply lag has exceeded specified threshold in 19c

We had a situation where in multi standby database environment , one of the standby database was lagging behind from the primary database. The log gap between primary and standby database was pretty huge. However, in primary we had the archives present so eventually after disabling/enabling LOG_ARCHIVE_DEST_3(As this was pointing to the 2^nd standby site) the RFS process started transporting the archives to the 2^nd standby database.

After executing following command the MRP process started applying logs in the standby site

SQL> alter database recover managed standby database disconnect from session;

However, when we checked in dataguard broker console, it was throwing a warning

ORA-16853: apply lag has exceeded specified threshold

DGMGRL> show configuration;

Configuration - jde_dg_config

Protection Mode: MaxPerformance

Members:

prod - Primary database

proddr - Physical standby database

sbdb - Physical standby database

Warning: ORA-16853: apply lag has exceeded specified threshold

Fast-Start Failover: Disabled

Configuration Status:

WARNING (status updated 52 seconds ago)

Solution:-

The reason of above warning is self explanatory, as one of our standby db (sbdb in this case) was out of sync for long time, the threshold value has been exceeded hence the warning.

[oracle@server1]$ dgmgrl /

DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jan 18 23:15:18 2023

Version 19.16.0.0.0

Welcome to DGMGRL, type "help" for information.

Connected to "PROD"

Connected as SYSDG.

DGMGRL> edit database sbdb set property ApplyLagThreshold=0;

Property "applylagthreshold" updated

DGMGRL> edit database sbdb set property TransportLagThreshold=0;

Property "transportlagthreshold" updated

DGMGRL> show database verbose sbdb

Database - sbdb

Role: SNAPSHOT STANDBY

Transport Lag: 0 seconds (computed 1 second ago)

Apply Lag: 60 minutes 16 seconds (computed 1 second ago)

Instance(s):

PROD

Properties:

DGConnectIdentifier = 'sbdb'

ObserverConnectIdentifier = ''

FastStartFailoverTarget = ''

PreferredObserverHosts = ''

LogShipping = 'ON'

RedoRoutes = ''

LogXptMode = 'ASYNC'

DelayMins = '0'

Binding = 'optional'

MaxFailure = '0'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyLagThreshold = '0'

TransportLagThreshold = '0'

TransportDisconnectedThreshold = '30'

ApplyParallel = 'AUTO'

ApplyInstances = '0'

StandbyFileManagement = ''

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '0'

LogArchiveMinSucceedDest = '0'

DataGuardSyncLatency = '0'

LogArchiveTrace = '0'

LogArchiveFormat = ''

DbFileNameConvert = ''

LogFileNameConvert = ''

ArchiveLocation = ''

AlternateLocation = ''

StandbyArchiveLocation = ''

StandbyAlternateLocation = ''

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

LogXptStatus = '(monitor)'

SendQEntries = '(monitor)'

RecvQEntries = '(monitor)'

HostName = 'az-psdvdb1'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=SBDB_DGMGRL)(INSTANCE_NAME=PROD)(SERVER=DEDICATED)))'

TopWaitEvents = '(monitor)'

SidName = '(monitor)'

Log file locations:

Alert log : /u01/oracle/diag/rdbms/sbdb/PROD/trace/alert_PROD.log

Data Guard Broker log : /u01/oracle/diag/rdbms/sbdb/PROD/trace/drcPROD.log

Database Status:

SUCCESS

DGMGRL>

So, we can resolve the warning by setting ApplyLagThreshold and TransportLagThreshold to Zero .

How to fix AHF-00074: Perl Modules not found : Data::Dumper

While installing Autonomous Health Framework (AHF) on Oracle Linux 7 in azure vm, we faced the below error

[root@az-psdvdb1 u01]# ./ahf_setup

AHF Installer for Platform Linux Architecture x86_64

AHF Installation Log : /tmp/ahf_install_223200_6496_2023_01_11-23_33_23.log

Starting Autonomous Health Framework (AHF) Installation

AHF Version: 22.3.2 Build Date: 202212190711

PERL : /bin/perl

[ERROR] : AHF-00074: Perl Modules not found : Data::Dumper

After going through the installation log mentioned above , it was found the system couldn’t find perl-Data-Dumper in the server.

To verify it,

[root@az-psdvdb1 u01]# rpm -q perl-Data-Dumper

package perl-Data-Dumper is not installed

Also, tried finding the rpm, but looks like it could only find the missing perl module in existing installed oracle home.

[root@az-psdvdb1 u01]# find / -name Dumper.pm

/u01/oracle/19.3.0/perl/lib/5.28.1/x86_64-linux-thread-multi/Data/Dumper.pm

So , let’s Install perl-Data-Dumper

[root@az-psdvdb1 u01]# yum install perl-Data-Dumper

Loaded plugins: langpacks, ulninfo

ksplice-uptrack | 2.9 kB 00:00:00

ol7_UEKR6 | 3.0 kB 00:00:00

ol7_addons | 3.0 kB 00:00:00

ol7_epel | 3.6 kB 00:00:00

ol7_latest | 3.6 kB 00:00:00

ol7_optional_latest | 3.0 kB 00:00:00

packages-microsoft-com-prod | 3.0 kB 00:00:00

(1/2): ol7_epel/x86_64/updateinfo | 830 kB 00:00:00

(2/2): ol7_epel/x86_64/primary_db | 16 MB 00:00:00

Resolving Dependencies

--> Running transaction check

---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================

Package Arch Version Repository Size

========================================================================================================================

Installing:

perl-Data-Dumper x86_64 2.145-3.el7 ol7_latest 47 k

Transaction Summary

========================================================================================================================

Install 1 Package

Total download size: 47 k

Installed size: 97 k

Is this ok [y/d/N]: y

Downloading packages:

perl-Data-Dumper-2.145-3.el7.x86_64.rpm | 47 kB 00:00:00

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Installing : perl-Data-Dumper-2.145-3.el7.x86_64 1/1

Verifying : perl-Data-Dumper-2.145-3.el7.x86_64 1/1

Installed:

perl-Data-Dumper.x86_64 0:2.145-3.el7

Complete!

[root@az-psdvdb1 u01]#

Now lets check the module again

[root@az-psdvdb1 u01]# find / -name Dumper.pm

/usr/lib64/perl5/vendor_perl/Data/Dumper.pm

/u01/oracle/19.3.0/perl/lib/5.28.1/x86_64-linux-thread-multi/Data/Dumper.pm

Now lets retry the installation

[oracle@az-psdvdb1 u01]$ ./ahf_setup

AHF Installer for Platform Linux Architecture x86_64

AHF Installation Log : /tmp/ahf_install_223200_29632_2023_01_12-00_28_15.log

Starting Autonomous Health Framework (AHF) Installation

AHF Version: 22.3.2 Build Date: 202212190711

Default AHF Location : /home/oracle/oracle.ahf

Do you want to install AHF at [/home/oracle/oracle.ahf] ? [Y]|N : n

….

…

So this resolved the issue.

Shell Script to run preclone on appstier in EBS R12

Shell Script to run preclone on appstier

vi /backup/appspreclone.sh

#!/bin/bash

. /RUP10F/apps/apps_st/appl/APPSUAT_ccuine18.env #This is apps env file

cd /RUP10F/inst/apps/UAT_ccuine18/admin/scripts

perl adpreclone.pl appsTier <<EOF #Provide apps password as per your env

appsuat

EOF

$ chmod 775 /backup/appspreclone.sh

Now schedule it in crontab

crontab -e

1 0 * * * /backup/appspreclone.sh

Shell Script to run preclone on dbtier in EBS R12

Shell Script to run preclone on dbtier

vi /backup/dbpreclone.sh

#!/bin/bash

. /RUP10F/db/tech_st/11.2.0/UAT_ccuine18.env #This is database env file.

cd /RUP10F/db/tech_st/11.2.0/appsutil/scripts/UAT_ccuine18

perl adpreclone.pl dbTier <<EOF

appsuat # Provide apps password as per your env

EOF

$ chmod 775 /backup/dbpreclone.sh

Now schedule it in crontab

crontab -e

0 0 * * * /backup/dbpreclone.sh