Find out Oracle home directory of running Oracle instance on Linux

If /etc/oratab is deleted or corrupted, then
ps aux | grep ora_pmon
oracle   10937  0.0  0.0 2445960 6732 ?        Ss   May25  11:01 ora_pmon_CDRUAT
strings /proc/10937/environ | grep ORACLE_HOME
ORACLE_HOME=/opt/oracle/product/12.1.0.2/ee_3

Recovering a Database to a Previous Incarnation

Environment

Oracle 12.1, RMAN

Problem

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

Solution

Find a previous incarnation that you need.
Option 1: connect to a target database and catalog, run the following command and choose "Inc Key"
RMAN> LIST INCARNATION OF DATABASE DBPRD1;

Option 2: Connect to the recovery catalog, run the following command and choose DBINC_KEYB. Option 2 is more convenient as it provides more information, e.g. PARENT_DBINC_KEY.
select * from RC_DATABASE_INCARNATION where name='DBPRD1' order by DBINC_KEY;

Then use the incarnation key in RESET DATABASE, exempli gratia:
reset database to incarnation 3587196;
run {
set until time = "to_date('2019-05-27 10:00:00','YYYY-MM-DD HH24:MI:SS')";
allocate channel ch01 type 'SBT_TAPE' PARMS 'some parameters';
restore database;
recover database;
alter database open resetlogs;
}

ORACLE data block corrupted after restoring database (2)

A test database was restored from production backup. Some object have NOLOGGING, so recovery for such object will fail to roll changes forward.
When such object is used, the following errors happen

ORA-01578: ORACLE data block corrupted (file # 42, block # 1029253)
ORA-01110: data file 42: '/data001/oradata/CDRSIT/cdrsit_STG_DATA_02.dbf'

Find the affected segment:

select segment_type, owner, segment_name, 
case segment_type WHEN 'INDEX' THEN 'ALTER INDEX '||owner||'.'||segment_name||' UNUSABLE; ALTER INDEX '||owner||'.'||segment_name||' REBUILD;' 
                  WHEN 'TABLE' THEN 'TRUNCATE TABLE '||owner||'.'||segment_name||';'
end as SQLstatement
from dba_extents
where file_id = 42 -- change
and 1029253 -- change
between block_id and (block_id + blocks -1);

If it is an index, we can (make unusable and rebuild) or (drop and recreate).

Moving Oracle EM 12c OMR to another host

Environment

Oracle Enterprise Manager 12c (12.1.0.5, OEM12c), Oracle database 11.2.0.4, Oracle Linux 5.7

Solution

Moving Oracle Management Repository to another host.

On OMS host, Set ORACLE_HOME to OMS oracle home, test:

which emctl

Check the OMS repository configuration on the old host

emctl config oms -list_repos_details

Oracle Enterprise Manager Cloud Control 12c Release 5

Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.

Repository Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dtooem2)(PORT=1521)))(CONNECT_DATA=(SID=OEMDB)))

Repository User : SYSMAN

Make sure that no EM jobs are running.

Stop the OMS instances using the command emctl stop oms

emctl stop oms

Oracle Enterprise Manager Cloud Control 12c Release 5

Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.

Stopping WebTier...

WebTier Successfully Stopped

Stopping Oracle Management Server...

Oracle Management Server Successfully Stopped

Oracle Management Server is Down

Duplicate database with RMAN

Prepare INIT file. Check parameters with any directories.

Create SPFILE! Or DUPLICATE will fail:

RMAN-11001: Oracle Error:

ORA-32001: write to SPFILE requested but no SPFILE is in use

Create password file for the auxiliary instance as you must connect with a net service name:

RMAN-06217: not connected to auxiliary database with a net service name

Check and update TNSNAMES for BOTH target and auxiliary instances or you get:

ORA-17629: Cannot connect to the remote database server

ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified

RMAN script

rman log=$LOG append << EOF

connect auxiliary SYS/xxx@OEMDB_new

connect target SYS/xxx@OEMDB_old

