DBA Maestro - Helpful DBA tips

Kerberos Authentication to Oracle Database using Active Directory

2025-12-11T16:52:00.000-06:00

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 Administrator generates the keytab using the following command:

ktpass -princ oracle/dbserver01.example.com@EXAMPLE.COM \

-pass <password> \

-mapuser svc_oracle_kerberos \

-crypto ALL \

-ptype KRB5_NT_PRINCIPAL \

-out C:\temp\oracle.keytab

2. The keytab file (oracle.keytab) is transferred securely to the Oracle DBA.

SECTION 2: Oracle Database Server Setup (Linux)

-----------------------------------------------

1. Copy the keytab file to the database server:

$ORACLE_HOME/network/admin/oracle.keytab

2. Modify sqlnet.ora on the database server:

SQLNET.AUTHENTICATION_SERVICES = (BEQ,KERBEROS5)

SQLNET.FALLBACK_AUTHENTICATION = TRUE

SQLNET.KERBEROS5_CONF = $ORACLE_HOME/network/admin/krb5.conf

SQLNET.KERBEROS5_CONF_MIT = TRUE

SQLNET.KERBEROS5_CLOCKSKEW = 6000

SQLNET.KERBEROS5_CC_NAME = /tmp/krb5cc_500

SQLNET.KERBEROS5_KEYTAB = $ORACLE_HOME/network/admin/oracle.keytab

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle

3. Create the krb5.conf file at $ORACLE_HOME/network/admin/krb5.conf:

[libdefaults]

default_realm = EXAMPLE.COM

clockskew = 6000

forwardable = true

renew_lifetime = 7d

rdns = false

ticket_lifetime = 24h

udp_preference_limit = 1

[realms]

EXAMPLE.COM = {

kdc = dc01.example.com:88

}

[domain_realm]

.example.com = EXAMPLE.COM

example.com = EXAMPLE.COM

4. Validate Kerberos ticket on Linux:

okinit dbuser1@EXAMPLE.COM

oklist

SECTION 3: Windows Client Workstation Setup

-------------------------------------------

1. Install MIT Kerberos for Windows (latest version). Select the full installation option.

2. Create krb5.ini at:

C:\ProgramData\MIT\Kerberos5\krb5.ini

[libdefaults]

default_realm = EXAMPLE.COM

clockskew = 6000

noaddresses = true

forwardable = yes

[realms]

EXAMPLE.COM = {

kdc = dc01.example.com:88

}

[domain_realm]

EXAMPLE.COM = EXAMPLE.COM

.EXAMPLE.COM = EXAMPLE.COM

example.com = EXAMPLE.COM

.example.com = EXAMPLE.COM

3. Initialize Kerberos ticket:

set KRB5CCNAME=FILE:C:\Temp\krb5cc_dbuser1

kinit dbuser1@EXAMPLE.COM

"C:\Program Files\MIT\Kerberos\bin\klist.exe"

4. Modify sqlnet.ora in the Oracle client directory

(<ORACLE_CLIENT_HOME>/network/admin):

SQLNET.AUTHENTICATION_SERVICES = (KERBEROS5)

SQLNET.KERBEROS5_CONF = C:\ProgramData\MIT\Kerberos5\krb5.ini

SQLNET.KERBEROS5_CC_NAME = C:\Temp\krb5cc_dbuser1

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle

5. Update tnsnames.ora:

APPPDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver01.example.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = apppdb)

)

6. Set OS authentication prefix to blank in the CDB:

ALTER SYSTEM SET os_authent_prefix='' SCOPE=spfile;

7. Restart the database instance.

SECTION 4: Test Kerberos Authentication

---------------------------------------

From the Windows client:

sqlplus /@APPPDB

You should be logged in automatically using your Active Directory credentials without being prompted for a password.

TROUBLESHOOTING TIPS

--------------------

- Use klist on Windows to verify the Kerberos ticket.

- Ensure krb5.ini and krb5.conf files match exactly.

- Check Oracle Net trace files for authentication errors.

- Use oklist on Linux to inspect tickets during server-side testing.

Revive RMAN: Mastering the Art of Resuming Failed Duplicate Operations

2025-04-10T15:13:00.008-05:00

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 for file <datafile_name>

So I started working on getting this completed. After researching, I found that MOS has a good note on getting things going; however, it was intensive. With that being said, I was about to pivot to a different solution, which involved restoring a full backup from OCI storage. But before doing so, I decided to see what would happen if I simply tried to rerun the active duplicate. So here's what I did...

Create a pfile from my spfile for the standby.
Shutdown the standby database instance.
Removed my spfile that was created as part of the RMAN duplicate process.
Startup nomount the standby instance using the pfile I created.
Removed entries referencing spfile from my RMAN duplicate cmd file (see my previous blog posts on standby builds for examples)
Restart the RMAN duplicate.

End result....IT WORKED!!!

Excerpt from the RMAN duplicate logfile...

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

Using previous duplicated file +DATAC1/FAKEDB/DATAFILE/system_fake.311.9876543210 for datafile 1 with checkpoint SCN of 14235402086660

Using previous duplicated file +DATAC1/FAKEDB/DATAFILE/sysaux_fake.301.9876543211 for datafile 2 with checkpoint SCN of 14235402113022

Using previous duplicated file +DATAC1/FAKEDB/DATAFILE/fake_data_t2.297.9876543212 for datafile 3 with checkpoint SCN of 14235402115530

Using previous duplicated file +DATAC1/FAKEDB/DATAFILE/users_fake.306.9876543213 for datafile 4 with checkpoint SCN of 14235402117901

Using previous duplicated file +DATAC1/FAKEDB/DATAFILE/fake_dba.305.9876543214 for datafile 5 with checkpoint SCN of 14235402120263

Using previous duplicated file +DATAC1/FAKEDB/DATAFILE/admin_fake_data.304.9876543215 for datafile 6 with checkpoint SCN of 14235402125881

Using previous duplicated file +DATAC1/FAKEDB/DATAFILE/stage_fake_data.291.9876543216 for datafile 7 with checkpoint SCN of 14235402128955

Using previous duplicated file +DATAC1/FAKEDB/DATAFILE/dimension_fake_data.294.9876543217 for datafile 8 with checkpoint SCN of 14235402131767

Using previous duplicated file +DATAC1/FAKEDB/DATAFILE/dimension_fake_idx.296.9876543218 for datafile 9 with checkpoint SCN of 14235402133743
...
...
...
...
set newname for clone datafile 2265 to new;
set newname for clone datafile 2266 to new;
set newname for clone datafile 2267 to new;
set newname for clone datafile 2268 to new;
set newname for clone datafile 2269 to new;
set newname for clone datafile 2270 to new;
set newname for clone datafile 2271 to new;
set newname for clone datafile 2272 to new;
set newname for clone datafile 2273 to new;
set newname for clone datafile 2274 to new;
set newname for clone datafile 2275 to new;
set newname for clone datafile 2276 to new;

restore
from nonsparse section size
20 g from service
'FAKEDB' clone datafile
2198, 2204, 2205, 2206, 2207, 2208, 2209, 2210, 2211, 2212, 2213, 2214, 2215, 2216, 2217, 2218, 2219, 2220, 2221, 2222,
2223, 2224, 2225, 2226, 2227, 2228, 2229, 2230, 2231, 2232, 2233, 2234, 2235, 2236, 2237, 2238, 2239, 2240, 2241, 2242,
2243, 2244, 2245, 2246, 2247, 2248, 2249, 2250, 2251, 2252, 2253, 2254, 2255, 2256, 2257, 2258, 2259, 2260, 2261, 2262,
2263, 2264, 2265, 2266, 2267, 2268, 2269, 2270, 2271, 2272, 2273, 2274, 2275, 2276 ;
sql 'alter system archive log current';
}

executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

...

The highlighted parts were mainly what I was looking for to determine that this actually worked. Not sure if Oracle has simply improved on the RMAN duplicate failure process or if I just got lucky.

Hope this helps anyone/everyone who may be experiencing this issue and thinks that they have to start over from scratch. There's HOPE!!!

