Rename ASM disk group in Oracle 12c RAC Database

In this article I am illustrating how to rename ASM Disk group in oracle 12c.

The tool renamedg facilitates to rename the ASM disk group(s) in oracle.  There are two options available in renamedg tool to rename diskgroup(s).

1. Specify “BOTH” argument in the PHASE parameter of renamedg tool.
2. Specify “one and two” arguments in the PHASE parameter of renamedg tool.

  How to rename the ASM disk group using “BOTH” argument.

• Take the backup of the database (optional, but for the safer side).
• Bring down the database. (If your disk groups are actively used by any Database processes)
• Log in to ASM Instance and :-
  • Get disk group(s) and its associated disk(s) details.
  • Dismount the disk group(s) from all the nodes.
    

    (You cannot use renamedg tools to rename CRS disk group because: (1) You cannot dismount the disk group , as it’s being used by Cluster actively. (2) You cannot rename the disk group(s) or disk(s) when CRS is down.)

• Use renamedg utility to rename the disk group. This is a command line utility.
• After renaming the disk groups, you can rename the disks in disk group that assign to the disk group. Before rename the disks in disk group, you need to mount the disk group in restricted mode.
• Once rename activities completed, Mount the entire disk group manually that was renamed.
• The original disk group resource is not automatically deleted. The status of the old disk group resource can be checked with the Oracle Clusterware Control (CRSCTL) crsctl stat res -t command and then manually deleted with the Server Control Utility (SRVCTL) srvctl remove disk group command. . If those disk group are associated with Database, you need to remove database resource and add it again.

Quick Commands:

• Get Disk Details

select dg.name “DG Name” ,di.name “Disk Name”,di.path

from v$asm_diskgroup dg, v$asm_disk di

where dg.GROUP_NUMBER=di.GROUP_NUMBER

order by 1;

DG Name	Disk Name	PATH
PRAD_CTL1	PRAD_CTL1_0000	/dev/rhdiskpower10
PRAD_CTL2	PRAD_CTL2_0000	/dev/rhdiskpower11
PRAD_CTL3	PRAD_CTL3_0000	/dev/rhdiskpower12
PRAD_REDOA1	PRAD_REDOA1_0000	/dev/rhdiskpower6
PRAD_REDOA2	PRAD_REDOA2_0000	/dev/rhdiskpower7
PRAD_REDOB1	PRAD_REDOB1_0000	/dev/rhdiskpower8
PRAD_REDOB2	PRAD_REDOB2_0000	/dev/rhdiskpower9
PRAD_ROOT	PRAD_ROOT_0000	/dev/rhdiskpower2
PRAD_SEED	PRAD_SEED_0000	/dev/rhdiskpower5
PPRADTRN_DATA1	PPRADTRN_DATA1_0000	/dev/rhdiskpower3
PPRADTRN_INDX1	PPRADTRN_INDX1_0000	/dev/rhdiskpower4

• Dismount Diskgroup

            alter diskgroup <DG Name> dismount;

• Rename Diskgroup

Syntax:    renamedg [phase={one|two |both}] dgname=diskgroup

       newdgname=newdiskgroup [config=configfile]

     [asm_diskstring=discoverystring, discoverystring …]

     [clean={true|false}] [check={true|false}]

     [confirm={true|false}] [verbose={ true|false}]

     [keep_voting_files={true|false}]

Example: $ renamedg dgname=PRAD_ROOT newdgname=PRAD1_ROOT asm_diskstring=’/dev/emcpowerp1′ verbose=true

• Mount Diskgroup Restricted
  

alter diskgroup <DG Name> mount restricted;

• Rename Disk name

alter diskgroup <DG Name> rename disks all;

• Mount Diskgroup
  

alter diskgroup <DG Name> mount;

---

How to rename the ASM disk group using “one and two” argument ( Only Commands).

Steps are all same as to rename the disk group(s) with “both” argument, except the usage of  renamedg.

• Below command generates the configuration file to be used in phase two.

$renamedg phase=one dgname=<Current DG Name>  newdgname=<New DG name>      asm_diskstring=’/devices/disk*’ config=/tmp/disks.conf verbose=true

• Rename the disk group using configuration file.

$ renamedg phase=two dgname=<Current DG Name> newdgname=<New DG Name> config=/tmp/disks.conf   verbose=true

• Mount Diskgroup Restricted
  

alter diskgroup <DG Name> mount restricted;

• Rename Disk name

alter diskgroup <DG Name> rename disks all;

                                                ***

ASM Instance Discovery

In this article I thought will give you quick points that help to understand how RDBMS discover ASM.

RDBMS Discovery of ASM

1. ASM Instance registers with Cluster Synchronization Services (CSS) daemon running as bin when it starts.
2. RDBMS when tries to access the ASM file for the first time, it needs to establish connection to the local ASM instance. These connection details are NOT stored anywhere and you don’t need to configure them. RDBMS contact CSS daemon and gets passed the connect string for the ASM Instance. The RDBMS then connects as sysdba to the ASM instance using OS authentication.
3. The initial connection between the ASM instance and the RDBMS instance is known as the umbilicus, and remains active as long as the RDBMS instance has any ASM files open.
4. RDBMS‘s ASMB process that connects to ASM Instance. It will remain connected till there are open ASM files.
5. The ASM side of the connection is a foreground process , called the umbilicus foreground (UFG).
6. RDBMS and ASM instances exchange critical messages over the umbilicus.
7. Failure of the umbilicus is fatal to the RDBMS instance because the connection is critical to maintaining the integrity of the disk group.

                                                  ***

Advanced Compression Option for Data Pump and License

Oracle Advanced Compression

Oracle Advanced Compression provides comprehensive data compression and Information Lifecycle Management (ILM) capabilities for all types of data: structured/unstructured, backup, network, archive, and Data Guard Redo Transport traffic. To facilitate automated ILM, Heat Map and Automatic Data Optimization enable organizations to maintain system-generated data usage statistics, at the row and segment levels, and to set policies that implement data compression/movement automatically.

Advanced Compression is an option that you have to pay for when you are using it.

The Oracle Advanced Compression option contains the following features:

• Advanced Row Compression
• Advanced LOB Compression
• Advanced LOB Deduplication
• RMAN Backup Compression (RMANDEFAULT COMPRESS does not require the Advanced Compression option)
• Data Pump Data Compression (COMPRESSION=METADATA_ONLYdoes not require the Advanced Compression option)
• Heat Map
• Automatic Data Optimization
• Data Guard Redo Transport Compression
• Advanced Network Compression
• Optimization for Flashback Data Archive History Tables
• Storage Snapshot Optimization
• Online Move Partition (to any compressed format)
• Exadata Flash Cache Compression: This feature can be enabled only on Exadata storage servers, and all database processors that access the Exadata storage servers must be licensed for Oracle Advanced Compression.
• Advanced Index Compression
• Hybrid Columnar Compression Row-Level Locking (Requires Exadata, Supercluster, ZFS, or FS1 storage).

Data Pump Compression Feature

DBAs cannot always control the use of Advance Compression options. For Instance, Using COMPRESSION=METADATA_ONLY (which is default) option in expdp does not require the Advanced Compression option, however COMPRESSION=ALL option required advance compression option. There is no way to avoid the usage without revoking right to use data pump at all.

License

Bug 8478082 : DISALLOW UNLICENSED COMPRESSION FEATURE WHILE USING DATA PUMP

