ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

As this message clearly explains, this error means that the operation you are going to perform that returned this error, needs database to be mounted EXCLUSIVELY - by only one instance. In my case, I was trying to drop a RAC database when I faced ORA-01586. I realized that the other instance of my RAC database is still up and it is needed that all the instances of a RAC database should be down before we can drop a database.

CRS-4700: The Cluster Time Synchronization Service is in Observer mode

This is very important to keep the time of cluster nodes synchronized across the cluster. Time difference among nodes can cause issues, and sometime it can cause the node(s) restart.

There are 2 ways of keeping the time synchronized across the RAC nodes: using NTP at OS level, or using Cluster Time Synchronization Service that runs as a RAC resource.

Segment Space Growth History and Forecast

I have already written articles to get tablespace space usage history and forecast (10g and 11g, 12c and above) and database space usage history and forecast (10g and 11g, 12c and above). Here, I will explain how we can get the same information about segments, and can forecast the future growth of segments.

ORA-39510 While Starting Cluster Health Monitor Repository (MGMTDB)

MGMTDB, also called Management Repository Database was introduced in 12c Grid Infrastructure. This repository database is used to store Cluster Health Monitor data so that this monitoring data can be used for Grid Infrastructure troubleshooting. This database resource is set to start automatically on the start of Grid Infrastructure, but, there could be some scenarios where MGMTDB might not start because of some issues. One of the similar problems I face because of which MGMTDB could not start automatically. When I tried to start Management Repository Database manually, it failed to start and threw following error messages.

ORA-00313 ORA-00312 ORA-17503 ORA-15173 in Standby Alert Log File

Errors in file /u01/app/oracle/diag/rdbms/standby_db/standby_db/trace/standby_db_lgwr_393229.trc: ORA-00313: open failed for members of log group 12 of thread 2 ORA-00312: online log 12 thread 2: '+FRA_DG/standby_db/redolog12_02.rdo' ORA-17503: ksfdopn:2 Failed to open file +FRA_DG/standby_db /redolog12_02.rdo

Creating a Real Application Cluster(RAC) 12c database using DBCA

After installing Oracle RAC, next step is to create a RAC database. Following is the process of creating a RAC database using dbca. Same method works on Windows or Unix based platforms.To invoke dbca, open a command line and run command “dbca”. On Windows, DBCA can also be invoked from Program Menu..

Oracle Flex Cluster

Oracle Real Application Cluster is a feature that was first introduced in Oracle 9i (Oracle Parallel Servers was a similar feature available before 9i, but not as robust as RAC) and has been an essential part of Oracle’s Maximum Availability Architecture. Since its advent in Oracle 9i, it has evolved a lot. Oracle 9i onward, every new release of Oracle has introduced something new in RAC which made it even more resilient, fault tolerant, and easy to manage architecture.

    

Oracle Flex ASM

Starting 12c, Oracle has introduced two very interesting features to add up to MAA (Maximum Availability Architecture), Oracle Flex Cluster and Oracle Flex ASM. In this article I will be explaining and demonstrating about Oracle flex ASM.

In flex ASM, you don’t need ASM instance to be running on each node (the Hub Node) because database instance can communicate with some other ASM instance running on any other node in the cluster.

Oracle Patches Types

In Oracle, there are different types of patching available; there are several types of patches released by Oracle that could be applied to the software you are running. There are on-off patches to fix bugs, regularly released patches, and security patches. In the following I will explain about different types of patches that are available to be installed on your RDBMS software.

Installing Oracle 12c Release 2 RAC on Linux 6 and Linux 7

This article explains how to install a 2 nodes Oracle 12c Release 2 Real Application Cluster (RAC) on Oracle Linux 6 and 7. I did this installation on Oracle Virtual Box by creating 2 virtual machines with shared storage. OS platform I used is Oracle Enterprise Linux 7, and Oracle GI and RDBMS version is 12.2. Same installation guide should work for Linux 6 also as there are only a couple of differences in the steps that I will explain during this installation. There will be no difference in installation steps if there are more than 2 nodes in the RAC setup.

Running cluvfy (Cluster Verification Utility) during 12c R2 RAC installation

Following is the output of cluster verification utility that I executed during 12c R2 RAC installation. You can see 2 examples in the following. Example 1 demonstrated if you don’t have passwordless SSH connectivity configured for “grid” and “oracle” users, so you will need to execute this from each node one by one. Following output is from one node only.