Enjoy!

Redhat Linux logical volume extension

2023-02-27T11:40:00.005-06:00

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 example, there is/was 142G free so I am extending the LV by 20G

# lvextend -L +20G /dev/mapper/vg00-lvU01
Size of logical volume vg00/lvU01 changed from 20.00 GiB (5120 extents) to 40.00 GiB (10240 extents).
Logical volume vg00/lvU01 successfully resized.

3. After extending my LV, I have to grow it out using xfs_growfs. I tried resizefs but received an error.

# xfs_growfs /dev/mapper/vg00-lvU01
meta-data=/dev/mapper/vg00-lvU01 isize=512 agcount=4, agsize=1310720 blks
= sectsz=4096 attr=2, projid32bit=1
= crc=1 finobt=1, sparse=1, rmapbt=0
= reflink=1
data = bsize=4096 blocks=5242880, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0, ftype=1
log =internal log bsize=4096 blocks=2560, version=2
= sectsz=4096 sunit=1 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
data blocks changed from 5242880 to 10485760

4. Rechecked that it now reflects my correct size of 40G.

# df -h /u01
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg00-lvU01 40G 9.2G 31G 23% /u01

"Failed to start LSB: Bring up/down networking" after OEL 7 VM build

2019-10-31T12:18:00.003-05:00

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_MASK>
GATEWAY=<DEFAULT_GATEWAY>

4. Restart the network.

     # service network restart

5. Check to see that the IP address is up and pingable.

     # ip addr show

[root@localhost ~]# ip addr show
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:16:3e:2a:e4:33 brd ff:ff:ff:ff:ff:ff
    inet 192.168.10.10/24 brd 192.168.10.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::216:3eff:fe2a:e433/64 scope link
       valid_lft forever preferred_lft forever

     # ping 192.168.10.10

6. Remove any other miscellaneous ifcfg-e* files

     # cd /etc/sysconfig/network-scripts
     # rm ifcfg-enp0s3

ORA-17630: Mismatch in the remote file protocol version client 2 server 3

2018-07-16T18:22:00.000-05:00

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: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/16/2018 13:51:47
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on prm2 channel at 07/16/2018 13:51:47
ORA-17629: Cannot connect to the remote database server
ORA-17630: Mismatch in the remote file protocol version client 2 server 3

Recovery Manager complete.

After researching the issue, I found that it is a known Oracle bug. More specifically:

Bug 18633374 Error copying across remote servers with ASMCMD CP command (ASMCMD-8016, ORA-17628, ORA-17630, ORA-6512)

Therefore, I needed to apply patch 18633374 to the ORACLE_HOME that was running the SOURCE database. Upon applying the patch, I was able to proceed with building out the standby successfully using RMAN DUPLICATE with ACTIVE DATABASE.

Migrate ODA VM to another ODA (with vdisks)

2018-06-08T00:17:00.001-05:00

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
s/TESTVM/612aeba3b38f4527a4500e
c202a08e3f.img,xvda,w|
Domain : XEN_PVM
DriverDomain : False
ExpectedState : online
FailOver : false
IsSharedRepo : true
Keyboard : en-us
MaxMemory : 16384M
MaxVcpu : 4
Memory : 16384M
Mouse : OS_DEFAULT
Name : TESTVM
Networks : |type=netfront,bridge=net1|
NodeNumStart : 1
OS : OL_5
PrefNodeNum : 1
PrivateIP : None
ProcessorCap : 0
RepoName : demorepo
State : Online
TemplateName : otml_VM_OL7U2_ORA_BASE
VDisks : |oakvdk_vdisk_demorepo|
Vcpu : 4
cpupool : default-unpinned-pool
vncport : 5902

2. Be sure to cat the vm.cfg file in order to display where the vdisks are that are attached to the source ODA VM.

[root@oda1dom0]# cat /OVS/Repositories/demorepo/.ACFS/snaps/TESTVM/VirtualMachines/TESTVM/vm.cfg

vif = ['bridge=net1']
name = 'TESTVM'
extra = 'NODENAME=TESTVM'
builder = 'hvm'
cpus = '0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71'
vcpus = 4
memory = 16384
cpu_cap = 0
vnc = 1
serial = 'pty'
disk = [u'file:/OVS/Repositories/demorepo/.ACFS/snaps/TESTVM/VirtualMachines/TESTVM/612aeba3b38f4527a4500ec202a08e3f.img,xvda,w', u'file:/OVS/Repositories/demorepo/.ACFS/snaps/oakvdk_vdisk1/VirtualDisks/oakvdk_vdisk1,xvdb,w!', u'file:/OVS/Repositories/demorepo/.ACFS/snaps/oakvdk_vdisk2/VirtualDisks/oakvdk_vdisk2,xvdc,w!']
maxvcpus = 4
maxmem = 16384

3. Create *.img file snap by executing the below command on SOURCE ODA_BASE.
acfsutil snap create -p <vm_name> <snap_vm_repo_name> <vm_repo_mountpoint>

i.e. acfsutil snap create -p TESTVM TESTVM_repo_snap /u01/app/sharedrepo/demorepo

4. Create vdisk snap on SOURCE ODA_BASE.

acfsutil snap create -p oakvdk_vdisk1 vdisk1_snap /u01/app/sharedrepo/demorepo
acfsutil snap create -p oakvdk_vdisk2 vdisk2_snap /u01/app/sharedrepo/demorepo

5. Copy the files using gzip, rsync, tar, etc. to TARGET ODA (onto DOM0). Below example is using rsync.

rsync --archive --compress --hard-links --human-readable \
--inplace --no-whole-file --numeric-ids \
/u01/app/sharedrepo/demorepo/.ACFS/snaps/TESTVM_repo_snap/VirtualMachines/TESTVM \
192.168.100.10:/OVS/vm_stage

6. Tar up the file as to create a file to be used for importing the vmtemplate as part of the next steps.

tar cvSzf /OVS/vm_stage/TESTVM/TESTVM.tgz -C /OVS/vm_stage/TESTVM vm.cfg adafadfalasdfjladsflk60993qadfa.img

7. Import the bundle onto TARGET ODA's ODA_BASE. This should be done as root user.

[root@oda1db0 ~]# oakcli import vmtemplate TESTVM-template -files /OVS/vm_stage/TESTVM/TESTVM.tgz -repo appdevrepo -node 0

8. Clone VM using "oakcli clone vm" to layout the directories for the VDISKS to be copied directly to proper location from SOURCE ODA to TARGET ODA.

oakcli clone vm TESTVM -vmtemplate TESTVM-template -repo appdevrepo -node 0

9. Copy the vdisks to their final destination on the TARGET ODA's ODA_BASE. Below examples use rsync as the copying tool.

rsync --archive --compress --hard-links --human-readable \
--inplace --no-whole-file --numeric-ids \
/u01/app/sharedrepo/demorepo/.ACFS/snaps/vdisk2_snap/VirtualDisks/oakvdk_vdisk2 \
10.17.253.70:/u01/app/sharedrepo/appdevrepo/.ACFS/snaps/TESTVM/VirtualMachines/TESTVM

rsync --archive --compress --hard-links --human-readable \
--inplace --no-whole-file --numeric-ids \
/u01/app/sharedrepo/demorepo/.ACFS/snaps/vdisk1_snap/VirtualDisks/oakvdk_vdisk1 \
10.17.253.70:/u01/app/sharedrepo/appdevrepo/.ACFS/snaps/TESTVM/VirtualMachines/TESTVM

Note: I encountered the below error when I went to startup the VM.

OAKERR:7007 Error encountered while starting VM

The fix for this is to remove line "extra = 'xxxxxx' " from the vm.cfg (known Oracle documented issue)

10. You should now be able to start the VM and open the vmconsole.

oakcli start vm TESTVM; oakcli start vmconsole

11. After the VM has been successfully started and validated, you can now delete the ACFS snaps that were taken originally from SOURCE ODA_BASE.

acfsutil snap delete TESTVM_repo_snap /u01/app/sharedrepo/demorepo