There is no way to disable the use of any of the features that are part of the Advanced Compression option. You can track the usage of those features with DBA_FEATURE_USAGE_STATISTICS, and if you discover a feature is being used when it shouldn’t be, you can contact the user and ask them to stop using it.

 

***

Difference between physical standby database in a Data Guard and Active Data Guard

The difference between physical standby database in a Data Guard and Active Data Guard is as bit confusing.  Few DBAs assumes that active Data Guard need different configuration and implementation plan.  Few thinks both are same. As a DBA it is very focal to understand the difference, as active Data Guard involves license cost.  In this article I take a quick tour to make you all understand the overall difference of physical standby database in a Data Guard and Active Data Guard.

Data Guard

Data Guard provides the management, monitoring, and automation software to create and maintain one or more synchronized copies of a production database to protect Oracle data from failures, disasters, human error, and data corruptions while providing high availability for mission critical applications. Data Guard is included with Oracle Database Enterprise Edition. Oracle Data Guard forms an extension to the Oracle RDBMS. It aids in establishing and maintaining secondary “standby databases” as alternative/supplementary repositories to production “primary databases”.

Data Guard supports both physical standby and logical standby sites. Oracle Corporation makes Data Guard available only as a bundled feature included within its “Enterprise Edition” of the Oracle RDBMS.

Active Data Guard

Active Data Guard is an option license for Oracle Database Enterprise Edition. Active Data Guard enables advanced capabilities that that extend basic Data Guard functionality. These include:

• Real-Time Query – offload read-only workloads to an up-to-date standby database.
• Automatic Block Repair – automatic repair of physical corruption transparent to the user.
• Far Sync – zero data loss protection across any distance.
• Standby Block Change Tracking – enable incremental backups on an active standby.
• Active Data Guard Rolling Upgrade – make it simple to reduce planned downtime.
• Global Database Services – load balancing and service management across replicated databases.
• Application Continuity – make outages transparent to users.

If a physical standby database in a Data Guard configuration has any of the above features enabled, then the Active Data Guard option must be licensed for every such physical standby, and also for the primary database. Yes, what you read is right “must be licensed for every such physical standby, and also for the primary database”.

Below are the commands can give you more insight for DBA to understand  on Physical database in the Data Guard and Active Data Guard

 How to start physical standby database in a Data Guard

1. Bring Database in Mount State.
2. Put database in recovery mode.

Commands:

SQL> startup mount
SQL> alter database recover managed standby database using current logfile disconnect from session; – (11G and lesser version)   

SQL alter database recover managed standby database disconnect from session;  – Note: USING CURRENT LOGFILE is already deprecated for start command Real-Time Apply on Oracle Database 12c (12.1).

How to start Active Data Guard database in a Data Guard

1. Start the database in open mode.
2. Put database in recovery mode.

Commands:

SQL> startup open;
SQL> alter database recover managed standby database using current logfile disconnect from session;   -(11G and  lesser version)  

SQL> alter database recover managed standby database disconnect from session;  – Note: USING CURRENT LOGFILE is already deprecated for start command Real-Time Apply on Oracle Database 12c (12.1).

Conversion from Physical standby to Active Data Guard
We can convert the physical standby  into active Data Guard standby . Below are the steps

1. Stop Apply Services.
2. Shut the database.
3. Start the database in open mode.
4. Put database in recovery mode.

SQL> alter database recover managed standby database cancel;
SQL> shut immediate;

SQL> startup open;
SQL> alter database recover managed standby database using current logfile disconnect from session;  -(11G and  lesser version)  

SQL> alter database recover managed standby database disconnect from session;  – Note: USING CURRENT LOGFILE is already deprecated for start command Real-Time Apply on Oracle Database 12c (12.1).

It enables us to have a physical standby read only open, while redo apply is still done in the background.

How to Check if Active Data Guard is Enabled or Not 

Use the following query to confirm that Data Guard is in active mode:

From standby database

SQL> select open_mode,controlfile_type from v$database;

OPEN_MODE                                        CONTROLFILE

---

READ ONLY WITH APPLY                      STANDBY

This means Active Data Guard is enabled

SQL> select open_mode,controlfile_type from v$database;

OPEN_MODE                                       CONTROLFILE

---

Mounted                                                STANDBY

This means Active Data Guard is not enabled

In short physical standby database in Data Guard will be in mount mode and Active Data Guard will be in READ ONLY WITH APPLY mode.

                                                                ***

 

Physical Standby Processes Architecture (Apply Redo Logs)

A physical standby database is an exact, block-for-block copy of a primary database. A physical standby is maintained as an exact copy through a process called Redo Apply, in which redo data received from a primary database is continuously applied to a physical standby database using the database recovery mechanisms.This also means that rowids stay the same in a physical standby database environment.

A physical standby database can be opened for read-only access and used to offload queries from a primary database. If a license for the Oracle Active Data Guard option has been purchased, Redo Apply can be active while the physical standby database is open, thus allowing queries to return results that are identical to what would be returned from the primary database. This capability is known as the real-time query feature.

How Physical Standby works 

On the Primary database site

The log writer process (LGWR) collects transactions from the log buffer and writes to the online redo logs.

The archiver process (ARCH) creates a copy of the online redo logs, and writes to the local archive destination.

Depending on the configuration, the archiver process or log writer process can also transmit redo logs to standby database. When using the log writer process, you can specify synchronous or asynchronous network transmission of redo logs to remote destinations.

Data Guard achieves asynchronous network I/O using LGWR network server process (LNS). These network severs processes are deployed by LOG_ARCHIVE_DEST_n initialization parameter.

Data Guard’s asynchronous log transport (i.e. the Maximum Performance mode) is recommended for a configuration in which the network distance is up to thousands of miles, providing continual maximum performance, while minimizing the risks of transaction loss in the event of a disaster.

On the Standby database site

The remote file server process (RFS) receives archived redo logs from the primary database. The primary site launches the RFS process during the first log transfer.

The redo logs information received by the RFS process can be stored as either standby redo logs or archived redo logs. Data Guard introduces the concept of standby redo logs (separate pool of log file groups).

Standby redo logs must be archived by the ARCH process to the standby archived destination before the managed recovery process (MRP) applies redo log information to the standby database.

The fetch archive log (FAL) client is the MRP process. The fetch archive log (FAL) server is a foreground process that runs on the primary database and services the fetch archive log requests coming from the FAL client. A separate FAL server is created for each incoming FAL client. FAL_SERVER specifies the FAL (fetch archive log) server for a standby database. The value is an Oracle Net service name, which is assumed to be configured properly on the standby database system to point to the desired FAL server.FAL_CLIENT specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through the FAL_SERVER parameter, to refer to the FAL client. The value is an Oracle Net service name, which is assumed to be configured properly on the FAL server system to point to the FAL client (standby database). Thanks to the FAL_CLIENT and FAL_SERVER parameters, the managed-recovery process in the physical database will automatically check and resolve gaps at the time redo is applied. This helps in the sense that you don’t need to perform the transfer of those gaps by yourself. FAL_CLIENT and FAL_SERVER only need to be defined in the initialization parameter file for the standby database(s). It is possible; however, to define these two parameters in the initialization parameter for the primary database server to ease the amount of work that would need to be performed if the primary database were required to transition its role.

Prior to Oracle 11g, Redo Apply only worked with the standby database in the MOUNT state, preventing queries against the physical standby whilst media recovery was in progress. This has changed in Oracle 11g.When using Data Guard Broker (DG_BROKER_START = TRUE), the monitor agent process named Data Guard Broker Monitor (DMON) is running on every site (primary and standby) and maintain a two-way communication.

