Redo Transport Compression in a Data Guard Environment (Doc ID 729551.1)     APPLIES TO: Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2] Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. ***Checked for relevance on 16-Jul-2014*** PURPOSE Oracle Database 11g has the ability to compress redo as it is transmitted over a network by Data Guard Redo Transport Services. In certain environments, redo compression can: ·       Improve data protection by reducing redo transport lag ·       Reduce network utilization ·       Provide faster redo gap resolution ·       Reduce redo transfer time Redo transport compression is a feature of the Oracle Advanced Compression option. You must purchase a license for this option before using the redo transport compression feature.  Compression of redo transport for versions prior to Oracle Database 11g Release 1 can be accomplished using third party WAN accelerators, such as those available from Cisco, Riverbed, F5, and other vendors. SCOPE Compression is most beneficial in achieving recovery point objectives (RPO) when used over low bandwidth networks. As a general statement, as network bandwidth increases, the RPO benefit is reduced. Compressing redo in a Data Guard environment is particularly beneficial if: The database redo rate is higher than the available network bandwidth. Sufficient CPU resources are available for the compression processing. CPU consumption will increase in higher network bandwidth environments since potentially a larger percentage of time is spent compressing redo compared to transmitting redo. For example, gap resolution testing showed that with OC1 (51.8 Mbits/sec) and T3 (44.7 Mbits/sec) networks, 50% of one CPU was consumed per ARCH process during the compression operation, while with a 100Mbits network an entire CPU was consumed per ARCH process. If reducing RPO in the case of disaster is a priority then compression is not recommended for network bandwidths above100 Mbits/sec, since the process of compression can actually increase overall redo transfer time. If reduced network utilization is a priority over RPO, evaluate compression even with high network bandwidths. Oracle recommends testing to determine the degree of benefit that will be realized. DETAILS From Oracle Database 11g Release 2 onward, setting the COMPRESSION attribute of the redo transport destination will compress redo transmitted by all Data Guard transport methods (SYNC, ASYNC, ARCH), including redo transmitted to resolve archive log gaps, and all Data Guard protection modes (Maximum Protection, Maximum Availability, and Maximum Performance). In Oracle Database 11g Release 1, setting the COMPRESSION attribute of the redo transport destination will only compress redo transmitted to resolve an archive log gap. Oracle also supports compression of ASYNC and ARCH redo transport (Maximum Performance) by using a hidden parameter in conjunction with enabling the COMPRESSION attribute as documented below. By default REDO_TRANSPORT_COMPRESS_ALL is FALSE in 11.1  Enabling Data Guard Redo Transport Compression To enable compression, set the COMPRESSION attribute of the redo transport destination to ENABLE. For example: LOG_ARCHIVE_DEST_2='SERVICE=<SERVICE_NAME> COMPRESSION=ENABLE DB_UNIQUE_NAME=<DB_UNIQUE_NAME>' If using Data Guard Broker, specify the following syntax: DGMGRL> EDIT DATABASE '<DB_NAME>' SET PROPERTY 'RedoCompression' = ENABLE;  Test Results and Other Guidance Oracle MAA testing showed that with a redo compression ratio of 35% or more, redo transmission time was reduced by 15-35%.                                                Network Throughput Network Bandwidth Compression Disabled Compression Enabled 11 Mbit 1.3 MB/sec 2.1 MB/sec 22 Mbit 2.8 MB/sec 4.2 MB/sec 45 Mbit 5.7 MB/sec 8.5 MB/sec 90 Mbit 11.3 MB/sec 13.4 MB/sec   Oracle MAA testing showed the following benefits when testing with a low bandwidth network (less than 100Mbits/sec) and high redo generation rate (> 20 MB/sec) where the redo compression ratio was 60%: Redo transport lag consistently remained less than 10 seconds even though the database redo rate far exceeded the network throughput. In previous test runs where compression was not enabled, redo transmission could not keep pace with redo generation, and redo transport lag continued to increase linearly over time for the duration of the test making it impossible to achieve recovery point objectives. Minimum impact on production workload when sufficient CPU and IO resources are available. In addition, Oracle MAA testing showed: Gap resolution time was reduced by 35-60% Network latency did not impact network throughput Using the MAX_CONNECTIONS attribute to transmit redo in multiple network sessions showed no benefit.   Enabling Data Guard Redo Transport Compression for ASYNC when using Oracle Database 11g Release 1 To enable compression when using ASYNC or ARCH redo transport mode and Oracle Database 11g Release 1, set the COMPRESSION attribute of the redo transport destination to ENABLE, and set the initialization parameter _REDO_TRANSPORT_COMPRESS_ALL to TRUE (this initialization parameter is not needed from Oracle Database 11g Release 2 onward). For example: LOG_ARCHIVE_DEST_2='SERVICE=<SERVICE_NAME> ASYNC COMPRESSION=ENABLE DB_UNIQUE_NAME=<DB_UNIQUE_NAME>' _REDO_TRANSPORT_COMPRESS_ALL=TRUE If using Data Guard Broker, then you must set the _REDO_TRANSPORT_COMPRESS_ALL=TRUE parameter in the database parameter file as stated above.  To enable the COMPRESSION attribute on the LOG_ARCHIVE_DEST parameter you must edit the database property within DGMGRL as specified below: DGMGRL> EDIT DATABASE '<DB_NAME>' SET PROPERTY 'RedoCompression' = ENABLE;  How to estimate the compression ratio that can be achieved: Data Guard redo transport compression uses the same zlib compression engine at level 1 as gzip -1. To estimate the compression ratio you can achieve using Data Guard redo transport compression, first compress an archived redo log file with gzip:       $ gzip -1 <archive redo logfile>.arc Then use the gzip --list option to explicitly display the compression ratio: $ gzip --list <archive redo logfile.arc>.gz Note: Use gzip version 1.3.3 or later. Refer to www.gzip.org for the latest information on gzip.

 

 

