CRS-2730: Resource 'ora.erpdb.db' depends on resource 'ora.RMAN_BKP.dg'

Cause : If you want to delete resource from clusterware which are using by another service then you have to remove dependence.

Solution : Remove the dependence before remove service.


[grid@erpdb01 ~]$ crsctl delete res ora.RMAN_BKP.dg
CRS-2730: Resource 'ora.erpdb.db' depends on resource 'ora.RMAN_BKP.dg'
CRS-4000: Command Delete failed, or completed with errors.
[grid@erpdb01 ~]$ srvctl config database -d erpdb
Database unique name: erpdb
Database name: erpdb
Oracle home: /u01/app/oracle/product/11.2.4/db_1
Oracle user: oracle
Spfile: +DATA/erpdb/spfileerpdb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: erpdb
Database instances: erpdb1,erpdb2
Disk Groups: DATA,FRA,RMAN_BKP
Mount point paths:
Services:
Type: RAC
Database is administrator managed
[grid@erpdb01 ~]$ su oracle
Password:
[oracle@erpdb01 grid]$ cd
[oracle@erpdb01 ~]$ . ./.bash_profile
[oracle@erpdb01 ~]$ srvctl modify database -d erpdb -a "DATA,FRA"
[oracle@erpdb01 ~]$ srvctl config database -d erpdb
Database unique name: erpdb
Database name: erpdb
Oracle home: /u01/app/oracle/product/11.2.4/db_1
Oracle user: oracle
Spfile: +DATA/erpdb/spfileerpdb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: erpdb
Database instances: erpdb1,erpdb2
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Database is administrator managed
[oracle@erpdb01 ~]$
[grid@erpdb01 ~]$ crsctl delete res ora.RMAN_BKP.dg
[grid@erpdb01 ~]$

How to kill session from non-DBA User / Grant kill session privilege to non-DBA user.

ALTER SYSTEM privilege is required to kill session, it is not safe to give ALTER SYSTEM privilege to non-DBA user, but when you have to give kill session privilege to non-DBA you can create procedure to kill session and grant them to execute.


