12cR1 RMAN Restore/Duplicate from ASM to Non ASM takes a longer time waiting for the ASMB process

Yet another exciting journey with Oracle bugs and challenges. Here is the story for you.

One of our recent successful migrations was a single instance Oracle EBS 12cR1 database to Oracle Super Cluster M7 as a RAC database with 2 instances on the same DB version (12.1.0.2). Subsequently, the customer wants to run through EBS cloning and set up an Oracle active data guard configuration.

The target systems are not Super Cluster. The requirement to clone and set up an Oracle data guard is to configure as a single-instance database onto a filesystem (non-ASM). After initiating the cloning procedure using the DUPLICATE TARGET DATABASE method trough RMAN, we noticed that RMAN is taking significant time to restore(ship) the data files to the remote server. Also, the following warning messages were appeared in the alert.log:

ASMB started with pid=63, OS id=18085

WARNING: failed to start ASMB (connection failed) state=0x1 sid=''

WARNING: ASMB exiting with error

Starting background process ASMB

Sat Mar 11 13:53:24 2017

ASMB started with pid=63, OS id=18087

WARNING: failed to start ASMB (connection failed) state=0x1 sid=''

WARNING: ASMB exiting with error

Starting background process ASMB

Sat Mar 11 13:53:27 2017

ASMB started with pid=63, OS id=18089

WARNING: failed to start ASMB (connection failed) state=0x1 sid=''

WARNING: ASMB exiting with error

Starting background process ASMB

 

The situation raised couple of concerns in our minds:

1. Why is the restore is too slow from RMAN? (while there is no Network latency and DB files are not so big sized)
2. Why Oracle is looking for an ASM instance on a non-Cluster home? (not even a standard Grid home)

After some initial investigation, we come across following MOS Docs:

• '12c RMAN Operations from ASM To Non-ASM Slow (Doc ID 2081537.1)'. 
• WARNING: failed to start ASMB after RAC Database on ASM converted to Single Instance Non-ASM Database (Doc ID 2138520.1)

According to the above MOS Docs, this is an expected behavior  due to an  Unpublished BUG 19503821:  RMAN CATALOG EXTREMELY SLOW WHEN MIGRATING DATABASE FROM ASM TO FILE SYSTEM

You need to apply a patch 19503821 to overcome from the bug.


If you similar demand, make sure you apply the patch in your environmet before you proceed with the restore/duplicate procedure.

-- Excerpt from the above notes:

APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]
 Information in this document applies to any platform.
 
SYMPTOMS:

1*. RAC Database with ASM has been converted or restored to Standalone Single Instance Non-ASM Database.
2*. From the RDBMS alert.log, it is showing continuous following messages.

3*.RMAN Restore/Duplicate from ASM to Non ASM in 12.1 take a longer time waiting for the ASMB process.
4*.Any RMAN command at the mount state which involves Non ASM location can take more time.

 SOLUTION:


Apply the patch 19503821, if not available for your version/OS then please log a SR with the support to get the patch for your version.

12cR2 new features for Developers and DBAs - Here is my pick (Part 2)

In Part 1, I have outlined a few (my pick) 12cR2 new features useful for Developers and DBAs. In the part 2, I am going to discuss a few more new features.

Read/Write and Read-Only Instances

Read-write and read-only database instances of the same primary database can coexist in an Oracle Flex Cluster.

Advanced Index Compression

Prior to this release, the only form of advanced index compression was low compression. Now you can also specify high compression. High compression provides even more space savings than low compression.

PDBs Enhancements

• I/O Rate Limits for PDBs
• Different character sets of PDBs in a CDB
• PDB refresh to periodically propagate changes from a source PDB to its cloned copy
• CONTAINERS hint : When a CONTAINERS ()query is submitted, recursive SQL statements are generated and executed in each PDB. Hints can be passed to these recursive SQL statements by using the CONTAINERS statement-level hint. 
• Cloning PDB no longer to be in R/W mode : Cloning of a pluggable database (PDB) resolves the issue of setting the source system to read-only mode before creating a full or snapshot clone of a PDB.
• Near Zero Downtime PDB Relocation:This new feature significantly reduces downtime by leveraging the clone functionality to relocate a pluggable database (PDB) from one multitenant container database (CDB) to another CDB. The source PDB is still open and fully functional while the actual cloning operation is taking place.
• Proxy PDB: A proxy pluggable database (PDB) provides fully functional access to another PDB in a remote multitenant container database (CDB). This feature enables you to build location-transparent applications that can aggregate data from multiple sources that are in the same data center or distributed across data centers.

Oracle Data Pump Parallel Export of Metadata: The PARALLEL parameter for Oracle Data Pump, which previously applied only to data, is extended to include metadata export operations. The performance of Oracle Data Pump export jobs is improved by enabling the use of multiple processes working in parallel to export metadata.

Renaming Data Files During Import

Oracle RAC :

• Server Weight-Based Node Eviction :Server weight-based node eviction acts as a tie-breaker mechanism in situations where Oracle Clusterware needs to evict a particular node or a group of nodes from a cluster, in which all nodes represent an equal choice for eviction. In such cases, the server weight-based node eviction mechanism helps to identify the node or the group of nodes to be evicted based on additional information about the load on those servers. Two principle mechanisms, a system inherent automatic mechanism and a user input-based mechanism exist to provide respective guidance.
• Load-Aware Resource Placement : Load-aware resource placement prevents overloading a server with more applications than the server is capable of running. The metrics used to determine whether an application can be started on a given server, either as part of the startup or as a result of a failover, are based on the anticipated resource consumption of the application as well as the capacity of the server in terms of CPU and memory.

