ORACLE DATABASE PROBLEM AND SOLUTIONS

Tuesday, January 17, 2023

Switchover with Data Guard Broker : DGMGRL

Switchover with Data Guard Broker

DGMGRL>

DGMGRL> show configuration;

Configuration - prod_ddb

Protection Mode: MaxPerformance

Members:

ddb - Primary database

ddb_dr - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS (status updated 11 seconds ago)

DGMGRL>

DGMGRL> switchover to ddb_dr

Performing switchover NOW, please wait...

Operation requires a connection to database "ddb_dr"

Connecting ...

Connected to "ddb_dr"

Connected as SYSDBA.

New primary database "ddb_dr" is opening...

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

Starting instance "ddb"...

Connected to an idle instance.

ORACLE instance started.

Connected to "ddb"

Database mounted.

Connected to "ddb"

Switchover succeeded, new primary is "ddb_dr"

DGMGRL>

DGMGRL> show configuration

Configuration - prod_ddb

Protection Mode: MaxPerformance

Members:

ddb_dr - Primary database

ddb - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS (status updated 78 seconds ago)

DGMGRL>

DGMGRL> switchover to ddb

Performing switchover NOW, please wait...

Operation requires a connection to database "ddb"

Connecting ...

Connected to "ddb"

Connected as SYSDBA.

New primary database "ddb" is opening...

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

Starting instance "ddb_dr"...

Connected to an idle instance.

ORACLE instance started.

Connected to "ddb_dr"

Database mounted.

Connected to "ddb_dr"

Switchover succeeded, new primary is "ddb"

DGMGRL>

DGMGRL> show configuration

Configuration - prod_ddb

Protection Mode: MaxPerformance

Members:

ddb - Primary database

ddb_dr - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS (status updated 77 seconds ago)

DGMGRL>

Sunday, January 15, 2023

How to resolve ORA error like ORA-16058 during oracle datagaurd setup .

Execute below steps on primary database

SQL> alter system set log_archive_dest_state_2='DEFER';

SQL> alter system set log_archive_dest_state_2='ENABLE';

SQL> select dest_id, status, destination, error from v$archive_dest where dest_id<=2;

DEST_ID STATUS DESTINATION ERROR

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

1 VALID USE_DB_RECOVERY_FILE_DEST

2 VALID ddb_dr

SQL>

Client Connectivity in Data Guard Configuration | HA service

Client Connectivity in Data Guard Configuration

create database service on primary database server

exec DBMS_SERVICE.CREATE_SERVICE (service_name => 'ddb_ha', network_name => 'ddb_ha');

create or replace procedure start_db_ha_service

v_role VARCHAR(30);

begin

select DATABASE_ROLE into v_role from V$DATABASE;

if v_role = 'PRIMARY' then

DBMS_SERVICE.START_SERVICE('ddb_ha');

else

DBMS_SERVICE.STOP_SERVICE('ddb_ha');

end if;

end;

TRIGGER TO START SERVICE ON DB STARTUP:

create or replace TRIGGER db_hasrv_on_startup

after startup on database

begin

start_db_ha_service;

end;

TRIGGER TO START SERVICE ON DB ROLECHANGE:

create or replace TRIGGER db_hasrv_on_role_change

after db_role_change on database

begin

start_db_ha_service;

end;

Start the new service on primary

SQL> exec start_db_ha_service;

SQL> alter system archive log current;

SQL>

SQL> exec DBMS_SERVICE.CREATE_SERVICE (service_name => 'ddb_ha', network_name => 'ddb_ha');

PL/SQL procedure successfully completed.

SQL> create or replace procedure start_db_ha_service

v_role VARCHAR(30);

begin

select DATABASE_ROLE into v_role from V$DATABASE;

if v_role = 'PRIMARY' then

DBMS_SERVICE.START_SERVICE('ddb_ha');

else

DBMS_SERVICE.STOP_SERVICE('ddb_ha');

end if;

end;

/ 2 3 4 5 6 7 8 9 10 11 12

Procedure created.

SQL> create or replace TRIGGER db_hasrv_on_startup

after startup on database

begin

start_db_ha_service;

end;

2 3 4 5 6

Trigger created.

SQL>

SQL> create or replace TRIGGER db_hasrv_on_role_change

after db_role_change on database

begin

start_db_ha_service;

end;

/ 2 3 4 5 6

Trigger created.

SQL>

SQL> exec start_db_ha_service;

PL/SQL procedure successfully completed.

SQL>

SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>

tnsnames.ora (client site)

DDB =

(DESCRIPTION =

(ADDRESS_LIST=

(ADDRESS = (PROTOCOL = TCP)(HOST = 9.82.83.159)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 72.471.436.842)(PORT = 1521))

)

