student guide

Dataguard:

GLOBAL_DBNAME = <dbuniquename>_DGMGRL.example.com

 Example: listener.ora on Primary Hosts

Static listener entries are needed for Data Guard broker

operations:

[enode01]$ cat $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = eastdb_DGMGRL.example.com)

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

(SID_NAME = eastdb1)))

[enode02]$ cat $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = eastdb_DGMGRL.example.com)

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

(SID_NAME = eastdb2)))

Dynamic service registration allows the LREG process of a database instance to identify its

available services to the listener without entries in the listener.ora configuration file. The

listener then acts as a port mapper for those services.

However, when the database instance is stopped, the listener discards all information for the

dynamic services related to that database. Any attempt to establish a network session to the

unknown service will usually receive the error message “ORA-12514: Listener does not

currently know of service requested in connect descriptor.” Static registration allows the

listener to know of a service, even if the database instance is not running. This is often

important with tools and utilities that try to remotely start and stop a database instance.

To enable DGMGRL to restart instances during the course of broker operations, a static

service must be registered with the local listener and assume a static service name of

db_unique_name_DGMGRL.db_domain.

Note: Static "_DGMGRL" entries are no longer needed as of Oracle Database 12.1.0.2 in

Oracle Data Guard broker configurations that are managed by Oracle Restart, RAC On Node

or RAC as the broker will use the clusterware to restart an instance.

Example: listener.ora on Standby Hosts

Static listener entries are needed for Data Guard broker

operations:

[wnode03]$ cat $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = westdb_DGMGRL.example.com)

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

(SID_NAME = westdb1)))

[wnode04]$ cat $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = westdb_DGMGRL.example.com)

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

(SID_NAME = westdb2)))

To enable DGMGRL to restart instances during the course of broker operations, a static

service must be registered with the local listener and assume a static service name of

db_unique_name_DGMGRL.db_domain.

Note: Static "_DGMGRL" entries are no longer needed as of Oracle Database 12.1.0.2 in

Oracle Data Guard broker configurations that are managed by Oracle Restart, RAC On Node

or RAC as the broker will use the clusterware to restart an instance.

 

Dynamic service registration

Dynamic service registration allows a database instance to

identify its available services to the listener. Available service

names are determined by the following parameters:

• DB_UNIQUE_NAME, DB_NAME, and DB_DOMAIN

• SERVICE_NAMES

• INSTANCE_NAME

• LOCAL_LISTENER and REMOTE_LISTENER

Dynamic service registration is configured in the database initialization file. It does not require any configuration in the listener.ora file. If not specified, the value for the SERVICE_NAMES parameter defaults to the global database name, a name comprised of the DB_UNIQUE_NAME and DB_DOMAIN parameters in the initialization parameter file. If not explicitly defined, the DB_UNIQUE_NAME parameter defaults to the value DB_NAME. The value for the INSTANCE_NAME parameter defaults to the Oracle system identifier (SID). All of these

names can be explicitly defined to non-default values.

 

The SERVICE_NAMES parameter specifies one or more names by which clients can connect to the instance. The instance registers its service names with the listener. You can specify multiple service names to distinguish among different uses of the same database. For example:

SERVICE_NAMES=PROD,DG_PRMY,DG_RW,MAIN_REPORTING

By default, the listener registration (LREG) process registers service information with its locallistener on the default local address of TCP/IP port 1521. To have the LREG process register with a local listener that does not use TCP/IP port 1521, configure the LOCAL_LISTENER parameter in the initialization parameter file to locate the local listener

 

A remote listener is a listener residing on one computer that redirects connections to a database instance on another computer. You can configure registration to remote listeners using the REMOTE_LISTENER parameter.

 

Dynamic service registration allows the LREG process of a database instance to identify its

available services to the listener without entries in the listener.ora configuration file. The listener then acts as a port mapper for those services.

 

However, when the database instance is stopped, the listener discards all information for the dynamic services related to that database. Any attempt to establish a network session to the unknown service will usually receive the error message “ORA-12514: Listener does not currently know of service requested in connect descriptor.” Static registration allows the listener to know of a service, even if the database instance is not running. This is often important with tools and utilities that try to remotely start and stop a database instance.

 

To enable DGMGRL to restart instances during the course of broker operations, a static service must be registered with the local listener and assume a static service name of db_unique_name_DGMGRL.db_domain.

Note: Static "_DGMGRL" entries are no longer needed as of Oracle Database 12.1.0.2 in Oracle Data Guard broker configurations that are managed by Oracle Restart, RAC On Node or RAC as the broker will use the clusterware to restart an instance.

 

Example: listener.ora on Standby Hosts

Static listener entries are needed for Recovery Manager

operations:

[wnode03]$ cat $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER_CLONE =

(SID_LIST =

(SID_DESC =

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

(SID_NAME = MYDB1)))

LISTENER_CLONE =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)

(HOST = MYHOST) (PORT = 1531)(IP = FIRST))))