Protection Mode

 Maximum Protection

This mode offers the highest level of data protection. Data is synchronously transmitted to the standby database from the primary database and transactions are not committed on the primary database unless the redo data is available on at least one standby database configured in this mode. If the last standby database configured in this mode becomes unavailable, processing stops on the primary database. This mode ensures no-data-loss.

Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down.

Maximum Availability

This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Under normal operations, transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log AND based on user configuration, one of the following is true:

• redo has been received at the standby, I/O to the standby redo log has been initiated, and acknowledgement sent back to primary
• redo has been received and written to standby redo log at the standby and acknowledgement sent back to primary

If the primary does not receive acknowledgement from at least one synchronized standby, then it operates as if it were in maximum performance mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database.

 If the primary database fails, then this mode ensures no data loss will occur provided there is at least one synchronized standby in the Oracle Data Guard configuration.  

Transactions on the primary are considered protected as soon as Oracle Data Guard has written the redo data to persistent storage in a standby redo log file. Once that is done, acknowledgment is quickly made back to the primary database so that it can proceed to the next transaction. This minimizes the impact of synchronous transport on primary database throughput and response time. To fully benefit from complete Oracle Data Guard validation at the standby database, be sure to operate in real-time apply mode so that redo changes are applied to the standby database as fast as they are received. Oracle Data Guard signals any corruptions that are detected so that immediate corrective action can be taken.

Maximum Performance

This mode offers slightly less data protection on the primary database, but higher performance than maximum availability mode. In this mode, as the primary database processes transactions, redo data is asynchronously shipped to the standby database. The commit operation of the primary database does not wait for the standby database to acknowledge receipt of redo data before completing write operations on the primary database. If any standby destination becomes unavailable, processing continues on the primary database and there is little effect on primary database performance. This is the default mode.

Performance Versus Protection in Maximum Availability Mode

When you use Maximum Availability mode, it is important to understand the possible results of using the LOG_ARCHIVE_DEST_n attributes SYNC/AFFIRM versus SYNC/NOAFFIRM(FastSync) so that you can make the choice best suited to your needs.

When a transport is performed using SYNC/AFFIRM, the primary performs write operations and waits for acknowledgment that the redo has been transmitted synchronously to the physical standby and written to disk. A SYNC/AFFIRM transport provides an additional protection benefit at the expense of a performance impact caused by the time required to complete the I/O to the standby redo log.

When a transport is performed using SYNC/NOAFFIRM, the primary performs write operations and waits only for acknowledgement that the data has been received on the standby,not that it has been written to disk. The SYNC/NOAFFIRM transport can provide a performance benefit at the expense of potential exposure to data loss in a special case of multiple simultaneous failures.

With those definitions in mind, suppose you experience a catastrophic failure at the primary site at the same time that power is lost at the standby site. Whether data is lost depends on the transport mode being used. In the case of SYNC/AFFIRM, in which there is a check to confirm that data is written to disk on the standby, there would be no data loss because the data would be available on the standby when the system was recovered. In the case of SYNC/NOAFFIRM, in which there is no check that data has been written to disk on the standby, there may be some data loss.

Archiving to Local Destinations Before Archiving to Remote Destinations

LGWR SYNC Archival to a Remote Destination with Standby Redo Log Files

LGWR ASYNC Archival with Network Server (LNSn) Processes

Acronyms

Acronym	Short For
ARC0	Archiver Process on Local Server
ARCH	Archiver
ASYNC	Asynchronous
DMON	Data Guard Monitor
DR	Disaster Recovery
FAL	Fetch Archive Log
LCR	Logical Change Record
LGWR	Logwriter
LNS	Network Server Process
LSP	Logical Standby Process (SQL Apply for Logical DG)
MRP	Managed Recovery Process (Redo Apply for Physical DG)
RFS	Remote File Server
SYNC	Synchronous
TAF	Transparent Application Failover

Step by Step approach to create an Oracle 12c Data Guard Active Standby Database (Multitenant Database – 4 Node RAC)

In this post I have demonstrated Step by Step approach to create an Oracle 12c Data Guard Active Standby Database.

Environment Setup details

• Primary database  up and running on 4 node RAC
• Data files, OCR and voting Disks(in separate DG)  are in ASM storage.
• Binaries are in  files system .
• DB Password files and init<sid>.ora  files are in  $ORACLE_HOME/dbs
• Installed and Configured Grid Infra on Standby machines, 4 Node RAC.
• OCR and voting Disks(in separate DG)  are in ASM storage.
• Created required Disk group on Standby site for data files.
• RDBMS software installed in standby machines.
• Ensured Network connectivity between PRIMARY and STANDBY server (TCP/IP – Port 1521 & 1525).

Environment details – Primary Databases

OS Version: Linux 6.2

Oracle Version: 12.1.0.2

Host Server: LABPRD1, LABPRD2, LABPRD3, LABPRD4

Primary DB : PRASPRD Instances : PRASPRD1,PRASPRD2,PRASPRD3,PRASPRD4

Storage System: ASM for Data Storage and Filesystem for Binaries

Grid Home(Binaries): /prasprd/gridinfra/grid1

RDBMS Home(Binaries) :/prasprd/db/db1

Disk Groups: CRS1(High Redundancy, For OCR and Voting Disk), LABDATA2(Data files, Red logs and Control files)

Environment details – Standby Databases

OS Version: Linux 6.2

Oracle Version: 12.1.0.2

Host Server : LABSBY1, LABSBY2, LABSBY3, LABSBY4

Primary DB : PRASSBY   Instances : PRASPRD1,PRASPRD2,PRASPRD3,PRASPRD4

Storage System: ASM for Data Storage and Filesystem for Binaries

Grid Home(Binaries): /prasprd/gridinfra/grid1

RDBMS Home(Binaries) :/prasprd/db/db1

Disk Groups: CRS1(High Redundancy, For OCR and Voting Disk), LABDATA3(Data files, Red logs and Control files)

Procedure

1. Enable force logging in PRIMARY.
2. Create SRL (standby redo logs) in PRIMARY.
3. Copy init and password file from primary to STANDBY.
4. Create directory structure in STANDBY.
5. Make Standby parameter changes in the parameter file of PRIMARY.
6. Make Standby parameter changes in the parameter file of STANDBY.
7. Add an entry in the oratab file
8. Establish the connectivity between PRIMARY and STANDBY.
9. Start the STANDBY instance
10. Use RMAN duplicate to create standby database from PRIMARY database.
11. Start the MRP process in STANDBY.
12. Verify whether the log are shipped and applied properly @the STANDBY.
13. Start other standby RAC nodes.
14. Verify Pluggable database’s status.

Step by Step Active Standby Build

1.       Enable force logging on PRIMARY

SQL> alter database force logging;

2.       Configure Standby Redo Log on PRIMARY

 a. Check the log files and sizes.

SQL>SELECT GROUP#, BYTES FROM V$LOG;

b. Create SRL

SQL> ALTER DATABASE ADD STANDBY LOGFILE  THREAD <TN> GROUP <GN>  (‘/path/<File Name>’,  ‘/path/<File Name>’   )  SIZE  <S> ;

NOTE: SIZE OF STANDBY LOG FILE SHOULD BE SAME AS ONLINE LOG FILE

 c. Verify the standby redo log file groups were created .Verify this in standby after standby build.