Creating Oracle Database using DBCA in 12c R2

Database Configuration Assistant (dbca) is an Oracle provided tool to create a new database. In this document I would explain how to create a new 12cR1 database using dbca. For database creation steps for Oracle 12cR1, follow this document. Steps and screens are almost same for other Oracle database versions and different OS platforms.

Creating ASM disks using ASM Filter Driver (ASMFD)

Starting 12.1.0.2 we can use ASM Filter Drive (ASMFD) to create ASM disks to be used for ASM diskgroups. I have already explain in this article that how we configure ASMFD during GI installation, and this article for migration from ASMLib to ASMFD. In this article I will show you how to create/add new ASM disks using ASMFD so that we can use them for existing or new diskgroups.

ASMCMD-9524: AFD configuration failed 'ERROR: ASMLib deconfiguration failed'

While configuring ASMFD (ASM Filer Driver), I was playing around and started facing this error after issuing “afd_configure” command

[root@salman11 ~]# /u01/app/12.2.0/grid/bin/asmcmd afd_configure ASMCMD-9524: AFD configuration failed 'ERROR: ASMLib deconfiguration failed'

I simply stopped oracleasm and acfsload service and after that reissued the “afd_configure” command, and it started working fine.
Point to note is that we need to stop oracleasm and acfsload before configuring ASMFD using “afd_configure”.

Migrating from ASMLib to Oracle ASM Filter Driver (ASMFD)

Starting 12.1.0.2, ASMFD can be used instead of ASMLib for the ASM disks management. In this article I will explain how to migrate from ASMLib to ASMFD. I already have ASMLib configured on my server and I want to move to using ASMFD. For new GI installation, we can configure ASMFD for our ASM disks from GI installation wizard. Method explained here works for both standalone GI installations as well as for RAC.

Configuring Oracle ASM Filter Driver (ASMFD) and Labelling Disks

If you already have 12.2.0.1 and above GI/RAC installation and you want to migrate to ASMFD, click here.

Starting 12.2.0.1, downloaded Grid Infrastructure (GI) software zip is extracted directly in to the directly we have already chosen as ORACLE_HOME for GI. After extraction, we execute $ORACLE_HOME/gridSetup.sh script to initiate the setup wizard. If we want to use ASMFD (ASM Filter Driver), then after we have provisioned the disk/partition at OS level, we can configure ASMFD as follows. We no longer need ASMLib to setup ASM disks once we start using ASMFD.

Disabling Transparent Huge Pages on Linux 6 and Linux 7

Oracle recommends that Transparent Huge Pages should be disabled before installation of Oracle software. In the following I will explain how to disable Transparent Huge Pages on Linux 6 and Linux 7. I am using Oracle Enterprise Linux where I see that this feature is already disabled. You can check for your release of Linux whether it is disabled or not, and disable it using method explained here. This is the official document for doing this task.

Fatal NI connect error 12537

If Fatal NI connect error 12537 appears in the alert logfile, following is how it would appear. There is another similar Fatal NI connect error 12547 discussed here.

Fatal NI connect error 12537, connecting to:  (LOCAL=NO)   VERSION INFORMATION:             TNS for Linux: Version 11.2.0.4.0 - Production

Error 1017 received logging on to the standby - Along with ORA-16191

Most probable reason for this error appearing in the alert log file of standby database is that primary database is not able to connect to the standby database to ship redo log data because the password for the SYS user in the standby database is not in sync with the primary database. Standby database is also not able to fetch the archive gap because of same discrepancy. Full error stack in the alert log file would look similar to the following.

File #... added to control file as UNNAMED000 and ORA-15041

On your standby database if you see a datafile with the name similar to ‘UNNAMED000’, it means that you have STANDBY_FILE_MANAGEMENT parameter set to auto and managed recovery process tried to create a new datafile (after you already added this file on primary database), but it could not create file because of some reason. I faced this error because my ASM diskgroup was full. Following were the entries in my alert log file of my standby database after adding a datafile in my primary database using command ‘ALTER TABLESPACE my_tablespace ADD DATAFILE ‘+DATA/datafile06.dbf’ SIZE 20g’

ORA-38701: Flashback database log...

