Thinking Out Loud

November 6, 2021

Flashback DataGuard Primary Database

Filed under: 11g,Dataguard,dgmgrl,flashback — mdinh @ 1:26 am

Practicing flashback just in case it is needed during production deployment.

The disadvantage is commands will be different for each environment vs simple and consistent

recover managed standby database cancel and

recover managed standby database using current logfile disconnect

--------------------------------------------------
--- CHECK FLASHBACK ENABLED:
--------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

### PRIMARY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;

CURRENT_SCN NAME                           OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
    1056941 HAWK                           READ WRITE           PRIMARY          YES

SQL>

-- Enable flashback if required:
alter database flashback on;
select current_scn,name,open_mode,database_role,flashback_on from v$database;

### STANDBY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;

CURRENT_SCN NAME                           OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
    1037296 HAWK                           MOUNTED              PHYSICAL STANDBY YES

SQL>

-- Enable flashback if required:
recover managed standby database cancel; 
alter database flashback on;
select current_scn,name,open_mode,database_role,flashback_on from v$database;

--------------------------------------------------
--- CREATE GRP:
--------------------------------------------------

### PRIMARY:
SQL> create restore point RP_TEST guarantee flashback database;

Restore point created.

SQL>

--------------------------------------------------
--- CHECK GRP:
--------------------------------------------------

Depending on database version, GRP is replicated to standby.

### PRIMARY:
SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

       SCN NAME                           GUA
---------- ------------------------------ ---
   1057112 RP_TEST                        YES

SQL>

### STANDBY:
SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

no rows selected

SQL>

##################################################
### FLASHBACK TO RESTORE POINT:
##################################################

### Flashback PRIMARY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;

CURRENT_SCN NAME                           OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
    1057767 HAWK                           READ WRITE           PRIMARY          YES

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size             469765280 bytes
Database Buffers         1124073472 bytes
Redo Buffers                7319552 bytes
Database mounted.

SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

       SCN NAME                           GUA
---------- ------------------------------ ---
   1057112 RP_TEST                        YES

SQL> flashback database to restore point RP_TEST;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL>

### Flashback STANDBY:
SQL> recover managed standby database cancel;
ORA-16136: Managed Standby Recovery not active

--- (USE PRIMARY v$restore_point.SCN)
SQL> flashback standby database to scn &scn;
Enter value for scn: 1057112
old   1: flashback standby database to scn &scn
new   1: flashback standby database to scn 1057112

Flashback complete.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL>

--------------------------------------------------
--- Check DG using dgmgrl:
--------------------------------------------------

DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Databases:
    hawk      - Primary database
    hawk_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawk

Database - hawk

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    hawk

Database Status:
SUCCESS

DGMGRL> show database hawk_stby

Database - hawk_stby

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 12 seconds ago)
  Apply Lag:       0 seconds (computed 12 seconds ago)
  Apply Rate:      0 Byte/s
  Real Time Query: OFF
  Instance(s):
    hawk

Database Status:
SUCCESS

DGMGRL>

Q.E.D.
Advertisement

November 3, 2021

AHFCTL setresourcelimit CPU

Filed under: AHF_TFA — mdinh @ 2:54 am

It was detemrined that AHF was using too much CPU affecting RAC cluster performance.

AHF was shutdown temporarily over the weekend to prevent issues with application.

--- SHORT version to limit CPU resource:

# /opt/oracle.ahf/bin/ahfctl setresourcelimit -value 0.5
Tool: tfa, Resource: cpu, Limit value: 0.5 set successfully

# /opt/oracle.ahf/bin/ahfctl getresourcelimit
Tool: tfa, Resource: cpu, Limit value: 0.5
Tool: tfa, Resource: kmem no resource limit set
Tool: tfa, Resource: swmem no resource limit set
--- LONG version - start AHF and limit CPU resource:

# /opt/oracle.ahf/bin/ahfctl startahf -all
Starting TFA..
Waiting up to 100 seconds for TFA to be started..
. . . . . 
. . . . . 
. . . . . 
. . . . . 
. . . . . 
. . . . . 
. . . . . 
. . . . . 
. . . . . 
Successfully started TFA Process..
. . . . . 
TFA Started and listening for commands

INFO: Starting exachk scheduler in background. 

Details for the process can be found at /u01/app/grid/oracle.ahf/data/xxxxxxxxxxxxxx-21acd-9gu5j2/diag/exachk/compliance_start_251021_112642.log

