foalabs

Shell Script to create a Physical Standby Server for a 19c Oracle Database In this post I am sharing a script which can use useful for automating the creation of a physical standby. Note: Both my server are OCI Compute and we will used opc key for connecting between opc user and then using oracle user for database. Database is CDB-PDB and having 19c Database. Script: Please change script accordingly for your needs. #!/bin/bash #set -x # Database details and configurations PRIMARY_DB_SID="EBSCDB" STANDBY_DB_SID="SEBSCDB" PRIMARY_DB_HOST="db.example.com" STANDBY_DB_HOST="ebs-server2.subnet09191039.vcn09191039.oraclevcn.com" DB_UNIQUE_NAME_PRIMARY="EBSCDB" DB_UNIQUE_NAME_STANDBY="SEBSCDB" PRIMARY_ORACLE_HOME="/u01/install/APPS/19.0.0" PRIMARY_ORACLE_BASE="/u01/install/APPS" STANDBY_ORACLE_HOME="/u01/install/APPS/19.0.0" STANDBY_ORACLE_BASE="/u01/install/APPS" SSH_KEY_PATH="/home/oracle...

Change password in windows RDP session We might encounter error on RDP when using Ctrl + Alt + Del Solution Click Start. Type "osk" and open the " On Screen Keyboard ". Once its open, hold  Ctrl  +  Alt  on your  Physical Keyboard , then click on the  Del  key in the  On Screen Keyboard Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Query to Check for Errors in the Alert Log SELECT      TO_CHAR(originating_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS error_time,     message_text FROM      v$diag_alert_ext WHERE      message_text LIKE '%ORA-%'     AND originating_timestamp >= SYSDATE - 1/24  -- last 1 hour ORDER BY      originating_timestamp DESC; SELECT      originating_timestamp,      message_text FROM      v$diag_alert_ext WHERE      REGEXP_LIKE(message_text, 'ORA-\d{5}')  -- Matches all ORA-XXXXX error messages AND      originating_timestamp >= SYSDATE - (1/24)  -- Retrieves messages from the last 1 hour AND      message_text NOT LIKE 'ORA-0%'  -- Excludes only ORA-0 errors (e.g., ORA-00000) ORDER BY      originating_timestamp DESC; Please do like and subscribe to my youtube channel: https://www.youtube.com/@f...

Compile all Invalids Manually in Oracle Database Script: DECLARE    CURSOR invalid_objects_cur IS       SELECT object_type, object_name, owner       FROM dba_objects       WHERE status = 'INVALID'       AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'VIEW',                            'TRIGGER', 'SYNONYM', 'TYPE', 'TYPE BODY', 'MATERIALIZED VIEW'); BEGIN    FOR obj IN invalid_objects_cur LOOP       BEGIN          -- Compile the invalid object          IF obj.object_type = 'PACKAGE' THEN             EXECUTE IMMEDIATE 'ALTER PACKAGE ' || obj.owner || '.' || obj.object_name || ' COMPILE';          ELSIF obj.object_type = 'PACKAGE BODY' THEN             EXECUTE IMMEDIATE '...

TDE Key Store Management in Oracle Database Once a keystore is created, it cannot be deleted. It is important to test thoroughly before creating one in a production environment. Steps to Set Up a Keystore: Login as sysdba sqlplus / as sysdba Create the Keystore: Create a keystore by setting a password. ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY tde_key#$03; A new folder called tde is created under the wallet_root directory: cd /opt/app/oracle/wallet/ $ ls tde cd tde/ $ ls ewallet.p12 Open the Keystore: Open the keystore with the password: ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY tde_key#$03; Set the Master Key: Set the master key with a backup. ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'master key' IDENTIFIED BY tde_key#$03 WITH BACKUP USING 'masterbackup'; This creates a backup file: ewallet.p12 ewallet_2019080809241127_masterbackup.p12 Create Auto-login for Keystore: Generate an auto-login keystore. ADMINISTER KEY MANAGEMENT CREATE LOCA...

Unplug PDB command fails ORA-46680 Master Keys Of The Container Database Must Be Exported Facing the issue while unplugging PDB after the clone. SQL> alter pluggable database PDB1 unplug into '/u01/app/oracle/19.0.0/dbs/PDB1.xml' * ERROR at line 1: ORA-46680: master keys of the container database must be exported SQL> alter pluggable database pdb1 close; Pluggable database altered. SQL> conn / as sysdba Connected. SQL> alter pluggable database pdb1 unplug into '/u01/app/oracle/19.0.0/dbs/PDB1.xml'; alter pluggable database pdb1 unplug into '/u01/app/oracle/19.0.0/dbs/PDB1.xml' * ERROR at line 1: ORA-46680: master keys of the container database must be exported  This PDB have TDE configured and so the TDE mkeyid have to be exported into .exp file before the PDB unplug. So later during this PDB plug, the mkeyid can be imported from this .exp file into the new container wallet file. Solution: Export the TDE encryption keys of this PDB, before PDB unplug...