acfsutil snap delete vdisk1_snap /u01/app/sharedrepo/demorepo

acfsutil snap delete vdisk2_snap /u01/app/sharedrepo/demorepo

That should cover it. This is the procedure that I used to successfully migrate VMs from one virtualized ODA to another virtualized ODA. I hope this helps!

ORA-00600: error in Queue Table SYS.SYS$SERVICE_METRICS_TAB

2018-01-21T02:11:00.002-06:00

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;

RMAN-06023: no backup or copy of datafile X found to restore

2018-01-21T02:07:00.001-06:00

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 CURRENT incarnation in the controlfile, then you will need to change the controlfile CURRENT incarnation to the one found in the file that is being cataloged.

This prevents database from restoring backups that belong to old CURRENT incarnation.

RMAN considers backup available for being restored if the backup incarnation and CURRENT incarnation in controlfile are the same.

RMAN restore of ODA X6-2-HA RAC database

2018-01-21T02:00:00.003-06:00

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                     2932632 bytes
Variable Size                415236200 bytes
Database Buffers             650117120 bytes
Redo Buffers                   5455872 bytes

2. Restore the spfile from backup taken.

RMAN> restore spfile from '/backup/LABTEST/ctrl_c-1771719914-20180118-00';

Starting restore at 18-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/LABTEST/ctrl_c-1771719914-20180118-00
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 18-JAN-18

3. Set the DBID for the database being restored.

RMAN> set dbid 1771719914;

executing command: SET DBID

4. Restore the controlfile from autobackup.

RMAN> restore controlfile from '/backup/LABTEST/ctrl_c-1771719914-20180118-00';

Starting restore at 18-JAN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=181 instance=labtest1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/datastore/labtest/LABTEST/controlfile/o1_mf_f5zk5rxs_.ctl
Finished restore at 18-JAN-18

5. Mount the database with the controlfile that was just restored.

RMAN> alter database mount;

using target database control file instead of recovery catalog
Statement processed

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
2       B 0 A DISK        17-JAN-18       1       1       NO         FULL_011718_172826
6       B A A DISK        17-JAN-18       1       1       NO         FULL_011718_172826
7       B A A DISK        17-JAN-18       1       1       NO         INCR_011718_220001
8       B 1 A DISK        17-JAN-18       1       1       NO         INCR_011718_220001
10      B 1 A DISK        17-JAN-18       1       1       NO         INCR_011718_220001
11      B 1 A DISK        17-JAN-18       1       1       NO         INCR_011718_220001
12      B A A DISK        17-JAN-18       1       1       NO         INCR_011718_220001
13      B F A DISK        18-JAN-18       1       1       NO         TAG20180118T151334

RMAN> report schema;

Starting implicit crosscheck backup at 18-JAN-18
allocated channel: ORA_DISK_1
Crosschecked 8 objects
Finished implicit crosscheck backup at 18-JAN-18

Starting implicit crosscheck copy at 18-JAN-18
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-JAN-18

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_18/o1_mf_1_14_f6201oyo_.arc

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name LABTEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_system_f5zk5vs5_.dbf
2    0        SYSAUX               ***     /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_sysaux_f5zk5y1n_.dbf
3    0        UNDOTBS1             ***     /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_undotbs1_f5zk5yyz_.dbf
4    0        UNDOTBS2             ***     /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_undotbs2_f5zk677x_.dbf
5    0        USERS                ***     /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_users_f5zk67kc_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_temp_f5zk5z7r_.tmp

6. Restore the database.

RMAN> restore database;

Starting restore at 18-JAN-18
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_system_f5zk5vs5_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_sysaux_f5zk5y1n_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_undotbs1_f5zk5yyz_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_undotbs2_f5zk677x_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_users_f5zk67kc_.dbf
channel ORA_DISK_1: reading from backup piece /backup/LABTEST/FULL_LABTEST_1771719914_20180117_03sotse8_1_1.bkp
channel ORA_DISK_1: piece handle=/backup/LABTEST/FULL_LABTEST_1771719914_20180117_03sotse8_1_1.bkp tag=FULL_011718_172826
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 18-JAN-18

7. Recover the database.

RMAN> recover database;

Starting recover at 18-JAN-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_system_f621nm7q_.dbf
destination for restore of datafile 00002: /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_sysaux_f621nm8v_.dbf
destination for restore of datafile 00003: /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_undotbs1_f621nm9n_.dbf
destination for restore of datafile 00004: /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_undotbs2_f621nmb4_.dbf
destination for restore of datafile 00005: /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_users_f621nmbm_.dbf
channel ORA_DISK_1: reading from backup piece /backup/LABTEST/INCR_LABTEST_1771719914_20180117_09soucbg_1_1.bkp
channel ORA_DISK_1: piece handle=/backup/LABTEST/INCR_LABTEST_1771719914_20180117_09soucbg_1_1.bkp tag=INCR_011718_220001
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_17/o1_mf_1_8_f603g8f0_.arc
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_18/o1_mf_1_9_f61w5hxq_.arc
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_18/o1_mf_1_10_f61w5jxs_.arc
archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_18/o1_mf_1_11_f61ybnd6_.arc
archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_18/o1_mf_1_12_f61ybod8_.arc
archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_18/o1_mf_1_13_f61zwfo3_.arc
archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_18/o1_mf_1_14_f6201oyo_.arc
archived log for thread 2 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_17/o1_mf_2_7_f603g8lb_.arc
archived log for thread 2 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_18/o1_mf_2_8_f61ybo5h_.arc
archived log for thread 2 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_18/o1_mf_2_9_f61zwgcx_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_17/o1_mf_1_8_f603g8f0_.arc thread=1 sequence=8
archived log file name=/u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_17/o1_mf_2_7_f603g8lb_.arc thread=2 sequence=7
archived log file name=/u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_18/o1_mf_1_9_f61w5hxq_.arc thread=1 sequence=9
archived log file name=/u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_18/o1_mf_2_8_f61ybo5h_.arc thread=2 sequence=8
archived log file name=/u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_18/o1_mf_1_10_f61w5jxs_.arc thread=1 sequence=10
archived log file name=/u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_18/o1_mf_1_11_f61ybnd6_.arc thread=1 sequence=11
archived log file name=/u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_18/o1_mf_1_12_f61ybod8_.arc thread=1 sequence=12
archived log file name=/u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_18/o1_mf_2_9_f61zwgcx_.arc thread=2 sequence=9
archived log file name=/u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_18/o1_mf_1_13_f61zwfo3_.arc thread=1 sequence=13
archived log file name=/u01/app/oracle/fast_recovery_area/datastore/labtest/LABTEST/archivelog/2018_01_18/o1_mf_1_14_f6201oyo_.arc thread=1 sequence=14
unable to find archived log
archived log thread=2 sequence=10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/18/2018 15:43:12
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 10 and starting SCN of 683417

8. Attempted to open the database via resetlogs but received an error about block change tracking. Therefore, I disabled block change tracking in order to successfully open the database.

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 01/18/2018 15:44:11
ORA-19751: could not create the change tracking file
ORA-19750: change tracking file: '/u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/changetracking/o1_mf_f5zm3sf3_.chg'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

RMAN> alter database disable block change tracking;

Statement processed

RMAN> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

RMAN> alter database open;

Statement processed

RMAN> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

9. Shutdown the database.

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> exit

Recovery Manager complete.

10. I checked the status of and started the database via srvctl. Restore completed.

[oracle@dzpubpoda0 ~]$ srvctl status database -d labtest
Instance labtest1 is not running on node dzpubpoda0
Instance labtest2 is not running on node dzpubpoda1

[oracle@dzpubpoda0 ~]$ srvctl start database -d labtest

[oracle@dzpubpoda0 ~]$ srvctl status database -d labtest
Instance labtest1 is running on node dzpubpoda0
Instance labtest2 is running on node dzpubpoda1

