dbca -createCloneTemplate -sourceSID source_database_sid | -sourceDB source_database_name -templateName new_database_template_name [-promptForWalletPassword] [-rmanParallelism parallelism_integer_value] [-maxBackupSetSizeInMB maximum_backup_set_size_in_MB] [-dataFileBackup {true | false}] [-datafileJarLocation data_files_backup_directory] [-sysDBAUserName SYSDBA_user_name] [-sysDBAPassword SYSDBA_user_password] [-useWalletForDBCredentials {true | false} -dbCredentialsWalletPassword wallet_account_password -dbCredentialsWalletLocation wallet_files_directory] [-uploadToCloud -opcLibPath OPC_library_path -opcConfigFile OPC_configuration_file_name [-rmanEncryptionPassword rman_encryption_password ] [-compressBackup { true | false } ] [-walletPassword database_wallet_password]Documentation here
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Friday, April 25, 2025
Arguments that can be passed to "dbca -createCloneTemplate" usage output
What is the reason behind the message: "xauth: file /root/.Xauthority does not exist "?
What does the message that you sometimes see when attempting to launch X applications on a Linux server
It means that the .Xauthority file, which stores X11 authentication cookies, does not yet exist in your home directory (in this case, /root/).
This is common when:
X11 forwarding has not been set up or used, yet.
You are logging in as root and have not initialized X11 in that session.
The file was deleted or never created.
To work around the issue, follow these steps:
1. Make sure xauth is installed
If these work, your X11 forwarding is properly set up, and .Xauthority will be created automatically.
6. If you need to manually create the .Xauthority file
This is rarely necessary, but you can run:
"xauth: file /root/.Xauthority does not exist"really mean?
It means that the .Xauthority file, which stores X11 authentication cookies, does not yet exist in your home directory (in this case, /root/).
This is common when:
To work around the issue, follow these steps:
1. Make sure xauth is installed
# RHEL/Centos su - yum install xauth #Debian apt install xauth2. Make sure you use the -X flag (X forwarding) when connecting to the server
ssh -X root@yourserver3. On the server, edit the file /etc/ssh/sshd_config, and make sure these lines exist and are set to yes
X11Forwarding yes X11UseLocalhost yes4. Restart ssh daemon
systemctl restart sshd5. Try launching a graphical app, like xterm
If these work, your X11 forwarding is properly set up, and .Xauthority will be created automatically.
6. If you need to manually create the .Xauthority file
This is rarely necessary, but you can run:
touch ~/.Xauthority xauth generate :0 . trusted
Wednesday, April 23, 2025
How to create a database with non-default blocksize using dbca
Prerequisites:
You have a valid Oracle 19c installation in a server
You have set your Oracle environment already, so that your PATH, ORACLE_SID etc already exists when you execute your script
1. create a response file called $ORACLE_SID.rsp, in this case, tstdwh1.rsp:
Also notice the absence of the directive "databaseType" - it is only meaningful if you let DBCA choose a built-in template based on that type (like OLTP or Data Warehouse).
2. execute the dbca directly in the shell:
1. create a response file called $ORACLE_SID.rsp, in this case, tstdwh1.rsp:
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0 gdbName=tstdwh1.oric.no sid=tstdwh1 databaseConfigType=SI policyManaged=false createServerPool=false force=false createAsContainerDatabase=false templateName=/u01/oracle/product/19c/assistants/dbca/templates/New_Database.dbt sysPassword=mysecreatpassword systemPassword=mysecreatpassword datafileJarLocation={ORACLE_HOME}/assistants/dbca/templates/ datafileDestination=/data02/oradata/{DB_UNIQUE_NAME}/ recoveryAreaDestination=/data04/fra/{DB_UNIQUE_NAME} storageType=FS characterSet=AL32UTF8 nationalCharacterSet=AL16UTF16 registerWithDirService=false listeners=LISTENER skipListenerRegistration=false variables=ORACLE_BASE_HOME=/u01/oracle/product/19c,DB_UNIQUE_NAME=tstdwh1,ORACLE_BASE=/u01/oracle,PDB_NAME=,DB_NAME=tstdwh1,ORACLE_HOME=/u01/oracle/product/19c,SID=tstdwh1 initParams=undo_tablespace=UNDOTBS1,sga_target=4710MB,db_block_size=32768BYTES,nls_language=NORWEGIAN,dispatchers=(PROTOCOL=TCP) (SERVICE=tstdwh1XDB),diagnostic_dest={ORACLE_BASE},control_files=("{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl", "/u04/fra/{DB_UNIQUE_NAME}/control02.ctl"),remote_login_passwordfile=EXCLUSIVE,audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,processes=400,pga_aggregate_target=1570MB,nls_territory=NORWAY,local_listener=LISTENER_TSTDWH1,db_recovery_file_dest_size=7851MB,open_cursors=300,log_archive_format=%t_%s_%r.dbf,db_domain=oric.no,compatible=19.0.0,db_name=tstdwh1,db_recovery_file_dest=/u04/fra/{DB_UNIQUE_NAME},audit_trail=db sampleSchema=false memoryPercentage=40 automaticMemoryManagement=falseNotice the parameter db_block_size=32768BYTES in the "variables" directive, and the fact that I am using the New_Database.dbt template. This template is more flexible than the others delivered by Oracle, but makes the whole database creation process take more time, as they do not include any databaes files.
Also notice the absence of the directive "databaseType" - it is only meaningful if you let DBCA choose a built-in template based on that type (like OLTP or Data Warehouse).
2. execute the dbca directly in the shell:
dbca -createDatabase -responsefile ./${ORACLE_SID}.rsp -silentOr, create a wrapper around the command, make it executable and execute it in the background:
#!/bin/bash start=$(date +%s.%N) export GLOGFILE=run_dbca.log touch ${GLOGFILE} chmod 666 ${GLOGFILE} exec 1> ${GLOGFILE} 2>&1 echo "Now running run_dbca.sh" echo "Resetting oratab..." echo '' > /etc/oratab if [ $? -ne 0 ]; then echo "Could not erase oratab. Exit." exit 1 else echo "oratab erased. Continuing..." fi export instanceAlive=`ps -ef| grep pmon | grep -v grep |awk '{ print $8}' | cut -f 3 -d"_"` if [ ! -z ${instanceAlive} ]; then echo "Instance for database $ORACLE_SID is already running. Shut it down first" exit 1 fi dbca -createDatabase -responsefile ./${ORACLE_SID}.rsp -silent echo "Finished running run_dbca.sh" dur=$(echo "$(date +%s.%N) - $start" | bc) printf "Total execution time for run_dbca.sh: %.6f seconds\n" $dur exit 0
chmod 755 run_dbca.sh nohup ./run_dbca.sh &
Friday, April 11, 2025
Solution to "ORA-12546: TNS:permission denied" when starting sqlplus
Problem: when trying to start sqlplus, the error below is thrown:
It needs the setuid permissions. Without those permissions, it cannot open sockets or shared memory properly — and the ORA-12546 is thrown when trying to connect via sqlplus.
Solution: Relink the oracle binary
ORA-12546: TNS:permission deniedCause: Incorrect permissions on the oracle binary.
It needs the setuid permissions. Without those permissions, it cannot open sockets or shared memory properly — and the ORA-12546 is thrown when trying to connect via sqlplus.
Solution: Relink the oracle binary
oracle@myserver:[cdb]# ls -l $ORACLE_HOME/bin/oracle -rw-r--r-- 1 oracle dba 0 Jan 24 09:57 /u01/oracle/product/19c/bin/oracle oracle@myserver:[cdb]# cd $ORACLE_HOME/rdbms/lib oracle@myserver:[cdb]# make -f ins_rdbms.mk ioracleAfterwards, permissions are correct:
oracle@myserver:[cdb]# ls -l $ORACLE_HOME/bin/oracle -rwsr-s--x 1 oracle dba 462603312 Apr 11 10:54 /u01/oracle/product/19c/bin/oracle
Wednesday, April 9, 2025
How to mimic sqlplus variable behaviour in PostgreSQL
Here is how you can mimic the usage of variables at the prompt using psql and PostgreSQL like you would with oracle and sqlplus/sqlc:
\connect mydb \prompt 'Enter value for querid: ' v_quid \echo You entered: :v_quid SELECT query_sql_text FROM query_store.query_texts_view WHERE query_text_id = :'v_quid';Run it:
sql -h pgserver1.oric.no -U dbadmin -d postgres -f find_rest_of_statement.sql Enter value for querid: -7644745457188850826 You entered: -7644745457188850826 query_sql_text ---------------------------------------------------------------------------------------------------------------------- THE QUERY TEXT WILL SHOW UP HERE :-)
Wednesday, April 2, 2025
How to check the number of rows inserted into the different partitions in a PostgreSQL partitioned table
Create the table:
CREATE TABLE mytable ( id bigserial, key text COLLATE pg_catalog."default" NOT NULL, outdata jsonb, partno integer, koffset bigint, handled_at timestamp with time zone, inserted_at timestamp with time zone NOT NULL DEFAULT now(), kyear integer NOT NULL, -- Ensure it's NOT NULL to work properly with partitions nk boolean NOT NULL, CONSTRAINT mytable_pkey PRIMARY KEY (id,kyear) ) PARTITION BY RANGE (kyear);Create the partitions. Make it suitable for ranges that adhere to the standard YYYYMM:
CREATE TABLE mytable_202501 PARTITION OF mytable FOR VALUES FROM (202501) TO (202502); CREATE TABLE mytable_202502 PARTITION OF mytable FOR VALUES FROM (202502) TO (202503);Insert some test values:
INSERT INTO mytable (key, outdata, partno, koffset, handled_at, kyear, nk) VALUES ('A123', '{"data": "test1"}', 101, 500, NOW(), 202501, TRUE), ('B456', '{"data": "test2"}', 102, 600, NOW(), 202501, FALSE), ('C789', '{"data": "test3"}', 103, 700, NOW(), 202501, TRUE); INSERT INTO mytable (key, outdata, partno, koffset, handled_at, kyear, nk) VALUES ('D111', '{"data": "test4"}', 104, 800, NOW(), 202502, FALSE), ('E222', '{"data": "test5"}', 105, 900, NOW(), 202502, TRUE), ('F333', '{"data": "test6"}', 106, 1000, NOW(), 202502, FALSE);Confirm that the data is indeed there:
select * from mytable;Finally, verify that the rows have been commited to two different partitions:
musicdb=> SELECT relname, n_tup_ins musicdb-> FROM pg_stat_all_tables musicdb-> WHERE relname IN ('mytable_202501', 'mytable_202502'); relname | n_tup_ins ----------------+----------- mytable_202501 | 3 mytable_202502 | 3 (2 rows)
Wednesday, March 5, 2025
How to manually purge your standard audit trail
In this post I will show you how you can manage your audit trail using the package dbms_audit_mgmt.
It can be used for all kinds of audit trails, both OS, XML, unified and standard auditing.
You refer to them using the constants for audit_trail_types, found here
In this post, I am using the DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
Note: If you simply want to purge the entire audit trail, use the procedure below:
BEGIN DBMS_AUDIT_MGMT.clean_audit_trail( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, use_last_arch_timestamp => FALSE); END; /Be careful:
Setting the use_last_arch_timestamp to FALSE will effectlivly do a TRUNCATE TABLE SYS.AUD$ behind the scenes and takes only a couple of minutes to execute, even with millions of rows to be purged. You may lose your audit data, which can have legal consequences.
If desirable, audit trails can be cleaned based on the "Last Archive Timestamp" value.
The Last Archive Timestamp represents the timestamp of the most recent audit record that was securely archived:
SQL> SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS; no rows selectedNo rows returned from this query means that there has been no previous cleanup performed.
In cases where you want to keep some of your audit data, you can manually set the "Last Archive Timestamp" to a value of your choice.
Here I set it to a month ago from today:
BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP ( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, LAST_ARCHIVE_TIME => SYSTIMESTAMP-30); END; /
If you try to use the SET_LAST_ARCHIVE_TIMESTAMP procedure before cleanup has been initialized, you will receive error
ERROR at line 1: ORA-46258: Cleanup not initialized for the audit trail ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 61 ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 2283 ORA-06512: at line 17
So make sure to initialize the cleanup first. Note that "initialization" will automatically relocate the AUD$ table to the SYSAUX tablespace.
Use the IS_CLEANUP_INITIALIZED procedure to verify the state of the cleanup before and after the the INIT_CLEANUP has been executed.
Put the following in a file called check_init_status.sql:
SET SERVEROUTPUT ON BEGIN IF DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN DBMS_OUTPUT.PUT_LINE('Cleanup is initialized.'); ELSE DBMS_OUTPUT.PUT_LINE('Cleanup is not initialized.'); END IF; END; /Check status:
sqlplus / as sysdba@check_init_status.sql Cleanup is not initialized. PL/SQL procedure executed.To initialize cleanup of the standard audit trail, put the following in a file called init_cleanup.sql:
BEGIN DBMS_AUDIT_MGMT.INIT_CLEANUP ( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, default_cleanup_interval => 48); END; /Initialize cleanup:
sqlplus / as sysdba@init_cleanup.sqlCheck status again:
sqlplus / as sysdba@check_init_status.sql Cleanup is initialized. PL/SQL procedure executed.
Note that you need sufficient space in the SYSAUX tablespace to proceed. Autoextend on the datafiles will not help, there need to be sufficiently allocated space in advance:
ERROR at line 1: ORA-46267: Insufficient space in 'SYSAUX' tablespace, cannot complete operation ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1087 ORA-06512: at line 3
How many rows do we have in our standard audit trail?
select count(*) from sys.aud$;
"COUNT(*)" |
---|
141 362 956 |
How old is that data?
select min(ntimestamp#) "mindate", max(ntimestamp#) "maxdate" from sys.aud$;
"mindate" | "maxdate" |
---|---|
21.11.2016 08:29:16,325777 | 04.02.2020 14:08:49,660074 |
So our data dates back to 2016. Time to clean up.
The "use_last_arch_timestamp" make sure I will preserve audit statements more recent than one month:
BEGIN DBMS_AUDIT_MGMT.clean_audit_trail( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, use_last_arch_timestamp => TRUE); END; /Let's check the status after the execution of the clean_audit_trail-procedure: How many rows do we have left in our standard audit trail?
select count(*) from sys.aud$;
"COUNT(*)" |
---|
2 356 049 |
How old is that data?
select min(ntimestamp#) "mindate", max(ntimestamp#) "maxdate" from sys.aud$;
"mindate" | "maxdate" |
---|---|
04.03.2020 08:09:01,325371 | 04.02.2020 14:08:49,660074 |
Subscribe to:
Posts (Atom)