DBA Maestro - Helpful DBA tips

Kerberos Authentication to Oracle Database using Active Directory ================================================================= This guide provides step-by-step instructions to configure Kerberos-based authentication using Active Directory for Oracle databases, including all relevant OS, Oracle, and network settings for successful authentication between Windows clients and Oracle Database on Linux. Environment Overview -------------------- Database Server: - OS: Oracle Linux Server release 7.9 - Oracle Version: 19.28.0.0.0 - PDB Name: APPPDB - Hostname: dbserver01.example.com - Keytab Path: $ORACLE_HOME/network/admin/oracle.keytab Active Directory: - Realm: EXAMPLE.COM - AD Domain Controller: dc01.example.com - Oracle AD Service Account: svc_oracle_kerberos Client Workstation: - OS: Windows 11 Enterprise - Kerberos Implementation: MIT Kerberos for Windows - AD Username: dbuser1@EXAMPLE.COM SECTION 1: Active Directory Configuration ----------------------------------------- 1. AD Adm...

  Hey Oracle DBAs! It's been a while, Oracle community! But I'm back with a helpful post. :-) Today, I faced an issue while performing an RMAN duplicate to build out a standby database (Oracle version 19.25) on an ExaCS VM. The database is 110 TB in size. I was doing an active duplicate over the WAN for 2 days. Everything was going smoothly until it reached ~94% completion... and then it FAILED!! I didn't want to risk restarting the entire RMAN duplicate process again out of concern that the same issue would occur. Here's my error: RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 04/10/2025 09:57:48 RMAN-05501: aborting duplication of target database RMAN-03015: error occurred in stored script Memory Script ORA-19875: multisection restore or copy not complete ...

I needed to extend a LV as it was small for what I needed to do. As you can see below, it was originally 20G in size. As root, I did the following steps: # df -h /u01 Filesystem Size Used Avail Use% Mounted on /dev/mapper/vg00-lvU01 20G 9.1G 11G 46% /u01 1. First, I had to check for free size on the Volume Group. # vgdisplay --- Volume group --- VG Name vg00 System ID Format lvm2 Metadata Areas 1 Metadata Sequence No 8 VG Access read/write VG Status resizable MAX LV 0 Cur LV 7 Open LV 7 Max PV 0 Cur PV 1 Act PV 1 VG Size <222.02 GiB PE Size 4.00 MiB Total PE 56836 Alloc PE / Size 20480 / 80.00 GiB Free PE / Size 36356 / <142.02 GiB VG UUID poBYqN-t5P3-t6rE-UfeI-eaXj-OoMe-l1a39z 2. From above exam...

I imported newly built OEL 7.7 VM from Oracle VirtualBox to an ODA (Oracle Database Appliance). Upon starting up the VM, the network was showing as being up for device "eth0" however the /etc/sysconfig/network-scripts/ifcfg-eth0 was existent at the moment. I restarted the network service but received the "Failed to start LSB: Bring up/down networking" error. I followed the below steps to resolve this error. 1.  Stop and disable the NetworkManager service.      # systemctl stop NetworkManager.service      # systemctl disable NetworkManager.service 2.  Enable the network service.      # systemctl enable network.service 3.  Create the ifcfg-eth0  and populate with the appropriate information.      # vi /etc/sysconfig/network-scripts/ifcfg-eth0 contents: TYPE=Ethernet BOOTPROTO=static NAME=eth0 DEVICE=eth0 ONBOOT=yes IPADDR=<IP_ADDRESS> NETMASK=<SUBNET_MAS...

I was trying to create an Oracle Data Guard environment (physical standby) from an Oracle database running version 12.1.0.2. The database was running an older PSU version ("Database Patch Set Update : 12.1.0.2.161018 (24006101)") to be specific. Upon trying to build the standby by using RMAN DUPLICATE with ACTIVE DATABASE, I ran into the below error: executing Memory Script Starting backup at 16-JUL-18 RMAN-03009: failure of backup command on prm1 channel at 07/16/2018 13:51:46 ORA-17629: Cannot connect to the remote database server ORA-17630: Mismatch in the remote file protocol version client 2 server 3 continuing other job steps, job failed will not be re-run released channel: prm1 released channel: prm2 released channel: prm3 released channel: prm4 released channel: prm5 released channel: stb1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: ====================...

There wasn't any helpful blog entries out there for migrating a virtualized ODA VM that contains vdisks to another virtualized ODA.  Below is what I used in order to migrate the VMs over.  I'm using ACFS snapshots in order to successfully migrate my VMs. Always remember to test, test, and test before using this on a production ODA/VM. 1.  Execute the "oakcli show vm <vm_name>" in order to see the files that make-up the VM. [root@oda1dom0] # oakcli show vm TESTVM The Resource is : TESTVM         AutoStart       :       never         CPUPriority     :       100         Disks           :       |file:/OVS/Repositories/demorepo/.A                                 CFS/snaps/TESTVM/VirtualMachine ...

If this error is coming in Oracle database, then take downtime and do the following:  Drop the queue table - exec dbms_aqadm.drop_queue_table( queue_table=>'SYS.SYS$SERVICE_METRICS_TAB', force => TRUE) select object_name, object_type from dba_objects where object_name like '%SYS$SERVICE_%'; ----> no rows should be returned. Follow the below steps to recreate the queue table. SQL>STARTUP SQL>alter system enable restricted session; SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql SQL>alter system disable restricted session;

