Dears,
In this blog, we will see how we can remove all but keep only mandatory components of the oracle database. This silent mode command is very useful for automation and setting up dbs without using xterm.
Command:
dbca -silent -createDatabase -templateName New_Database.dbt \
-gdbname DBCASLNT \
-sid DBCASLNT \
-responseFile NO_VALUE \
-sysPassword ora19c \
-systemPassword ora19c \
-createAsContainerDatabase false \
-databaseType MULTIPURPOSE \
-automaticMemoryManagement false \
-totalMemory 2000 \
-storageType FS \
-datafileDestination /oradata \
-useOMF true \
-redoLogFileSize 50 \
-emConfiguration NONE \
-dbOptions OMS:false,JSERVER:false,SPATIAL:false,IMEDIA:false,ORACLE_TEXT:false,CWMLITE:false,SAMPLE_SCHEMA:false,APEX:false,DV:false \
-initParams db_block_size=8192 \
-characterSet AL32UTF8 \
-nationalCharacterSet AL16UTF16 \
-ignorePreReqs
-gdbname DBCASLNT \
-sid DBCASLNT \
-responseFile NO_VALUE \
-sysPassword ora19c \
-systemPassword ora19c \
-createAsContainerDatabase false \
-databaseType MULTIPURPOSE \
-automaticMemoryManagement false \
-totalMemory 2000 \
-storageType FS \
-datafileDestination /oradata \
-useOMF true \
-redoLogFileSize 50 \
-emConfiguration NONE \
-dbOptions OMS:false,JSERVER:false,SPATIAL:false,IMEDIA:false,ORACLE_TEXT:false,CWMLITE:false,SAMPLE_SCHEMA:false,APEX:false,DV:false \
-initParams db_block_size=8192 \
-characterSet AL32UTF8 \
-nationalCharacterSet AL16UTF16 \
-ignorePreReqs
Output:
[oracle@vcentos79-oracle-sby1 dba]$ dbca -silent -createDatabase -templateName New_Database.dbt \
> -gdbname DBCASLNT \
> -sid DBCASLNT \
> -responseFile NO_VALUE \
> -sysPassword ora19c \
> -systemPassword ora19c \
> -createAsContainerDatabase false \
> -databaseType MULTIPURPOSE \
> -automaticMemoryManagement false \
> -totalMemory 2000 \
> -storageType FS \
> -datafileDestination /oradata \
> -useOMF true \
> -redoLogFileSize 50 \
> -emConfiguration NONE \
> -dbOptions OMS:false,JSERVER:false,SPATIAL:false,IMEDIA:false,ORACLE_TEXT:false,CWMLITE:false,SAMPLE_SCHEMA:false,APEX:false,DV:false \
> -initParams db_block_size=8192 \
> -characterSet AL32UTF8 \
> -nationalCharacterSet AL16UTF16 \
> -ignorePreReqs
[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
Prepare for db operation
10% complete
Creating and starting Oracle instance
13% complete
14% complete
20% complete
Creating database files
21% complete
30% complete
Creating data dictionary views
33% complete
39% complete
40% complete
41% complete
42% complete
48% complete
51% complete
54% complete
60% complete
Completing Database Creation
66% complete
69% complete
70% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
/u01/app/oracle/cfgtoollogs/dbca/DBCASLNT.
Database Information:
Global Database Name:DBCASLNT
System Identifier(SID):DBCASLNT
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/DBCASLNT/DBCASLNT.log" for further details.
[oracle@vcentos79-oracle-sby1 dba]$
Time taken: 30minutes (we chose custom template, so it is normal).
Verify db creation:
Instance & DB names created as per dbca command line inputs:
SQL> select name,open_mode,cdb from v$database;
NAME OPEN_MODE CDB
--------- -------------------- ---
DBCASLNT READ WRITE NO
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ----------
DBCASLNT OPEN
db_block_size created as per dbca command line inputs:
SQL> show parameter block_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
Components we disabled in dbca command line input werent created:
SQL> select comp_id,comp_name,status,version from dba_registry order by 1;
COMP_ID COMP_NAME STATUS VERSION
--------------- ---------------------------------------- ---------- ------------------------------
CATALOG Oracle Database Catalog Views VALID 19.0.0.0.0
CATPROC Oracle Database Packages and Types VALID 19.0.0.0.0
OWM Oracle Workspace Manager VALID 19.0.0.0.0
RAC Oracle Real Application Clusters OPTION OFF 19.0.0.0.0
XDB Oracle XML Database VALID 19.0.0.0.0
SQL>
NLS settings (char & nchar created as per our input):
SQL> select parameter,value from nls_database_parameters order by 1;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET AL32UTF8 <<<<
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CHARACTERSET AL16UTF16 <<<<
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_RDBMS_VERSION 19.0.0.0.0
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
20 rows selected.
SGA/PGA setting as per our memorytotal setting:
SQL> sho parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
...
sga_max_size big integer 1504M
...
sga_target big integer 1504M
unified_audit_sga_queue_size integer 1048576
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 2G
pga_aggregate_target big integer 500M
SQL>
List of default users created from this dbca command: Notice all the accounts are oracle maintained accounts.
USERNAME ACCOUNT_STATUS CREATED LAST_LOGIN EXPIRY_DATE LOCK_DATE O
------------------------------ ------------------------------ ------------------- --------------------------------------------------------------------------- ------------------- ------------------- -
AUDSYS LOCKED 22/05/2023 21:53:46 22/05/2023 22:17:45 Y
SYS OPEN 22/05/2023 21:53:46 18/11/2023 21:53:46 Y
SYSBACKUP LOCKED 22/05/2023 21:53:46 22/05/2023 22:17:45 Y
SYSDG LOCKED 22/05/2023 21:53:46 22/05/2023 22:17:45 Y
SYSKM LOCKED 22/05/2023 21:53:46 22/05/2023 22:17:45 Y
SYSRAC LOCKED 22/05/2023 21:53:46 22/05/2023 22:17:45 Y
SYSTEM OPEN 22/05/2023 21:53:46 22-MAY-23 10.17.44.000000000 PM +01:00 18/11/2023 21:53:46 Y
OUTLN LOCKED 22/05/2023 21:53:47 22/05/2023 22:17:45 Y
GSMADMIN_INTERNAL LOCKED 22/05/2023 21:56:09 22/05/2023 22:17:45 Y
GSMUSER LOCKED 22/05/2023 21:56:09 22/05/2023 22:17:45 Y
DIP LOCKED 22/05/2023 21:56:20 22/05/2023 21:56:20 Y
XS$NULL EXPIRED & LOCKED 22/05/2023 21:56:54 22/05/2023 21:56:54 22/05/2023 21:56:54 Y
DBSFWUSER LOCKED 22/05/2023 21:57:05 22/05/2023 22:17:45 Y
REMOTE_SCHEDULER_AGENT LOCKED 22/05/2023 21:57:05 22/05/2023 22:17:45 Y
ORACLE_OCM LOCKED 22/05/2023 21:58:15 22/05/2023 22:17:45 Y
SYS$UMF LOCKED 22/05/2023 22:05:12 22/05/2023 22:17:45 Y
DBSNMP LOCKED 22/05/2023 22:10:10 22/05/2023 22:17:45 Y
APPQOSSYS LOCKED 22/05/2023 22:10:12 22/05/2023 22:17:45 Y
GSMCATUSER LOCKED 22/05/2023 22:10:30 22/05/2023 22:17:45 Y
GGSYS LOCKED 22/05/2023 22:10:34 22/05/2023 22:17:45 Y
ANONYMOUS EXPIRED & LOCKED 22/05/2023 22:12:10 22/05/2023 22:17:45 22/05/2023 22:17:45 Y
XDB LOCKED 22/05/2023 22:12:10 22/05/2023 22:17:45 Y
WMSYS LOCKED 22/05/2023 22:17:03 22/05/2023 22:17:45 Y
23 rows selected.
OMF was used for dbfile, redolog and controlfile:
SQL> select name,value from v$parameter where name = 'spfile';
NAME VALUE
------------------------------------------------------------ ------------------------------------------------------------
spfile /u01/app/oracle/product/19.0.0/db_1/dbs/spfileDBCASLNT.ora
SQL> select tablespace_name,file_id,file_name,round(bytes/1024/1024),round(maxbytes/1024/1024) from dba_Data_files order by 1,2;
TABLESPACE FILE_ID FILE_NAME ROUND(BYTES/1024/1024) ROUND(MAXBYTES/1024/1024)
---------- ---------- ------------------------------------------------------------ ---------------------- -------------------------
SYSAUX 2 /oradata/DBCASLNT/datafile/o1_mf_sysaux_l6qolmcj_.dbf 550 32768
SYSTEM 1 /oradata/DBCASLNT/datafile/o1_mf_system_l6qolcog_.dbf 700 32768
UNDOTBS1 3 /oradata/DBCASLNT/datafile/o1_mf_undotbs1_l6qolqqf_.dbf 240 32768
USERS 4 /oradata/DBCASLNT/datafile/o1_mf_users_l6qom9gz_.dbf 5 32768
SQL> select * from v$controlfile order by 1;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS CON_ID
------- ------------------------------------------------------------ --- ---------- -------------- ----------
/oradata/DBCASLNT/controlfile/o1_mf_l6qol8wd_.ctl NO 16384 646 0
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ------------------------------------------------------------ --- ----------
1 ONLINE /oradata/DBCASLNT/onlinelog/o1_mf_1_l6qol99j_.log NO 0
2 ONLINE /oradata/DBCASLNT/onlinelog/o1_mf_2_l6qol9lf_.log NO 0
3 ONLINE /oradata/DBCASLNT/onlinelog/o1_mf_3_l6qol9tr_.log NO 0
SQL>
All the attributes were created as we requested. We verified only the attributes we called out to be controlled.
2nd round validation:
Compared the template created from custom db template against the template created from the db created from custom template.
The OFA file structures were diffferent (since I allowed oracle to choose OFA for template creation from preexisting db) + few other param like db_domain, local_listener, controlfile paths were different, but I guess they were expected in such comparision. Since the template from template lacks real file locations vs the template from db has file locations.
Rest all are were looking good.
This closes the blog.
YouTube video:
Thank you!
No comments:
Post a Comment