[oracle@dzpubpoda0 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Jan 18 15:47:22 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: LABTEST (DBID=1771719914)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name LABTEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    700      SYSTEM               YES     /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_system_f621nm7q_.dbf
2    600      SYSAUX               NO      /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_sysaux_f621nm8v_.dbf
3    300      UNDOTBS1             YES     /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_undotbs1_f621nm9n_.dbf
4    200      UNDOTBS2             YES     /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_undotbs2_f621nmb4_.dbf
5    5        USERS                NO      /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_users_f621nmbm_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u02/app/oracle/oradata/datastore/.ACFS/snaps/labtest/LABTEST/datafile/o1_mf_temp_f621t812_.tmp

RMAN>

Grid Infrastructure network issue on ODA X5-2

2018-01-21T01:50:00.001-06:00

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/bond0, static
Subnet IPv6:
Ping Targets:
Network is enabled
Network is individually enabled on nodes:
Network is individually disabled on nodes:

##############

[root@oraprd2 ~]# route -n

Kernel IP routing table

Destination Gateway Genmask Flags Metric Ref Use Iface

0.0.0.0 10.10.10.1 0.0.0.0 UG 0 0 0 bond0

10.10.0.0 0.0.0.0 255.255.0.0 U 0 0 0 bond0

192.168.16.0 0.0.0.0 255.255.255.0 U 0 0 0 ibbond0

###################

Could this be the culprit of the problems that client has been having since having their ODA installed in their DC? I investigated further.

I tried to update it by following MOS note ID 1504734.1

srvctl modify network -netnum 1 -subnet 10.10.10.0/255.255.0.0/bond0

This wouldn't work as when I checked the network in GI after making the change, it still reflected 10.10.0.0.

[root@oraprd1 network-scripts]# srvctl start nodeapps -n oraprd1
PRCR-1013 : Failed to start resource ora.net1.network
PRCR-1064 : Failed to start resource ora.net1.network on node oraprd1
CRS-5017: The resource action "ora.net1.network start" encountered the following error:
CRS-5008: Invalid attribute value: bond0 for the network interface
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/oraprd1/crs/trace/crsd_orarootagent_root.trc".

CRS-2674: Start of 'ora.net1.network' on 'oraprd1' failed
PRCR-1079 : Failed to start resource ora.oraprd1.vip
CRS-5017: The resource action "ora.net1.network start" encountered the following error:
CRS-5008: Invalid attribute value: bond0 for the network interface
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/oraprd1/crs/trace/crsd_orarootagent_root.trc".

CRS-2674: Start of 'ora.net1.network' on 'oraprd1' failed
CRS-5017: The resource action "ora.net1.network start" encountered the following error:
CRS-5008: Invalid attribute value: bond0 for the network interface
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/oraprd2/crs/trace/crsd_orarootagent_root.trc".

CRS-2674: Start of 'ora.net1.network' on 'oraprd2' failed
CRS-2632: There are no more servers to try to place resource 'ora.oraprd1.vip' on that would satisfy its placement policy
PRCR-1013 : Failed to start resource ora.ons
PRCR-1064 : Failed to start resource ora.ons on node oraprd1
CRS-5017: The resource action "ora.net1.network start" encountered the following error:
CRS-5008: Invalid attribute value: bond0 for the network interface
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/oraprd1/crs/trace/crsd_orarootagent_root.trc".

CRS-2674: Start of 'ora.net1.network' on 'oraprd1' failed

So I suggested to the client that we switch the subnet in its entirety and switch to an entirely different VLAN.

After client defined the new VLAN IPs, I went to work changing the IPs on their entire ODA (VIPs, Public IPs, SCAN IPs, ILOM IPs) to those of the new VLAN IPs.

Upon doing so, the network packet drops ceased and the timeouts ceased as well. Problem solved.

RMAN-10038: database session for channel prm3 terminated unexpectedly

2017-05-08T08:02:00.000-05:00

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/testdb/datafile/welldoc10.391.940509955
channel prm4: starting datafile copy
input datafile file number=00146 name=+DATA/testdb/datafile/welldoc10_lob.407.940509955
RMAN-03009: failure of backup command on prm3 channel at 05/07/2017 20:45:51
RMAN-10038: database session for channel prm3 terminated unexpectedly
ORACLE error from target database:
ORA-03135: connection lost contact
Process ID: 23071
Session ID: 1767 Serial number: 139

channel prm3 disabled, job failed on it will be run on another channel
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-03114: not connected to ORACLE
RMAN-03002: failure of Duplicate Db command at 05/07/2017 20:45:51
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on default channel at 05/07/2017 20:45:51
RMAN-10038: database session for channel default terminated unexpectedly

Recovery Manager complete.

Recreate/drop Oracle database link for another schema/user

2017-04-01T11:15:00.000-05:00

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;

Dynamically gather DDL for Oracle database tablespaces

2017-04-01T10:52:00.004-05:00

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','UNDOTBS2','TOOLS','TEMP')) order by 1
/
select 'spool off' from dual;
spool off
--@tbsp_ddl_gen.sql -- This will auto execute the tbsp_ddl_gen.sql script

GoldenGate - PLW-06009: procedure "XXX" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

2017-04-01T10:13:00.000-05:00

I was in the middle of configuring OGG for a client and came across an issue when getting to the portion of installing support for sequences. This script creates some procedures to be used by OGG processes.

I hope this helps someone as it took me some digging around to figure this one out. Enjoy!

SQL> @sequence

Please enter the name of a schema for the GoldenGate database objects:

GGADMIN

Setting schema name to GGADMIN

SP2-0804: Procedure created with compilation warnings

UPDATE_SEQUENCE STATUS:

Line/pos Error

---------- -----------------------------------------------------------------

58/14 PLW-06009: procedure "UPDATESEQUENCE" OTHERS handler does not end

in RAISE or RAISE_APPLICATION_ERROR

GETSEQFLUSH

Line/pos Error

---------- -----------------------------------------------------------------

No errors No errors

SEQTRACE

Line/pos Error

---------- -----------------------------------------------------------------

No errors No errors

REPLICATE_SEQUENCE STATUS:

Line/pos Error

---------- -----------------------------------------------------------------

261/16 PLW-06009: procedure "REPLICATESEQUENCE" OTHERS handler does not

end in RAISE or RAISE_APPLICATION_ERROR

395/29 PLW-06009: procedure "REPLICATESEQUENCE" OTHERS handler does not

end in RAISE or RAISE_APPLICATION_ERROR

65/14 PLW-06009: procedure "REPLICATESEQUENCE" OTHERS handler does not

end in RAISE or RAISE_APPLICATION_ERROR

STATUS OF SEQUENCE SUPPORT

--------------------------------------------------------------

ERRORS in Oracle Sequence Replication Support

Problem was due to plsql_warnings being enabled. To workaround the issue, I disabled the plsql_warnings at the session level as to not interfere with client's specific parameter settings at the system level.

FIX:

SQL> alter session set plsql_warnings='DISABLE:ALL';

SQL> @sequence.sql

SQL> @sequence

Please enter the name of a schema for the GoldenGate database objects:

GGADMIN

Setting schema name to GGADMIN

UPDATE_SEQUENCE STATUS:

Line/pos Error

---------- -----------------------------------------------------------------

No errors No errors

GETSEQFLUSH

Line/pos Error

---------- -----------------------------------------------------------------

No errors No errors

SEQTRACE

Line/pos Error

---------- -----------------------------------------------------------------

No errors No errors

REPLICATE_SEQUENCE STATUS:

Line/pos Error

---------- -----------------------------------------------------------------

No errors No errors

STATUS OF SEQUENCE SUPPORT

--------------------------------------------------------------

SUCCESSFUL installation of Oracle Sequence Replication support

Upgrade from 11gR2 RAC to 12cR1 RAC using cloning methodology

2016-01-05T13:46:00.000-06:00

12.1.0.2 Grid Infrastructure Upgrade: From 11.2.0.4 To 12.1.0.2 Using GI Clone

I used the following methodology to upgrade from 11.2.0.4 to 12.1.0.2 RAC. This is particularly useful from a time perspective to avoid having to do a separate patching when it comes to applying bundle patches (Exadata) or PSUs. I tested this process in my non-production environment and highly advise you to do the same to ensure the process is seamless.

A) Prepare SOURCE GI home (In this case it is 12.1.0.2) to be cloned:
********************************
Follow documentation for "Preparing the Oracle Grid Infrastructure Home for Cloning" at https://docs.oracle.com/database/121/CWADD/clonecluster.htm#CWADD92116