run {

ALLOCATE CHANNEL tgt10 TYPE DISK;

ALLOCATE AUXILIARY CHANNEL dup1 TYPE DISK;

duplicate target database to '${ORACLE_SID}' from active database nofilenamecheck

logfile

group 1 ('/data001/oradata/${ORACLE_SID}/redo01.log') size 256M reuse,

group 2 ('/data001/oradata/${ORACLE_SID}/redo02.log') size 256M reuse,

group 3 ('/data001/oradata/${ORACLE_SID}/redo03.log') size 256M reuse,

group 4 ('/data001/oradata/${ORACLE_SID}/redo04.log') size 256M reuse;

}

exit;

EOF

Shutdown the old OMR database.

Correct the connect descriptor for the repository by running the following command on each OMS.

emctl config oms -store_repos_details -repos_conndesc "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=upooem1)(PORT=1521)))(CONNECT_DATA=(SID=OEMDB)))" -repos_user SYSMAN

Oracle Enterprise Manager Cloud Control 12c Release 5

Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.

Enter Repository User's Password :

Successfully updated datasources and stored repository details in Credential Store.

If there are multiple OMSs in this environment, run this store_repos_details command on all of them.

And finally, restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.

[oracle@dtooem2 ~]$ emctl stop oms -all

Oracle Enterprise Manager Cloud Control 12c Release 5

Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.

Stopping WebTier...

WebTier Successfully Stopped

Stopping Oracle Management Server...

Oracle Management Server Already Stopped

AdminServer Successfully Stopped

Oracle Management Server is Down

[oracle@dtooem2 ~]$ emctl start oms

Oracle Enterprise Manager Cloud Control 12c Release 5

Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.

Starting Oracle Management Server...

Starting WebTier...

WebTier Successfully Started

Oracle Management Server Successfully Started

Oracle Management Server is Up

Do not do the next step

[dtooem2:ora] /home/oracle # emctl config repos -host upooem1 -oh /opt/oracle/product/11.2.0.4/ee_2 -conn_desc "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=upooem1)(PORT=1521)))(CONNECT_DATA=(SID=OEMDB)))"

Oracle Enterprise Manager Cloud Control 12c Release 5

Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.

Please enter repository password:

No valid repository target found! Please enter the repository name:

OEMDB

Enter password :

Login successful

Target "OEMDB:oracle_database" modified successfully

Command completed successfully!

And it updated “Monitoring Configuration” of the old database target.

The Oracle Documentation says: “No database on host "B" has been discovered.”. The opposite worked well for me.

Discover the new database target first. Then

emctl config repos -host upooem1 -oh /opt/oracle/product/11.2.0.4/ee_2 -conn_desc "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=upooem1)(PORT=1521)))(CONNECT_DATA=(SID=OEMDB)))"

Oracle Enterprise Manager Cloud Control 12c Release 5

Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.

Please enter repository password:

Enter password :

Login successful

Target "OEMDB1:oracle_database" modified successfully

Command completed successfully!

Go to “Management Services and Repository” and update “Target Setup” > “Monitoring Configuration”

Configure backups for the new OMR database.

Oracle Documentation

Enterprise Manager Cloud Control Advanced Installation and Configuration Guide -> 19 Backing Up and Recovering Enterprise Manager

https://docs.oracle.com/cd/E24628_01/install.121/e24089/ha_backup_recover.htm#EMADV10745

Creating a file system for Oracle database

Environment: Oracle Linux 7.5

Steps:

1. Create Virtual Disk on VM.

List all disks.

lsblk

lsblk -o name,mountpoint,label,size,uuid,partlabel,partuuid,serial,wwn,fstype

2. Create a partition table and a GPT partition: mkpart <PART-LABEL> <START> <END>

parted /dev/xvdc

(parted) mktable gpt

(parted) mkpart OMR 0% 100%

(parted) quit

3. Create EXT4 file system

mkfs.ext4 -T largefile4 /dev/xvdc1

lsblk -o name,mountpoint,label,size,uuid,partlabel,partuuid,serial,wwn,fstype

4. Mount and set permissions

mkdir -p /data001/oradata/OEMDB

vi /etc/fstab

add the following:

UUID=xxxx /data001/oradata/OEMDB ext4 noatime,nodiratime,nobarrier 1 2

mount /data001/oradata/OEMDB

