Category: Oracle

RMAN-06136: ORACLE error from auxiliary database: ORA-00201: control file version 11.2.0.4.0 incompatible with ORACLE version 11.2.0.0.0

  1. Check parameter ‘compatible’ on both source and target databases.
  2. Set compatible value the same on target as on the source.
  3. Rerun RMAN duplicate database again.

HOW TO ENABLE ARCHIVE LOG MODE IN ORACLE 19c RAC

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 338
Current log sequence 340
SQL> select log_mode,name from v$database;

LOG_MODE     NAME

NOARCHIVELOG TESTDB
$ srvctl stop database -d TESTDB
$ srvctl start database -d TESTDB -o mount
SQL> alter database archivelog;
Database altered.
SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST' sid='*' scope=both;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 339
Next log sequence to archive 341
Current log sequence 341
$ srvctl stop database -d TESTDB
$ srvctl start database -d TESTDB

Copy ASM SPFILE From ASM Diskgroup to File System

ASMCMD> cd +DATAC1/TESTDB/PARAMETERFILE
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
PARAMETERFILE HIGH COARSE APR 25 23:00:00 Y spfile.921.1133441623


ASMCMD> pwd
+DATAC1/TESTDB/PARAMETERFILE
ASMCMD> ls spfile.921.1133441623
spfile.921.1133441623
ASMCMD> cp spfile.921.1133441623 /tmp/testdb_spfile.921.1133441623
copying +DATAC1/TESTDB/PARAMETERFILE/spfile.921.1133441623 -> /tmp/testdb_spfile.921.1133441623
ASMCMD> ls spfile.921.1133441623
spfile.921.1133441623
ASMCMD> exit

$ls -ltr /tmp/testdb_spfile.921.1133441623
-rw-r—–. 1 grid oinstall 6656 Apr 26 10:11 /tmp/testdb_spfile.921.1133441623

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Wed Apr 26 10:12:40 2023
Version 19.18.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.18.0.0.0

SQL> create pfile=’/tmp/initTESTDB.ora’ from spfile=’/tmp/testdb_spfile.921.1133441623′;

File created.

SQL> ! ls -ltr /tmp/initTESTDB.ora
-rw-r–r–. 1 grid oinstall 2564 Apr 26 10:13 /tmp/initTESTDB.ora

SQL> ! cat /tmp/initTESTDB.ora

database spfile created in wrong place under ASM

SYMPTOMS

DBA is trying to create a new spfile from a pfile as following :

SQL> create SPFILE='+DATAC1/TESTDB/' from pfile='/home/oracle/testdb.pfile';
File created.

Under ASM , the spile is link to the wrong location under ASM:

ASMCMD> ls -alr
Type Redund Striped Time Sys Name
N spfile =>
+DATAC1/DB_UNKNOWN/PARAMETERFILE/SPFILE.272.613162051
ASMCMD>

OR

ASMCMD> ls -alr
Type Redund Striped Time Sys Name
N spfile =>
+DATAC1/ASM/PARAMETERFILE/spfile.4186.1126528877
ASMCMD>

CAUSE

This is because the database instance has no open client session to the ASM instance at the time of the ‘create spfile’ command. That is, the ASM instance is not aware of the db name and therefore uses “DB_UNKNOWN” or ‘ASM’.

SOLUTION

When database in OPEN or MOUNT status, make sure  there is at least one connection to the ASM instance. 

SQL>select * from v$asm_client;

GROUP_NUMBER INSTANCE_NAME
------------ ----------------------------------------------------------------
DB_NAME  CLUSTER_NAME                    STATUS
-------- ------------------------------- ------------
SOFTWARE_VERSION
------------------------------------------------------------
COMPATIBLE_VERSION                                               CON_ID
------------------------------------------------------------ ----------
           0 +ASM1
TESTDB_ usa1234clu07cb8                 CONNECTED
19.0.0.0.0
19.0.0.0.0                                                            0

— Create spfile from a pfile

SQL>  create spfile='+datac1' from pfile='/home/oracle/testdb.pfile';

File created.

— check spfile is created in the right directory now

ASMCMD> pwd
+datac1
ASMCMD> cd TESTDB/
ASMCMD> ls PARAMETERFILE/
spfile.4186.1126530135

— configure TESTDB database with new spfile

$ srvctl modify  database -d TESTDB -spfile  '+DATAC1/TESTDB/PARAMETERFILE/spfile.4186.1126530135'

How To Move Data Guard Broker Configuration Files Onto ASM in 19c

By default, the broker configuration files of Oracle Data Guard are created onto local filesystem. In GI environment, we’d like those files are located in ASM filesystem.

Check Current Configurations 

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/dr1TESTDB.dat
dg_broker_config_file2               string      /u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/dr2TESTDB.dat

Stop the Broker 

SQL> alter system set dg_broker_start = FALSE;

Set the dg_broker_config_file1 & 2 parameters to the appropriate location required

SQL> alter system set dg_broker_config_file1 = '+DATAC1/TESTDB/dr1TESTDB.dat';
SQL> alter system set dg_broker_config_file2 = '+DATAC1/TESTDB/dr2TESTDB.dat';

Restart the Broker 

SQL> alter system set dg_broker_start = TRUE

Finally, the broker configuration files will be created in the new ASM location.

ASMCMD> pwd
+DATAC1/TESTDB
ASMCMD> ls -l dr*
Type             Redund  Striped  Time             Sys  Name
DATAGUARDCONFIG  HIGH    COARSE   JAN 13 11:00:00  N    dr1testdb.dat => +DATAC1/TESTDB/DATAGUARDCONFIG/TESTDB.979.1126004761
DATAGUARDCONFIG  HIGH    COARSE   JAN 13 11:00:00  N    dr2testdb.dat => +DATAC1/TESTDB/DATAGUARDCONFIG/TESTDB.980.1126004761
ASMCMD>