How to Enable Archive Log Mode in Oracle Database (ASM)

Check if the Archivelog mode is enabled

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            ?#/dbs/arch

Oldest online log sequence     4146

Current log sequence           4148

SQL> select log_mode from v$database;

LOG_MODE

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

NOARCHIVELOG

Enable Archive log mode using Flash Recovery Area

How to Enable Flash Recovery Area

To enable Flash Recovery Area you need to set db_recovery_file_dest_size and db_recovery_file_dest parameter.

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string

db_recovery_file_dest_size           big integer 0

SQL> alter system set db_recovery_file_dest_size=50G scope=both;

System altered.

SQL> alter system set db_recovery_file_dest='+DG_TST_FRA' scope=both;

System altered.

SQL> show parameter db_recovery_file

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      +DG_TST_FRA

db_recovery_file_dest_size           big integer 50G

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     4146

Current log sequence           4148

NOTE:As soon as you enable the FRA the archiving location is implicitly set to FRA even if the Archivelog mode is not enabled

Put the database in mount mode and enable the achivelog mode

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size                  8794848 bytes

Variable Size            1912605984 bytes

Database Buffers          218103808 bytes

Redo Buffers                7979008 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     4146

Next log sequence to archive   4148

Current log sequence           4148

If you don't want to use FRA you can instruct oracle to write the logs on some other location by setting log_archive_dest_ parameter.

DBCA fails with errors: [FATAL] [DBT-50000] Unable to check for available memory in "Specify configuration option" (Doc ID 2631718.1)

APPLIES TO: Oracle Database Configuration Assistant - Version 19.3.0.0.0 and later Information in this document applies to any platform. SYMPTOMS When create a 19c database, during the memory allocation portion in "Specify configuration option" it throws an DBT-50000 error. It looks like Doc ID 2543754.1, but in this environment did not set the ORA_CRS_HOME environment variable. CHANGES   CAUSE This issue is caused by Bug 29686671. SOLUTION Run dbca with "-J-Doracle.assistants.dbca.validate.ConfigurationParams=false" like a bellow. dbca -J-Doracle.assistants.dbca.validate.ConfigurationParams=false REFERENCES NOTE:1929376.1 - My Oracle Support - Automated Troubleshooting NOTE:29686671.8 - Bug 29686671 - WINDOWS: 19.3 DBCA Fails to Create a RAC Database With Error DBT-50000 NOTE:2543754.1 - DBCA fails with errors: [FATAL] [DBT-50000] Unable to check for available memory. NOTE:1927081.1 - SRDC - DBCA Issues: Checklist of Evidence to Supply NOTE:166650.1 - Working Effectively With Oracle Support - Best Practices

 