df -h

chown oracle:oinstall /data001/oradata/OEMDB

ls -ld /data001/oradata/OEMDB

5. Reducing reserved blocks

By default 5% of the space to be usable only by root. E.g. You can reduce it to 0.5% by doing:

tune2fs -m 0.5 /dev/xvdc1

tune2fs -l /dev/xvdc1

Documentation

Improve I/O Performance On ext3/ext4 File Systems With The "noatime" Mount Option (Doc ID 1561740.1)

Available Mount Options to Improve ext4 Filesystem Performance (Doc ID 1476869.1)

Supported and Recommended File Systems on Linux (Doc ID 236826.1)

Oracle Database - Filesystem & I/O Type Supportability on Oracle Linux 6 (Doc ID 1601759.1)

Create XFS file system

In similar way, create FRA 30GiB, XFS file system, /data001/oradata/fast_recovery_area

mkfs.xfs /dev/xvde1

mkdir -p /data001/oradata/fast_recovery_area

lsblk -o name,mountpoint,label,size,uuid,partlabel,partuuid,serial,wwn,fstype

vi /etc/fstab

UUID=xxxx /data001/oradata/fast_recovery_area  xfs  defaults 0 0

mount /data001/oradata/fast_recovery_area

chown oracle:oinstall /data001/oradata/fast_recovery_area

ls -ld /data001/oradata/fast_recovery_area

Memory swapping on Linux

Environment
Oracle Linux 7 with Oracle databases 12.1

Monitoring
free -h
top
vmstat [options] [delay [count]]

monitoring a particular process
ps -o "ppid pid vsz rss pmem comm args" --pid
top -p  
pmap -x

adding SWAP to top and changing SORT column

a. Run the TOP command:

# top

b. On your keyboard press the " f " key and scroll down using the arrow key until you have selected "SWAP" then press to select it. This should add a " * " symbol in front of it.

c. While still selecting "SWAP"  press the arrow key, which highlights the entire SWAP line, and using the arrow key move it up to one of the first options (anywhere above "COMMAND").

d. While still having "SWAP" selected, type the " s " key which will configure top to SORT by the currently selected option, in this case SWAP.

e. Finally " q " to save the configuration changes and view the results.

f. Perform your review as needed and press "q" again to exit top command.

g. If you want to save this top configuration press W (upper case)

Kernel parameters and tuned service

check the current parameters

sysctl -a|grep 'dirty\|swap'

check status of tuned.service, it overwrites parameters in /etc/sysctl.conf !

systemctl status tuned.service

Check the active tune profile. 

tuned-adm active

Current active profile: virtual-guest

see all profiles

cd /usr/lib/tuned

ls -lh

For example, modify the current profile

cd /usr/lib/tuned/virtual-guest

vi tuned.conf

comment existing vm.* parameters and add the following

# recommended by RedHat. Deploying Oracle Database 12c Release 2 on Red Hat Enterprise Linux 7.

vm.swappiness = 1

vm.dirty_background_ratio = 3

vm.dirty_ratio = 80

vm.dirty_expire_centisecs = 500

vm.dirty_writeback_centisecs = 100

re-activate the changed profile

tuned-adm profile virtual-guest

show the current kernel parameters

sysctl -a|grep 'dirty\|swap'

MOSC Documentation

Linux OS, Swapping and Databases (Doc ID 1295478.1)

How to Check Swap Usage Live via the 'top' Command on Oracle Linux 5, 6, and 7. (Doc ID 2422888.1)

Why is SWAP being used instead of available physical memory? (Doc ID 2404462.1)

Oracle Linux 7 - sysctl parameter doesn't take effect after reboot (Doc ID 2195319.1)

Oracle Linux: Modifying kernel parameters using sysctl (Doc ID 390279.1)

What are Dirty Pages? (Doc ID 2304722.1)

RedHat Documentation

Deploying Oracle Database 12c Release 2 on Red Hat Enterprise Linux 7

Red Hat Enterprise Linux 7 Performance Tuning Guide Chapter 3. Tuned

Red Hat Enterprise Linux 7 Performance Tuning Guide 3.2. Performance Tuning with tuned and tuned-adm