If you are trying to list the restore points you have, and your flashback logs are not available on the disk (in the FRA), you might face ORA-38701.

SQL> select * from v$restore_point; select * from v$restore_point               * ERROR at line 1: ORA-38701: Flashback database log 1 seq 1 thread 1: "/u01/app/oracle/fast_recovery_area/DBMASK/flashback/o1_mf_c4xj2hrd_.flb"

ORA-38760: This database instance failed to turn on flashback database

I faced this error message while opening my database, after restoring the database from a cold backup (OS level copy of all database files). I realized that when I backed up my database, it had flashback feature turned on and there was also a guaranteed restore point at the time I took the backup. And this time when I was trying to open this database, there were no old flashback logs available in fast recovery area.

Data Masking using OEM Cloud Control

Data masking is a technique whereby we mask the sensitive data in the cloned copies of production databases (cloned for testing purposes) so that testing databases don’t contain any sensitive information like credit card numbers or other personal information data like email address or phone numbers etc. Once we create a clone of production database, we mask the data before handing over to the users.

Transparent Data Encryption in 12c

One of my previous article explains how to setup TransparentData Encryption in 11g. In this article I would discuss how to implement Transparent Data Encryption (TDE) in 12c database to encrypt the data in tables/column and tablespace. Following is the link to official 12c documentation I used to write this article https://docs.oracle.com/database/121/ASOAG/asotrans_config.htm#ASOAG10474.

ORA-46630: keystore cannot be created at the specified location

This error comes while creating a keystore at a location where there is already a keystore exists and you can see ewallet.p12 file already present there.

To solve the problem, use a different location to create a keystore (use ENCRYPTION_WALLET_LOCATION in sqlnet.ora file to specify the keystore location), or move this ewallet.p12 file to some other location.

ORA-46633: creation of a password-based keystore failed

You can face this error while creating a keystore but you have specified a wrong keystore location while executing the statement.

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'C:\APP\ORACLE\ADMIN\WALLET\SALMAN12C\WALLET' IDENTIFIED BY salman12; ADMINISTER KEY MANAGEMENT CREATE KEYSTORE

ORA-46658: keystore not open in the container

You may face this error when database needs to access the keystore and keystore is not open. For example, while changing keystore password, or while creating/rotating master encryption key. In the following example, I faced this error while creating master encryption key.

ORA-28417: password-based keystore is not open

You may face this error while creating a master encryption key while having/using auto-login (or local auto-login) keyswtore, but your password-based keystore is still closed which is required to be open before you can create master encryption key.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY salman12 WITH BACKUP USING 'initial_backup' CONTAINER = ALL ;

ORA-28354: Encryption wallet, auto login wallet, or HSM is already open

This error comes when you try to open a password-based keystore, and the keystore is already open.

SQL> SELECT WRL_PARAMETER, STATUS, WALLET_TYPE FROM V$ENCRYPTION_WALLET; WRL_PARAMETER                                                        STATUS                                            WALLET_TYPE ---------------------------------------- ------------------------------ -------------------- ----------------------------------------- C:\APP\ORACLE\ADMIN\SALMAN12C\WALLET     OPEN_NO_MASTER_KEY             PASSWORD

ORA-46661: keystore not open in root container

In a multi tenant environment, this error comes when you try to open a password-based keystore in a pluggable database and the keystore in root container is no already open. Please note that to open keystore in any pluggable database, the keystore in root container (CDB$ROOT) must be opened first, or alternatively you can use CONTAINER=ALL option while opening keystore in the root container so that keystore for all the pluggable databases is also opened.

Transparent Data Encryption in 11g

In this article I would discuss how to implement Transparent Data Encryption (TDE) in 11g. TDE is used to encrypt data at table/column level or tablespace level. This ensures that encrypted data is accessible only by using encryption key that is stored in either Wallet or Hardware Security Module.

For RAC setups, encryption key needs to be stored/copies on all the RAC nodes. In dataguard setup, encryption key should also be copied on dataguard sites.

ORA-28365: wallet is not open

This error means that you are trying to perform some operation in the database which requires encryption wallet to be open, but wallet is not open. In 11g, use following command to open the wallet. Provide password after “identified by” clause. In the following scenario, password is “welcome”.

alter system set encryption wallet open identified by "welcome1";