You can configure a new listener (if necessary) or optionally update the default listener with a static service entry for your physical standby database. This entry is needed because the instance is shut down and restarted during standby database creation using RMAN.

 

 Source : Student Guide

 

optimizer_adaptive_features

optimizer_adaptive_features

During the compilation of a SQL statement, the optimizer decides whether to use dynamic statistics by considering whether the available statistics are sufficient to generate an optimal execution plan. If the available statistics are insufficient, then the optimizer uses dynamic statistics to augment the statistics. With adaptive dynamic statistics (ADS), Oracle uses dynamic statistics automatically when the optimizer deems it necessary. The resulting statistics are persistent in the statistics repository, making them available to other queries.

Adaptive query optimization controls only adaptive dynamic statistics and not the default dynamic statistics.

When optimizer_adaptive_features (or optimizer_adaptive_statistics) = TRUE and optimizer_dynamic_sampling = 2 (default), then ADS can happen for parallel queries.

When optimizer_adaptive_features (or optimizer_adaptive_statistics) = FALSE and optimizer_dynamic_sampling is not 11, then ADS will not happen. However, the default dynamic sampling level is still honored.

If optimizer_dynamic_sampling is set to 11, then ADS is enabled for all queries irrespective of setting of the parameter optimizer_adaptive_features or optimizer_adaptive_statistics.

ADS is always used when SQL Plan Directives request dynamic sampling.

1Z0-066 - Must Read

1.  DGM-1695

1) In general, when Observer is started, it will create a file called "fsfo.dat" file. So can't start observer one more time without stopping the previously ran observer.

2) Determine where to store the observer state file (fsfo.dat file):

The observer maintains state information in a file. By default the file is named fsfo.dat and is created in the working directory where the observer is started.
The state file is locked when the observer is running to prevent multiple observers from using the same file. It's generally a good idea to store the state file in a directory associated with the database to avoid locking issues when running multiple observers on the same host.

Example:
mkdir -p /u01/app/oracle/admin/db1/observer

3) Observer startup command:
Here's a one-liner observer startup for *nix. Note the use of "/@<tns_alias>" to login using the wallet.

$ nohup dgmgrl /@db1 "start observer file='/u01/app/oracle/admin/db1/observer/fsfo.dat'" \

Snapshot Standby:

A snapshot standby database can be converted back into a physical standby database at any time

A Physical Standby Database can be converted into a Logical Standby Database.

If datafiles grow while a database is a snapshot standby database, then they shrink when

converted back to a physical standby database.

Flashback logs are used to convert a snapshot standby database back into a physical standby

database.

A guaranteed restore point is created automatically when a physical standby database is

converted into a snapshot standby database.

Data Guard monitoring activities may be performed using Enterprise Manager Cloud Control

On the Data Guard Performance page, you can monitor the Redo Generation Rate in the Primary

database, and the Apply Rate and Lag Times in the Standby Database.

Look at the Standby Database Progress Summary Section. This displays the Transport lag and the

Apply lag in a bar chart format. The Transport lag is the time difference between the last update on the

primary database, and the last received redo on the standby. Whereas, the Apply lag is the same

difference but pertaining to the last applied redo on the standby.

Flashback Database is similar to conventional point-in-time recovery in its effects. It enables you to

return a database to its state at a time in the recent past. Flashback Database uses its own logging

mechanism, creating flashback logs and storing them in the fast recovery area. You can only use

Flashback Database if flashback logs are available. Flashback Database is used to convert a snapshot

standby database back into a physical standby database.

When we convert the physical standby database to snapshot standby database a guaranteed restore

point is created. This guaranteed restore point is used to flashback a snapshot standby database to its

original state.

Maximum Availability:

The MAXAVAILABILITY protection mode requires that there be at least one standby database

configured to use standby redo log files, with its LogXptMode configurable database property set to

SYNC.

Maximum Protection:

11gR2 eliminates this restriction. You can upgrade to maxprotection from maxavailability without primary database restart. Only thing is that you need to first upgrade from maxperformance to maxavailability and then to maxprotection.

If you want to directly upgrade to maxprotection from maxperformance, then it requires primary database restart.

Is it possible to change the protection mode to maximum protection using Enterprise Manager Cloud Control?

A logical standby database is the only standby database in the data guard configuration.

Changing from MaxAvailability to MaxProtection

Upgrading to MaxProtection requires primary database restart regardless of current protection mode (being it MaxPerformance or MaxAvailability).

Note:- 11gR2 eliminates this restriction. You can upgrade to maxprotection on the fly as long as you are in max protection.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Succeeded.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPROTECTION;

Succeeded.

Please note that you should have connected as a valid sysdba user with password (dgmgrl sys/<pwd>)

If at all you have connected as (dgmgrl /), then this attempt will fail "ORA-01031: insufficient privileges"

The Maximum protection mode ensures that no data loss will occur if the primary database fails. To

provide this level of protection, the redo data needed to recover a transaction must be written to both

the online redo log and to the standby redo log on at least one synchronized standby database before