Configuring Disk Devices Manually for Oracle ASM (without ASMLib)

Environment: Oracle Linux Server release 7.3 on Oracle VM 3.4.3

In Oracle VM Manager, edit VM and add a new Virtual Disk, e.g.

 

In the VM as root, see the new disk:

 

lsblk

NAME    MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT

xvda    202:0    0   30G  0 disk

├─xvda1 202:1    0  512M  0 part /boot

├─xvda2 202:2    0    8G  0 part /

├─xvda3 202:3    0    4G  0 part /tmp

├─xvda4 202:4    0    1K  0 part

├─xvda5 202:5    0  1.5G  0 part /home

└─xvda6 202:6    0   16G  0 part [SWAP]

xvdb    202:16   0   50G  0 disk

└─xvdb1 202:17   0   50G  0 part /opt/oracle

xvdc    202:32   0   10G  0 disk

└─xvdc1 202:33   0   10G  0 part /opt/netbackup

xvdd    202:48   0  800G  0 disk

└─xvdd1 202:49   0  800G  0 part /data001/oradata/CDRDEV

xvde    202:64   0  200G  0 disk

└─xvde1 202:65   0  200G  0 part /data001/fast_recovery_area

xvdf    202:80   0  800G  0 disk

└─xvdf1 202:81   0  800G  0 part /data001/oradata/CDRSIT

xvdg    202:96   0  100G  0 disk

└─xvdg1 202:97   0  100G  0 part /data001/archivelogs

xvdh    202:112  0  800G  0 disk

└─xvdh1 202:113  0  800G  0 part /data001/oradata/CDRDEV1

xvdi    202:128  0  800G  0 disk

└─xvdi1 202:129  0  800G  0 part /data001/oradata/CDRSIT1

xvdj    202:144  0  800G  0 disk

└─xvdj1 202:145  0  800G  0 part

 

For persistent device naming, we can configure ASMLIB or set udev rules. We need to use a partition UUID (not a filesystem UUID) in udev rules. For this, use parted to create a GPT partition.

parted /dev/xvdj

mktable gpt

 

to create a GPT partition:

mkpart PART-LABEL START END

e.g.

mkpart asmpart1 0% 100%

(parted) print

Model: Xen Virtual Block Device (xvd)

Disk /dev/xvdj: 859GB

Sector size (logical/physical): 512B/512B

Partition Table: gpt

Disk Flags:

 

Number  Start   End    Size   File system  Name      Flags

 1      1049kB  859GB  859GB               asmpart1

 

udevadm info --query=property /dev/xvdj1

DEVLINKS=/dev/disk/by-partlabel/asmpart1 /dev/disk/by-partuuid/48940eb5-f6fa-4558-aeba-b12b81199e27 /dev/oracleasm/disk1

DEVNAME=/dev/xvdj1

DEVPATH=/devices/vbd-51856/block/xvdj/xvdj1

DEVTYPE=partition

ID_FS_TYPE=oracleasm

ID_FS_USAGE=filesystem

ID_PART_ENTRY_DISK=202:144

ID_PART_ENTRY_NAME=asmpart1

ID_PART_ENTRY_NUMBER=1

ID_PART_ENTRY_OFFSET=2048

ID_PART_ENTRY_SCHEME=gpt

ID_PART_ENTRY_SIZE=1677717504

ID_PART_ENTRY_TYPE=ebd0a0a2-b9e5-4433-87c0-68b6b72699c7

ID_PART_ENTRY_UUID=48940eb5-f6fa-4558-aeba-b12b81199e27

ID_PART_TABLE_TYPE=gpt

MAJOR=202

MINOR=145

SUBSYSTEM=block

TAGS=:systemd:

USEC_INITIALIZED=800372237892

 

 

vi /etc/udev/rules.d/99-oracle-asmdevices.rules

KERNEL=="xvd??", ENV{ID_PART_ENTRY_UUID}=="48940eb5-f6fa-4558-aeba-b12b81199e27", SYMLINK+="oracleasm/disk1", OWNER="oracle", GROUP="dba", MODE="0660"

 

To implement the new rules:

udevadm trigger

 