# /opt/oracle.ahf/bin/ahfctl setresourcelimit -value 0.5
Tool: tfa, Resource: cpu, Limit value: 0.5 set successfully

# /opt/oracle.ahf/bin/ahfctl getresourcelimit
Tool: tfa, Resource: cpu, Limit value: 0.5
Tool: tfa, Resource: kmem no resource limit set
Tool: tfa, Resource: swmem no resource limit set

# /opt/oracle.ahf/bin/ahfctl set autodiagcollect=ON
Successfully set autodiagcollect=ON
.-------------------------------------------------.
|           xxxxxxxxxxxxxx-21acd-9gu5j2           |
+-----------------------------------------+-------+
| Configuration Parameter                 | Value |
+-----------------------------------------+-------+
| Auto Diagcollection ( autodiagcollect ) | ON    |
'-----------------------------------------+-------'

# /opt/oracle.ahf/bin/ahfctl set chaautocollect=ON
Successfully set chaautocollect=ON
.---------------------------------.
|   xxxxxxxxxxxxxx-21acd-9gu5j2   |
+-------------------------+-------+
| Configuration Parameter | Value |
+-------------------------+-------+
| chaautocollect          | ON    |
'-------------------------+-------'

# /opt/oracle.ahf/bin/ahfctl set minicollection=ON
Successfully set minicollection=ON
.-----------------------------------------------------------.
|                xxxxxxxxxxxxxx-21acd-9gu5j2                |
+---------------------------------------------------+-------+
| Configuration Parameter                           | Value |
+---------------------------------------------------+-------+
| Generation of Mini Collections ( minicollection ) | ON    |
'---------------------------------------------------+-------'

# /opt/oracle.ahf/bin/ahfctl statusahf
.--------------------------------------------------------------------------------------------------------------------.
| Host                        | Status of TFA | PID    | Port | Version    | Build ID             | Inventory Status |
+-----------------------------+---------------+--------+------+------------+----------------------+------------------+
| xxxxxxxxxxxxxx-21acd-9gu5j2 | RUNNING       | 151372 | 5000 | 21.1.4.0.0 | 21140020210628122659 | COMPLETE         |
| xxxxxxxxxxxxxx-21acd-9gu5j1 | RUNNING       |  89572 | 5000 | 21.1.4.0.0 | 21140020210628122659 | COMPLETE         |
'-----------------------------+---------------+--------+------+------------+----------------------+------------------'

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

Master node = xxxxxxxxxxxxxx-21acd-9gu5j1

exachk daemon version = 21.1.4

Install location = /opt/oracle.ahf/exachk

Started at = Mon Oct 25 11:27:10 CDT 2021

Scheduler type = TFA Scheduler

------------------------------------------------------------
ID: exachk.autostart_client_exatier1
------------------------------------------------------------
AUTORUN_FLAGS  =  -usediscovery -profile exatier1 -syslog -dball -showpass -tag autostart_client_exatier1 -readenvconfig
COLLECTION_RETENTION  =  7
AUTORUN_SCHEDULE  =  3 2 * * 1,2,3,4,5,6
------------------------------------------------------------
------------------------------------------------------------
ID: exachk.autostart_client
------------------------------------------------------------
AUTORUN_FLAGS  =  -usediscovery -syslog -tag autostart_client -readenvconfig
COLLECTION_RETENTION  =  14
AUTORUN_SCHEDULE  =  3 3 * * 0
------------------------------------------------------------

Next auto run starts on Oct 26, 2021 02:03:00

ID:exachk.AUTOSTART_CLIENT_EXATIER1

# /opt/oracle.ahf/bin/tfactl get collect -match
.------------------------------------------------------------------------------.
|                          xxxxxxxxxxxxxx-21acd-9gu5j2                         |
+----------------------------------------------------------------------+-------+
| Configuration Parameter                                              | Value |
+----------------------------------------------------------------------+-------+
| ISA Data Gathering ( collection.isa )                                | ON    |
| collectTrm                                                           | OFF   |
| collectAllDirsByFile                                                 | ON    |
| Auto Diagcollection ( autodiagcollect )                              | ON    |
| Generation of Mini Collections ( minicollection )                    | ON    |
| chaautocollect                                                       | ON    |
| Maximum File Collection Size (MB) ( maxFileCollectionSize )          | 5120  |
| Maximum Collection Size of Core Files (MB) ( maxCoreCollectionSize ) | 500   |
| minTimeForAutoDiagCollection                                         | 300   |
'----------------------------------------------------------------------+-------'

Blog at WordPress.com.