ORACLE DATABASE PROBLEM AND SOLUTIONS

Tuesday, May 25, 2021

Oracle Dataguard Switchover using DGMGRL - Error : ORA-01017: invalid username/password; logon denied

DGMGRL Error : ORA-01017: invalid username/password; logon denied

DGMGRL> switchover to india_stby;

Performing switchover NOW, please wait...

Operation requires a connection to database "india_stby"

Connecting ...

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

connect to instance "india_stby" of database "india_stby"

we have to provide sys user credentials (username/password pair) to connect to database in DGMGRL also check sec_case_sensitive_logon database parameter .

In order to resolve ORA-01017 this error , we have to connect to dgmgrl utility using below method :

[oracle@ip-10-0-1-12 ~]$ dgmgrl

DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue May 25 14:02:07 2021

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys@india

Password:

Connected to "india"

Connected as SYSDBA.

DGMGRL>

DGMGRL> show configuration;

Configuration - india

Protection Mode: MaxPerformance

Members:

india - Primary database

india_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS (status updated 48 seconds ago)

DGMGRL>

DGMGRL> switchover to india_stby

Performing switchover NOW, please wait...

Operation requires a connection to database "india_stby"

Connecting ...

Connected to "india_stby"

Connected as SYSDBA.

New primary database "india_stby" is opening...

Operation requires start up of instance "india" on database "india"

Starting instance "india"...

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Database mounted.

Database opened.

Connected to "india"

Switchover succeeded, new primary is "india_stby"

DGMGRL>

ANURAG PANDEY

I am Anurag Pandey, working as Cloud Database Architect | Migration & Modernization Expert . Database Administration and Implementation. Provide technical guidance and work with global team. Extensive Experience in Database Installation and Configuration, Real Application Cluster (RAC), ASM, Dataguard , Patching, Upgrade, RMAN, Performance Tuning, Backup and Recovery, Azure, Google, AWS Cloud, AWS RDS, MySQL, Database Migration Service(DMS),CloudEndure - MGN, Azure Site Recovery, Azure Migrate, Datapump, terraform automation, Ansible

Oracle 12c- Perform Dataguard Switchover Using DGMGRL Utility

Oracle 12c- Perform Dataguard Switchover Using DGMGRL Utility

[oracle@ip-10-0-1-12 ~]$ dgmgrl

DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue May 25 14:02:07 2021

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys@india

Password:

Connected to "india"

Connected as SYSDBA.

DGMGRL

DGMGRL> edit database india set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.12)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=india)(INSTANCE_NAME=india)(SERVER=DEDICATED)))';

DGMGRL> edit database india_stby set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.37)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=india_stby)(INSTANCE_NAME=india_stby)(SERVER=DEDICATED)))';

[oracle@ip-10-0-1-12 ~]$ dgmgrl

DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue May 25 14:02:07 2021

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys@india

Password:

Connected to "india"

Connected as SYSDBA.

DGMGRL>

DGMGRL> show configuration;

Configuration - india

Protection Mode: MaxPerformance

Members:

india - Primary database

india_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS (status updated 48 seconds ago)

DGMGRL>

DGMGRL> switchover to india_stby

Performing switchover NOW, please wait...

Operation requires a connection to database "india_stby"

Connecting ...

Connected to "india_stby"

Connected as SYSDBA.

New primary database "india_stby" is opening...

Operation requires start up of instance "india" on database "india"

Starting instance "india"...

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Database mounted.

Database opened.

Connected to "india"

Switchover succeeded, new primary is "india_stby"

DGMGRL>

DGMGRL> show configuration;

Configuration - india

Protection Mode: MaxPerformance

Members:

india_stby - Primary database

india - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS (status updated 28 seconds ago)

DGMGRL>

[oracle@ip-10-0-1-37 ~]$ !sq

sqlplus sys/sys@india_stby as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 25 14:05:53 2021

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name ,open_mode from v$database;

NAME OPEN_MODE

--------- --------------------

INDIA READ WRITE

SQL>

SQL> insert into hr.abc values(2,'From standby after primary');

1 row created.

SQL> commit;

Commit complete.

SQL>

On new standby

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

