dbca -silent -createCloneTemplate -sourceDB mydb01 -templateName minimal_nonmt_seed -maintainFileLocations true -sysDBAPassword mysecretpassword -sysDBAUserName sys -rmanParallelism 2 -dataFileBackup true -datafileDestination /u01/oracle/oradata/19c/assistants/dbca/templates
Thursday, October 5, 2023
How to create a template that includes a seed database using dbca
This is how you can create a new template which will include the database "mydb01" as a seed database for future installations:
Tuesday, October 3, 2023
What to do if your flash recovery area is filled with "foreign archive logs"?
One of my databases came to a halt when the flash recovery area filled completely.
It wasn't a problem with space preassure, but merely a condition that made the flash recovery area *seem* full, when it really wasn't.
To clear the archiver stuck condition is easy enough, simply increase the value of db_recovery_file_dest_size, but how to fix the source of the problem?
My flash recovery area was, at the time, filled up with foreign archive logs:
To clear them out of the flash recovery area, you need to first crosscheck them:
The flash recovery area is not full anymore:
Documented in Doc ID 1617965.1 "What commands may be used to remove foreign archivelog files?" at Oracle Support.
It wasn't a problem with space preassure, but merely a condition that made the flash recovery area *seem* full, when it really wasn't.
To clear the archiver stuck condition is easy enough, simply increase the value of db_recovery_file_dest_size, but how to fix the source of the problem?
My flash recovery area was, at the time, filled up with foreign archive logs:
SYS@CDB$ROOT SQL> select * from v$flash_recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 14.04 0 235 0 BACKUP PIECE .06 0 44 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 0 0 0 0 FOREIGN ARCHIVED LOG 85.8 0 1558 0 AUXILIARY DATAFILE COPY 0 0 0 0 8 rows selected.To fix this, log on to the database using rman:
rman target / nocatalogHere you can list the foreign archivelogs:
list foreign archivelogs all;They were all from may 2023, and they have ended up here since the database was cloned using storage snapshots.
To clear them out of the flash recovery area, you need to first crosscheck them:
RMAN> crosscheck foreign archivelog all;Then, delete them:
RMAN> delete noprompt foreign archivelog all ;The crosscheck is important. Without it, Oracle cannot delete the entries from the controlfile, which means that tha v$flash_recovery_area will still be reported as full.
The flash recovery area is not full anymore:
SYS@CDB$ROOT SQL> select * from v$flash_recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 18.21 0 280 0 BACKUP PIECE .06 0 44 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 0 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0 8 rows selected.
Documented in Doc ID 1617965.1 "What commands may be used to remove foreign archivelog files?" at Oracle Support.
Wednesday, September 27, 2023
Using the dbca General Purpose template and the resulting database
If you execute the dbca GUI and choose the simple configuration, you will be using the "General Purpose" template, which gives you an out-of-the-box database based on a seed database. There is not much you can do to adjust the resulting database to your needs when you select this option.
But, you do have the option to save the response file before the dbca GUI starts the actual creation process. Using the response file instead of the GUI, you have a couple of options to override the use of OMF and the naming of the subdirectories used for data files:
This response file can be used to execute dbca silently, like this:
If dbca is run again without this value, the files will not be of type OMF anymore:
But, you do have the option to save the response file before the dbca GUI starts the actual creation process. Using the response file instead of the GUI, you have a couple of options to override the use of OMF and the naming of the subdirectories used for data files:
The reponse file, after peeling off the comments, contains the following:
Notice how the dbca withholds the value you supply for SYS and SYSTEM passwords, while it prints out the value for datafileDestination and recoveryAreaDestination in the response file for you.
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
gdbName=mydb.skead.no
sid=mydb
databaseConfigType=SI
policyManaged=false
createServerPool=false
force=false
createAsContainerDatabase=false
numberOfPDBs=0
useLocalUndoForPDBs=true
templateName=/u01/oracle/product/19c/assistants/dbca/templates/General_Purpose.dbc
sysPassword=
systemPassword=
runCVUChecks=FALSE
dvConfiguration=false
olsConfiguration=false
datafileJarLocation={ORACLE_HOME}/assistants/dbca/templates/
datafileDestination=/u01/oradata/{DB_UNIQUE_NAME}/
recoveryAreaDestination=/u04/flash_recovery_area/{DB_UNIQUE_NAME}
storageType=FS
characterSet=AL32UTF8
registerWithDirService=false
listeners=LISTENER
skipListenerRegistration=false
variables=ORACLE_BASE_HOME=/u01/oracle/product/19c,DB_UNIQUE_NAME=mydb,ORACLE_BASE=/u01/oracle,PDB_NAME=,DB_NAME=mydb,ORACLE_HOME=/u01/oracle/product/19c,SID=mydb
initParams=undo_tablespace=UNDOTBS1,sga_target=4718MB,db_block_size=8KB,dispatchers=(PROTOCOL=TCP) (SERVICE={SID}XDB),diagnostic_dest={ORACLE_BASE},remote_login_passwordfile=EXCLUSIVE,db_create_file_dest=/data2/oradata/{DB_UNIQUE_NAME}/,audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,processes=320,pga_aggregate_target=1573MB,local_listener=LISTENER_MYDB,db_recovery_file_dest_size=8256MB,open_cursors=300,db_domain=skead.no,compatible=19.0.0,db_name=mydb,db_recovery_file_dest=/data4/flash_recovery_area/{DB_UNIQUE_NAME},audit_trail=db
sampleSchema=false
memoryPercentage=40
databaseType=MULTIPURPOSE
automaticMemoryManagement=false
totalMemory=0
This response file can be used to execute dbca silently, like this:
dbca -silent -createDatabase -responsefile youresponsefilename.rspThis will create the database MYDB with OMF naming:
select file_name from dba_data_files: /data2/oradata/MYDB/datafile/o1_mf_users__vbctspxs_.dbf /data2/oradata/MYDB/datafile/o1_mf_undotbs1__vbcsmzql_.dbf /data2/oradata/MYDB/datafile/o1_mf_system__vbb9h6kc_.dbf /data2/oradata/MYDB/datafile/o1_mf_sysaux__vbcc2o9j_.dbf select member from v$logfile MEMBER -------------------------------------------------- /data2/oradata/MYDB/onlinelog/o1_mf_3__vbdwcyb1_.log /data4/fra/MYDB/onlinelog/o1_mf_3__vbdwxrdx_.log /data2/oradata/MYDB/onlinelog/o1_mf_2__vbdwbqv8_.log /data4/fra/MYDB/onlinelog/o1_mf_2__vbdwxb5f_.log /data2/oradata/MYDB/onlinelog/o1_mf_1__vbdw9lqy_.log /data4/fra/MYDB/onlinelog/o1_mf_1__vbdwtx1r_.logThis is because the response file contains the value
initParams=db_create_file_dest=/data2/oradata/{DB_UNIQUE_NAME}/
If dbca is run again without this value, the files will not be of type OMF anymore:
SYS@mydb>select member from v$logfile; MEMBER ---------------------------------------- /u01/oradata/MYDB/redo03.log /u01/oradata/MYDB/redo02.log /u01/oradata/MYDB/redo01.log SYS@mydb>select member from v$logfile; MEMBER ---------------------------------------- /u01/oradata/MYDB/redo03.log /u01/oradata/MYDB/redo02.log /u01/oradata/MYDB/redo01.logIf you alter the directions
datafileDestination=/u01/oradata/{DB_UNIQUE_NAME}/ recoveryAreaDestination=/data4/flash_recovery_area/{DB_UNIQUE_NAME}to
datafileDestination=/u01/oradata/mydb recoveryAreaDestination=/data4/flash_recovery_area/mydbyou will also be able to override the fact that dbca creates the subdirectories in capital letters by default.
Tuesday, September 26, 2023
What are dbca templates and how do they work?
What are dbca templates?
Are there different types of templates?
Yes:
Why use templates?
Templates can be used just like scripts, but they are more powerful than scripts because you have the option of duplicating a database. Duplication saves time because you copy the files of an existing database, referred to as a seed database, to the correct locations.
When using templates, beware of the following:
1. Database options (for example, Data Vault or Spatial) can be configured or modified only for a custom database or nonseed template (.dbt file).
2. You cannot modify database options for a seed template (includes data file backups).
3. Using a seed template is a much quicker operation, than using a non-seed template, but less flexible.
What templates are delivered with a standard Oracle 19c software installation?
Upon completion of an Oracle 19c software installation, you will find the following files in the directory $ORACLE_HOME/assistants/dbca/templates:
New_Database.dbt
Seed_Database.dfb
Seed_Database.ctl
Data_Warehouse.dbc
General_Purpose.dbc
pdbseed.xml
They are used by the different templates:
Both the Data_Warehouse.dbc and the General_Purpose.dbc are pointing to the Seed_Database.dbf when they are used, which means they will be create a database for you quickly but you will have to accept the options and standard given by the template
Documentation:2.3.5 Managing Templates with DBCA
Database Configuration Assistant (DBCA) templates are XML files that contain information required to create a database.
What are templates used for?
What are templates used for?
Templates are used in DBCA to create new databases and duplicate existing databases.
What information does a template contain?
The information in templates includes database options, initialization parameters, and storage attributes (for data files, tablespaces, control files, and online redo log files).
Are there different types of templates?
Yes:
| Type | File extension | Includes Data Files | Database Structure | Configurable? |
|---|---|---|---|---|
| Seed | .dbc | Yes |
|
No |
| nonseed | .dbt | No | This type of template is used to create a new database. It contains the characteristics of the database to be created. Nonseed templates are more flexible than their seed counterparts because all data files and online redo log files are created to your specification, and names, sizes, and other attributes can be changed as required. |
Yes |
Why use templates?
Templates can be used just like scripts, but they are more powerful than scripts because you have the option of duplicating a database. Duplication saves time because you copy the files of an existing database, referred to as a seed database, to the correct locations.
When using templates, beware of the following:
1. Database options (for example, Data Vault or Spatial) can be configured or modified only for a custom database or nonseed template (.dbt file).
2. You cannot modify database options for a seed template (includes data file backups).
3. Using a seed template is a much quicker operation, than using a non-seed template, but less flexible.
What templates are delivered with a standard Oracle 19c software installation?
Upon completion of an Oracle 19c software installation, you will find the following files in the directory $ORACLE_HOME/assistants/dbca/templates:
They are used by the different templates:
| Template | Workload | Files used |
|---|---|---|
| Data warehouse | Users perform numerous, complex queries that process large volumes of data. | Seed_Database.dbf |
| General Purpose or Transaction processing | Many concurrent users perform numerous transactions that require rapid access to data. | Seed_Database.dbf |
| Custom database | Allows for maximum flexibility in defining a database; you can change any of the settings for the database being created. | New_Database.dbt |
Documentation:
Friday, September 22, 2023
How to remove the Oracle Workspace Manager component from an Oracle database
I have installed a database through dbca, unchecking all the options boxes when going through the setup wizard:
Upon completion, there is still a component left that I do not want to have installed: the Oracle Workspace Manager.
It is registred in the database's registry:
SYS@orcl>SQL>SELECT COMP_NAME,COMP_ID,VERSION,STATUS FROM DBA_REGISTRY; COMP_NAME COMP_ID VERSION STATUS ---------------------------------------- ------------------------------ ------------------------------ ---------------- Oracle Database Catalog Views CATALOG 19.0.0.0.0 VALID Oracle Database Packages and Types CATPROC 19.0.0.0.0 VALID Oracle Real Application Clusters RAC 19.0.0.0.0 OPTION OFF Oracle XML Database XDB 19.0.0.0.0 VALID Oracle Workspace Manager OWM 19.0.0.0.0 VALIDTo remove the OWM component:
SYS@orcl>SQL> @?/rdbms/admin/owmuinst.plb Session altered. PL/SQL procedure executed. PL/SQL procedure executed. Session altered.Option is now deinstalled:
SYS@orcl>SQL>SELECT COMP_NAME,COMP_ID,VERSION,STATUS FROM DBA_REGISTRY; COMP_NAME COMP_ID VERSION STATUS ---------------------------------------- ------------------------------ ------------------------------ ------------------ Oracle Database Catalog Views CATALOG 19.0.0.0.0 VALID Oracle Database Packages and Types CATPROC 19.0.0.0.0 VALID Oracle Real Application Clusters RAC 19.0.0.0.0 OPTION OFF Oracle XML Database XDB 19.0.0.0.0 VALIDWhat exactly is the OWM ?
In a nutshell, it's a version tool for data within a schema. It has been around since Oracle 9i
See documentation here
Particulary the whitepaper
Check out Tim Hall's article on oracle-base.com about the subject.
Thursday, September 14, 2023
How to install an extension in a postgreSQL database
Are the desired extensions available in our installation?
Source : https://www.postgresql.org/docs/current/view-pg-available-extensions.html
Connect to the relevant database and create the extensions in the schema you want them:
Create extension is documented here
[postgres@myserver.no ~]$ echo "SELECT * FROM pg_available_extensions;" | psql | egrep 'cube|earthdistance'; cube | 1.5 | | data type for multidimensional cubes earthdistance | 1.1 | | calculate great-circle distances on the surface of the EarthYes they are.
Source : https://www.postgresql.org/docs/current/view-pg-available-extensions.html
Connect to the relevant database and create the extensions in the schema you want them:
postgres=# \connect mydb1 You are now connected to database "mydb1" as user "postgres". mydb1=# create extension if not exists cube with schema myschema1 cascade; CREATE EXTENSION mydb1=# create extension if not exists earthdistance with schema myschema1 cascade; CREATE EXTENSION mydb1=# exitCheck if they have indeed been installed:
[postgres@myserver.no ~]$ psql mydb01
psql (15.4)
Type "help" for help.
sit1=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+---------------------+------------------------------------------------------------------------
cube | 1.5 | myschema1 | data type for multidimensional cubes
earthdistance | 1.1 | myschema1 | calculate great-circle distances on the surface of the Earth
pg_qualstats | 2.0.4 | public | An extension collecting statistics about quals
pg_stat_statements | 1.10 | public | track planning and execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(5 rows)
Create extension is documented here
Solution for [WARNING] ORA-01081: cannot start already-running ORACLE - shut it down first when running dbca
I received the following error multiple times, when executing dbca like below:
Reason:
Turns out, there were old memory segments left behind that dbca considers a part of a running instance. Even if
Solution:
dbca -createDatabase -responsefile ./cdb.rsp -silent 2023-09-14 15:05:45.435 CEST ] Prepare for db operation DBCA_PROGRESS : 10% [ 2023-09-14 15:05:45.505 CEST ] Copying database files DBCA_PROGRESS : 12% [ 2023-09-14 15:05:47.250 CEST ] [WARNING] ORA-01081: cannot start already-running ORACLE - shut it down first DBCA_PROGRESS : 40% DBCA_PROGRESS : 100% [ 2023-09-14 15:05:47.616 CEST ] [FATAL] Recovery Manager failed to restore datafiles. Refer logs for details. DBCA_PROGRESS : 10% DBCA_PROGRESS : 0%At first I thought it was because I had forgotten to terminate some processes owned by oracle. And I also made sure that /etc/oratab was empty before I started.
Reason:
Turns out, there were old memory segments left behind that dbca considers a part of a running instance. Even if
ps -fu oraclegave no processes in the list.
Solution:
su - oracle sqlplus / as sysdba shutdown abortGiven that you've set ORACLE_SID to the same value you used in your response file, this will properly shut down your instance, and clean up the memory segments left behind from the previous run.
Subscribe to:
Posts (Atom)