SQL> SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$STANDBY_LOG;

(Primary)

GROUP#	THREAD#	SEQUENCE#	ARC	STATUS
9	1	0	YES	UNASSIGNED
10	1	0	YES	UNASSIGNED
11	2	0	YES	UNASSIGNED
12	2	0	YES	UNASSIGNED
13	3	0	YES	UNASSIGNED
14	3	0	YES	UNASSIGNED
15	4	0	YES	UNASSIGNED
16	4	0	YES	UNASSIGNED

(Standby – Below table output  after the standby is created)

GROUP#	THREAD#	SEQUENCE#	ARC	STATUS
9	1	0	YES	UNASSIGNED
10	1	509	YES	ACTIVE
11	2	0	YES	UNASSIGNED
12	2	298	YES	ACTIVE
13	3	0	YES	UNASSIGNED
14	3	500	YES	ACTIVE
15	4	0	YES	UNASSIGNED
16	4	287	YES	ACTIVE

**Here I have 4 nodes having 2 redo groups each node, total 8 groups; created 8 more standby log groups 2 per node.

3.       Copy init and password file from primary to standby

Copy initPRASPRD1.ora to standby database (all 4 Nodes), name of init<sid>.ora needs to be changed depends on instance names

          Copy the password file orapwPRASPRD1 from primary to standby database (all 4 nodes), name of init needs to be changed depends on instance names

4.       Create directory structure in standby

 $ mkdir -p  /u01/app/oracle/admin/prasprd/adump (on all 4 node)

5.       Make Standby parameter changes in the parameter file of PRIMARY. 

Add below entries in the  init<sid>.ora.(Make the entry in one Node pfile and copy to other nodes)

$ cat initprasprd1.ora

 ##########DG Enrty ################

*.log_archive_max_processes='8'

*.db_unique_name=PRASPRD'

*.log_archive_config='dg_config=(PRASSBY,PRASPRD)'

*.log_archive_dest_1='location=+LABDATA2 valid_for=(all_logfiles,all_roles)

*.log_Archive_dest_2='service=PRASSBY_DR async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=PRASSBY'

*.log_file_name_convert='+LABDATA3','+LABDATA2'

*.db_file_name_convert='+LABDATA3','+LABDATA2'

*.fal_server=PRASSBY_DR;

*.fal_client=PRASPRD;

#############DG Entry ############################

 6.       Make Standby parameter changes in the parameter file of Standby                                     

Add below entries in the  init<sid>.ora.(Make the entry in one Node pfile and copy to other nodes)

 

$ cat initprasprd1.ora

##########DG Enrty ################

*.log_archive_max_processes='8'

*.db_unique_name='PRASSBY'

*.standby_file_management='AUTO'

*.log_archive_config='dg_config=(PRASPRD,PRASSBY)'

*.log_archive_dest_1='location=+LABDATA3 valid_for=(all_logfiles,all_roles) db_unique_name=PRASSBY'

*.log_Archive_dest_2='service=PRASPRD async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=PRASPRD'

*.log_file_name_convert='+LABDATA2','+LABDATA3'

*.db_file_name_convert='+LABDATA2','+LABDATA3'

*.fal_server=PRASPRD

*.fal_client=PRASSBY_DR

#############DG Entry ############################

7.       Add an entry in the oratab file

PRASPRD1: /prasprd/db/db1: N  (Node1)

PRASPRD2: /prasprd/db/db1: N  (Node2)

PRASPRD3: /prasprd/db/db1: N  (Node3)

PRASPRD4: /prasprd/db/db1: N  (Node4)

8.       Establish the connectivity between PRIMARY and STANDBY.   

On Standby (Add a static entry in the listener.ora for PRASSBY)

 

LISTENER12C =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST =LABSBY1.me.domain)(PORT = 1525))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1525))

    )

  )

SID_LIST_LISTENER12C =

  (SID_LIST =

 (SID_DESC =

      (GLOBAL_DBNAME = PRASSBY)

      (ORACLE_HOME = /prasprd/db/db1)

      (SID_NAME = PRASPRD1)

    )

 )

Reload the listener

$ lsnrctl reload listener12c

 LSNRCTL for Linux: Version 12.1.0.2.0 - Production on Tue Jan 6 12:11:31 2015

 Copyright (c) 1991, 2014, Oracle.  All rights reserved.

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= LABPRD1.me.domain) (PORT=1525)))

The command completed successfully

On Primary (Add a below entry on tnsnames.ora)

PRASSBY_DR =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = LABSBY1.me.domain)(PORT = 1525))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME =PRASSBY)

    )

  )

 PRASPRD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =LABPRD1.corporate.domain)(PORT = 1525))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PRASPRD)

    )

  )

 On Standby(Add a below entry on tnsnames.ora)

PRASSBY_DR =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = LABSBY1.corporate.domain)(PORT = 1525))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME =PRASSBY)

    )

  )

PRASPRD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =LABPRD1.corporate.domain)(PORT = 1525))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PRASPRD)

    )

  )

9.       Start the STANDBY database instance.

 $ . oraenv

ORACLE_SID = [prasprd1] ? prasprd1

The Oracle base has been set to /u01/app/orabase

 oradb@PRASSBY1.ME.DOMAIN:/home/oradb ~]sqlplus / as sysdba

 SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 6 12:11:31 2015

 Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 Connected to an idle instance.

 SQL> startup nomount;

 ORACLE instance started.

Total System Global Area  XXXXXXXXX bytes

Fixed Size                  XXXXXXX bytes

Variable Size             XXXXXXXXX bytes

Database Buffers           XXXXXXXX bytes

Redo Buffers                XXXXXXX bytes

10.       Use RMAN duplicate to create standby database on Primary database.

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Jan 6 14:40:12 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

 

