Troubleshoot oem status down

If a target shows down in oem best way to troubleshoot is logging into agent from browser and clicking the target and select metric called response. Once you click it, a new page with error message will be displayed. This makes troubleshooting a lot easier.

https://haeissapbgwv.hke.local:3872/emd/browser/main/

Just replace the red part with agent name of the target and enter host oracle id and password.

RAC dataguard

standby

Source database : frepo

unq - frepo
db_name - frepo
instance names - frepo1, frepo2


Target database : frepodr

unq - frepodr
db_name - frepo
instance names - frepo1, frepo2

Create temporary listener

LISTENER_TEMP=
      (DESCRIPTION=
       (ADDRESS_LIST=
        (ADDRESS= (PROTOCOL=TCP)(HOST=exadb01)(PORT=1525))
      )
     )

SID_LIST_LISTENER_TEMP =
    (SID_LIST=
     (SID_DESC=
     (SID_NAME=frepo1)
     (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1)
     )
    )
******************************************************************************
Primary

Add tns entries of source and target on all the 2 nodes.

frepo =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pxadb01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = frepo)
    )
  )

frepoDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = exadb01)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = frepo1)
    )
  )
******************************************************************************
Standby

Add tns entries of source and target on all the 3 nodes.

frepo =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pxadb01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = frepo)
    )
  )

frepoDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = exadb01)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = frepo1)
    )
  )
******************************************************************************
Primary
ALTER DATABASE FORCE LOGGING;
alter system set log_archive_max_processes=4 scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+RECO_DG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=frepo' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=frepodr VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=frepodr' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both sid='*';
alter system set STANDBY_FILE_MANAGEMENT='AUTO'scope=both sid='*';
alter system set FAL_SERVER='frepodr'scope=both sid='*';
alter system set FAL_CLIENT='frepo'scope=both sid='*';
alter system set log_archive_config='dg_config=(frepo,frepodr)' scope=both sid='*';


create pfile from spfile;

alter user sys identified by "xxxxxxxx" account unlock;

orapwd file=orapwfrepo1 password=xxxxxxxx entries=5 force=y

scp  initfrepo1.ora oracle@exadb01:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initfrepo1.ora



scp  orapwfrepo1 oracle@exadb01:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwfrepo1
scp  orapwfrepo1 oracle@exadb02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwfrepo2


******************************************************************************
Standby

At this point Parameter file on standby server will have all parameters including RAC

Rename the init file as below to differentiate

cp initfrepo1.ora initfrepo1.ora.rac

Now edit the initfrepo1.ora and remove all the RAC related parameters which includes below

instance related, cluster_database, threads, undo etc.. Keep the frepo1_xxx parameters as is.

2nd instance pfile

frepo1.__db_cache_size=1056964608
frepo1.__java_pool_size=16777216
frepo1.__large_pool_size=33554432
frepo1.__pga_aggregate_target=536870912
frepo1.__sga_target=1610612736
frepo1.__shared_io_pool_size=0
frepo1.__shared_pool_size=419430400
frepo1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/frepo/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='frepo'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=frepodrXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_listener='exadb-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1610612736
*.standby_file_management='AUTO'
*.db_unique_name='frepodr'
*.LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=frepodr'
*.LOG_ARCHIVE_DEST_2='SERVICE=frepo VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=frepo'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.FAL_SERVER='frepo'
*.FAL_CLIENT='frepodr'
*.control_files='+DATA/frepo/controlfile/control01.ctl'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.log_archive_config='dg_config=(frepo,frepodr)'




Create respective directories here as per pfile

. oraenv=frepo1

startup nomount

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

Primary

rman target sys/xxxxxxxx@frepo auxiliary sys/xxxxxxxx@frepoDR


duplicate target database for standby from active database dorecover;

After finish exit rman console

******************************************************************************
Standby

create a rac pfile called initmain.ora
It looks like below

frepo1.__db_cache_size=1056964608
frepo2.__db_cache_size=1056964608
frepo2.__java_pool_size=16777216
frepo1.__java_pool_size=16777216
frepo1.__large_pool_size=33554432
frepo2.__large_pool_size=33554432
frepo2.__pga_aggregate_target=536870912
frepo1.__pga_aggregate_target=536870912
frepo2.__sga_target=1610612736
frepo1.__sga_target=1610612736
frepo2.__shared_io_pool_size=0
frepo1.__shared_io_pool_size=0
frepo2.__shared_pool_size=419430400
frepo1.__shared_pool_size=419430400
frepo2.__streams_pool_size=0
frepo1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/frepo/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='frepo'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
frepo1.instance_number=1
frepo2.instance_number=2
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_listener='pxadb-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1610612736
*.standby_file_management='AUTO'
frepo2.thread=2
frepo1.thread=1
frepo2.undo_tablespace='UNDOTBS2'
frepo1.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest='+DATA'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=frepodrXDB)'
*.db_unique_name='frepodr'
*.LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=frepodr'
*.LOG_ARCHIVE_DEST_2='SERVICE=frepo VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=frepo'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.FAL_SERVER='frepo'
*.FAL_CLIENT='frepodr'
*.control_files='+DATA/frepo/controlfile/control01.ctl'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.log_archive_config='dg_config=(frepo,frepodr)'



