Creating multiple databases with dbca

 Asked to create a number of oracle dbs all the same version, all the same.

Manually ran the dbca and saved the response file.

Edited the response file, changed the sys and system password entries and globally replaced the db name so could run the dbca from the command line and let it just do its thing. 

Remember g/^#/d and g/^$/d will replace the commented / blank lines in vi if you want to make the file easier to read.

$ORACLE_HOME/bin/dbca -silent -createDatabase -responseFile /home/oracle/dbca_oradb2.rsp

Did not want to do this for every db so put this together, bit quick and dirty but seems to do the trick.

In the response file changed the database name to dummy and let sed replace it with the correct name; tried to do this with unix variables but dbca would not pick the correct value and was up against the clock so did it this way for quickness.

#!/bin/sh

oracle_env()

{
ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1; export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin; export PATH
}

oracle_env

array=(oradb1 oradb2 oradb3 oradb4 oradb5 oradb6)

for i in "${array[@]}" ; do
ORACLE_SID=${i}; export ORACLE_SID
echo $ORACLE_SID

sed "s/dummy/$i/g" dbca.rsp > dbca_$i.rsp

$ORACLE_HOME/bin/dbca -silent -createDatabase -responseFile /home/oracle/dbca_$i.rsp

done

When finished you can delete the dbs in the same way.

dbca -silent -deleteDatabase -sourceDB oradb2 -sysDBAUserName sys -sysDBAPassword Password#2020

As always please feel free to improve and let me know.


No comments:

Downgrade 19c to 12c


Not something I expected to do but was asked to download some 19c dbs after the dev lot hit an issue with 19 and wanted to go back to 12!

Some quick notes  as I don't expect ever to do this again - maybe 😉

[oracle@ora02 dbs]$ . oraenv

Pick the correct 19c databases

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@ora02 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 26 09:06:05 2020

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> create pfile='$ORACLE_HOME/dbs/initcms.ora' from spfile ** change as approp.

2 /

File created.

Shutdown immediate and change init file removing the __unified pga line


Copy init’dbname’.ora to 12c home/dbs/init’dbname’.ora - 12c will need this to startup

e.g.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

cp $ORACLE_HOME/dbs/initdb1.ora /u01/app/oracle/product/12.2.0/dbhome_1/dbs/.

Then

SQL> startup downgrade;

ORACLE instance started.

Total System Global Area 2147481656 bytes

Fixed Size 8898616 bytes

Variable Size 553648128 bytes

Database Buffers 1577058304 bytes

Redo Buffers 7876608 bytes

Database mounted.

Database opened.


SQL> set termout on serverout on echo on timing on

SQL> spool /home/oracle/downgrade_db1.log

Run the grant admin and the clean audit or you will see error when running the


ERROR at line 1:

ORA-20001: Downgrade cannot proceed - Unified Audit Trail data exists.Please

clean up the data first using DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL.

ORA-06512: at line 75


SQL> grant ADMINISTER DATABASE TRIGGER to SYSTEM;

Grant succeeded.

SQL> exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp => FALSE);

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/catdwgrd.sql

Approx 5 mins…

…….

SQL>

SQL> Rem ***********************************************************************

SQL> Rem END catdwgrd.sql

SQL> Rem ***********************************************************************

SQL>

SQL>shutdown immediate;

SQL> exit


When migrating to 19c the timezone had been upgraded so had to do this to downgrade or hit issues.

export ORA_TZFILE=/u01/app/oracle/product/12.2.0/dbhome_1/oracore/zoneinfo/timezone_26.dat

set environment to Oracle 12 and change sid to the correct database i.e. db1

oracle@ora02 dbs]$ export

ORA_TZFILE=/u01/app/oracle/product/12.2.0/dbhome_1/oracore/zoneinfo/timezone_26.dat

[oracle@usze2qmbtora02 dbs]$ sqlplus / as sysdba

exit

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 26 09:51:18 2020

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

Connected to an idle instance.

SQL> startup upgrade;

Remember you need to use the 19c init.file copied earlier with the __unified parameter removed or you will hit

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initdb1.ora'

SQL> startup upgrade pfile='/u99/media/init_db1.ora'

LRM-00101: unknown parameter name '__unified_pga_pool_size'

ORA-01078: failure in processing system parameters

SQL> startup upgrade -> need init file copied earlier

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 8622776 bytes

Variable Size 654314824 bytes

Database Buffers 1476395008 bytes

Redo Buffers 8151040 bytes

Database mounted.

Database opened.

SQL>

SQL> set termout on echo on timing on

SQL>@?/rdbms/admin/catrelod.sql

Check that this correctly

@?/rdbms/admin/utlrp.sql


SELECT comp_name, status, substr(version,1,10) as version

from dba_server_registry order by modified


COMP_NAME STATUS
----------------------------------- ----------------------------------------

VERSION
----------------------------------------

Oracle Real Application Clusters OPTION OFF

12.2.0.1.0

Oracle Database Catalog Views VALID

12.2.0.1.0

Oracle Database Packages and Types VALID

12.2.0.1.0

Oracle XML Database VALID

12.2.0.1.0

Oracle Workspace Manager VALID

12.2.0.1.0

JServer JAVA Virtual Machine VALID

12.2.0.1.0

Oracle Database Java Packages VALID

12.2.0.1.0

Oracle Text VALID

12.2.0.1.0

Oracle Multimedia VALID

12.2.0.1.0

Oracle XDK VALID

12.2.0.1.0

OLAP Analytic Workspace VALID

12.2.0.1.0

Spatial VALID

12.2.0.1.0

Oracle OLAP API VALID

12.2.0.1.0

Oracle Label Security VALID

12.2.0.1.0

Oracle Database Vault VALID

12.2.0.1.0


15 rows selected.


Shutdown - (change oratab to v12)

Set environment to grid 19c

. oraenv

+ASM

Remove database from srvctl


[oracle@usze2qmbtora02 ~]$ srvctl remove database -d ora01 

This will remove it from the /etc/oratab so manually put it back as 12

Now we need add the database back as 12c and start it as 12c

Still using the grid 19c environment (19c grid to 12 for another day!)

[oracle@usze2qmbtora02 ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/bin/srvctl add database -d oradb1 -o /u01/app/oracle/product/12.2.0/dbhome_1

[oracle@usze2qmbtora02 ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/bin/srvctl start database -db oradb1 

Run “crsctl stat res -t” and check the db is up and running – shows as 12c but under a 19c grid control - bit odd but hey ho. 

After some research found this :

1 comment:
Subscribe to: Posts (Atom)