[oracle@ip-10-0-1-12 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 25 14:06:28 2021

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name ,open_mode from v$database;

NAME OPEN_MODE

--------- --------------------

INDIA READ ONLY WITH APPLY

SQL>

SQL> select * from hr.abc;

ID NAME

---------- --------------------------------------------------

1 A

SQL> /

ID NAME

---------- --------------------------------------------------

2 From standby after primary

1 A

SQL>

alert log

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

Initiating a healthcheck...

05/25/2021 14:02:50

Forwarding CTL_SWITCH operation to member india for processing

Updated broker configuration file available, loading from "/u01/app/oracle/product/12.2.0/dbhome_1/dbs/dr2india_stby.dat"

05/25/2021 14:02:51

Switchover processing to this database has started

Notifying Oracle Clusterware to prepare target standby database for switchover

05/25/2021 14:03:01

Updated broker configuration file available, loading from "/u01/app/oracle/product/12.2.0/dbhome_1/dbs/dr1india_stby.dat"

New primary database after switchover

Informing client: OPENING instance 'india_stby' of database 'india_stby'

Informing client: STARTUP instance 'india' of database 'india'

Switchover completed, notifying client

Proceeding with buildup after switchover...

Notifying Oracle Clusterware to buildup the primary database after switchover

Opening database after Switchover

05/25/2021 14:03:04

Evaluating archivelog destinations for switchover

SWITCHOVER TO india_stby completed successfully, new primary database is india_stby

ANURAG PANDEY

Tuesday, May 18, 2021

Oracle Trace File Analyzer (TFA)

Oracle Trace File Analyzer (TFA)

Oracle Trace File Analyzer helps you perform real-time health monitoring, fault detection & diagnosis via a single interface. It will securely consolidate all distributed diagnostic data.

[oracle@ip-10.1.1.1.1 bin]$ ps -ef|grep tfa

root 687 1 0 05:50 ? 00:00:00 /bin/sh /etc/init.d/init.tfa run >/dev/null 2>&1 </dev/null

root 837 1 0 05:50 ? 00:00:16 /u01/app/oracle/tfa/ip-10.1.1.1.1/tfa_home/jre/bin/java -Xms128m -Xmx512m oracle.rat.tfa.TFAMain /u01/app/oracle/tfa/ip-10.1.1.1.1/tfa_home

oracle 22559 8133 0 07:05 pts/2 00:00:00 grep --color=auto tfa

[oracle@ip-10.1.1.1.1 bin]$ cd /u01/app/oracle/tfa

[root@ip-10.1.1.1.1 bin]#

[root@ip-10.1.1.1.1 bin]# ./tfactl status

.---------------------------------------------------------------------------------------------------.

+--------------+---------------+-----+-------+------------+----------------------+------------------+

| ip-10.1.1.1.1 | RUNNING | 837 | 31183 | 12.2.1.0.0 | 12210020161122170355 | COMPLETE |

'--------------+---------------+-----+-------+------------+----------------------+------------------'

[root@ip-10.1.1.1.1 bin]#

[root@ip-10.1.1.1.1 bin]# ./tfactl toolstatus

.----------------------------------------.

| External Support Tools |

+--------------+--------------+----------+

| Host | Tool | Status |

+--------------+--------------+----------+

| ip-10.1.1.1.1 | events | DEPLOYED |

| ip-10.1.1.1.1 | orachk | DEPLOYED |

| ip-10.1.1.1.1 | vi | DEPLOYED |

| ip-10.1.1.1.1 | dbglevel | DEPLOYED |

| ip-10.1.1.1.1 | exachk | DEPLOYED |

| ip-10.1.1.1.1 | managelogs | DEPLOYED |

| ip-10.1.1.1.1 | menu | DEPLOYED |

| ip-10.1.1.1.1 | changes | DEPLOYED |

| ip-10.1.1.1.1 | param | DEPLOYED |

| ip-10.1.1.1.1 | history | DEPLOYED |

| ip-10.1.1.1.1 | triage | DEPLOYED |

| ip-10.1.1.1.1 | calog | DEPLOYED |

| ip-10.1.1.1.1 | grep | DEPLOYED |

| ip-10.1.1.1.1 | summary | DEPLOYED |

| ip-10.1.1.1.1 | ls | DEPLOYED |

| ip-10.1.1.1.1 | srdc | DEPLOYED |

| ip-10.1.1.1.1 | pstack | DEPLOYED |

| ip-10.1.1.1.1 | tail | DEPLOYED |

| ip-10.1.1.1.1 | alertsummary | DEPLOYED |

| ip-10.1.1.1.1 | ps | DEPLOYED |

'--------------+--------------+----------'

[root@ip-10.1.1.1.1 bin]#

[root@ip-10.1.1.1.1 bin]# pwd

/u01/app/oracle/tfa/bin

[root@ip-10.1.1.1.1 bin]#

ANURAG PANDEY

Saturday, May 8, 2021

Setup Data Guard Broker Configuration in Oracle Database

How to setup Data Guard Broker Configuration

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

Primary database : india

Standby database : india_stby

Add static entry for the DGMGRL in the listener.ora on both the primary and standby servers

Standby listener.ora

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

oracle@ip-10-0-1-37 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = india_stby)