ls -la /dev/oracleasm/disk1

lrwxrwxrwx. 1 root root 8 Feb 15 12:32 /dev/oracleasm/disk1 -> ../xvdj1

 

ls -la /dev/xvd*

brw-rw----. 1 root   disk 202,   0 Feb 15 12:32 /dev/xvda

brw-rw----. 1 root   disk 202,   1 Feb 15 12:32 /dev/xvda1

brw-rw----. 1 root   disk 202,   2 Feb 15 12:32 /dev/xvda2

brw-rw----. 1 root   disk 202,   3 Feb 15 12:32 /dev/xvda3

brw-rw----. 1 root   disk 202,   4 Feb 15 12:32 /dev/xvda4

brw-rw----. 1 root   disk 202,   5 Feb 15 12:32 /dev/xvda5

brw-rw----. 1 root   disk 202,   6 Feb 15 12:32 /dev/xvda6

brw-rw----. 1 root   disk 202,  16 Feb 15 12:32 /dev/xvdb

brw-rw----. 1 root   disk 202,  17 Feb 15 12:32 /dev/xvdb1

brw-rw----. 1 root   disk 202,  32 Feb 15 12:32 /dev/xvdc

brw-rw----. 1 root   disk 202,  33 Feb 15 12:32 /dev/xvdc1

brw-rw----. 1 root   disk 202,  48 Feb 15 12:32 /dev/xvdd

brw-rw----. 1 root   disk 202,  49 Feb 15 12:32 /dev/xvdd1

brw-rw----. 1 root   disk 202,  64 Feb 15 12:32 /dev/xvde

brw-rw----. 1 root   disk 202,  65 Feb 15 12:32 /dev/xvde1

brw-rw----. 1 root   disk 202,  80 Feb 15 12:32 /dev/xvdf

brw-rw----. 1 root   disk 202,  81 Feb 15 12:32 /dev/xvdf1

brw-rw----. 1 root   disk 202,  96 Feb 15 12:32 /dev/xvdg

brw-rw----. 1 root   disk 202,  97 Feb 15 12:32 /dev/xvdg1

brw-rw----. 1 root   disk 202, 112 Feb 15 12:32 /dev/xvdh

brw-rw----. 1 root   disk 202, 113 Feb 15 12:32 /dev/xvdh1

brw-rw----. 1 root   disk 202, 128 Feb 15 12:32 /dev/xvdi

brw-rw----. 1 root   disk 202, 129 Feb 15 12:32 /dev/xvdi1

brw-rw----. 1 root   disk 202, 144 Feb 15 12:32 /dev/xvdj

brw-rw----. 1 oracle dba  202, 145 Feb 15 12:32 /dev/xvdj1

 

to see all disks and partitions:

lsblk -o name,mountpoint,label,size,uuid,partlabel,partuuid,serial,wwn,fstype

 

Alternatively, you can use a partition label name ID_PART_ENTRY_NAME:

cat /etc/udev/rules.d/99-oracle-asmdevices.rules

KERNEL=="xvd??", ENV{ID_PART_ENTRY_UUID}=="48940eb5-f6fa-4558-aeba-b12b81199e27", SYMLINK+="oracleasm/disk1", OWNER="oracle", GROUP="dba", MODE="0660"

KERNEL=="xvd??", ENV{ID_PART_ENTRY_NAME}=="asmpart2", SYMLINK+="oracleasm/disk2", OWNER="oracle", GROUP="dba", MODE="0660"

 

lsblk -o name,partlabel,partuuid

NAME    PARTLABEL PARTUUID

xvda

├─xvda1

├─xvda2

├─xvda3

├─xvda4

├─xvda5

└─xvda6

xvdb

└─xvdb1

xvdc

└─xvdc1

xvdd

└─xvdd1

xvde

└─xvde1

xvdf

└─xvdf1

xvdg

└─xvdg1

xvdh

└─xvdh1

xvdi

└─xvdi1

xvdj

└─xvdj1 asmpart1  48940eb5-f6fa-4558-aeba-b12b81199e27

xvdk

└─xvdk1 asmpart2  0b8ba20c-a10d-4564-b582-0692fd9657e8