Oracle® Clusterware Administration and Deployment Guide
12c Release 1 (12.1)
Part Number E16794-16
********************************

A01) Stop all databases via srvctl (run by oracle)

srvctl stop database -d {db_unique_name}

A02) Stop CRS (run by root)

export ORACLE_HOME=/u01/app/12.1.0.2/grid
$ORACLE_HOME/bin/crsctl stop crs

A03) Validate that everything is shutdown on the GI home and DB homes

ps -ef |grep pmon
ps -ef |egrep "d.bin reboot" |grep -v grep

B) Create a Copy of the Oracle Grid Infrastructure Home

B01) Create backup of existing home (run as root)

cp -prf /u01/app/12.1.0.2/grid /u01/app/12.1.0.2/grid_bak

B02) Cleanup the unnecessary files. (run as root)

cd /u01/app/12.1.0.2/grid_bak
rm -rf log/td04db01
rm -rf gpnp/td04db01
find gpnp -type f -exec rm -f {} \;
rm -rf cfgtoollogs/*
rm -rf crs/init/*
rm -rf cdata/*
rm -rf crf/*
rm -rf network/admin/*.ora
rm -rf crs/install/crsconfig_params
find . -name '*.ouibak' -exec rm {} \;
find . -name '*.ouibak.1' -exec rm {} \;
rm -rf root.sh*
rm -rf rdbms/audit/*
rm -rf rdbms/log/*
rm -rf inventory/backup/*

B03) Create compressed copy of GI home to be used for cloning (run as root)

cd /u01/app/12.1.0.2/grid_bak
tar -zcvpf /<backup_location>/gridHome.tgz .

chown oracle:oinstall /<backup_location>/gridHome.tgz
chown oracle:oinstall /u01/app/12.1.0.2/grid_bak
chmod 755 /u01/app/12.1.0.2/grid_bak

B04) Startup the clusterware for the node (run as root)

export ORACLE_HOME=/u01/app/12.1.0.2/grid
$ORACLE_HOME/bin/crsctl start crs

C) Deploy the 12.1.0.2 GI home (gridHome.tgz from above step) on the destination cluster nodes where GI 11.2.0.4 is installed and active:
**********************************
Follow documentation for "Step 2: Deploy the Oracle Grid Infrastructure Home on the Destination Nodes" at https://docs.oracle.com/database/121/CWADD/clonecluster.htm#CWADD92125

Oracle® Clusterware Administration and Deployment Guide
12c Release 1 (12.1)

**********************************

C01) As root on each node of cluster, make the directory for the GI HOME. Unzip the tar ball. Then chmod the required files to clear setuid and setgid information from the Oracle binary.

mkdir -p /u01/app/12.1.0.2/grid

cd /u01/app/12.1.0.2/grid
tar -zxvf /u01/app/oracle/patches/gridHome.tgz

chown -R oracle:oinstall /u01/app/12.1.0.2

chmod u+s /u01/app/12.1.0.2/grid/bin/oracle
chmod g+s /u01/app/12.1.0.2/grid/bin/oracle
chmod u+s /u01/app/12.1.0.2/grid/bin/extjob
chmod u+s /u01/app/12.1.0.2/grid/bin/jssu
chmod u+s /u01/app/12.1.0.2/grid/bin/oradism

C02) Execute the clone.pl script on ALL cluster nodes 1 node at a time on the destination system (DO NOT run root.sh as instructed):
*************************************
Follow documentation for "Step 3: Run the clone.pl Script on Each Destination Node" at https://docs.oracle.com/database/121/CWADD/clonecluster.htm#CWADD92125

Oracle® Clusterware Administration and Deployment Guide
12c Release 1 (12.1)

*************************************

I created the following script to echo out the perl command that needs to be executed (as oracle user).
## As ORACLE

> cat run_for_gi.ksh
mkdir -p /home/oracle/dba/patches/12102/install

SCRIPT_DIR=/home/oracle/dba/patches/12102/install
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/12.1.0.2/grid
ORACLE_INV=/u01/app/oraInventory
cd $ORACLE_HOME/clone
THIS_NODE=`hostname -s`
node1=
node2=
node3=
node4=

E01=ORACLE_HOME=${ORACLE_HOME}
E02=ORACLE_HOME_NAME=Ora12c_gridinfrahome1
E03=ORACLE_BASE=${ORACLE_BASE}
E04=INVENTORY_LOCATION=${ORACLE_INV}
C01="-O'\"CLUSTER_NODES={$node1,$node2,$node3,$node4}\"'"
C02="-O'\"LOCAL_NODE=${THIS_NODE}\"' CRS=TRUE"

echo "perl $ORACLE_HOME/clone/bin/clone.pl $E01 $E02 $E03 $E04 $C01 $C02 |tee $SCRIPT_DIR/cloneGridHome_12c.out "

## Execute the perl command that appears on the screen after running the above script.

C03) BEFORE running config.sh, set CRS=false for 12.1.0.2 in inventory.xml as two GI homes can not have CRS=true flag at the same time. If this step is not completed config.sh would error with " [INS-40406] The installer detects no existing Oracle GI software on the system."

a. Backup inventory.xml on all nodes
cp -p /u01/app/oraInventory/ContentsXML/inventory.xml /u01/app/oraInventory/ContentsXML/inventory.xml.bak

b. Run below command on all nodes:
/u01/app/12.1.0.2/grid/oui/bin/runInstaller -updateNodeList ORACLE_HOME="/u01/app/12.1.0.2/grid" CRS=false

PLEASE NOTE: You will receive the following error which appears to be ok.

/u01/app/12.1.0.2/grid/oui/bin/runInstaller -updateNodeList ORACLE_HOME="/u01/app/12.1.0.2/grid" CRS=false
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 24575 MB Passed
'UpdateNodeList' failed.
'UpdateNodeList' failed.

C04) BEFORE running config.sh, on all nodes, copy crsconfig_params file from SOURCE 12.1.0.2 GI home to 12.1.0.2 DESTINATION GI home. Modify crsconfig_params accordingly to reflect proper values for destination (cluster name, host names, node names, install_node, etc) details.

If you don't perform this step, config.sh may show failure at cluvfy step with error " INS-20802 Oracle Cluster Verification Utility failed."

config.sh log file would show "INFO: Unable to obtain network interface list from Oracle ClusterwarePRCT-1011 : Failed to run "oifcfg". Detailed error: null"

C05) Run config.sh, choose upgrade option. Run rootupgrade.sh when prompted.

Make sure you DON'T use sudo to run rootupgrade.sh. You should use real root user login to run rootupgrade.sh. If sudo is used to rootupgrade.sh then upgrade may not be successful and crs activeversion would still show 11.2.0.4 even though its running from 12.1.0.2 GI home.

After rootupgrade.sh is completed and configuration done, use can execute cluvfy to validate the health of the cluster is good. This process was very seamless for me as I was very meticulous on following these instructions. The GI stack is the most sensitive so I was extremely careful with ensuring these steps were documented clearly.

D) Steps for the cloning of the DB HOME.

D01) Create source tar ball for 12.1.0.2 db home binaries.

cd /u01/app/oracle/product/12.1.0.2/dbhome_1
tar -zcvpf /<backup_location>/dbHome.tgz .

D02) Copy it to all server in the cluster or place in a shared filesystem/location.

D03) Make directory for the binaries and script directory.

mkdir -p /u01/app/oracle/product/12.1.0.2/dbhome_1
mkdir -p /home/oracle/dba/patches/12102/install

D04) Change to the db home directory.

cd /u01/app/oracle/product/12.1.0.2/dbhome_1
tar -zxvf /<backup_location>/dbHome.tgz

D05) Place below commands in a small script, update the node names (if there are less than 4 nodes - adjust parameter C01 accordingly), and execute. Run the command that is displayed as output.

SCRIPT_DIR=/home/oracle/dba/patches/12102/install
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
cd $ORACLE_HOME/clone
THIS_NODE=`hostname -s`
node1=
node2=
node3=
node4=

E01=ORACLE_HOME=${ORACLE_HOME}
E02=ORACLE_HOME_NAME=OraDb12c_home1
E03=ORACLE_BASE=${ORACLE_BASE}
C01="-O'\"CLUSTER_NODES={$node1,$node2,$node3,$node4}\"'"
C02="-O LOCAL_NODE=$THIS_NODE"

echo "perl $ORACLE_HOME/clone/bin/clone.pl $E01 $E03 $E02 $C01 $C02 |tee $SCRIPT_DIR/clonedbHome_12c.out "

D06) Have UNIX admin run root.sh on each node.

/u01/app/oracle/product/12.1.0.2/dbhome_1/root.sh

Recover from datafile block corruption leveraging physical standby

2015-06-05T09:28:00.005-05:00

Here are the steps I used to recover my primary database by leveraging a Dataguard environment.

## on standby

1. stop managed recovery on standby.

SQL> alter database recover managed standby database cancel;

2. Backup as a copy of the datafile from standby.

RMAN> backup as copy datafile 2 format '/app/oracle/temp/sysaux_01.dbf';

3. SCP the backup copy of the datafile to the primary database server.

scp /app/oracle/temp/sysaux_01.dbf <target_server>:/app/oracle/temp

## on primary

4. Take corrupt datafile offline.

SQL> alter database datafile 2 offline;

5. Catalog datafile to the primary database so the controlfile knows about it.

RMAN> catalog datafilecopy '/app/oracle/temp/sysaux_01.dbf';

6. Restore and recover the datafile in question.

RMAN> restore datafile 2;

RMAN> recover datafile 2;

7. Bring the datafile back online.

SQL> alter database datafile 2 online;

8. Validate that the corruption is no longer present.

RMAN> validate check logical datafile 2;

SQL> select * from v$database_block_corruption;

RMAN-05001: auxiliary file name conflicts with a file used by the target database

2015-06-05T09:12:00.002-05:00

RMAN-05001: auxiliary file name <file_name> conflicts with a file used by the target database

I ran into an issue where I was trying to clone a new copy of non-production database (using production database as the source) by leveraging the RMAN clone via active database to save me some time and work.

I ran into the error where I couldn't successfully clone the database. I made sure that my environment, pfile, RMAN script were configured correctly. After much time of troubleshooting, I figured out the issue. After remembering that the source database had a DG environment, it occurred to me that was what was causing the error. Reason being, the source had log_file_name_convert and db_file_name_convert configured which was being "carried over" onto my auxiliary instance hence causing the error mentioned above. So all I needed to do was simply reset those two parameters within my RMAN script (as seen below) and I was able to successfully clone the database without issues.

Hopefully, this helps someone save a lot of time and effort with troubleshooting.

run {

allocate channel prm1 type disk;

allocate channel prm2 type disk;

allocate channel prm3 type disk;

allocate channel prm4 type disk;

allocate channel prm5 type disk;

allocate channel prm6 type disk;

allocate auxiliary channel stb1 type disk;

DUPLICATE TARGET DATABASE TO <db_name>

FROM ACTIVE DATABASE

SPFILE

SET CONTROL_FILES='+DATA_STG'

SET DB_CREATE_FILE_DEST '+DATA'

SET db_recovery_file_dest='+DATA'

SET db_recovery_file_dest_size='100G'

SET audit_file_dest='/app/oracle/admin/<db_name>/adump'

reset db_file_name_convert

reset log_file_name_convert

reset log_archive_config

reset log_archive_dest_2;

}

Apply PSU to OEM 12cR4

2015-01-16T15:36:00.001-06:00

Below are the steps I followed for patching OMS for OEM 12c R4. I came across an issue with creating my property file. I give an example of what it should look like upon completion.

Creating the property file :

1. Run the following script to create the WebLogic encrypted configuration and key files.

mkdir /app/oracle/Middleware/oms/oms_encr

<OMS_HOME>/OPatch/wlskeys/createkeys.sh –oh <full path of platform OMS Oracle Home> -location <location to put the encrypted files>

e.g. $OMS_HOME/OPatch/wlskeys/createkeys.sh -oh $OMS_HOME -location /app/oracle/Middleware/oms/oms_encr

Please enter weblogic admin server username: weblogic
Please enter weblogic admin server password:
Creating the key file can reduce the security of your system if it is not kept in a secured location after it is created. Creating new key...

Trying to get configuration and key files for the given inputs...
This operation will take some time. Please wait for updates...
User configuration file created: /app/oracle/Middleware/oms/oms_encr/config
User key file created: /app/oracle/Middleware/oms/oms_encr/key
'createKeys' succeeded.

2.  Create the property file with the following entries:

To retrieve the WebLogic Administrator Server URL details, run the following commands on the OMS home that you are patching:
On Unix:


$ $ORACLE_HOME/bin/emctl status oms -details

On Windows:


%ORACLE_HOME%\bin\emctl.bat status oms -details

The output displayed contains the WebLogic Adminstrator Server 
details. Here is an example on how to construct the URL with these 
output details.


Example:


WLS Domain Information

Domain Name : GCDomain
Admin Server Host : oem12cr3.idc.oracle.com
Admin Server HTTPS Port: 7103

To construct the Administrator Server URL, use the following syntax:
t3s://<admin server host>:<port>

In this example, the URL translates as follows:
t3s://oem12cr3.idc.oracle.com:7103

Property file should look similar to the following once completed:

mkdir /app/oracle/Middleware/oms/files
vi property_file.txt



cat property_file.txt

AdminServerURL=t3s://oemserver1.artisdba.com:7102
AdminConfigFile=/app/oracle/Middleware/oms/oms_encr/config
AdminKeyFile=/app/oracle/Middleware/oms/oms_encr/key

Applying the PSU:



1. Run the following command on each OMS instance home to ensure that 
you do not have any issues with configuration and binary prerequisite 
checks:



Please note: the OMS should be online when you do this step only.


export ORACLE_HOME=$OMS_HOME
export PATH=$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$PATH:

cd <PATCH_TOP_DIR>/19218456

While Installing the System patch
$ opatchauto apply -analyze  [–property_file <location of property file>]


e.g. opatchauto apply -analyze -property_file "/app/oracle/Middleware/oms/files/property_file.txt"


2. If this comes back without any issues, proceed with applying the PSU.  If not, address the errors, then proceed with next steps.

cd <PATCH_TOP_DIR>/19218456/

emctl stop oms

Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
Oracle Management Server is Down

opatchauto apply -property_file "/app/oracle/Middleware/oms/files/property_file.txt"
OPatch Automation Tool
Copyright (c) 2014, Oracle Corporation.  All rights reserved.


OPatchauto version : 11.1.0.12.3
OUI version        : 11.1.0.12.0
Running from       : /app/oracle/Middleware/oms
Log file location  : /app/oracle/Middleware/oms/cfgtoollogs/opatch/opatch2015-01-16_16-05-10PM_1.log

OPatchauto log file: /app/oracle/Middleware/oms/cfgtoollogs/opatchauto/19218456/opatch_oms_2015-01-16_16-05-11PM_deploy.log

Configuration Validation: Success

Running apply prerequisite checks for sub-patch(es) "19218456" and Oracle Home "/app/oracle/Middleware/oms"...
Please monitor OPatch log file: /app/oracle/Middleware/oms/cfgtoollogs/opatch/19218456_Jan_16_2015_16_05_10/ApplyPrereq2015-01-16_16-09-57PM_2.log
Sub-patch(es) "19218456" are successfully analyzed for Oracle Home "/app/oracle/Middleware/oms"

To continue, OPatchauto will do the following:
[Patch and deploy artifacts]   : Apply sub-patch(es) [ 19218456 ] to Oracle Home "/app/oracle/Middleware/oms";
                                 Apply RCU artifact with patch "/app/oracle/Middleware/oms/.patch_storage/19218456_Oct_14_2014_03_22_39/original_patch"


Do you want to proceed? [y|n]
y
User Responded with: Y

Applying sub-patch "19218456" to Oracle Home "/app/oracle/Middleware/oms"...
Please monitor OPatch log file: /app/oracle/Middleware/oms/cfgtoollogs/opatch/19218456_Jan_16_2015_16_05_10/apply2015-01-16_16-13-27PM_4.log

Updating repository with RCU reference file "/app/oracle/Middleware/oms/.patch_storage/19218456_Oct_14_2014_03_22_39/original_patch"

Complete Summary
================


All log file names referenced below can be accessed from the directory "/app/oracle/Middleware/oms/cfgtoollogs/opatch/2015-01-16_16-05-10PM_SystemPatch_19218456_1"

Patching summary:
-----------------

Binaries of the following sub-patch(es) have been applied successfully:

  Oracle Home Name   Sub-patches                                    Log file
  ----------------   -----------                                    --------
           oms12c1      19218456   19218456_apply2015-01-16_16-13-27PM_4.log

Deployment summary:
-------------------

The following artifact(s) have been successfully deployed:

  Artifacts                                                  Log file
  ---------                                                  --------
        SQL   rcu_applypatch_original_patch_2015-01-16_16-14-33PM.log


Log file location: /app/oracle/Middleware/oms/cfgtoollogs/opatchauto/19218456/opatch_oms_2015-01-16_16-05-11PM_deploy.log

OPatchauto succeeded.

emctl start oms

Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
Starting WebTier...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up

ORA-00338: log {n} of thread {n} is more recent than control file

2015-01-13T14:43:00.001-06:00

I received the following error in a primary database's alert log that I was supporting recently.

ORA-00312: online log 5 thread 1: '+DATA/db_name/onlinelog/group_5.297.846947859'
ORA-00338: log 5 of thread 1 is more recent than control file

The database has a DataGuard environment as well and below are the steps that I took to resolve the issue.

1. First, I checked the status of the redo logs and their current sizes and status.

SQL> select group#,bytes/1024/1024,status from v$log;

GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
1 50 INACTIVE
2 50 INACTIVE
3 50 INACTIVE
4 250 INACTIVE
5 250 INACTIVE
6 250 CURRENT

2. As you can see, groups 1, 2, 3 have 50 MB redo log group sizes while group 4, 5, 6 are 250 MB redo log group sizes. This is something that I highly suspect was/is the issue. So I decided to drop the undersized redo log groups and replace them with the appropriate sized ones. Being that groups 1, 2, 3 are inactive, I can drop them without any errors.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 1 ('+DATA') SIZE 250M;

Database altered.

SQL> alter database add logfile group 2 ('+DATA') SIZE 250M;

Database altered.

SQL> alter database add logfile group 3 ('+DATA') SIZE 250M;

Database altered.

SQL> select group#,bytes/1024/1024,status from v$log;

GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
1 250 UNUSED
2 250 UNUSED
3 250 UNUSED
4 250 INACTIVE
5 250 INACTIVE
6 250 CURRENT

3. Now that the primary database is squared away, I move on to the DataGuard environment. First, I check the status of each group here as well.

SQL> select group#,bytes/1024/1024,status from v$log order by 1;

GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
1 50 CLEARING
2 50 CLEARING
3 50 CLEARING
4 250 CLEARING
5 250 CLEARING
6 250 CURRENT

4. Before I can make any changes to the DG environment, I have to cancel the managed recovery process.

SQL> alter database recover managed standby database cancel;

Database altered.

5. Then I clear the logfile groups that I want to drop/readd. These are the same groups that replaced on the primary.

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

6. Notice that the status now saying "UNUSED". They are safe to drop after I change the standby file management from AUTO to MANUAL.

SQL> select group#,bytes/1024/1024,status from v$log order by 1;

GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
1 50 UNUSED
2 50 UNUSED
3 50 UNUSED
4 250 CLEARING
5 250 CLEARING
6 250 CURRENT

6 rows selected.

SQL> alter system set standby_file_management='manual';

System altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 1 ('+DATA') size 250M;

Database altered.

SQL> alter database add logfile group 2 ('+DATA') size 250M;

Database altered.

SQL> alter database add logfile group 3 ('+DATA') size 250M
SQL> /

Database altered.

SQL> select group#,bytes/1024/1024,status from v$log order by 1;

GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
1 250 UNUSED
2 250 UNUSED
3 250 UNUSED
4 250 CLEARING
5 250 CLEARING
6 250 CURRENT

6 rows selected.

7. All looks well now. Now, I change the standby file management back to AUTO and re-enable the managed recovery process.

SQL> alter system set standby_file_management='AUTO';

System altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

Issue should now be resolved. Hope this helps!

Clone database without backup 11gR2

2014-07-10T23:06:00.000-05:00

Here are the majority of the steps for creating a clone 11gR2 database.

1. Either configure your listener under the ORACLE_HOME for the standby (typically using NETCA) or if you are using 11gR2 RAC, you can use the Grid Infrastructure listener.ora to add your entries. Configure listener.ora with your clone SID and reload listener.
(example)
SID_LIST_LISTENER =
   (SID_DESC =
   (GLOBAL_DBNAME = clone)
   (SID_NAME = clone)
   (ORACLE_HOME = <ORACLE_HOME>)
   )
  )
   lsnrctl reload LISTENER

3. Modify tnsnames.ora in $TNS_ADMIN on both primary and standby locations to include both your standby database TNS entry and the primary database TNS entry.

(example)

   clone =
  (DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = < standby_hostname>)(PORT = 1521))
   )
   (CONNECT_DATA =
   (SERVICE_NAME = clone)
   )
  )
trgtdb =
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = <primary_hostname>)(PORT = 1521))
   (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = trgtdb)
   )
  )

4. Test connectivity both ways between both db instances via tnsping.
(example)
   tnsping clone
   tnsping trgtdb

5. Pre-create your directory structure for the standby database under the $ORACLE_BASE/admin.
(example)
   mkdir -p $ORACLE_BASE/admin/clone/adump

6. Copy oracle password file from production to your standby server under $ORACLE_HOME/dbs, also create dummy parameter file for auxiliary instance.

scp <target_host_name>:orapwtrgtdb $ORACLE_HOME/dbs/orapwclone
echo "db_name=clone" > $ORACLE_HOME/dbs/initclone.ora
echo "memory_target=<primary_memory_target>" >> $ORACLE_HOME/dbs/initclone.ora

7. Create clone.cmd file for RMAN to build out the clone database across the network.
(example)

connect target sys/oracle@trgtdb;

connect auxiliary sys/oracle@clone;

run {

allocate channel prm1 type disk;

allocate channel prm2 type disk;
allocate channel prm3 type disk;
allocate channel prm4 type disk;

allocate auxiliary channel stb1 type disk;

duplicate target database to "clone" from active database

spfile

PARAMETER_VALUE_CONVERT '/trgtdb/','/clone/'

set 'db_unique_name'='clone'

set 'control_files'='+DATA'

set db_create_file_dest='+DATA'

set db_create_online_log_dest_1='+DATA'

set db_recovery_file_dest='+FRA'

set db_recovery_file_dest_size='15G'

set cluster_database='FALSE'

set instance_number='1'

nofilenamecheck;

}

8. Startup the instance in NOMOUNT state and then start the RMAN duplicate job.
(example)
SQL> startup nomount
SQL> exit
nohup rman cmdfile clone.cmd log clone.log &

9. After the clone is completed, the clone instance should be now open read/write.

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 11 13:38:55 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

clone

ORA-01031: insufficient privileges (login as sysdba)

2014-06-03T22:53:00.005-05:00

It's a been a while since my last post. As you may know, I normally blog about solutions that are hard to find that haven't already been blogged about. Here's my latest encounter...

I was receiving this error when trying to login via "sqlplus / as sysdba" after completing an Oracle 11gR2 binary install using the "database home clone" methodology. I checked that the binary permissions were set correctly, which they were. Then finally I stumbled across someone else that had had this problem where the $ORACLE_HOME/rdbms/lib/config.c file wasn't configured properly.

Copyright (c) 1982, 2011, Oracle. All rights reserved.

ERROR:
ORA-01031: insufficient privileges

So I did the following:

cd <ORACLE_HOME>/rdbms/lib

cp -p config.c config.c.bak

vi config.c (with below changes)

relink oracle

config.c before changes:

/* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */

/* Refer to the Installation and User's Guide for further information. */

/* IMPORTANT: this file needs to be in sync with

rdbms/src/server/osds/config.c, specifically regarding the

number of elements in the ss_dba_grp array.

#define SS_DBA_GRP ""

#define SS_OPER_GRP ""

#define SS_ASM_GRP ""

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};

config.c after changes:

/* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */

/* Refer to the Installation and User's Guide for further information. */

/* IMPORTANT: this file needs to be in sync with

rdbms/src/server/osds/config.c, specifically regarding the

number of elements in the ss_dba_grp array.

#define SS_DBA_GRP "oinstall"

#define SS_OPER_GRP "oinstall"

#define SS_ASM_GRP ""

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};

Snapshot standby gone bad....almost

2013-10-26T14:37:00.003-05:00

I was recently involved in DR testing involving two standby databases. For the testing, I simply converted the 2 standby databases from physical standbys to snapshot standbys.

After the testing had ended from an application perspective, I started to convert the standby databases from snapshot standby back to physical standby. In the process of doing so, I received the following ORA-0600 error.

SQL > ALTER DATABASE CONVERT TO PHYSICAL STANDBY
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [krhahws_02], [], [], [], [], [],
[], [], [], [], [], []

Oh no! Never fear, I thought... I went to Oracle Support to research this issue and found the following resolution:

 1. set disk_asynch_io=FALSE in the init.ora (this setting is just
    going to be temporary for repeating the "flashback database").
 2. shutdown then "startup mount" the database, to pick up the new
    parameter setting for disk_asynch_io
 3. verify the disk_asynch_io parameter is now FALSE via:
     select name,value from v$parameter where name='disk_asynch_io';
 4. repeat the same "flashback database" command that just gave the
    ORA-00600:[krhahws_02], this time the operation is mostly a
    no-op as the datafile contents have already been flashed back
    to the target scn, but this time the final step, which clears
    the v$datafile.first_nonlogged_scn if needed, should complete
    correctly with no errors.
 5. remove "disk_asynch_io=FALSE" (added in #1 above) from the
    init.ora
 6. shutdown and "startup mount" the database to pick up the
    original parameter setting (of TRUE) for disk_asynch_io
 7. verify the disk_asynch_io parameter is now TRUE via
     select name,value from v$parameter where name='disk_asynch_io';
 8. open resetlogs, as usual following a "flashback database"

I implemented the workaround per Metalink note ID; however, I still couldn't convert the databases back to physical standby successfully.  I received the following error:

SQL > alter database convert to physical standby;
alter database convert to physical standby
*
ERROR at line 1:
ORA-01122: database file 108 failed verification check
ORA-01110: data file 108:
'+DATA/testdb/datafile/tbspname2008_01.755.796547639'
ORA-01207: file is more recent than control file - old control file

I tried to manually flashback to the GRP that is created as part of the conversion to snapshot standby. No dice!

I was beginning to think that the database was hosed until I decided to try to something else. I decided to open the database and start over again with converting the database back to physical standby with the disk_async_io being set to FALSE. So here's what I did.

SQL> alter database open resetlogs;

Database altered.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size 2235208 bytes

Variable Size 2432697528 bytes

Database Buffers 1828716544 bytes

Redo Buffers 12132352 bytes

Database mounted.

SQL > alter database convert to physical standby;

Database altered.

Well, what do you know.... It worked! After the conversion back to physical standby worked, I shutdown the database, did a startup mount of the database, and placed it back in auto recovery mode.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size 2235208 bytes

Variable Size 2432697528 bytes

Database Buffers 1828716544 bytes

Redo Buffers 12132352 bytes

Database mounted.

SQL > alter database recover managed standby database using current logfile disconnect;

Database altered.

I was back in business! I set disk_async_io parameter back to TRUE, bounced the standby, and restarted the auto recovery.

Just when I thought I had to rebuild 2 monstrous standby databases. I hope this helps others from having to rebuild!

Network ACL error message: bad argument

2013-10-26T07:10:00.003-05:00

I recently ran across this issue with a client that I was working while they were performing DR testing with their physical standbys. I converted their DR databases into snapshot standby mode (11g new feature). Everything was going well until the following errors occurred.

Here were my findings.

When trying to send an email using UTL_MAIL can result in the following error:

ORA-29261: bad argument

ORA-06512: at "SYS.UTL_TCP", line 17

ORA-06512: at "SYS.UTL_TCP", line 267

ORA-06512: at "SYS.UTL_SMTP", line 161

ORA-06512: at "SYS.UTL_SMTP", line 197

ORA-06512: at "SYS.UTL_MAIL", line 395

ORA-06512: at "SYS.UTL_MAIL", line 608

This error can result if SMTP_OUT_SERVER is not set as a database parameter or if the SUBJECT parameter in the UTL_MAIL call is omitted.

SQL> sho parameter smtp_out_server

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

smtp_out_server string

If the parameter has no value set, as above, correct this by setting up the email server name as an initialization parameter.

In my case, it was exactly this vs. the subject in the email being missing.

SQL> alter system set smtp_out_server = '<email_server_name>:<port_number>';

After doing this and having client try to reproduce the error, the problem didn't come back.

Hope this helps!

Active Data Guard Far Sync

2013-10-17T12:44:00.001-05:00

One of the many valuable benefits of Oracle 12c Database Data Guard.

Zero data loss protection and application continuity at any distance. More to come...

Configure TDE wallet on RAC

2013-01-22T15:03:00.000-06:00

To configure Oracle TDE (Transparent Data Encryption) wallet on a RAC, you need to do a few things to ensure that each database has its own wallet independent of each other. Here are the steps that I completed in order to configure this:

1. Set the environment variable ORACLE_UNQNAME on the OS in the .bash_profile. (In my case, it was Linux as this was 11.2.0.3 RAC on Linux). This env variable should be set to the database unique name (not instance name).

Please note: this needs to be configured in a way in each time the environment is sourced (. oraenv), the ORACLE_UNQNAME env variable will also be re-sourced.

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`

2. Set the environment variable also via srvctl.

srvctl setenv database -d RAC-HR -T "ORACLE_UNQNAME=RAC-HR"

3. Create wallet directory.

mkdir /u01/app/oracle/admin/${ORACLE_UNQNAME}/wallet

4. Configure sqlnet.ora as follows (example):

ENCRYPTION_WALLET_LOCATION =

(SOURCE = (METHOD = FILE)

(METHOD_DATA =

(DIRECTORY=/u01/app/oracle/admin/$ORACLE_UNQNAME/wallet)))

4. Create the wallet.

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "<password>";

5. Open wallet (if not already open).

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "<password>";

6. To configure auto login for wallet (optional), do the following:

orapki wallet create -wallet <wallet_location> -auto_login

7. Change permissions on directory and files. If you're not using a shared filesystem to locate the wallet(s), you'll need to complete the following on all applicable nodes in the RAC after you have copied the wallet files to exact same location across all nodes.

cd /u01/app/oracle/admin/$ORACLE_UNQNAME/

chmod 700 wallet

cd wallet

chmod 600 ewallet.p12

8. After initially creating the encryption wallet (and optionally a (local) auto-open wallet), navigate

to the directory that stores the Oracle Wallet and set the ‘immutable’ bit with:

# chattr +i ewallet.p12

# chattr +i cwallet.sso

Any attempt to delete the wallet (by root or any other user) fails; re-key operations that write to

the wallets will fail as well, so for re-key operations, the ‘immutable’ bit must be unset:

# chattr -i ewallet.p12

# chattr -i cwallet.sso

8. Please review the TDE Best practices at the following link:

http://www.oracle.com/technetwork/database/security/twp-transparent-data-encryption-bes-130696.pdf