(SID_NAME = india_stby)

(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)

)

(SID_DESC =

(GLOBAL_DBNAME = india_dgmgrl)

(SID_NAME = india_stby)

(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)

)

(SID_DESC =

(GLOBAL_DBNAME = india_stby_dgmgrl)

(SID_NAME = india_stby)

(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)

)

[oracle@ip-10-0-1-37 admin]$

Primary listener.ora

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

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = india)

(SID_NAME = india)

(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)

)

(SID_DESC =

(GLOBAL_DBNAME = india_dgmgrl)

(SID_NAME = india_stby)

(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)

)

(SID_DESC =

(GLOBAL_DBNAME = india_stby_dgmgrl)

(SID_NAME = india_stby)

(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)

)

ALTER SYSTEM SET dg_broker_start=true scope=both;

SQL>

SQL> ALTER SYSTEM SET dg_broker_start=true scope=both;

System altered.

SQL>

SQL> show parameter broker

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)),

((TYPE=EMON)(BROKERS=1))

dg_broker_config_file1 string /u01/app/oracle/product/12.2.0

/dbhome_1/dbs/dr1india_stby.da

dg_broker_config_file2 string /u01/app/oracle/product/12.2.0

/dbhome_1/dbs/dr2india_stby.da

dg_broker_start boolean TRUE

use_dedicated_broker boolean FALSE

SQL>

on stanndby

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

SQL> /

Thread Last Sequence Received Last Sequence Applied Difference

---------- ---------------------- --------------------- ----------

1 29 29 0

SQL>

SQL> ALTER SYSTEM SET dg_broker_start=true scope=both;

System altered.

SQL>

SQL> show parameter broker

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)),

((TYPE=EMON)(BROKERS=1))

dg_broker_config_file1 string /u01/app/oracle/product/12.2.0

/dbhome_1/dbs/dr1india_stby.da

dg_broker_config_file2 string /u01/app/oracle/product/12.2.0

/dbhome_1/dbs/dr2india_stby.da

dg_broker_start boolean TRUE

use_dedicated_broker boolean FALSE

SQL>

DGMGRL>

DGMGRL> create configuration 'india' as primary database is 'india' connect identifier is india;

Configuration "india" created with primary database "india"

DGMGRL>

DGMGRL> show configuration;

Configuration - india

Protection Mode: MaxPerformance

Members:

india - Primary database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL>

DGMGRL> add database 'india_stby' as connect identifier is india_stby maintained as physical;

Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.

DGMGRL>

DGMGRL> add database 'india_stby' as connect identifier is india_stby maintained as physical;

Database "india_stby" added

DGMGRL>

DGMGRL> show configuration

Configuration - india

Protection Mode: MaxPerformance

Members:

india - Primary database

india_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL>

DGMGRL> enable configuration;

Enabled.

DGMGRL>

DGMGRL> show configuration

Configuration - india

Protection Mode: MaxPerformance

Members:

india - Primary database

india_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS (status updated 51 seconds ago)

DGMGRL>

DGMGRL> show database india InconsistentProperties

INCONSISTENT PROPERTIES

INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE

DGMGRL>

DGMGRL> show database india_stby InconsistentProperties

INCONSISTENT PROPERTIES

INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE

DGMGRL>

DGMGRL> show database india

Database - india

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

india

Database Status:

SUCCESS

DGMGRL> show database india_stby

Database - india_stby

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: 1.00 KByte/s

Real Time Query: ON

Instance(s):

india_stby

Database Status:

SUCCESS

DGMGRL>

DGMGRL> show database india_stby statusreport

STATUS REPORT

INSTANCE_NAME SEVERITY ERROR_TEXT

DGMGRL>

DGMGRL> show database india statusreport

STATUS REPORT

INSTANCE_NAME SEVERITY ERROR_TEXT

DGMGRL>

ANURAG PANDEY