Infrastructure at your Service

Jérôme Dubar

odacli create-database -io doesn’t work for older databases in 19.9

Introduction

On ODA, you create your databases with odacli create-database. You can create a complete database, and it takes about 10 minutes, and you can also create an instance-only database. It will simply and quickly create a record in the ODA registry, start the instance with a basic pfile, and that’s it. Then you’re supposed to do the next step: feed up the database with a manual restore or an RMAN duplicate.

Create a 12.2 instance-only is not OK on ODA 19.9

Unfortunately, creating an instance-only doesn’t work for me with 19.9. I tried to create a 12.2 instance and it failed immediatly:

odacli list-dbhomes

ID                                       Name                 DB Version                               Home Location                                 Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
1de83958-c38e-4b9b-9bd3-6c510ce2d3c3     OraDB19000_home1     19.9.0.0.201020                          /u01/app/oracle/product/19.0.0.0/dbhome_1     Configured
c191668f-10de-4184-b22e-b953648bf4a7     OraDB12201_home1     12.2.0.1.201020                          /u01/app/oracle/product/12.2.0.1/dbhome_1     Configured


odacli create-database -dh 'c191668f-10de-4184-b22e-b953648bf4a7' -u ADM001BD_765D -n ADM001BD -r asm -io

odacli describe-job -i b86b28be-0d71-49db-a926-59b98d0c0d6c

Job details
----------------------------------------------------------------
                     ID:  b86b28be-0d71-49db-a926-59b98d0c0d6c
            Description:  Database service creation with db name: ADM001BD
                 Status:  Failure
                Created:  November 19, 2020 9:06:16 AM CET
                Message:  DCS-10001:Internal error encountered: Failed to run sql in method : createTemporaryInstance.Unable to startup standby instance in nomount mode as output conatins ora- :

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Database Service creation                November 19, 2020 9:06:19 AM CET    November 19, 2020 9:07:06 AM CET    Failure
Database Service creation                November 19, 2020 9:06:19 AM CET    November 19, 2020 9:07:06 AM CET    Failure
Setting up ssh equivalance               November 19, 2020 9:06:19 AM CET    November 19, 2020 9:06:19 AM CET    Success
Creating volume datADM001BD              November 19, 2020 9:06:19 AM CET    November 19, 2020 9:06:31 AM CET    Success
Creating volume rdoADM001BD              November 19, 2020 9:06:31 AM CET    November 19, 2020 9:06:43 AM CET    Success
Creating ACFS filesystem for DATA        November 19, 2020 9:06:43 AM CET    November 19, 2020 9:06:54 AM CET    Success
Creating ACFS filesystem for RECO        November 19, 2020 9:06:54 AM CET    November 19, 2020 9:07:05 AM CET    Success
Database Service creation                November 19, 2020 9:07:05 AM CET    November 19, 2020 9:07:06 AM CET    Failure
Auxiliary Instance Creation              November 19, 2020 9:07:05 AM CET    November 19, 2020 9:07:06 AM CET    Failure

Looking in the dcscli.log and dcs-agent.log (under /opt/oracle/dcs/log/) didn’t help. But I found a new init file in the database home:

cat /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initADM001BD.ora
db_name=ADM001BD
db_unique_name=ADM001BD_765D
wallet_root=+DATA/ADM001BD_765D,tde_configuration='KEYSTORE_CONFIGURATION=FILE'db_domain=intranet.ch

Is an instance really able to start on this kind of pfile? Let’s try.

cp /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initADM001BD.ora /tmp/

su - oracle

export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
export ORACLE_SID=ADM001BD
$ORACLE_HOME/bin/sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 19 10:01:20 2020

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

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/initADM001BD.ora';
LRM-00101: unknown parameter name 'wallet_root'
LRM-00101: unknown parameter name 'tde_configuration'
ORA-01078: failure in processing system parameters
exit

No for sure it can’t work. These wallet_root and tde_configuration parameters are for 18c and later databases. Don’t expect it will work on 11.2, 12.1 or 12.2.

Let’s also try to start a 19c instance with this pfile:

export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
$ORACLE_HOME/bin/sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 19 09:58:59 2020
Version 19.9.0.0.0

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

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/initADM001BD.ora';
ORACLE instance started.

Total System Global Area  436204272 bytes
Fixed Size                  8897264 bytes
Variable Size             285212672 bytes
Database Buffers          134217728 bytes
Redo Buffers                7876608 bytes

Yes a 19c instance can start on it.

I also tested an instance-only creation with odacli for a 19c and it worked fine, the init file being the same.

Workaround

As my database is in Failed status, my registry is not very clean:

odacli list-databases

ID                                       DB Name    DB Type  DB Version           CDB        Class    Shape    Storage    Status        DbHomeID      
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
8ad09a72-fbcf-410f-854b-cd8c945fb3de     DBTEST     Si       19.9.0.0.201020      false      Oltp     Odb1s    Asm        Configured   1de83958-c38e-4b9b-9bd3-6c510ce2d3c3
1d0de144-c93c-4f26-b00a-6cbbfafda6dd     ADM001BD   Si       12.2.0.1.201020      false      Oltp     Odb1     Asm        Failed       c191668f-10de-4184-b22e-b953648bf4a7

What you could do is to delete this database (it will be correctly deleted) and create a complete database: it takes longer but it will succeed for whatever version you use:

odacli create-database  -cs WE8ISO8859P15 -no-c -dh 'c191668f-10de-4184-b22e-b953648bf4a7' -u ADM001BD_765D -n ADM001BD -r asm -s odb1s -no-co

When database creation is complete, you need to remove all the files belonging to it, let’s generate a script for that purpose:

su - oracle
. oraenv <<< ADM001BD
sqlplus -s / as sysdba

set pages 0
set lines 200
set serveroutput off
set feedback off
spool /tmp/delete_files.txt
select 'asmcmd rm -rf '||file_name  from dba_data_files;
select 'asmcmd rm -rf '||file_name  from dba_temp_files;
select 'asmcmd rm -rf '||member from v$logfile;
select 'asmcmd rm -rf '||value from v$spparameter where name='control_files';
select 'asmcmd rm -rf '||value from v$parameter where name='spfile';
spool off
exit

su - grid
cat /tmp/delete_files.txt
asmcmd rm -rf +DATA/ADM001BD_765D/DATAFILE/undotbs1.298.1056884325                                                                                                                                
asmcmd rm -rf +DATA/ADM001BD_765D/DATAFILE/system.306.1056884289                                                                                                                                  
asmcmd rm -rf +DATA/ADM001BD_765D/DATAFILE/sysaux.304.1056884317                                                                                                                                  
asmcmd rm -rf +DATA/ADM001BD_765D/DATAFILE/users.309.1056884715                                                                                                                                   
asmcmd rm -rf +DATA/ADM001BD_765D/TEMPFILE/temp.310.1056884383                                                                                                                                    
asmcmd rm -rf +RECO/ADM001BD_765D/ONLINELOG/group_3.2390.1056884377                                                                                                                               
asmcmd rm -rf +RECO/ADM001BD_765D/ONLINELOG/group_2.2389.1056884377                                                                                                                               
asmcmd rm -rf +RECO/ADM001BD_765D/ONLINELOG/group_1.2386.1056884377                                                                                                                               
asmcmd rm -rf +RECO/ADM001BD_765D/CONTROLFILE/current.2385.1056884373                                                                                                                             
asmcmd rm -rf +DATA/ADM001BD_765D/PARAMETERFILE/spfile.308.1056884469   
sh /tmp/delete_files.txt
exit

su - oracle
. oraenv <<< ADM001BD
startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initADM001BD.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area    1073741824 bytes

Fixed Size                     8628936 bytes
Variable Size                293602616 bytes
Database Buffers             763363328 bytes
Redo Buffers                   8146944 bytes

Conclusion

This little bug is a bit disappointing, but there is a workaround. I opened a SR in MOS and will keep you informed if a better workaround is proposed by Oracle.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Jérôme Dubar
Jérôme Dubar

Consultant