the transaction commits. To ensure that data loss cannot occur, the primary database will shut down,

rather than continue processing transactions, if it cannot write its redo stream to at least one

synchronized standby database.

Dataguard With Multi-tenant Database

If you execute a switchover or failover operation, the entire CDB undergoes the role change.

To administer a multitenant environment, you must have the CDB_DBA role. 

RMAN BACKUP AT STANDBY SITE:

If you have a recovery catalog configured you can connect to it and take a backup.

Both the Primary and Standby database should use the same recovery catalog. Even though these database share the same DBID,Rman is able to differentiate the standby database from Primary .

Note you do not need to register the standby database in the catalog if the primary is already registered. Simply connect to the standby as target and then connect to recovery catalog for taking backup.

Enterprise Manager Cloud Control Data Guard configuration
verification wizard

It checks that supplemental logging is turned on if there is a logical standby database in the configuration.

It verifies that parameter settings in the SPFILE or in memory or both, are consistent with the

broker configuration properties for that database.

It checks that the current data protection level is consistent with the broker’s configured data

protection mode.

Usually Data Guard configuration verification is kind of health check of Data Guard, it does many things

like performing complete health check, verifying the FSFO status, determination of protection mode,

availability of standby redo log files, Data Guard status whether valid or not, After the log switch

whether the sequence is shipping to standby or not and Data Guard properties. To initiate Data Guard

verification Navigation is "Availability --> Verify Data Guard Configuration".

After converting your physical standby database to a logical database, you get an error: Error: ORA-16810 multiple errors or warnings detected for database Fast-Start Failover: DISABLED
Configuration Status:

Reinstate both the primary and physical standby databases. The broker will automatically detect

that PRODSBY is a logical standby update to the metadata.

DGMGRL REINSTATE DATABASE

The target database should be MOUNTED and DGMGRL should be connected to the primary

database.

To reinstate the failed primary database, start it to the mounted state. Then run DGMGRL, connect to

the new primary database and reinstate the old primary database.

Recovery from Loss of Datafiles on the Standby Database

1. Connect SQL*Plus to the standby database.

2. Stop Redo Apply using the SQL ALTER DATABASE ... statement.

3. In a separate terminal, start RMAN and connect to both the standby and recovery catalog

databases (use the TARGET keyword to connect to the standby instance).

4. Issue the following RMAN commands to restore and recover datafiles on the standby database:

RESTORE DATAFILE <n,m,...>;

RECOVER DATABASE;

5. To restore a tablespace, use the RMAN 'RESTORE TABLESPACE tbs_name1, tbs_name2, ...'

command.

6. At the SQL*Plus prompt, restart Redo Apply using the SQL ALTER DATABASE ...

statement.

Switchover :

Snapshot standby database test cases:

DGMGRL> show configuration

Configuration - mahesh_12c_dg_config

  Protection Mode: MaxPerformance

  Members:

  orcl12    - Primary database

    orcl12_sb - Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 31 seconds ago)

SQL> create tablespace test datafile size 10m;

Tablespace created.

SQL> drop tablespace test;

drop tablespace test

*

ERROR at line 1:

ORA-38881: Cannot drop tablespace TEST on primary database due to guaranteed

restore points.

DGMGRL> connect sys/*******@orcl12_sb

Connected as SYSDBA.

DGMGRL> failover to orcl12_sb;

Converting database "orcl12_sb" to a Physical Standby database, please wait...

Oracle Clusterware is restarting database "orcl12_sb" ...

Continuing to convert database "orcl12_sb" ...

Database "orcl12_sb" converted successfully

Performing failover NOW, please wait...

Failover succeeded, new primary is "orcl12_sb"

DGMGRL>

important

 

Troubleshooting Performance Issues (Doc ID 1377446.1) 10g New Feature: REDO LOGS SIZING ADVISORY (Doc ID 274264.1) Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1) Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) (Doc ID 1503653.1)

SQL

SQL Delimiter

SQL> select name||'|'||open_mode||'|'||database_role from v$database;

NAME||'|'||OPEN_MODE||'|'||DATABASE_ROLE

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

ORCLCDB|READ WRITE|PRIMARY

SQL> set heading off

SQL> /

ORCLCDB|READ WRITE|PRIMARY

SQL> set feedback off

SQL> /

ORCLCDB|READ WRITE|PRIMARY

SQL> set echo off

SQL> /

ORCLCDB|READ WRITE|PRIMARY

SQL>

SQL Date

SQL> select to_char(sysdate,('DD-MON-YYYY HH24:MI:SS'))||','||NAME FROM JTEST1;

07-AUG-2020 05:16:30,ORCLCDB

SQL>

OCI

Oracle Autonomous Database Cloud 2019 Specialist

1Z0-931

https://learn.oracle.com/ols/course/working-with-oracle-autonomous-database-2019/35573/55727

gDBClone reach version 3.0.4, now available on MOS

https://blogs.oracle.com/db_maintenance/gdbclone-reach-version-304,-now-available-on-mos