Drop And Recreate Undo Tablespace

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

https://docs.oracle.com/database/121/SQLRF/statements_7003.htm#SQLRF01403

1. Check the existing UNDO details:

SQL> show parameter undo

 

NAME                                 TYPE        VALUE

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

temp_undo_enabled                    boolean     FALSE

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS3

2. Create a new undo tablespace:

SQL>  CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u02/oradata/ARCH/undotbs101.dbf' SIZE 500M AUTOEXTEND ON RETENTION GUARANTEE;

3. Update undo_tablespace parameter

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1 SCOPE=BOTH;

 

System altered.

 

SQL>  show parameter undo

 

NAME                                 TYPE        VALUE

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

temp_undo_enabled                    boolean     FALSE

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

4.Drop the old undo tablespace

SQL>  DROP TABLESPACE UNDOTBS3 INCLUDING CONTENTS AND DATAFILES;

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

1. Check for the active sessions for rollback segment in old tablespace if drop unsuccessfull.

set pagesize 200

set lines 200

set long 999

col username for a9

SELECT a.name,b.status , d.username , d.sid , d.serial#

FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d

WHERE  a.usn = b.usn

AND    a.usn = c.xidusn

AND    c.ses_addr = d.saddr

AND    a.name IN (

  SELECT segment_name

  FROM dba_segments

 WHERE tablespace_name = 'UNDOTBS3'

);

NAME                           STATUS          USERNAME         SID    SERIAL#

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

_SYSSMU10_46915872145$          PENDING OFFLINE DBACLASS         193      732119

5. Kill the active session(s) which is using old tablespace

SQL> alter system kill session '193,732119' immediate;

System altered.

ORA-01665: control file is not a standby control file


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE nodelay DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE nodelay DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01665: control file is not a standby control file
SQL>
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> SHU IMMEDIATE;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 629149632 bytes
Database Buffers 197132288 bytes
Redo Buffers 3952640 bytes
Database mounted.
SQL> SELECT database_role FROM v$database;

DATABASE_ROLE
--------------------
PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

SQL> SELECT database_role FROM v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL>

How To Change Flash Recovery Area Location

Below are the steps for changing the flash recovery area(FRA destination).

1. Check the current FRA destination.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> show parameter db_recovery_file   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest                string      /dbaclassdb/oradata/FRA db_recovery_file_dest_size           big integer 20G     SQL> select name from V$RECOVERY_FILE_DEST;   NAME                                   -------------------------------------- /dbaclassdb/oradata/FRA

2. Change db_recovery_file_dest parameter.

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

SQL> alter system set db_recovery_file_dest='/fradg/oradata/FRA' scope=both;   System altered.     select name from V$RECOVERY_FILE_DEST; NAME                                   SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID -------------------------------------- ----------- ---------- ----------------- --------------- ---------- /fradg/oradata/FRA               2.1475E+10          0                 0               0          0

From onwards new files will be created in this new location. However, the flashback logs will be c

For the FLASHBACK logfiles to be able to pick up the new ‘db_recovery_file_dest’ location, we just need to disable and enable the flashback, which needs a downtime.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

1.  Drop restore point if any   SQL> select GUARANTEE_FLASHBACK_DATABASE,NAME from v$restore_point; GUARANTEE_FLASHBACK_DATABASE     NAME -------------------------------- ----------------------------- YES                              RESTORE_UPGRDE     SQL > Drop restore point  restore_point RESTORE_UPGRDE ;   2. Turn off and turn on flashback :   ALTER DATABASE FLASHBACK OFF; ALTER DATABASE FLASHBACK ON;