Make spfile from that


create spfile='+DATA/frepo/parameterfile/spfilefrepo.ora' from pfile='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initmain.ora';

shut immediate;

vi initfrepo1.ora
spfile='+DATA/frepo/parameterfile/spfilefrepo.ora'

scp  initfrepo1.ora oracle@exadb02:/opt/app/oracle/product/11.2.0.4/dbs/initfrepo2.ora

******************************************************************************
Standby

Adding the 2 instances to cluster


srvctl add database -d frepo -o /u01/app/oracle/product/11.2.0.4/dbhome_1 -p +DATA/frepo/parameterfile/spfilefrepo.ora -r physical_standby -a DATA,REDO

srvctl add instance -d frepo -i frepo1 -n exadb01
srvctl add instance -d frepo -i frepo2 -n exadb02

srvctl start database -d frepo

srvctl status database -d frepo

srvctl config database -d frepo

******************************************************************************
Standby
After verifying the config stop the database on all the 2 instances and start on a single instance.

srvctl start instance -d frepo -i frepo1

sqlplus / as sysdba

start MRP process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

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


Note

To stop MRP process use

alter database recover managed standby database cancel;
******************************************************************************

To very dataguard sync use below commands

Standby

select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

PRIMARY

select thread#,max(sequence#) from v$archived_log group by thread#;

or