Enhanced Rapid Home Provisioning and Patch Management

TDE Tablespace Live Conversion: You can now encrypt, decrypt, and rekey existing tablespaces with Transparent Data Encryption (TDE) tablespace live conversion. A TDE tablespace can be easily deployed, performing the initial encryption that migrates to an encrypted tablespace with zero downtime. This feature also enables automated deep rotation of data encryption keys used by TDE tablespace encryption in the background with zero downtime.

Fully Encrypted Database: Transparent Data Encryption (TDE) tablespace encryption is applied to database internals including SYSTEM, SYSAUX, and UNDO.

TDE Tablespace Offline Conversion: This release introduces new SQL commands to encrypt tablespace files in place with no storage overhead. You can do this on multiple instances across multiple cores. Using this feature requires downtime, because you must take the tablespace temporarily offline. With Data Guard configurations, you can either encrypt the physical standby first and switchover, or encrypt the primary database, one tablespace at a time.

12cR2 new features for Developers and DBAs - Here is my pick (Part 1)

Since the announcement of 12cR2 on-premises availability, the Oracle community become energetic and busy tweeting/blogging the new features, demonstrating installation & upgrades. Hence, I have decided to pick my favorite list of 12cR2 new features for Developers and & DBAs. Here is the high-level summary, until I write a detailed post for each feature (excerpt from Oracle 12cR2 new features document).

Command history for SQL * Plus: Pre 12cR2, this could be achieved through a workaround, now, the history command would do the magic for you.

Materialized Views: Real-Time Materialized Views: Materialized views can be used for query rewrite even if they are not fully synchronized with the base tables and are considered stale. Using materialized view logs for delta computation together with the stale materialized view, the database can compute the query and return correct results in real time.

For materialized views that can be used for query rewrite all of the time, with the accurate result being computed in real time, the result is optimized and fast query processing for best performance. This alleviates the stringent requirement of always having to have fresh materialized views for the best performance.

Materialized Views: Statement-Level Refresh: In addition to ON COMMIT and ON DEMAND refresh, the materialized join views can be refreshed when a DML operation takes place, without the need to commit such a transaction. This is predominantly relevant for star schema deployments.

The new ON STATEMENT refresh capability provides more flexibility to the application developers to take advantage of the materialized view rewrite, especially for complex transactions involving multiple DML statements. It offers built-in refresh capabilities that can replace customer-written trigger-based solutions, simplifying an application while offering higher performance.

Oracle Data Guard Database Compare: This new tool compares data blocks stored in an Oracle Data Guard primary database and its physical standby databases. Use this tool to find disk errors (such as lost write) that cannot be detected by other tools like the DBVERIFY utility.

Subset Standby: A subset standby enables users of Oracle Multitenant to designate a subset of the pluggable databases (PDBs) in a multitenant container database (CDB) for replication to a standby database. 

Automatically Synchronize Password Files in Oracle Data Guard Configurations: This feature automatically synchronizes password files across Oracle Data Guard configurations. When the passwords of SYS, SYSDG, and so on, are changed, the password file at the primary database is updated and then the changes are propagated to all standby databases in the configuration.

Preserving Application Connections to An Active Data Guard Standby During Role Changes: Currently, when a role change occurs and an Active Data Guard standby becomes the primary, all read-only user connections are disconnected and must reconnect, losing their state information. This feature enables a role change to occur without disconnecting the read-only user connections. Instead, the read-only user connections experience a pause while the state of the standby database is changed to primary. Read-only user connections that use a service designed to run in both the primary and physical standby roles are maintained. Users connected through a physical standby only role continue to be disconnected.

Oracle Data Guard for Data Warehouses: The use of NOLOGGING for direct loads on a primary database has always been difficult to correct on an associated standby database. On a physical standby database the data blocks were marked unrecoverable and any SQL operation that tried to read them would return an error. Or, for a logical standby database, SQL apply would stop upon encountering the invalidation redo.

Rolling Back Redefinition: There is a new ROLLBACK parameter for the FINISH_REDEF_TABLE procedure that tracks DML on a newly redefined table so that changes can be easily synchronized with the original table using the SYNC_INTERIM_TABLE procedure.

The new V$ONLINE_REDEF view displays runtime information related to the current redefinition procedure being executed based on a redefinition session identifier.

Online Conversion of a Nonpartitioned Table to a Partitioned Table: Nonpartitioned tables can be converted to partitioned tables online. Indexes are maintained as part of this operation and can be partitioned as well. The conversion has no impact on the ongoing DML operations.

Online SPLIT Partition and Subpartition: The partition maintenance operations SPLIT PARTITION and SPLIT SUBPARTITION can now be executed as online operations for heap organized tables, allowing the concurrent DML operations with the ongoing partition maintenance operation.

Online Table Move: Nonpartitioned tables can be moved as an online operation without blocking any concurrent DML operations. A table move operation now also supports automatic index maintenance as part of the move.

Oracle Database Sharding: Sharding with Oracle Database 12c Release 2 (12.2) is an architecture for suitable online transaction processing (OLTP) applications where data is horizontally partitioned across multiple discrete Oracle databases, called shards, which share no hardware or software. The collection of shards is presented to an application as a single logical Oracle database.

Stay tuned for Part 2..