Enable and disable data guard broker

https://logic.edchen.org/how-to-stop-data-guard-services/

1. Login to primary db server

$ dgmgrl sys/Manager_1@sslcdb

DGMGRL> show configuration

2. Check the apply and transport status:

Disable Data Guard with Broker

DGMGRL> show database sslcdb

Database - SSLCDBDR

Role:               PRIMARY

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

@stanby

$ dgmgrl sys/Manager_1@sslcdbdr

DGMGRL> show database sslcdbdr

Database - SSLCDBDR

  Role:               PHYSICAL STANDBY

3. DGMGRL Stop Transport

disable transport service of the primary database.

dgmgrl sys/Manager_1@sslcdb

DGMGRL> EDIT DATABASE sslcdbdr SET STATE='TRANSPORT-OFF';

Succeeded.

4. DGMGRL Stop Apply

dgmgrl sys/Manager_1@sslcdbdr

DGMGRL> edit database SSLCDBDR set state='APPLY-OFF';

Succeeded.

5. Disable DG Configuration

DGMGRL> DISABLE CONFIGURATION;

Disabled.

Enable Data Guard with Broker

6. Disable DG Configuration

DGMGRL> ENABLE CONFIGURATION;

Enableabled.

7. DGMGRL Start Transport

Enableable transport service of the primary database.

dgmgrl sys/Manager_1@sslcdb

DGMGRL> EDIT DATABASE sslcdbdr SET STATE='TRANSPORT-ON';

Succeeded.

8. DGMGRL Start Apply

dgmgrl sys/Manager_1@sslcdbdr

DGMGRL> edit database SSLCDBDR set state='APPLY-ON';

Succeeded.

SQL> show parameter dg_broker_start

NAME                                 TYPE        VALUE

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

dg_broker_start                      boolean     TRUE

SQL> select name, value from v$parameter where name = 'dg_broker_start';

NAME                       VALUE

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

dg_broker_start            TRUE

 SQL query to get DB info

spool dbinfo.html
set markup html on
select * from v$version;
select * from v$instance;
select * from v$parameter;
select * from v$resource_limit;
select * from dba_users;
select * from dba_registry;
SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90

SELECT
a.ksppinm "Parameter",
decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
c.ksppstvl "Instance",
decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
decode(p.isdefault,'FALSE','F','TRUE','T') "D",
a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
ORDER BY a.ksppinm;

set markup html off
spool off

 

ORA-10873: file 1 needs to be either taken out of backup mode or media recovered

Scenario :

Database went down due to power fluctuation and during that time hot backup was running.So once we started the database we face the following issue.

Error :

ORA-10873: file 1 needs to be either taken out of backup mode or media recovered

ORA-01110: data file 1: ‘D:\ORADB\RECTCDB\system01.dbf’

Cause :

Database is in backup mode while it went down.

Solution :

Step 1 : Sqlplus  ‘/as sysdba’

Step 2 : startup mount

Step 3 : Check backup file using below command.

select * from V$BACKUP

Step 4 : The following command can be used to take all of the data files out of hot backup mode:

SQL> ALTER DATABASE END BACKUP;

Step 5 :  SQL> Alter database open;

 

Enable and Disable the dataguard steps

Disable Data Guard

On Standby Database:
1. Cancel the Recovery job with following command

alter database recover managed standby database cancel;

2. Stop the destination

alter system set log_archive_dest_state_2=DEFER;


On Primary Database:
1. Stop the standby destination for transport archives.

ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;


Enable the dataguard:
On Primary DB:
1. Enable the archive log transport:

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

On Standby DB:
1. Start the recovery process with following command:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;

2. Enable the log archive for switchover condition needed.

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

 
Shutdown and Start the Primary / Standby database dataguard.

https://dbaclass.com/article/remove-dataguard-configuration-from-primary-database/

 

Remove Dataguard Configuration From Primary Database:

    If you wish to remove the dataguard/standby setup from primary database i.e to make the primary database as standalone database without any standby database, then follow below steps.

1.Put primary database on maximum performance mode:

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

2. Remove below parameters from spfile of primary database:

FAL_SERVER
FAL_CLIENT
LOG_ARCHIVE_CONFIG
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT
LOG_ARCHIVE_DEST_n – pointing to standby database like _2 or _3
LOG_ARCHIVE_DEST_STATE_n 
STANDBY_ARCHIVE_DEST
STANDBY_FILE_MANAGEMENT

alter system reset LOG_ARCHIVE_CONFIG scope=spfile;
alter system reset DB_FILE_NAME_CONVERT scope=spfile;
alter system reset LOG_FILE_NAME_CONVERT scope=spfile;
alter system reset STANDBY_FILE_MANAGEMENT scope=spfile;
alter system reset FAL_SERVER scope=spfile;
alter system reset FAL_CLIENT scope=spfile;
alter system reset LOG_ARCHIVE_DEST_STATE_2 scope=spfile;
alter system reset LOG_ARCHIVE_DEST_2 scope=spfile; --- destination pointing to standby
alter system reset STANDBY_ARCHIVE_DEST scope=spfile;

3. Drop all standby log files from primary:

SELECT GROUP# FROM V$STANDBY_LOG;

alter database drop standby logfile group 6;
alter database drop standby logfile group 7;
alter database drop standby logfile group 8;
alter database drop standby logfile group 9;

4. Restart the database to reflect the parameters

shutdown immediate;
startup

Now the standby database is completely separated from primary.
If you want to use the standby database as a standalone database, then follow below steps on standby

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
SQL> SHUTDOWN IMMEDIATE;
SQL>STARTUP

You can change dbid using NID utility( as dbid would be same as primary)

 

ORA-02396: exceeded maximum idle time, please connect again.

Resolution

To resolve the above error there are three approaches:

1.Set the idle time to unlimited on the Oracle database. 
alter profile <profile_name> limit idle_time UNLIMITED;

before

After 

 

DBMS_SCHEDULER. 

Default time zone change.

DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained (Doc ID 467722.1)

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';

Session altered.

SQL> select dbms_scheduler.stime from dual;

STIME

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

06/10/2021 07:09:12 CST6CDT CDT

SELECT *  FROM   dba_scheduler_global_attribute  WHERE  attribute_name = 'DEFAULT_TIMEZONE';  

SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','Asia/Colombo');