select max(sequence#) from v$archived_log where  resetlogs_change#=(SELECT resetlogs_change# FROM v$database)

resize or extend /u01 on exadata

First find how much space available to extend

[root@xxx-bac02 ~]# vgdisplay -s
  "VGExaDb" 3.27 TiB  [185.00 GiB used / 3.09 TiB free]

Here we have 3.09TB free

First check the existing size of /u01

df -kh |grep u01

/dev/mapper/VGExaDb-LVDbOra1                  99G   27G   67G  29% /u01

Currently it is 100GB

I want to increase to 150GB

So here are the commands

lvresize -L +50G /dev/mapper/VGExaDb-LVDbOra1

resize2fs  /dev/mapper/VGExaDb-LVDbOra1

Now if you recheck the size again

df -kh |grep u01

/dev/mapper/VGExaDb-LVDbOra1                 148G   36G  105G  26% /u01

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

For XFS file system on new exadata machines

determine if it xfs or ext etc

[root@nasipedwdb-bac02 ~]# df -Th |grep /

Filesystem                                  Type      Size  Used Avail Use% Mounted on

/dev/mapper/VGExaDb-LVDbSys1                xfs        15G   12G  3.3G  79% /

lvresize -L +50G /dev/mapper/VGExaDb-LVDbSys1

xfs_growfs -d /

[root@nasipedwdb-bac02 ~]# df -kh

Filesystem                                   Size  Used Avail Use% Mounted on

/dev/mapper/VGExaDb-LVDbSys1                  65G   12G   54G  19% /

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

creating swapspace on exadata

[root@n dev]# swapon -s

Filename                                Type            Size    Used    Priority

/dev/dm-4                               partition       16777212        0       -2

Add 110gb swap file

dd if=/dev/zero of=/swapspace bs=1M count=112640

mkswap /swapspace

chmod 600 swapspace

swapon /swapspace

 swapon -s

Filename                                Type            Size    Used    Priority

/dev/dm-4                               partition       16777212        0       -2

/swapspace                              file    115343356       0       -3

[root@ /]# free -g

              total        used        free      shared  buff/cache   available

Mem:           1007         382         341          48         283         555

Swap:           125           0         125

Set persistent permissions across reboot in linux and UDEV rules for ASM disks

For asm disks owner should be oracle:dba

You need to setup udev rules or edit rc.local 

else after reboot permissions will change back to root

There are 2 methods to do it. Oracle recommends setting up UDEV

Here is alternative to udev

vi /etc/rc.local

#!/bin/bash

# THIS FILE IS ADDED FOR COMPATIBILITY PURPOSES

#

# It is highly advisable to create own systemd services or udev rules

# to run scripts during boot instead of using this file.

#

# In contrast to previous versions due to parallel execution during boot

# this script will NOT be run after all other services.

#

# Please note that you must run 'chmod +x /etc/rc.d/rc.local' to ensure

# that this script will be executed during boot.

touch /var/lock/subsys/local

chown oracle:dba /dev/mapper/mpatha

chown oracle:dba /dev/mapper/mpathb

chown oracle:dba /dev/mapper/mpathc

chmod 660 /dev/mapper/mpatha

chmod 660 /dev/mapper/mpathb

chmod 660 /dev/mapper/mpathc

After that check status of service. If its not enabled enable it

systemctl status rc-local.service

systemctl enable rc-local

systemctl start rc-local

2nd method using UDEV

#############preparing disks for ASM

/usr/lib/udev/scsi_id -g -u -d /dev/sdc

3624a93707784b3a3c17b437c00011074

/usr/lib/udev/scsi_id -g -u -d /dev/sdd

3624a93707784b3a3c17b437c0001106f

/usr/lib/udev/scsi_id -g -u -d /dev/sde

3624a93707784b3a3c17b437c00011071

vi /etc/scsi_id.config

options=-g

wq!

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

KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="3624a93707784b3a3c17b437c00011074", SYMLINK+="asm-disk1", OWNER="oracle", GROUP="dba", MODE="0660"

KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="3624a93707784b3a3c17b437c0001106f", SYMLINK+="asm-disk2", OWNER="oracle", GROUP="dba", MODE="0660"

KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="3624a93707784b3a3c17b437c00011071", SYMLINK+="asm-disk3", OWNER="oracle", GROUP="dba", MODE="0660"

##############Load rules

/sbin/partprobe /dev/sdc1

/sbin/partprobe /dev/sdd1

/sbin/partprobe /dev/sde1

##################Test rules

udevadm test /block/sdc/sdc1

udevadm test /block/sdd/sdd1

udevadm test /block/sde/sde1

#################Restart UDEV Service

udevadm control --reload-rules

check permissions

cd /dev

ls -lHL /dev/tas*

chown -R oracle:dba asm-disk1

chown -R oracle:dba asm-disk2

chown -R oracle:dba asm-disk3

chmod 660 asm-disk1

chmod 660 asm-disk2

chmod 660 asm-disk3

ls -lHL /dev/asm*

set password not to expire linux

To check current password expire date

# chage -l tibero
Last password change                                    : Jul 03, 2019
Password expires                                        : Oct 02, 2019
Password inactive                                       : never
Account expires                                         : never
Minimum number of days between password change          : 0
Maximum number of days between password change          : 91
Number of days of warning before password expires       : 7

To change

# passwd -x -1 tibero

Adjusting aging data for user tibero.

passwd: Success

Verify change again

# chage -l tibero

Last password change                                    : Jul 03, 2019

Password expires                                        : never

Password inactive                                       : never

Account expires                                         : never

Minimum number of days between password change          : 0

Maximum number of days between password change          : -1

Number of days of warning before password expires       : 7

Oracle database and EMC network MML error

using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 7 days
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 04/18/2019 11:00:14
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2



You need to link below

$ cd $ORACLE_HOME/lib

$ ln -s /usr/lib/libnsrora.so libobk.so

Network ACL error after 12c database upgrade

begin
    dbms_network_acl_admin.append_host_ace (
    host=>'*',
    lower_port => 1,
    upper_port => 65535,
         ace=> sys.xs$ace_type( privilege_list=>sys.XS$NAME_LIST('JDWP') ,
                                principal_name=>'HMA_TM_PROD',
                                principal_type=>sys.XS_ACL.PTYPE_DB));
end;
/


BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => '*',
    lower_port => 1,
    upper_port => 65535,
    ace        => xs$ace_type(privilege_list => xs$name_list('connect'),
                              principal_name => 'HMA_TM_PROD',
                              principal_type => xs_acl.ptype_db));
END;
/

*************************************
send test email from database

Create proc

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_message   IN VARCHAR2,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);
  UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.quit(l_mail_conn);
END;
/

Add privs


BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'send_mailx.xml',
    description  => 'Purpose of the acl is to send mail',
    principal    => 'sravan',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

   DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'send_mailx.xml',
    host        => '*',
    lower_port  => 1,
    upper_port  => 9999);

  COMMIT;
END;
/


Send
BEGIN
  send_mail(p_to        => 'xxx10563@xxx.local',
            p_from      => 'xxx-dba-alerts@xxx.local',
            p_message   => 'This is a test message.',
            p_smtp_host => '10.120.xx.xx');
END;
/