SQL> CREATE OR REPLACE PROCEDURE SP_KILL_SESSION (P_SID NUMBER, P_SERIAL NUMBER)
AS
  2    3     V_USER_NAME   VARCHAR2 (30);
  4  BEGIN
  5     SELECT USERNAME
  6       INTO V_USER_NAME
  7       FROM V$SESSION
  8      WHERE SID = P_SID AND SERIAL# = P_SERIAL;
  9
 10     IF V_USER_NAME IN ('DEVUSER')
 11     THEN
 12        EXECUTE IMMEDIATE
 13           'ALTER SYSTEM KILL SESSION ''' || P_SID || ',' || P_SERIAL || '''';
 14     ELSIF V_USER_NAME IS NULL
 15     THEN
      RAISE_APPLICATION_ERROR (-20001, 'Invalid Session ID');
 16   17     END IF;
 18  END SP_KILL_SESSION;
 19  /

Procedure created.

SQL> grant execute on sys.SP_KILL_SESSION to devuser;

Grant succeeded.

SQL>
SQL> CREATE SYNONYM DEVUSER.SP_KILL_SESSION FOR SYS.SP_KILL_SESSION;

Synonym created.

########### Now login into devuser schema ############

SQL> conn devuser/devuser
Connected.
SQL>
SQL> SELECT SID, SERIAL# FROM V$SESSION
WHERE USERNAME='DEVUSER';  2

       SID    SERIAL#
---------- ----------
      3978      49965

########### Open another terminal and login into devuser schema ############

SQL> SELECT SID, SERIAL# FROM V$SESSION
WHERE USERNAME='DEVUSER';   2

       SID    SERIAL#
---------- ----------
      3418      29112
      3978      49965

SQL> exec SP_KILL_SESSION(3418,29112);

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT SID, SERIAL# FROM V$SESSION
WHERE USERNAME='DEVUSER';  2

       SID    SERIAL#
---------- ----------
      3978      49965

ACFS-03171: Insufficient contiguous free ASM Diskgroup space

Cause : 

1. Check if there are available space exist in your disk group.
2. Check re balance process are running or not.

Solution:

1. If disk space are not available reduce the size or add new LUN in your disk group.
2. If re balance process are running wait to complete the process.

erpdcdb01 acfsutil size +300G /backup
acfsutil size: ACFS-03171: Insufficient contiguous free ASM Diskgroup space.  Check the ASM alert log.
erpdcdb01 asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  Y         512   4096  8388608   2150400   317160                0          317160              0             N  BACKUP/
MOUNTED  EXTERN  N         512   4096  4194304   3686400  1456524                0         1456524              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  4194304   1228800  1043700                0         1043700              0             N  FRA/
MOUNTED  HIGH    N         512   4096  4194304     25600    24212            10240            4657              0             Y  OCR_VOT/
MOUNTED  EXTERN  N         512   4096  1048576    204800   177992                0          177992              0             N  REDO/
ASMCMD> exit
erpdcdb01


erpdcdb01 sqlplus "/as sysasm"

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 15 20:00:30 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
EST_MINUTES ERROR_CODE
----------- --------------------------------------------
           1 REBAL RUN           1          1      15841      32731        837
         20


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
erpdcdb01

How to perform Backup and Restore of ASM Metadata

[grid@erpdcdb01 ~]$ asmcmd
ASMCMD> md_backup /u01/Stage/asmbackup/asmmetadata.backup -G RMAN_BKP
Disk group metadata to be backed up: RMAN_BKP
Current alias directory path: ASM
Current alias directory path: SBLCBS/BACKUPSET
Current alias directory path: SBLCBS/BACKUPSET/2016_11_29
Current alias directory path: SBLCBS/BACKUPSET/2016_12_06
Current alias directory path: SBLCBS
Current alias directory path: ASM/DUMPSET
ASMCMD> exit
[grid@erpdcdb01 ~]$
[grid@erpdcdb01 ~]$ srvctl status diskgroup -g RMAN_BKP
Disk Group RMAN_BKP is running on erpdcdb01,erpdcdb02
[grid@erpdcdb01 ~]$ srvctl stop diskgroup -g RMAN_BKP -f
[grid@erpdcdb01 ~]$ srvctl status diskgroup -g RMAN_BKP
Disk Group RMAN_BKP is not running
[grid@erpdcdb01 ~]$ srvctl remove  diskgroup -g RMAN_BKP -f
[grid@erpdcdb01 ~]$ srvctl status diskgroup -g RMAN_BKP
PRCA-1000 : ASM Disk Group RMAN_BKP does not exist
PRCR-1001 : Resource ora.RMAN_BKP.dg does not exist
[grid@erpdcdb01 ~]$ asmcmd lsdg -g
Inst_ID  State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
      2  MOUNTED  EXTERN  N         512   4096  1048576    322553   288859                0          288859              0             N  DATA/
      1  MOUNTED  EXTERN  N         512   4096  1048576    322553   288859                0          288859              0             N  DATA/
      2  MOUNTED  EXTERN  N         512   4096  1048576     92158    61454                0           61454              0             N  FRA/
      1  MOUNTED  EXTERN  N         512   4096  1048576     92158    61454                0           61454              0             N  FRA/
      2  MOUNTED  NORMAL  N         512   4096  1048576     12285    11359             4095            3632              0             Y  OCR/
      1  MOUNTED  NORMAL  N         512   4096  1048576     12285    11359             4095            3632              0             Y  OCR/
[grid@erpdcdb01 ~]$
[grid@erpdcdb01 ~]$ sqlplus "/as sysasm"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 6 07:57:24 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> # drop diskgroup RMAN_BKP FORCE INCLUDING CONTENTS;
Don't perform this in production. It will drop data from your diskgroup.

Diskgroup dropped.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
[grid@erpdcdb01 ~]$
[grid@erpdcdb01 ~]$ asmcmd
ASMCMD> md_restore /u01/Stage/asmbackup/asmmetadata.backup  --full
Current Diskgroup metadata being restored: RMAN_BKP
Diskgroup RMAN_BKP created!
System template AUTOBACKUP modified!
System template DATAFILE modified!
System template DUMPSET modified!
System template OCRFILE modified!
System template PARAMETERFILE modified!
System template ASMPARAMETERFILE modified!
System template CHANGETRACKING modified!
System template ONLINELOG modified!
System template CONTROLFILE modified!
System template BACKUPSET modified!
System template XTRANSPORT modified!
System template FLASHBACK modified!
System template DATAGUARDCONFIG modified!
System template TEMPFILE modified!
System template FLASHFILE modified!
System template XTRANSPORT BACKUPSET modified!
System template ARCHIVELOG modified!
Directory +RMAN_BKP/SBLCBS re-created!
Directory +RMAN_BKP/ASM re-created!
Directory +RMAN_BKP/SBLCBS/BACKUPSET re-created!
Directory +RMAN_BKP/ASM/DUMPSET re-created!
Directory +RMAN_BKP/SBLCBS/BACKUPSET/2016_12_06 re-created!
Directory +RMAN_BKP/SBLCBS/BACKUPSET/2016_11_29 re-created!
ASMCMD> exit
[grid@erpdcdb01 ~]$
[grid@erpdcdb01 ~]$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576    322553   288859                0          288859              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     92158    61454                0           61454              0             N  FRA/
MOUNTED  NORMAL  N         512   4096  1048576     12285    11359             4095            3632              0             Y  OCR/
MOUNTED  EXTERN  N         512   4096  1048576     92158    92104                0           92104              0             N  RMAN_BKP/
ASMCMD> exit
[grid@erpdcdb01 ~]$
[grid@erpdcdb01 ~]$ srvctl status diskgroup -g RMAN_BKP
Disk Group RMAN_BKP is running on sbldcdb01
[grid@erpdcdb01 ~]$ srvctl stop diskgroup -g RMAN_BKP
[grid@erpdcdb01 ~]$ srvctl start diskgroup -g RMAN_BKP

How to perform flashback table forward and backward

CREATE TABLE TEST_FLASH(ID NUMBER);

ALTER TABLE TEST_FLASH ENABLE ROW MOVEMENT;

TRUNCATE TABLE TEST_FLASH;

INSERT INTO TEST_FLASH
SELECT LEVEL FROM DUAL CONNECT BY LEVEL<10000;

SELECT CURRENT_SCN FROM V$DATABASE;-- 63441120

INSERT INTO TEST_FLASH
SELECT LEVEL+10000 FROM DUAL CONNECT BY LEVEL<10000;

SELECT CURRENT_SCN FROM V$DATABASE;-- 63441184

SELECT COUNT(*) FROM TEST_FLASH; -- 19998

FLASHBACK TABLE TEST_FLASH to scn 63441120;

SELECT COUNT(*) FROM TEST_FLASH; -- 9999

FLASHBACK TABLE TEST_FLASH to scn 63441184;

SELECT COUNT(*) FROM TEST_FLASH; -- 19998

0481-103 Cannot open a file in the /var/spool/cron/crontabs directory.

bash-4.3$ crontab -l
0481-103 Cannot open a file in the /var/spool/cron/crontabs directory.
A file or directory in the path name does not exist.
bash-4.3$ su root
root's Password:
# bash
bash-4.3#
bash-4.3# cd /var/spool/cron/crontabs
bash-4.3# vi oracle
bash-4.3# ls -ltr
total 40
-rw-r--r--    1 adm      cron           2016 Sep 22 2009  adm
-rw-r--r--    1 sys      cron            849 Sep 22 2009  sys
-rw-------    1 root     cron           1280 Aug 31 18:03 root
-rw-r--r--    1 root     cron           1118 Aug 31 19:00 uucp
-rw-r--r--    1 root     system           10 Dec 04 16:21 oracle
bash-4.3#
bash-4.3# chown root:cron oracle
bash-4.3# vi /var/adm/cron/cron.allow
bash-4.3# cat /var/adm/cron/cron.allow
root
oracle
bash-4.3# su oracle
bash-4.3$ crontab -l
#Cron job

How to fix timezone issue in RAC Database.

When mismatch between OS time zone and clusterware time zone SYSDATE and  CURRENT_DATE will return different value, because of at the time of clusterware starting lintener started using clusterware time zone.

Solution : 

1. Shutdown clusterware from root user

crsctl stop crs

2. Change the clusterware time $GRID_HOME/crs/install/s_crsconfig__env.txt file using vi editor from root user. Before change take backup of this file.

bash-4.3# cat s_crsconfig_sbldcdb01_env.txt
### This file can be used to modify the NLS_LANG environment variable, which determines the charset to be used for messages.
### For example, a new charset can be configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8
### Do not modify this file except to change NLS_LANG, or under the direction of Oracle Support Services

#TZ=America/New_York
TZ=Asia/Dhaka
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
RT_GRQ=ON
EXTSHM=OFF
TNS_ADMIN=
ORACLE_BASE=

3. Startup the clusterware from root user

crsctl start crs