I was attempting to restore a backup via RMAN but received the following errors: RMAN-06023: no backup or copy of datafile 11 found to restore RMAN-06023: no backup or copy of datafile 10 found to restore RMAN-06023: no backup or copy of datafile 9 found to restore RMAN-06023: no backup or copy of datafile 8 found to restore RMAN-06023: no backup or copy of datafile 7 found to restore RMAN-06023: no backup or copy of datafile 6 found to restore RMAN-06023: no backup or copy of datafile 5 found to restore RMAN-06023: no backup or copy of datafile 4 found to restore RMAN-06023: no backup or copy of datafile 3 found to restore RMAN-06023: no backup or copy of datafile 2 found to restore RMAN-06023: no backup or copy of datafile 1 found to restore The issue was with the FRA (Fast Recovery Area). RMAN will catalog any objects in the Flash Recovery Area that will not be registered in the controlfile and if any of this files belongs to an incarnation different from CURR...

A client that I was supporting asked me to complete a dry run of a database restore on their newly built ODA.  Below are the steps that I performed to complete the database restore. Prerequisites: I built the RAC database using OAKCLI. I backed up the database with RMAN incremental level 0 + cumulative level 1. I dropped the database using SQLPLUS commands vs. OAKCLI as to not remove the ACFS directories for the database. 1. Startup the database instance in NOMOUNT. RMAN> startup nomount force; startup failed: ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '/u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/labtest/spfilelabtest.ora' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 starting Oracle instance without parameter file for retrieval of spfile Oracle instance started Total System Global Area    1073741824 bytes Fixed Size ...

I ran into a situation where client I had supported was having network timeouts and a huge amount of packet drops on their network when trying to connect to their ODA from the application stack.  After many hours (2 days to be exact) of troubleshooting, it was discovered that there was an issue with the Grid Infrastructure network configuration vs what the client's network was configured with. The client's default gateway was set to 10.10.10.1 . However when looking at their network configuration set in the GI, it reflected 10.10.0.0 as this is what Oracle was expecting it to be based off of ipcalc. ipcalc -bnm 10.10.40.42 255.255.0.0 NETMASK=255.255.0.0 BROADCAST=10.10.255.255 NETWORK= 10.10.0.0 ################ [root@oraprd1 .ssh]# oifcfg getif ibbond0  192.168.16.0  global  cluster_interconnect,asm bond0  10.10.0.0  global  public ################ # srvctl config network Network 1 exists Subnet IPv4: 10.10.0.0 /255.255.0.0...

Problem: I had an issue where I was trying to build a 1.7 TB Data Guard environment and kept receiving the highlighted error below.  I thought it was some type of fluke so I restarted the job 2 more times only to receive the same error. Fix: After researching the issue, I found it was because the sqlnet.ora needed to be configured correctly.  I added the entry " SQLNET.EXPIRE_TIME = 10 " to the sqlnet.ora on both the source and the target database servers under the RDBMS $ORACLE_HOME/network/admin.  Upon restarting the RMAN active database standby build, it was able to move past this error and built successfully. Starting backup at 07-MAY-17 channel prm1: starting datafile copy input datafile file number=00003 name=+DATA/testdb/datafile/undotbs1.262.940412881 channel prm2: starting datafile copy input datafile file number=00129 name=+DATA/testdb/datafile/welldoc1.390.940509955 channel prm3: starting datafile copy input datafile file number=00130 name=+DATA/...

Have you ever needed to recreate/drop a database link for another user/schema owner in an Oracle database?  Here's how I did it.  --  recreate Database link CREATE PROCEDURE schema_owner.create_db_lnk AS BEGIN     EXECUTE IMMEDIATE 'CREATE DATABASE LINK COGNOS.WORLD '             ||'CONNECT TO CRUD IDENTIFIED BY "password123" '             ||'USING ''(description=(address=(protocol=tcp)(host=host-scan)(port=1521))(connect_data=(service_name=test.artisdba.com)))'''; END create_db_lnk; / exec schema_owner.create_db_lnk; drop procedure schema_owner.create_db_lnk; --  Drop database link CREATE PROCEDURE schema_owner.drop_db_lnk AS BEGIN      EXECUTE IMMEDIATE 'DROP DATABASE LINK COGNOS.WORLD'; END drp_db_lnk; / exec schema_owner.drop_db_lnk; drop procedure schema_owner.drop_db_lnk;

Here is a script that I use to dynamically generate the SQL to recreate tablespaces in another database.  I essentially want all tablespaces that are non-system related.  You can model this script by whatever you need to use the package DBMS_METADATA.GET_DDL in your DBA life. -- @gen_ddl_tbsp.sql spool tbsp_ddl_gen.sql set lines 120 head off feed off pages 0 long  1000000 select 'set lines 120 pages 0 long 1000000' from dual; select 'spool tbsp_ddl.sql' from dual; select distinct 'SELECT DBMS_METADATA.GET_DDL(' || '''' || 'TABLESPACE' || '''' || ',' || '''' || tablespace_name || '''' ||') || ' ||'''' ||' /' ||'''' ||' FROM DUAL;' from dba_tablespaces where tablespace_name in (select tablespace_name from dba_tablespaces where tablespace_name not in ('SYSTEM','USERS','SYSAUX','UNDOTBS1','UN...