(CONNECT_DATA = (SERVICE_NAME = ddb_ha)

(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=60)(DELAY=20))

)

Saturday, January 14, 2023

Use Orapwd utility to create Password File In Oracle

orapwd file=orapwddb password=Rdx$1234567890 force=y

to avoid password verification:

orapwd file=orapwddb password=Rdx1234567890 force=y format=12

[oracle@dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 14 13:07:04 2023

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> alter user sys identified by Rdx1234567890;

User altered.

How to Disable firewall on linux server : firewalld

[root@oracle ~]#

[root@oracle ~]# systemctl status firewalld

● firewalld.service - firewalld - dynamic firewall daemon

Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)

Active: active (running) since Sat 2023-01-14 10:02:49 UTC; 50min ago

Docs: man:firewalld(1)

Main PID: 933 (firewalld)

CGroup: /system.slice/firewalld.service

└─933 /usr/bin/python2 -Es /usr/sbin/firewalld --nofork --nopid

Jan 14 10:02:48 oracle systemd[1]: Starting firewalld - dynamic firewall daemon...

Jan 14 10:02:49 oracle systemd[1]: Started firewalld - dynamic firewall daemon.

[root@oracle ~]#

[root@oracle ~]# service firewalld stop

Redirecting to /bin/systemctl stop firewalld.service

[root@oracle ~]#

[root@oracle ~]# systemctl status firewalld

● firewalld.service - firewalld - dynamic firewall daemon

Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)

Active: inactive (dead) since Sat 2023-01-14 10:55:01 UTC; 9s ago

Docs: man:firewalld(1)

Process: 933 ExecStart=/usr/sbin/firewalld --nofork --nopid $FIREWALLD_ARGS (code=exited, status=0/SUCCESS)

Main PID: 933 (code=exited, status=0/SUCCESS)

Jan 14 10:02:48 oracle systemd[1]: Starting firewalld - dynamic firewall daemon...

Jan 14 10:02:49 oracle systemd[1]: Started firewalld - dynamic firewall daemon.

Jan 14 10:55:01 oracle systemd[1]: Stopping firewalld - dynamic firewall daemon...

Jan 14 10:55:01 oracle systemd[1]: Stopped firewalld - dynamic firewall daemon.

[root@oracle ~]#

[root@oracle ~]# systemctl disable firewalld

Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.

Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@oracle ~]#

[root@oracle ~]# systemctl status firewalld

● firewalld.service - firewalld - dynamic firewall daemon

Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)

Active: inactive (dead)

Docs: man:firewalld(1)

Jan 14 10:02:48 oracle systemd[1]: Starting firewalld - dynamic firewall daemon...

Jan 14 10:02:49 oracle systemd[1]: Started firewalld - dynamic firewall daemon.

Jan 14 10:55:01 oracle systemd[1]: Stopping firewalld - dynamic firewall daemon...

Jan 14 10:55:01 oracle systemd[1]: Stopped firewalld - dynamic firewall daemon.

[root@oracle ~]#

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

Cause : database was crashed, could be database shut abort or database was not clean shutdown.

check alert log as well to get more details .

workaround :

[oracle@10.1.1.0 ~]$

[oracle@10.1.1.0 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 14 10:04:10 2023

Version 19.3.0.0.0

Connected to an idle instance.

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area 1610609200 bytes

Fixed Size 8897072 bytes

Variable Size 385875968 bytes

Database Buffers 1207959552 bytes

Redo Buffers 7876608 bytes

Database mounted.

SQL>

SQL> select log_mode from v$database;

LOG_MODE

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

NOARCHIVELOG

SQL>

SQL> alter database archivelog;

alter database archivelog

ERROR at line 1:

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

SQL> alter database open;

Database altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@10.1.1.0 ~]$

[oracle@10.1.1.0 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 14 10:23:27 2023

Version 19.3.0.0.0

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1610609200 bytes

Fixed Size 8897072 bytes

Variable Size 385875968 bytes

Database Buffers 1207959552 bytes

Redo Buffers 7876608 bytes

Database mounted.

SQL>

SQL> select log_mode from v$database;

LOG_MODE

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

NOARCHIVELOG

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> select log_mode from v$database;

LOG_MODE

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

ARCHIVELOG

SQL>

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 4

Next log sequence to archive 6

Current log sequence 6

SQL>

Friday, January 13, 2023

Listener.ora & Tnsnames.ora file for Oracle Database

listener.ora file (with static listener)

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

cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/19.0/dbhome1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.19.48)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ddb)

(ORACLE_HOME = /u01/app/oracle/product/19.0/dbhome1)

(SID_NAME = ddb)

)

tnsnames.ora file

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

cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0/dbhome1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DDB=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.19.48 )(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ddb)

)