RMAN> connect target sys/*****@PRASPRD connected to target database: PRASPRD (DBID=2845711114)

 

RMAN> connect auxiliary sys/*****@PRASSBY_DR

 

connected to auxiliary database: PRASPRD (not mounted)

 

run

 

{ allocate channel c1 type disk;

 

allocate channel c2 type disk;

 

allocate auxiliary channel aux type disk;

 

duplicate target database for standby from active database;

 

alter system set log_archive_max_processes=’8′;

 

alter system set db_unique_name=’PRASSBY’;

 

alter system standby_file_management=’AUTO’;

 

alter system set log_archive_config=’dg_config=(PRASPRD,PRASSBY)’;

 

alter system set log_archive_dest_1=’location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=PRASSBY’;

 

alter system set log_Archive_dest_2=’service=prasprd async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=PRASPRD’;

 

log_file_name_convert=’+LABDATA3′,’+LABDATA2′

 

db_file_name_convert=’+LABDATA3′,’+LABDATA2′ }

11.   Start the MRP process on standby database

SQL> startup ;

ORACLE instance started.

Total System Global Area  XXXXXXXXX bytes

Fixed Size                  XXXXXXX bytes

Variable Size              XXXXXXXXX bytes

Database Buffers            XXXXXXXXX bytes

Redo Buffers                 XXXXXXX bytes

Database mounted.

Database opened. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

                      Media recovery complete

12.       Verify MRP process on standby database

oradb@LABPRD.CORPORATE.DOMAIN:/home/oradb ~]ps -ef | grep mrp

oradb    23678     1  0 Jan05 ?        00:00:05 ora_mrp0_prasprd1

 SQL> select process,status,thread#,sequence#,blocks from v$managed_standby where process like ‘%MRP%’;

PROCESS	STATUS	THREAD#	SEQUENCE#	BLOCKS
MRP0	APPLYING_LOG	1	498	102400

13.     Verify whether the log are shipped and applied properly @the standby.

On Primary

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL > select thread#, max(sequence#) “Last Primary Seq Generated”

           From v$archived_log val, v$database vdb

           where val.resetlogs_change# = vdb.resetlogs_change#

             group by thread# order by 1;

THREAD#	LAST  PRIMARY SEQ GENERATED
1	497
2	283
3	484
4	277

 

On Standby

SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE#

“Last Sequence Received”,

APPL.SEQUENCE# “Last Sequence Applied”,

(ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG

WHERE

(THREAD#,FIRST_TIME )

 IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$ARCHIVED_LOG GROUP BY THREAD#))

ARCH,(SELECT THREAD# ,SEQUENCE#

FROM V$LOG_HISTORY

WHERE (THREAD#,FIRST_TIME )

 IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY

GROUP BY THREAD#)) APPL

WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

THREAD#	LAST SEQUENCE RECEIVED	LAST SEQUENCE APPLIED	DIFFERENCE
1	497	497	0
2	283	283	0
3	484	484	0
4	277	277	0

14.        Startup other RAC standby node.

SQL> startup open;

15.      Check pluggable database and status in PRIMARY and STANDBY.

On Primary

 

 SQL> select name, open_mode from v$pdbs; or show pdbs

NAME	OPEN_MODE
PDB$SEED	READ ONLY
PDB1	MOUNTED
PDB2	MOUNTED

 On Standby

 SQL> select name, open_mode from v$pdbs; or show pdbs

NAME	OPEN_MODE
PDB$SEED	READ ONLY
PDB1	MOUNTED
PDB2	MOUNTED

 

 

16.       Check database ROLE

 On Primary

 SQL> select NAME, DATABASE_ROLE, SWITCHOVER_STATUS,DB_UNIQUE_NAME,CDB,CON_ID from v$database;

 

NAME	DATABASE_ROLE	SWITCHOVER_STATUS	DB_UNIQUE_NAME	CDB	CON_ID
CDBORCL	PRIMARY	TO STANDBY	cdborcl	YES	0

 

On Standby

             SQL> select NAME, DATABASE_ROLE, SWITCHOVER_STATUS,DB_UNIQUE_NAME,CDB,CON_ID from v$database;

NAME	DATABASE_ROLE	SWITCHOVER_STATUS	DB_UNIQUE_NAME	CDB	CON_ID
CDBORCL	PHYSICAL STANDBY	NOT ALLOWED	cdborcdr	YES	0

 

 

17.       Confirm that the primary database protection mode

 

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

PROTECTION_MODE

——————————-

MAXIMUM PERFORMANCE

 ——————> At this point Physical standby creation completed ! Congrats.

Test SCENARIOS

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

 You can do several test scenarios to make sure changes are getting shipped to standby location.

• ·         Test 1 – Create Table on any PDB schema can verify the changes take effect on Standby site.

Primary

Create a table, insert some values

Standby

 1. alter pluggable database all open read only;

                  2.  Connect to PDB .

                  3. Select count (*) from schema.tablename

• ·         Test 2 – Clone a PDB and verify the changes take effect on Standby site.

Primary

CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb2adm IDENTIFIED BY meadmin;

SQL> select name, open_mode from v$pdbs; or show pdbs;

Standby

1.       Verify Alert log

2.  SQL> select name, open_mode from v$pdbs; or show pdbs;

Reference

• In this exercise we are bringing up standby database on PRASSBY i.e. on HOST LABSBY1.
• LISTENER 12c – This is a static Listener to build standby database and used 1525 port.
• PRASSBY_DR – Connect String to Connect DG Instance for Standby Build and Log Shipping
• PRASPRD – Connect String to Connect Primary Instance for Standby Build and Log Shipping

Oracle 12c Add / Modify SCAN IP, SCAN NAME and SCAN Listener

In this article I demonstrate how to Add/Modify the SCAN IP / SCAN Host and Configure  SCAN Listener  without reinstalling the Grid Infra environment. Some times because of the network team wrongly assign SCAN IP or DBA setup Grid infra environment with wrong SCAN IP / Hostname or the Change in the Network Subnet/Hostname later after the setup.

Check the Current set up  in Node1

racnodepdb01.localdomain.oragrid:/home/oragrid $ cat /etc/hosts

#####Prod IP######

10.133.65.201   racnodepdb01.localdomain            racnodepdb01

10.133.65.202   racnodepdb02.localdomain            racnodepdb02

####SCAN IP#####

10.133.65.205   racnodepdb01-orascan.localdomain    racnodepdb01-orascan

10.133.65.206   racnodepdb02-orascan.localdomain    racnodepdb02-orascan

####Heartbeat IP####

172.26.88.201   racnodepdb01-orahb.localdomain      racnodepdb01-orahb

172.26.88.202   racnodepdb02-orahb.localdomain      racnodepdb02-orahb

####VIP####

10.133.65.209   racnodepdb01-oravip.localdomain     racnodepdb01-oravip

10.133.65.210   racnodepdb02-oravip.localdomain     racnodepdb02-oravip

####NAS IP####

172.26.112.205  racnodepdb01-nas.localdomain        racnodepdb01-nas

172.26.112.206  racnodepdb02-nas.localdomain        racnodepdb02-nas

racnodepdb01.localdomain.oragrid:/home/oragrid $ crsctl stat res –t

——————————————————————————–

Name           Target  State        Server                   State details

——————————————————————————–

Local Resources

——————————————————————————–

ora.DATA_CRS1.dg

ONLINE  ONLINE       racnodepdb01              STABLE

ONLINE  ONLINE       racnodepdb02              STABLE

ora.LISTENER.lsnr

ONLINE  ONLINE       racnodepdb01              STABLE

ONLINE  ONLINE       racnodepdb02              STABLE

ora.asm

ONLINE  ONLINE       racnodepdb01              Started,STABLE

ONLINE  ONLINE       racnodepdb02              Started,STABLE

ora.net1.network

ONLINE  ONLINE       racnodepdb01              STABLE

ONLINE  ONLINE       racnodepdb02              STABLE

ora.ons

ONLINE  ONLINE       racnodepdb01              STABLE

ONLINE  ONLINE       racnodepdb02              STABLE

——————————————————————————–

Cluster Resources

——————————————————————————–

ora.LISTENER_SCAN1.lsnr

1        ONLINE  ONLINE       racnodepdb01              STABLE

ora.MGMTLSNR

1        ONLINE  ONLINE       racnodepdb01              169.254.172.119 172.

26.88.201,STABLE

ora.cvu

1        ONLINE  ONLINE       racnodepdb01              STABLE

ora.mgmtdb

1        ONLINE  ONLINE       racnodepdb01              Open,STABLE

ora.oc4j

1        ONLINE  ONLINE       racnodepdb01              STABLE

ora.racnodepdb01.vip

1        ONLINE  ONLINE       racnodepdb01              STABLE

ora.racnodepdb02.vip

1        ONLINE  ONLINE       racnodepdb02              STABLE

ora.scan1.vip

1        ONLINE  ONLINE       racnodepdb01              STABLE

——————————————————————————–

racnodepdb01.localdomain.oragrid:/home/oragrid $ srvctl status scan

SCAN VIP scan1 is enabled

SCAN VIP scan1 is running on node racnodepdb01

racnodepdb01.localdomain.oragrid:/home/oragrid $ srvctl status scan_listener

SCAN Listener LISTENER_SCAN1 is enabled

SCAN listener LISTENER_SCAN1 is running on node racnodepdb01

racnodepdb01.localdomain.oragrid:/home/oragrid $ srvctl config scan

SCAN name: racnodepdb01-orascan.localdomain, Network: 1

Subnet IPv4: 10.133.65.0/255.255.255.0/bond0.2265, static

Subnet IPv6:

SCAN 0 IPv4 VIP: 10.133.65.205

SCAN VIP is enabled.

SCAN VIP is individually enabled on nodes:

SCAN VIP is individually disabled on nodes:

racnodepdb01.localdomain.oragrid:/home/oragrid $ srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521

Registration invited nodes:

Registration invited subnets:

SCAN Listener is enabled.

SCAN Listener is individually enabled on nodes:

SCAN Listener is individually disabled on nodes:

Check the Current set up  in Node2

racnodepdb01.localdomain.oragrid:/home/oragrid $ cat /etc/hosts

#####Prod IP######

10.133.65.201   racnodepdb01.localdomain            racnodepdb01

10.133.65.202   racnodepdb02.localdomain            racnodepdb02

####SCAN IP#####

10.133.65.205   racnodepdb01-orascan.localdomain    racnodepdb01-orascan

10.133.65.206   racnodepdb02-orascan.localdomain    racnodepdb02-orascan

####Heartbeat IP####

172.26.88.201   racnodepdb01-orahb.localdomain      racnodepdb01-orahb

172.26.88.202   racnodepdb02-orahb.localdomain      racnodepdb02-orahb

####VIP####

10.133.65.209   racnodepdb01-oravip.localdomain     racnodepdb01-oravip

10.133.65.210   racnodepdb02-oravip.localdomain     racnodepdb02-oravip

####NAS IP####

172.26.112.205  racnodepdb01-nas.localdomain        racnodepdb01-nas

172.26.112.206  racnodepdb02-nas.localdomain        racnodepdb02-nas

racnodepdb02.localdomain.oragrid:/home/oragrid $ srvctl status scan

SCAN VIP scan1 is enabled

SCAN VIP scan1 is running on node racnodepdb01

racnodepdb02.localdomain.oragrid:/home/oragrid $ srvctl status scan_listener

SCAN Listener LISTENER_SCAN1 is enabled

SCAN listener LISTENER_SCAN1 is running on node racnodepdb01

racnodepdb02.localdomain.oragrid:/home/oragrid $srvctl config scan

SCAN name: racnodepdb01-orascan.localdomain, Network: 1

Subnet IPv4: 10.133.65.0/255.255.255.0/bond0.2265, static

Subnet IPv6:

SCAN 0 IPv4 VIP: 10.133.65.205

SCAN VIP is enabled.

SCAN VIP is individually enabled on nodes:

SCAN VIP is individually disabled on nodes:

racnodepdb02.localdomain.oragrid:/home/oragrid $srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521

Registration invited nodes:

Registration invited subnets:

SCAN Listener is enabled.

SCAN Listener is individually enabled on nodes:

SCAN Listener is individually disabled on nodes:

 Stop SCAN and SCAN_LISTENER (Use anyone node as GRID owner)

racnodepdb01.localdomain.oragrid:/home/oragrid$ srvctl stop scan_listener

racnodepdb01.localdomain.oragrid:/home/oragrid$ srvctl stop scan

racnodepdb01.localdomain.oragrid:/home/oragrid $ srvctl status scan

SCAN VIP scan1 is enabled

SCAN VIP scan1 is not running

racnodepdb01.localdomain.oragrid:/home/oragrid $ srvctl status scan_listener

SCAN Listener LISTENER_SCAN1 is enabled

SCAN listener LISTENER_SCAN1 is not running

racnodepdb01.localdomain.oragrid:/home/oragrid $srvctl config scan

SCAN name: racnodepdb01-orascan.localdomain, Network: 1

Subnet IPv4: 10.133.65.0/255.255.255.0/bond0.2265, static

Subnet IPv6:

SCAN 0 IPv4 VIP: 10.133.65.205

SCAN VIP is enabled.

SCAN VIP is individually enabled on nodes:

SCAN VIP is individually disabled on nodes:

racnodepdb01.localdomain.oragrid:/home/oragrid $srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521

Registration invited nodes:

Registration invited subnets:

SCAN Listener is enabled.

SCAN Listener is individually enabled on nodes:

SCAN Listener is individually disabled on nodes:

racnodepdb01.localdomain.oragrid:/home/oragrid $ crsctl stat res –t

——————————————————————————–

Name           Target  State        Server                   State details

——————————————————————————–

Local Resources

——————————————————————————–

ora.DATA_CRS1.dg

ONLINE  ONLINE       racnodepdb01              STABLE

ONLINE  ONLINE       racnodepdb02              STABLE

ora.LISTENER.lsnr

ONLINE  ONLINE       racnodepdb01              STABLE

ONLINE  ONLINE       racnodepdb02              STABLE

ora.asm

ONLINE  ONLINE       racnodepdb01              Started,STABLE

ONLINE  ONLINE       racnodepdb02              Started,STABLE

ora.net1.network

ONLINE  ONLINE       racnodepdb01              STABLE

ONLINE  ONLINE       racnodepdb02              STABLE

ora.ons

ONLINE  ONLINE       racnodepdb01              STABLE

ONLINE  ONLINE       racnodepdb02              STABLE

——————————————————————————–

Cluster Resources

——————————————————————————–

ora.LISTENER_SCAN1.lsnr

      1        OFFLINE OFFLINE                               STABLE

ora.MGMTLSNR

1        ONLINE  ONLINE       racnodepdb01              169.254.172.119 172.

26.88.201,STABLE

ora.cvu

1        ONLINE  ONLINE       racnodepdb01              STABLE

ora.mgmtdb

1        ONLINE  ONLINE       racnodepdb01              Open,STABLE

ora.oc4j

1        ONLINE  ONLINE       racnodepdb01              STABLE

ora.racnodepdb01.vip

1        ONLINE  ONLINE       racnodepdb01              STABLE

ora.racnodepdb02.vip

1        ONLINE  ONLINE       racnodepdb02              STABLE

ora.scan1.vip

      1        OFFLINE OFFLINE                               STABLE

 

Modify /etc/host as root with new SCAN HOST & IP – Configure and Setup od DNS is not explained here.

racnodepdb01.localdomain.oragrid:/home/oragrid $ cat /etc/hosts

#####Prod IP######

10.133.65.201   racnodepdb01.localdomain            racnodepdb01

10.133.65.202   racnodepdb02.localdomain            racnodepdb02

####SCAN IP#####

10.133.65.205   racnodepdb01-orascan.localdomain    racnodepdb01-orascan

10.133.65.206   racnodepdb02-orascan.localdomain    racnodepdb02-orascan

####Heartbeat IP####

172.26.88.201   racnodepdb01-orahb.localdomain      racnodepdb01-orahb

172.26.88.202   racnodepdb02-orahb.localdomain      racnodepdb02-orahb

####VIP####

10.133.65.209   racnodepdb01-oravip.localdomain     racnodepdb01-oravip

10.133.65.210   racnodepdb02-oravip.localdomain     racnodepdb02-oravip

####NAS IP####

172.26.112.205  racnodepdb01-nas.localdomain        racnodepdb01-nas

172.26.112.206  racnodepdb02-nas.localdomain        racnodepdb02-nas

#SCAN IP

10.133.65.51     racnodepdb-orascan.localdomain            racnodepdb-orascan

10.133.65.52     racnodepdb-orascan.localdomain            racnodepdb-orascan

10.133.65.53     racnodepdb-orascan.localdomain            racnodepdb-orascan

 

Change SCAN NAME  and Subnet

Here we modify racnodepdb01-orascan.localdomain to racnodepdb-orascan.localdomain with  no change in SUBNET or NETMASK

 Run as root

#GRID_HOME /bin/crsctl modify type ora.scan_vip.type -attr “ATTRIBUTE=SCAN_NAME,DEFAULT_VALUE=racnodepdb-orascan”   -unsupported

#GRID_HOME/bin/crsctl modify resource ora.net1.network -attr “USR_ORA_SUBNET=<new subnet id>”

#GRID_HOME/bin/crsctl modify resource ora.net1.network -attr “USR_ORA_NETMASK=<new subnet mask>”

Modify / Change SCAN

Run as root

# /racnode/oragrid/grid1/bin/srvctl modify scan -n racnodepdb-orascan

# /racnode/oragrid/grid1/bin/srvctl config scan

# /racnode/oragrid/grid1/bin/srvctl config scan

SCAN name: racnodepdb-orascan, Network: 1

Subnet IPv4: 10.133.65.0/255.255.255.0/bond0.2265, static

Subnet IPv6:

SCAN 0 IPv4 VIP: 10.133.65.51

SCAN VIP is enabled.

SCAN VIP is individually enabled on nodes:

SCAN VIP is individually disabled on nodes:

SCAN 1 IPv4 VIP: 10.133.65.52

SCAN VIP is enabled.

SCAN VIP is individually enabled on nodes:

SCAN VIP is individually disabled on nodes:

SCAN 2 IPv4 VIP: 10.133.65.53

SCAN VIP is enabled.

SCAN VIP is individually enabled on nodes:

SCAN VIP is individually disabled on nodes:

 # /racnode/oragrid/grid1/bin/srvctl modify scan_listener –u

# /racnode/oragrid/grid1/bin/srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521

Registration invited nodes:

Registration invited subnets:

SCAN Listener is enabled.

SCAN Listener is individually enabled on nodes:

SCAN Listener is individually disabled on nodes:

SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521

Registration invited nodes:

Registration invited subnets:

SCAN Listener is enabled.

SCAN Listener is individually enabled on nodes:

SCAN Listener is individually disabled on nodes:

SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521

Registration invited nodes:

Registration invited subnets:

SCAN Listener is enabled.

SCAN Listener is individually enabled on nodes:

SCAN Listener is individually disabled on nodes:

racnodepdb01.localdomain.oragrid:/home/oragrid $ crsctl stat res –t

——————————————————————————–

Name           Target  State        Server                   State details

——————————————————————————–

Local Resources

——————————————————————————–

ora.DATA_CRS1.dg

ONLINE  ONLINE       racnodepdb01              STABLE

ONLINE  ONLINE       racnodepdb02              STABLE

ora.LISTENER.lsnr

ONLINE  ONLINE       racnodepdb01              STABLE

ONLINE  ONLINE       racnodepdb02              STABLE

ora.asm

ONLINE  ONLINE       racnodepdb01              Started,STABLE

ONLINE  ONLINE       racnodepdb02              Started,STABLE

ora.net1.network

ONLINE  ONLINE       racnodepdb01              STABLE

ONLINE  ONLINE       racnodepdb02              STABLE

ora.ons

ONLINE  ONLINE       racnodepdb01              STABLE

ONLINE  ONLINE       racnodepdb02              STABLE

 

——————————————————————————–

Cluster Resources

——————————————————————————–

ora.LISTENER_SCAN1.lsnr

      1        OFFLINE OFFLINE                               STABLE

ora.LISTENER_SCAN2.lsnr

      1        OFFLINE OFFLINE                               STABLE

ora.LISTENER_SCAN3.lsnr

      1        OFFLINE OFFLINE                               STABLE

ora.MGMTLSNR

1        ONLINE  ONLINE       a            169.254.172.119 172.

26.88.201,STABLE

ora.cvu

1        ONLINE  ONLINE       racnodepdb01              STABLE

ora.mgmtdb

1        ONLINE  ONLINE       racnodepdb01              Open,STABLE

ora.oc4j

1        ONLINE  ONLINE       racnodepdb01              STABLE

ora.racnodepdb01.vip

1        ONLINE  ONLINE       racnodepdb01              STABLE

ora.phxiampdb02.vip

1        ONLINE  ONLINE       phxiampdb02              STABLE

ora.scan1.vip

      1        OFFLINE OFFLINE                               STABLE

ora.scan2.vip

      1        OFFLINE OFFLINE                               STABLE

ora.scan3.vip

      1        OFFLINE OFFLINE                               STABLE

——————————————————————————–

ORA-46362: Could not translate variable ORACLE_BASE in Oracle 12c

In this article I will demonstrate how to resolve the issue ORA-46362: Could not translate variable ORACLE_BASE.

Problem

After Database/ Instance manually added to cluster resource, DB /Instance or services is not getting started using srvctl shows below error

racnodepdb01.pii.star:/home/oradb>srvctl start service -db C1RACDB

PRCD-1133 : Failed to start services for database

CRS-5017: The resource action “ora.C1RACDB.iam_pracnode1.svc clean” encountered the following error:

ORA-46362: Could not translate variable ORACLE_BASE.

. For details refer to “(:CLSN00106:)” in “/racnode/orabase/diag/crs/racnodepdb02/crs/trace/crsd_oraagent_oradb.trc”.

CRS-5017: The resource action “ora.C1RACDB.iam_pracnode1.svc check” encountered the following error:

ORA-46362: Could not translate variable ORACLE_BASE.

. For details refer to “(:CLSN00109:)” in “/racnode/orabase/diag/crs/racnodepdb02/crs/trace/crsd_oraagent_oradb.trc”.

CRS-2680: Clean of ‘ora.C1RACDB.iam_pracnode1.svc’ on ‘racnodepdb02’ failed

CRS-5017: The resource action “ora.C1RACDB.adhoc_pracnode1.svc clean” encountered the following error:

ORA-46362: Could not translate variable ORACLE_BASE.

. For details refer to “(:CLSN00106:)” in “/racnode/orabase/diag/crs/racnodepdb02/crs/trace/crsd_oraagent_oradb.trc”.

……………………….

 

Resolution

Modify the database resource:

srvctl setenv database -db [DBNAME] -env ORACLE_BASE=$ORACLE_BASE

***

Standby Redologs

What is Standby Redo Logs?

 Standby Redo Logs (SRL) is similar to Online Redo Log (ORL). Only difference between two is that Standby Redo Log is used to store redo data received from another database (primary database).

The SRL files are where the Remote File Server (RFS) process at your standby database writes the incoming redo so that it is persistent on disk for recovery. SRL files are important for better redo transport performance and data protection. SRL are MUST in Maximum Availability or Maximum Protection mode and OPTIONAL (but recommended) in Maximum Performance mode.If there are no Standby Redo Log (SRL) files, then at each log switch in the primary database,the RFS process on the standby database that is serving an asynchronous standby destination has to create an archive log of the right size. While the RFS is busy doing creating the archive log file, the LNS process at the primary database has to wait, getting further and further behind the LGWR (in case of Maximum Performance mode). That is why it recommended to have Standby Redo Log (SRL) files in Maximum Performance mode also.

We generally configure them on our primary database as well in preparation for a role transition b/w primary-standby.Also, do not multiplex SRLs. Since Data Guard will immediately request.

 Why Standby Red Logs Needed?

 Prior to 10G Oracle Data Guard used to have an issue of losing the last redo log during catastrophic instance failure. If the primary instance is crashed or lost, the “current” redo log (as written by the LGWR process) in primary database redo log file, halts at the primary site. Archive file did not get generated on primary for the data which is in Redo log file, this data is not applied on standby and there would be the data loss forever on standby in case of primary site is lost.

 To avoid such situation In Oracle 10g and beyond we see an exciting new approach to Data Guard management whereby we write the current redo log to a “standby redo log”, allowing complete recovery in cases of catastrophic instance failure. A standby redo log resides on the standby database site. The standby redo log file is similar to an online redo log, except that a standby redo log is used to store redo data that has been received from a primary database.

In what scenarios Standby Redo Logs are required ?

Standby Redo Log is required if

1) Your standby database is in maximum protection or maximum availability mode.

2) If you are using Real-Time Apply on Standby Database.

3) If you are using Cascaded Destinations.

Step By Step Guide to to create Physical Standby Using RMAN Duplicate (backup based approach)

In this post I have demonstrated  Step By Step method to create ASM physical standby DB  from ASM primary DB  Using RMAN Duplicate (backup based approach)

Environment Details

OS Version : AIX 6.1

Oracle Version  : 11.2.0.3 PSU 8

Storage  System : ASM

Grid  Home : /prasprd/gridinfra/grid1

RDBMS Home :/prasprd/db/db1

Primary DB : PRASPRD Host: LABPRIM

Standby DB : PRASSBY  Host : LABSBY

Pre-Requisite

• Database (PRIMARY) is up and running and the data files, control files, redo log files, archive files residing in ASM.
• ASM instance and disk groups configured in STANDBY server.
• Network connectivity between PRIMARY and STANDBY server.

 

PROCEDURE

• Enable force logging in PRIMARY.
• Create SRL (standby redo logs) in PRIMARY.
• Backup the PRIMARY database.
• Make proper changes in the parameter file of PRIMARY.
• Create the parameter file for STANDBY .
• Copy the files (RMAN Backup, init.ora) to STANDBY.
• Establish the connectivity between PRIMARY and STANDBY.
• Start the STANDBY instance and use RMAN duplicate to create standby database.
• Create SRL on standby.
• Start the MRP process,
• Verify whether the log are shipped and applied properly @the standby .

Step by Step to create new physical standby

1. Enable Forced Logging on PRIMARY

SQL> ALTER DATABASE FORCE LOGGING;

2. Configure Standby Redo Log on PRIMARY

 a. Check the log files and sizes,

 SQL>SELECT GROUP#, BYTES FROM V$LOG;

 b. Create SRL

 ALTER DATABASE ADD STANDBY LOGFILE GROUP 4  (‘/path/<File Name>’,  ‘/path/<File Name>’   )  SIZE 100M  …. ;

 NOTE: SIZE OF STANDBY LOGFILE SHOULD BE SAME AS ONLINE LOGFILE

 c. Verify the standby redo log file groups were created (do this after the creation of standby database)

SQL> SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$STANDBY_LOG;

3. Use RMAN to backup PRIMARY database with archivelog & controlfile.

 RMAN>backup database format ‘/tmp/dbbkp/PRASPRD_%U’;

RMAN>backup archivelog all format ‘/tmp/dbbkp/PRASPRD_ARC_%U’;

RMAN> backup current controlfile for standby format ‘/tmp/dbbkp/PRASPRD_CONTROL.bkp’;

 4. Make the necessary changes to PRIMARY.

 DB_NAME=PRIMA

DB_UNIQUE_NAME=PRIMA

LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRASPRD,PRASSBY)’

LOG_ARCHIVE_DEST_2= ‘SERVICE=stdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRASSBY’

LOG_ARCHIVE_DEST_STATE_2=DEFER

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=PRASSBY

FAL_CLIENT=PRASPRD

DB_FILE_NAME_CONVERT=’ PRASSBY ‘,’PRASPRD’ (ASMDISKGROUP NAME)

LOG_FILE_NAME_CONVERT=’PRASSBY’,’PRASPRD’ (ASMDISKGROUP NAME)

STANDBY_FILE_MANAGEMENT=AUTO

 5. Create the parameter file for standby

 a. CREATE PFILE='<specify any location>’ from spfile; (@primary,)

b. Make the necessary changes.

 DB_NAME=PRIMA

DB_UNIQUE_NAME=PRASSBY

LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRASPRD,PRASSBY)’

CONTROL_FILES=’+DATA’,’+FRA’

DB_FILE_NAME_CONVERT=’PRASPRD’,’PRASSBY’  (ASMDISKGROUP NAME)

LOG_FILE_NAME_CONVERT=’PRASPRD’,’PRASSBY’ (ASMDISKGROUP NAME)

LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc

LOG_ARCHIVE_DEST_2= ‘SERVICE=PRIMA LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRASPRD’

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

STANDBY_FILE_MANAGEMENT=AUTO

FAL_SERVER=PRASPRD

FAL_CLIENT=PRASSBY

 6. Copy the files (RMAN Backup, init.ora) to STANDBY

 a) Copy the RMAN backup files created in /tmp/dbbkp (step3) to STANDBY in identical location

b) Copy the init.ora (step 5) to STANDBY

c) Recreate the password file in standby using orapwd utility

 7. Establish the connectivity between primary and standby.

 In PRIMARY (TNSNAMES.ORA)

 PRASSBY=

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = PRASSBY.pras.com)

)

)

In STANDBY (TNSNAMES.ORA)

PRASPRD=

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = PRASPRD.pras.com)

)

)

 NOTE: Use TNSPING <servicename> to check the connectivity between PRIMARY and STANDBY

 8. Start the STANDBY instance and use RMAN duplicate to create standby database

 NOTE: Make sure the ASM instance also running.

$export ORACLE_SID=PRASSBY

SQL>create spfile from pfile=’<specify the newly created parameter location>’

SQL>startup nomount

 NOTE: Connect to catalog if your primary database has catalog database.

 $RMAN target sys/<passwd>@primary catalog RMAN/RMAN@RMAN auxiliary sys/<passwd

RMAN> RUN {

allocate auxiliary channel C1 device type disk;

duplicate target database for standby; }

9. Create SRL(Standby Redo logs) on standby,

 ALTER DATABASE ADD STANDBY LOGFILE GROUP 4  (‘/path/<File Name>’,  ‘/path/<File Name>’   )  SIZE 100M  …. ;

 NOTE: SIZE OF STANDBY LOGFILE SHOULD BE SAME AS ONLINE LOGFILE

 10. Start the MRP process in PRIMARY

 In PRIMARY

 SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

 In STANDBY

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

11. Verify whether the log are shipped and applied properly in the standby

 a. execute on PRIMARY database

 SQL> ALTER SYSTEM SWITCH LOGFILE;

 b. execute on STANDBY database

 SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 c. Issue more log switches at PRIMARY

 SQL> ALTER SYSTEM SWITCH LOGFILE;

 d. Verify the new redo data was archived on the standby database.On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:

 SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 NOTE: Verify new archived redo log files were applied. At the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.

 SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;