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

ORA-19527: physical standby redo log must be renamed

Cause : It was due to LOG_FILE_NAME_CONVERT parameter.

Solution: Set the value into this parameter.

[oracle@sbdbdr01 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 29 20:45:10 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 Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 1
*
ERROR at line 1:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '+DATA/dcdb/onlinelog/redo-01-01.log'


SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='+DATA/dcdb/','+DATA/drdb/','+FRA/dcdb/','+FRA/drdb/' SCOPE= SPFILE;

System altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 3958439936 bytes
Fixed Size                  2259320 bytes
Variable Size            2197816968 bytes
Database Buffers         1744830464 bytes
Redo Buffers               13533184 bytes
SQL> alter database mount standby database;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

Database altered.

SQL>

How to perform flashback database in RAC Database.

bash-4.3$ srvctl stop database -d dcdbrac

bash-4.3$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 22 07:15:20 2016

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

Connected to an idle instance.

SQL> STARTUP MOUNT EXCLUSIVE;
ORACLE instance started.

Total System Global Area 6.3069E+10 bytes
Fixed Size                  2393728 bytes
Variable Size            9059699072 bytes
Database Buffers         5.3989E+10 bytes
Redo Buffers               18116608 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/8);


Flashback complete.

SQL> SQL>
SQL>
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
bash-4.3$ srvctl status database -d dcdbrac
Instance dcdbrac1 is running on node dcdbrac01
Instance dcdbrac2 is not running on node dcdbrac02
bash-4.3$ srvctl stop instance -d dcdbrac -i dcdbrac1
bash-4.3$ srvctl start database -d dcdbrac
bash-4.3$

How to Restore RMAN Backup from ASM to file System Database.

1. Connect into RMAN and restore spfile from Backup.

[oracle@OEL564ASMN1 dbs]$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 22 18:11:25 2016

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

connected to target database (not started)

RMAN> STARTUP FORCE NOMOUNT

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_2/dbs/initgolddb.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2260088 bytes
Variable Size                281019272 bytes
Database Buffers             780140544 bytes
Redo Buffers                   5517312 bytes

RMAN> RESTORE spfile FROM '/u01/rman_backup/rman_bkp_spfile_GOLDDB_12_20161122.bak';

Starting restore at 22-NOV-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/rman_backup/rman_bkp_spfile_GOLDDB_12_20161122.bak
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 22-NOV-16

RMAN> exit


Recovery Manager complete.

2. Connect into database and modify the parameter (control_files, db_create_file_dest, db_recovery_file_dest).

[oracle@OEL564ASMN1 dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 22 18:11:58 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 Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
SQL> alter system set control_files='/u01/app/oracle/oradata/controlfile01.ctl' scope=spfile;

System altered.

SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/' scope=spfile;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/oradata/' scope=spfile;

System altered.

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

3. Restore control file from RMAN Backup.

[oracle@OEL564ASMN1 dbs]$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 22 18:14:18 2016

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

connected to target database: GOLDDB (not mounted)

RMAN> RESTORE CONTROLFILE TO '/u01/app/oracle/oradata/controlfile01.ctl' FROM '/u01/rman_backup/rman_bkp_ctl_GOLDDB_928602759_11_1_0brlimk7_1_1';

Starting restore at 22-NOV-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 22-NOV-16

RMAN> exit


Recovery Manager complete.

4. Mount the database and create control file to trace file for find the data file name which we need to rename.

[oracle@OEL564ASMN1 dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 22 18:16:14 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 Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database mount;

Database altered.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/u01/app/oracle/oradata/control_file_records.sql';

Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@OEL564ASMN1 dbs]$

5. Open the control file and find out the name of datafile for generate recovery scripts.

[oracle@OEL564ASMN1 dbs]$ cat /u01/app/oracle/oradata/control_file_records.sql
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="golddb"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- WARNING! The current control file needs to be checked against
-- the datafiles to insure it contains the correct files. The
-- commands printed here may be missing log and/or data files.
-- Another report should be made after the database has been
-- successfully opened.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "GOLDDB" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/golddb/onlinelog/group_1.261.925664911'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '+DATA/golddb/onlinelog/group_2.262.925664915'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '+DATA/golddb/onlinelog/group_3.263.925664917'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/golddb/datafile/system.256.925664777',
  '+DATA/golddb/datafile/sysaux.257.925664781',
  '+DATA/golddb/datafile/undotbs1.258.925664783',
  '+DATA/golddb/datafile/users.259.925664783',
  '+DATA/golddb/datafile/example.265.925664949'
CHARACTER SET WE8MSWIN1252
;

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE

-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.
ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/golddb/tempfile/temp.264.925664933' REUSE;
-- End of tempfile additions.
--
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- WARNING! The current control file needs to be checked against
-- the datafiles to insure it contains the correct files. The
-- commands printed here may be missing log and/or data files.
-- Another report should be made after the database has been
-- successfully opened.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "GOLDDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/golddb/onlinelog/group_1.261.925664911'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '+DATA/golddb/onlinelog/group_2.262.925664915'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '+DATA/golddb/onlinelog/group_3.263.925664917'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/golddb/datafile/system.256.925664777',
  '+DATA/golddb/datafile/sysaux.257.925664781',
  '+DATA/golddb/datafile/undotbs1.258.925664783',
  '+DATA/golddb/datafile/users.259.925664783',
  '+DATA/golddb/datafile/example.265.925664949'
CHARACTER SET WE8MSWIN1252
;

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/golddb/tempfile/temp.264.925664933' REUSE;
-- End of tempfile additions.
--
[oracle@OEL564ASMN1 dbs]$

6. Restore the database from RMAN Backup. [ Recovery will replay error cause of redo log issue]

[oracle@OEL564ASMN1 ~]$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 22 18:22:19 2016

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

connected to target database: GOLDDB (DBID=1679955469, not open)

RMAN> RUN
{
2> SET NEWNAME FOR DATAFILE '+DATA/golddb/datafile/system.256.925664777'  TO '/u01/app/oracle/oradata/system01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/golddb/datafile/sysaux.257.925664781' TO '/u01/app/oracle/oradata/sysaux01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/golddb/datafile/undotbs1.258.925664783' TO '/u01/app/oracle/oradata/undotbs101.dbf';
SET NEWNAME FOR DATAFILE '+DATA/golddb/datafile/users.259.925664783' TO '/u01/app/oracle/oradata/users01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/golddb/datafile/example.265.925664949' TO '/u01/app/oracle/oradata/example01.dbf';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
3> 4> 5> 6> 7> 8> 9> 10> 11>
executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 22-NOV-16
Starting implicit crosscheck backup at 22-NOV-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 22-NOV-16

Starting implicit crosscheck copy at 22-NOV-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 22-NOV-16

searching for all files in the recovery area
cataloging files...
no files cataloged

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 00002 to /u01/app/oracle/oradata/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rman_backup/rman_bkp_df2_GOLDDB_928602649_6_1_06rlimgp_1_1
channel ORA_DISK_1: piece handle=/u01/rman_backup/rman_bkp_df2_GOLDDB_928602649_6_1_06rlimgp_1_1 tag=TAG20161122T171048
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 /u01/app/oracle/oradata/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/undotbs101.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rman_backup/rman_bkp_df1_GOLDDB_928602649_5_1_05rlimgp_1_1
channel ORA_DISK_1: piece handle=/u01/rman_backup/rman_bkp_df1_GOLDDB_928602649_5_1_05rlimgp_1_1 tag=TAG20161122T171048
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 22-NOV-16

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=928607018 file name=/u01/app/oracle/oradata/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=928607018 file name=/u01/app/oracle/oradata/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=928607018 file name=/u01/app/oracle/oradata/undotbs101.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=928607018 file name=/u01/app/oracle/oradata/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=928607018 file name=/u01/app/oracle/oradata/example01.dbf

Starting recover at 22-NOV-16
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_DISK_1: reading from backup piece /u01/rman_backup/rman_bkp_archive_GOLDDB_928602754_9_1_09rlimk2_1_1.arch
channel ORA_DISK_1: piece handle=/u01/rman_backup/rman_bkp_archive_GOLDDB_928602754_9_1_09rlimk2_1_1.arch tag=TAG20161122T171233
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_4_d38g6f68_.arc thread=1 sequence=4
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_4_d38g6f68_.arc RECID=3 STAMP=928607021
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=5
channel ORA_DISK_1: reading from backup piece /u01/rman_backup/rman_bkp_archive_GOLDDB_928602754_10_1_0arlimk2_1_1.arch
channel ORA_DISK_1: piece handle=/u01/rman_backup/rman_bkp_archive_GOLDDB_928602754_10_1_0arlimk2_1_1.arch tag=TAG20161122T171233
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_5_d38g6grf_.arc thread=1 sequence=5
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/oradata/GOLDDB/archivelog/2016_11_22/o1_mf_1_5_d38g6grf_.arc RECID=4 STAMP=928607022
unable to find archived log
archived log thread=1 sequence=6
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/22/2016 18:23:44
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 984242

RMAN>

7. Connect into database and perform log clear and open the database with resetlog.

[oracle@OEL564ASMN1 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 22 18:46:22 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 Partitioning, OLAP, Data Mining and Real Application Testing options

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.

SQL> alter database open RESETLOGS;

Database altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/users01.dbf
/u01/app/oracle/oradata/undotbs101.dbf
/u01/app/oracle/oradata/sysaux01.dbf
/u01/app/oracle/oradata/system01.dbf
/u01/app/oracle/oradata/example01.dbf

SQL>
SQL> col member format a35
col member format a65
set lines 130
set pages 100
select member, group# from v$logfile;SQL> SQL> SQL> SQL>

MEMBER                                                                GROUP#
----------------------------------------------------------------- ----------
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_3_d38hkmq7_.log             3
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_2_d38hkf99_.log             2
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_1_d38hk2jp_.log             1
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_1_d38hk2pw_.log             1
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_2_d38hkfht_.log             2
/u01/app/oracle/oradata/GOLDDB/onlinelog/o1_mf_3_d38hkmxq_.log             3

6 rows selected.

SQL>

Enable Flashback in oracle RAC database

bash-4.3$ srvctl stop database -d dcdbrac
bash-4.3$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 22 00:52:44 2016

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 6.3069E+10 bytes
Fixed Size                  2393728 bytes
Variable Size            9059699072 bytes
Database Buffers         5.3989E+10 bytes
Redo Buffers               18116608 bytes
Database mounted.
SQL> alter database flashback on;

Database altered.

SQL> show parameter DB_FLASHBACK_RETENTION_TARGET

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SQL> alter database open;

Database altered.

SQL> select flashback_on  from v$database;

FLASHBACK_ON
------------------
YES

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
bash-4.3$
bash-4.3$ srvctl stop instance -d dcdbrac -i dcdbrac1
bash-4.3$ srvctl start database -d dcdbrac
bash-4.3$

Enable/Disable the automatic startup of the Oracle Clusterware (CRS|HAS).

[root@dcdbrac01 ~]# crsctl config crs
CRS-4622: Oracle High Availability Services autostart is enabled.
[root@dcdbrac01 ~]#
[root@dcdbrac01 ~]# crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.
[root@dcdbrac01 ~]#
[root@dcdbrac01 ~]# cat /etc/oracle/scls_scr/dcdbrac01/root/ohasdstr
disable
[root@dcdbrac01 ~]# crsctl enable has
CRS-4622: Oracle High Availability Services autostart is enabled.
[root@dcdbrac01 ~]#
[root@dcdbrac01 ~]# crsctl enable crs
CRS-4622: Oracle High Availability Services autostart is enabled.
[root@dcdbrac01 ~]#

[root@dcdbrac01 ~]# crsctl disable has
CRS-4621: Oracle High Availability Services autostart is disabled.
[root@dcdbrac01 ~]# crsctl disable crs
CRS-4621: Oracle High Availability Services autostart is disabled.
[root@dcdbrac01 ~]# crsctl config crs
CRS-4621: Oracle High Availability Services autostart is disabled.
[root@dcdbrac01 ~]# crsctl config has
CRS-4621: Oracle High Availability Services autostart is disabled.

How to Restore OCR in RAC Database.

My Environment :

Host 1 : dcdbrac01
Host 2 : dcdbrac02
Cluser : dcdbrac-cluster

1. Find out the latest backup of OCR ( check in each server location of $CRS_HOME/cdata/cluster_name/)

[root@dcdbrac01 ~]$ echo $CRS_HOME
/u01/app/11.2.4/grid
[root@dcdbrac01 ~]$ cd $CRS_HOME/cdata/dcdbrac-cluster/
[root@dcdbrac01 dcdbrac-cluster]# ls -ltr
total 51408
-rw------- 1 root root 7520256 Oct  3 04:03 week.ocr
-rw------- 1 root root 7520256 Oct 10 04:04 week_.ocr
-rw------- 1 root root 7520256 Nov 16 11:41 day.ocr
-rw------- 1 root root 7520256 Nov 17 03:41 backup02.ocr
-rw------- 1 root root 7520256 Nov 17 07:41 backup01.ocr
-rw------- 1 root root 7520256 Nov 17 11:41 backup00.ocr
-rw------- 1 root root 7520256 Nov 17 11:41 day_.ocr
[root@dcdbrac01 dcdbrac-cluster]#

[root@dcdbrac02 dcdbrac-cluster]# ls -ltr
total 51408
-rw------- 1 root root 7520256 Nov  7 08:53 week.ocr
-rw------- 1 root root 7520256 Nov 14 08:53 week_.ocr
-rw------- 1 root root 7520256 Nov 19 15:23 day.ocr
-rw------- 1 root root 7520256 Nov 20 07:23 backup02.ocr
-rw------- 1 root root 7520256 Nov 20 11:23 backup01.ocr
-rw------- 1 root root 7520256 Nov 20 15:23 backup00.ocr
-rw------- 1 root root 7520256 Nov 20 15:23 day_.ocr
[root@dcdbrac02 dcdbrac-cluster]#

2. Force stop Grid Infrastructure in each node.

[root@dcdbrac01 ~]# crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'dcdbrac01'
CRS-2673: Attempting to stop 'ora.crsd' on 'dcdbrac01'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'dcdbrac01'
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'dcdbrac01'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'dcdbrac01'
CRS-2673: Attempting to stop 'ora.OCR.dg' on 'dcdbrac01'
CRS-2673: Attempting to stop 'ora.RMAN_BKP.dg' on 'dcdbrac01'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'dcdbrac01'
CRS-2673: Attempting to stop 'ora.sblcbs.db' on 'dcdbrac01'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'dcdbrac01' succeeded
CRS-2673: Attempting to stop 'ora.dcdbrac01.vip' on 'dcdbrac01'
CRS-2677: Stop of 'ora.RMAN_BKP.dg' on 'dcdbrac01' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'dcdbrac01' succeeded
CRS-2677: Stop of 'ora.scan1.vip' on 'dcdbrac01' succeeded
CRS-2672: Attempting to start 'ora.scan1.vip' on 'dcdbrac02'
CRS-2677: Stop of 'ora.sblcbs.db' on 'dcdbrac01' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'dcdbrac01'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'dcdbrac01'
CRS-2677: Stop of 'ora.FRA.dg' on 'dcdbrac01' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'dcdbrac01' succeeded
CRS-2677: Stop of 'ora.dcdbrac01.vip' on 'dcdbrac01' succeeded
CRS-2672: Attempting to start 'ora.dcdbrac01.vip' on 'dcdbrac02'
CRS-2676: Start of 'ora.scan1.vip' on 'dcdbrac02' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on 'dcdbrac02'
CRS-2676: Start of 'ora.dcdbrac01.vip' on 'dcdbrac02' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.OCR.dg' on 'dcdbrac01' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'dcdbrac01'
CRS-2677: Stop of 'ora.asm' on 'dcdbrac01' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'dcdbrac01'
CRS-2677: Stop of 'ora.ons' on 'dcdbrac01' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'dcdbrac01'
CRS-2677: Stop of 'ora.net1.network' on 'dcdbrac01' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'dcdbrac01' has completed
CRS-2677: Stop of 'ora.crsd' on 'dcdbrac01' succeeded
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'dcdbrac01'
CRS-2673: Attempting to stop 'ora.ctssd' on 'dcdbrac01'
CRS-2673: Attempting to stop 'ora.evmd' on 'dcdbrac01'
CRS-2673: Attempting to stop 'ora.asm' on 'dcdbrac01'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'dcdbrac01'
CRS-2677: Stop of 'ora.ctssd' on 'dcdbrac01' succeeded
CRS-2677: Stop of 'ora.evmd' on 'dcdbrac01' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'dcdbrac01' succeeded
CRS-2677: Stop of 'ora.asm' on 'dcdbrac01' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'dcdbrac01'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'dcdbrac01' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'dcdbrac01'
CRS-2677: Stop of 'ora.cssd' on 'dcdbrac01' succeeded
CRS-2673: Attempting to stop 'ora.crf' on 'dcdbrac01'
CRS-2677: Stop of 'ora.drivers.acfs' on 'dcdbrac01' succeeded
CRS-2677: Stop of 'ora.crf' on 'dcdbrac01' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'dcdbrac01'
CRS-2677: Stop of 'ora.gipcd' on 'dcdbrac01' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'dcdbrac01'
CRS-2677: Stop of 'ora.gpnpd' on 'dcdbrac01' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'dcdbrac01' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@dcdbrac01 ~]#

[root@dcdbrac02 ~]# crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.crsd' on 'dcdbrac02'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.oc4j' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.cvu' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.dcdbrac01.vip' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.OCR.dg' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.RMAN_BKP.dg' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.sblcbs.db' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'dcdbrac02'
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'dcdbrac02'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'dcdbrac02'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.cvu' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'dcdbrac02'
CRS-2677: Stop of 'ora.dcdbrac01.vip' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.dcdbrac02.vip' on 'dcdbrac02'
CRS-2677: Stop of 'ora.registry.acfs' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.RMAN_BKP.dg' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.scan2.vip' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.scan1.vip' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.dcdbrac02.vip' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.scan3.vip' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.oc4j' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.sblcbs.db' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'dcdbrac02'
CRS-2677: Stop of 'ora.DATA.dg' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.FRA.dg' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.OCR.dg' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'dcdbrac02'
CRS-2677: Stop of 'ora.asm' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'dcdbrac02'
CRS-2677: Stop of 'ora.ons' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'dcdbrac02'
CRS-2677: Stop of 'ora.net1.network' on 'dcdbrac02' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'dcdbrac02' has completed
CRS-2677: Stop of 'ora.crsd' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.ctssd' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.evmd' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.asm' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'dcdbrac02'
CRS-2677: Stop of 'ora.evmd' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.drivers.acfs' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.asm' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'dcdbrac02'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'dcdbrac02'
CRS-2677: Stop of 'ora.cssd' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.crf' on 'dcdbrac02'
CRS-2677: Stop of 'ora.crf' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'dcdbrac02'
CRS-2677: Stop of 'ora.gipcd' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'dcdbrac02'
CRS-2677: Stop of 'ora.gpnpd' on 'dcdbrac02' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'dcdbrac02' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@dcdbrac02 ~]#

3. Start the CRS stack in exclusive mode in node which contain latest OCR backup without CRS demon (In my case node 2 contain latest OCR backup).

[root@dcdbrac02 dcdbrac-cluster]# crsctl start crs -excl -nocrs
CRS-4123: Oracle High Availability Services has been started.
CRS-2672: Attempting to start 'ora.mdnsd' on 'dcdbrac02'
CRS-2676: Start of 'ora.mdnsd' on 'dcdbrac02' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'dcdbrac02'
CRS-2676: Start of 'ora.gpnpd' on 'dcdbrac02' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'dcdbrac02'
CRS-2672: Attempting to start 'ora.gipcd' on 'dcdbrac02'
CRS-2676: Start of 'ora.cssdmonitor' on 'dcdbrac02' succeeded
CRS-2676: Start of 'ora.gipcd' on 'dcdbrac02' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'dcdbrac02'
CRS-2672: Attempting to start 'ora.diskmon' on 'dcdbrac02'
CRS-2676: Start of 'ora.diskmon' on 'dcdbrac02' succeeded
CRS-2676: Start of 'ora.cssd' on 'dcdbrac02' succeeded
CRS-2672: Attempting to start 'ora.drivers.acfs' on 'dcdbrac02'
CRS-2679: Attempting to clean 'ora.cluster_interconnect.haip' on 'dcdbrac02'
CRS-2672: Attempting to start 'ora.ctssd' on 'dcdbrac02'
CRS-2681: Clean of 'ora.cluster_interconnect.haip' on 'dcdbrac02' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'dcdbrac02'
CRS-2676: Start of 'ora.drivers.acfs' on 'dcdbrac02' succeeded
CRS-2676: Start of 'ora.ctssd' on 'dcdbrac02' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'dcdbrac02' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'dcdbrac02'
CRS-2676: Start of 'ora.asm' on 'dcdbrac02' succeeded
[root@dcdbrac02 dcdbrac-cluster]#

4. Restore latest OCR 

[root@dcdbrac02 dcdbrac-cluster]# cd $CRS_HOME/cdata/dcdbrac-cluster/
[root@dcdbrac02 dcdbrac-cluster]# ls -ltr
total 51408
-rw------- 1 root root 7520256 Nov  7 08:53 week.ocr
-rw------- 1 root root 7520256 Nov 14 08:53 week_.ocr
-rw------- 1 root root 7520256 Nov 19 15:23 day.ocr
-rw------- 1 root root 7520256 Nov 20 07:23 backup02.ocr
-rw------- 1 root root 7520256 Nov 20 11:23 backup01.ocr
-rw------- 1 root root 7520256 Nov 20 15:23 backup00.ocr
-rw------- 1 root root 7520256 Nov 20 15:23 day_.ocr
[root@dcdbrac02 dcdbrac-cluster]#
[root@dcdbrac02 dcdbrac-cluster]# ocrconfig -restore backup00.ocr
[root@dcdbrac02 dcdbrac-cluster]#

5. Stop and Start the CRS

[root@dcdbrac02 ~]# crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.crsd' on 'dcdbrac02'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.dcdbrac01.vip' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.cvu' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.oc4j' on 'dcdbrac02'
CRS-2677: Stop of 'ora.cvu' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'dcdbrac02'
CRS-2677: Stop of 'ora.dcdbrac01.vip' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.scan3.vip' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.oc4j' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.OCR.dg' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.RMAN_BKP.dg' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'dcdbrac02'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.dcdbrac02.vip' on 'dcdbrac02'
CRS-2677: Stop of 'ora.FRA.dg' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.RMAN_BKP.dg' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.dcdbrac02.vip' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.OCR.dg' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'dcdbrac02'
CRS-2677: Stop of 'ora.asm' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'dcdbrac02'
CRS-2677: Stop of 'ora.ons' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'dcdbrac02'
CRS-2677: Stop of 'ora.net1.network' on 'dcdbrac02' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'dcdbrac02' has completed
CRS-2677: Stop of 'ora.crsd' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.ctssd' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.evmd' on 'dcdbrac02'
CRS-2673: Attempting to stop 'ora.asm' on 'dcdbrac02'
CRS-2677: Stop of 'ora.mdnsd' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.evmd' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.asm' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'dcdbrac02'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'dcdbrac02'
CRS-2677: Stop of 'ora.drivers.acfs' on 'dcdbrac02' succeeded
CRS-2677: Stop of 'ora.cssd' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.crf' on 'dcdbrac02'
CRS-2677: Stop of 'ora.crf' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'dcdbrac02'
CRS-2677: Stop of 'ora.gipcd' on 'dcdbrac02' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'dcdbrac02'
CRS-2677: Stop of 'ora.gpnpd' on 'dcdbrac02' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'dcdbrac02' has completed
CRS-4133: Oracle High Availability Services has been stopped.

[root@dcdbrac02 dcdbrac-cluster]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.
[root@dcdbrac02 dcdbrac-cluster]#

6. Now chech the status of Cluser 

[root@dcdbrac02 dcdbrac-cluster]# crsctl check cluster -all
**************************************************************
dcdbrac02:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
[root@dcdbrac02 dcdbrac-cluster]#

Size of Redo Generation

Daily 
=========================================
SELECT TRUNC (COMPLETION_TIME) "REPORT DATE ",
     COUNT (*) "LOG SWITCH" ,
     ROUND ( (SUM (BLOCKS * BLOCK_SIZE) / 1024 / 1024)) "REDO PER DAY IN MB"
FROM V$ARCHIVED_LOG
GROUP BY TRUNC (COMPLETION_TIME)

ORDER BY 1;

Hourly 
=========================================
SELECT TO_CHAR(FIRST_TIME, 'DD-MON-YYYY:HH12:AM') "REPORT DATE AND HOUR ",
     COUNT (*) "LOG SWITCH" ,
     ROUND ( (SUM (BLOCKS * BLOCK_SIZE) / 1024 / 1024)) "REDO PER DAY IN MB"
FROM GV$ARCHIVED_LOG
GROUP BY TO_CHAR(FIRST_TIME, 'DD-MON-YYYY:HH12:AM')

ORDER BY 1;

How to Change/Restore Schema Password

To perform this you have to sys access. Connect as sys user into database and perform the following ...

SQL> select name,  password from user$ where name='ERP_PROD';

NAME                           PASSWORD
------------------------------ ------------------------------
ERP_PROD                  FD2C8A7959F2E1BF

SQL> CONN ERP_PROD/ERP_PRODpass
Connected.
SQL> conn /as sysdba
Connected.
SQL> alter user ERP_PROD identified by ERP_PROD;

User altered.

SQL> conn ERP_PROD/ERP_PROD
Connected.
SQL> conn /as sysdba
Connected.
SQL> alter user ERP_PROD identified by values 'FD2C8A7959F2E1BF';

User altered.

SQL> CONN ERP_PROD/ERP_PRODpass
Connected.
SQL> conn /as sysdba
Connected.
SQL>

How to control data pump job.

This steps help you to control/stop/resume/kill data pump job. For control data pump job at first you have to identify the job name from DBA_DATAPUMP_JOBS View and then you can control the job using expdp/impdp by providing the value (JOB_NAME) of attach parameter.

1. Identify the jobs which you want to control.

SQL> SELECT JOB_NAME

FROM DBA_DATAPUMP_JOBS

WHERE STATE<>'NOT RUNNING';  2    3

JOB_NAME

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

SYS_IMPORT_SCHEMA_06

SYS_IMPORT_SCHEMA_07

2. Control the job using expdp/impdp

bash-4.2$ impdp system/password attach=SYS_IMPORT_SCHEMA_07

3. Kill the job

Import>

Import> kill_job

Are you sure you wish to stop this job ([yes]/no): yes

bash-4.2$

How to recover missing/corrupted Oracle Inventory.

There is two inventory in oracle are Global Inventory ($ORACLE_BASE/OraInventory) and a Local Inventory ($ORACLE_HOME/inventory). If you lost this inventory you can recover by following steps.

1. Find the location of oraInst.loc file which may have different location depending on your OS.

/var/opt/oracle/oraInst.loc file
or
/etc/oraInst.loc

2. Modify the file oraInst.loc file ( before modify take backup of this file).

cp /etc/oraInst.loc /etc/oraInst.loc.bak

mkdir /u01/oracle/oraInventory

cat /etc/oraInst.loc

---file contents---
inventory_loc=/u01/oracle/oraInventory
inst_group=oinstall
---file contents---

3. Change the permissions to be appropriate

chmod 644 /etc/oraInst.loc

4. For consistency, copy the file to Oracle home directory

cp $ORACLE_HOME/oraInst.loc $ORACLE_HOME/oraInst.loc.bak

cp /etc/oraInst.loc $ORACLE_HOME/oraInst.loc

5. Run Oracle Universal Installer from Oracle home as below:

cd /u01/appdr/oracle/product/11.2.0/db_1

./runInstaller -silent -attachHome ORACLE_HOME="/u01/appdr/oracle/product/11.2.0/db_1" ORACLE_HOME_NAME="OraDb11g_home1"

Output should like below:

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 65536 MB    Passed

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /u01/appdr/oraInventory

'AttachHome' was successful.

6. Verify oracle patch details 

$ORACLE_HOME/OPatch/opatch lsinventory -detail

How to move Oracle Binary (Oracle HOME) to new location.

This steps will introduce new location of oracle home. You have to apply patch after move binary home if patch already applied in your old binary home. Perform the following steps to move oracle home into new location.


1.  Install the Oracle Software in the new location (new Oracle Home).

2.  Stop the listeners and shutdown the database(s).

3.  Modify the location for the ORACLE_HOME in the oratab file.

4.  Change the location for the ORACLE_HOME in the listener.ora and tnsnames.ora files.

5.  Copy the pfile and/or spfile, orapwd files ($ORACLE_HOME/dbs) to new Oracle Home.

6.  Edit the database parameters using vi command such as background_dump_dest, user_dump_dest, control_files, diagnostic_dest (in Oracle 11g) and so on in the pfile/spfile to point to new Oracle Home.

7.  Change the login script or oraenv script (if exist) to reflect the new location for the Oracle Home.

8.  Modify the ORACLE_HOME and PATH environment variables into your bash profile scripts to reflect the new location.

9.  Start the database and listener.

Multiplexing redo log files.

[oracle@DB ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 30 17:59:02 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 Partitioning, Automatic Storage Management, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SQL> SELECT GROUP#,TYPE, MEMBER FROM GV$LOGFILE;

    GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
         3 ONLINE
+DG_REDO/ERPDB/ONLINELOG/group_3.263.911340093

         2 ONLINE
+DG_REDO/ERPDB/ONLINELOG/group_2.262.911340093

         1 ONLINE
+DG_REDO/ERPDB/ONLINELOG/group_1.261.911340093


SQL> SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 SIZE_IN_MB,ARCHIVED,STATUS FROM GV$LOG;

    GROUP#    THREAD#  SEQUENCE# SIZE_IN_MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         1          1      39979        100 NO  ACTIVE
         2          1      39980        100 NO  ACTIVE
         3          1      39981        100 NO  CURRENT

SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 5 ('+DG_DATA/ERPDB/ONLINELOG/redo-05-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-05-02.log') SIZE 1G,
GROUP 6 ('+DG_DATA/ERPDB/ONLINELOG/redo-06-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-06-02.log') SIZE 1G;
  2    3

Database altered.

SQL> SQL>
SQL>
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 SIZE_IN_MB,ARCHIVED,STATUS FROM GV$LOG;

    GROUP#    THREAD#  SEQUENCE# SIZE_IN_MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         1          1      39979        100 NO  ACTIVE
         2          1      39980        100 NO  ACTIVE
         3          1      39981        100 NO  CURRENT
         5          1          0       1024 YES UNUSED
         6          1          0       1024 YES UNUSED

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

System altered.

SQL> ALTER SYSTEM SWITCH LOGFILE ;

System altered.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 SIZE_IN_MB,ARCHIVED,STATUS FROM GV$LOG;

    GROUP#    THREAD#  SEQUENCE# SIZE_IN_MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         1          1      39979        100 NO  INACTIVE
         2          1      39980        100 NO  INACTIVE
         3          1      39981        100 NO  INACTIVE
         5          1      39982       1024 NO  ACTIVE
         6          1      39983       1024 NO  CURRENT

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 THREAD 1
GROUP 1 ('+DG_DATA/ERPDB/ONLINELOG/redo-01-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-01-02.log') SIZE 1G,
GROUP 2 ('+DG_DATA/ERPDB/ONLINELOG/redo-02-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-02-02.log') SIZE 1G;
  2    3

Database altered.

SQL> SQL>
SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 3 ('+DG_DATA/ERPDB/ONLINELOG/redo-03-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-03-02.log') SIZE 1G,
GROUP 4 ('+DG_DATA/ERPDB/ONLINELOG/redo-04-01.log','+DG_REDO/ERPDB/ONLINELOG/redo-04-02.log') SIZE 1G;  2    3

Database altered.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 SIZE_IN_MB,ARCHIVED,STATUS FROM GV$LOG;

    GROUP#    THREAD#  SEQUENCE# SIZE_IN_MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         1          1          0       1024 YES UNUSED
         2          1          0       1024 YES UNUSED
         3          1          0       1024 YES UNUSED
         4          1          0       1024 YES UNUSED
         5          1      39982       1024 NO  ACTIVE
         6          1      39983       1024 NO  CURRENT

6 rows selected.

SQL>

Warning: log write elapsed time 1288ms, size 78KB

This warning message will be generated only if the log write time is more than 500 ms. this is a storage performance issue, either the storage has to get faster, or the application has to commit less. So check the performance of storage and analyze the commit frequency of application.

erpdcdb02$ cat erpdcdb2_lgwr_5046526.trc
Trace file /u01/app/oracle/diag/rdbms/erpdcdb/erpdcdb2/trace/erpdcdb2_lgwr_5046526.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing option
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    AIX
Node name:      erpdcdb02
Release:        1
Version:        7
Machine:        00F8FB754C00
Instance name: erpdcdb2
Redo thread mounted by this instance: 2
Oracle process number: 23
Unix process pid: 5046526, image: oracle@erpdcdb02 (LGWR)


*** 2016-10-27 13:47:38.631
*** SESSION ID:(10811.1) 2016-10-27 13:47:38.631
*** CLIENT ID:() 2016-10-27 13:47:38.631
*** SERVICE NAME:(SYS$BACKGROUND) 2016-10-27 13:47:38.631
*** MODULE NAME:() 2016-10-27 13:47:38.631
*** ACTION NAME:() 2016-10-27 13:47:38.631

Warning: log write elapsed time 1288ms, size 78KB
kcrfw_update_adaptive_sync_mode: post->poll long#=20 sync#=49 sync=299 poll=10019 rw=416 ack=13 min_sleep=10019

*** 2016-10-28 06:50:44.380
Log file sync switching to polling
Current scheduling delay is 1 usec
Current approximate redo synch write rate is 16 per sec
kcrfw_update_adaptive_sync_mode: poll->post current_sched_delay=187 switch_sched_delay=1 current_sync_count_delta=0 switch_sync_count_delta=49

*** 2016-10-28 07:12:28.017
Log file sync switching to post/wait
Current approximate redo synch write rate is 0 per sec
kcrfw_update_adaptive_sync_mode: post->poll long#=6 sync#=16 sync=172 poll=10019 rw=779 ack=0 min_sleep=10019

*** 2016-10-29 01:39:22.838
Log file sync switching to polling
Current scheduling delay is 250 usec
Current approximate redo synch write rate is 5 per sec
kcrfw_update_adaptive_sync_mode: poll->post current_sched_delay=0 switch_sched_delay=250 current_sync_count_delta=0 switch_sync_count_delta=16

*** 2016-10-29 01:52:14.212
Log file sync switching to post/wait
Current approximate redo synch write rate is 0 per sec

Opatch version check failed in New environment.

opatch version is success but failed to apply patch due to Oracle bug. Resolve this bug by create .patch_storage directory in the GI Home manually as root user:

bash-4.2# opatch auto /u02/stage/psu_oct_2016/24436346 -ocmrf /u02/stage/psu_oct_2016/ocm.rsp
Executing /u01/app/11.2.0/grid/perl/bin/perl /u01/app/oracle/product/11.2.0/db_1/OPatch/crs/patch11203.pl -patchdir /u02/stage/psu_oct_2016 -patchn 24436346 -ocmrf /u02/stage/psu_oct_2016/ocm.rsp -paramfile /u01/app/11.2.0/grid/crs/install/crsconfig_params

This is the main log file: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatchauto2016-10-29_04-19-53.log

This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatchauto2016-10-29_04-19-53.report.log

2016-10-29 04:19:53: Starting Clusterware Patch Setup
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
The opatch minimum version  check for patch /u02/stage/psu_oct_2016/24436346/24006111 failed  for /u01/app/11.2.0/grid
The opatch minimum version  check for patch /u02/stage/psu_oct_2016/24436346/23054319 failed  for /u01/app/11.2.0/grid
The opatch minimum version  check for patch /u02/stage/psu_oct_2016/24436346/22502505 failed  for /u01/app/11.2.0/grid
Opatch version check failed for oracle home  /u01/app/11.2.0/grid
Opatch version  check failed
ERROR: update the opatch version for the failed homes and retry

opatch auto failed.
bash-4.2#
bash-4.2# opatch version
OPatch Version: 11.2.0.3.15

OPatch succeeded.
bash-4.2# cd /u01/app/11.2.0/grid/
bash-4.2#
bash-4.2# pwd
/u01/app/11.2.0/grid
bash-4.2#
bash-4.2# whoami
root
bash-4.2# mkdir .patch_storage
bash-4.2# chown grid:oinstall .patch_storage
bash-4.2#

How to find device information of ASM Disks.

1. Find out the asm Disk list using oralceasm listdisks command.
2. Find the device mapping information using oracleasm  querydisk command.
3. Find out the device using ls -l command.

[root@sbdbdr01 ~]# oracleasm listdisks
DB_ORCL_DATA_ASM_01
DB_ORCL_DATA_ASM_02
DB_ORCL_DATA_ASM_03
DB_ORCL_DATA_ASM_04
DB_ORCL_DATA_ASM_05
DB_ORCL_FRA_ASM_01
DB_ORCL_FRA_ASM_02
DB_ORCL_FRA_ASM_03
DB_ORCL_FRA_ASM_04
DB_ORCL_FRA_ASM_05
DB_ORCL_FRA_ASM_06
[root@sbdbdr01 ~]#
[root@sbdbdr01 ~]# oracleasm querydisk -d DB_ORCL_DATA_ASM_05
Disk "DB_ORCL_DATA_ASM_05" is a valid ASM disk on device [8, 97]
[root@sbdbdr01 ~]#
[root@sbdbdr01 ~]#
[root@sbdbdr01 ~]# ls -l /dev/* | grep 8, | grep 97
brw-rw----  1 root disk      8,  97 Oct 23 13:49 /dev/sdg1
[root@sbdbdr01 ~]#

How to drop ASM Disk.

[grid@sbdbdr01 ~]$ sqlplus "/as sysasm"

SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 23 12:47:18 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 Automatic Storage Management option

SQL> SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN disk_group_name        FORMAT a25           HEAD 'Disk Group Name'
COLUMN disk_file_path         FORMAT a30           HEAD 'Path'
COLUMN disk_file_name         FORMAT a20           HEAD 'File Name'
COLUMN disk_file_fail_group   FORMAT a20           HEAD 'Fail Group'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'File Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

BREAK ON report ON disk_group_name SKIP 1

COMPUTE sum LABEL ""              OF total_mb used_mb ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report

SELECT
    NVL(a.name, '[CANDIDATE]')                       disk_group_name
  , b.name                                           disk_file_name
  , b.failgroup                                      disk_file_fail_group
  , b.total_mb                                       total_mb
  , (b.total_mb - b.free_mb)                         used_mb
FROM
    v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
    a.name;
/SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> columns cleared
SQL> breaks cleared
SQL> computes cleared
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10
Disk Group Name           File Name            Fail Group           File Size (MB) Used Size (MB)
------------------------- -------------------- -------------------- -------------- --------------
DATA                      DB_ORCL_DATA_ASM_05  DB_ORCL_DATA_ASM_05          49,151         23,281
                          DB_ORCL_DATA_ASM_04  DB_ORCL_DATA_ASM_04          49,151         23,280
                          DB_ORCL_DATA_ASM_03  DB_ORCL_DATA_ASM_03          49,151         23,283
                          DB_ORCL_DATA_ASM_02  DB_ORCL_DATA_ASM_02          49,151         23,284
                          DB_ORCL_DATA_ASM_01  DB_ORCL_DATA_ASM_01          49,151         23,282
*************************                                           -------------- --------------
                                                                           245,755        116,410

FRA                       DB_ORCL_FRA_ASM_04   DB_ORCL_FRA_ASM_04           23,551          7,071
                          DB_ORCL_FRA_ASM_03   DB_ORCL_FRA_ASM_03           23,551          7,073
                          DB_ORCL_FRA_ASM_02   DB_ORCL_FRA_ASM_02           23,551          7,074
                          DB_ORCL_FRA_ASM_01   DB_ORCL_FRA_ASM_01           23,551          7,077
*************************                                           -------------- --------------
                                                                            94,204         28,295

RMAN_BKP                  DB_ORCL_FRA_ASM_05   DB_ORCL_FRA_ASM_05           23,551             59
*************************                                           -------------- --------------
                                                                            23,551             59

[CANDIDATE]                                                                      0              0
*************************                                           -------------- --------------
                                                                                 0              0

                                                                    -------------- --------------
Grand Total:                                                               363,510        144,764

11 rows selected.
SQL> select * from v$asm_operation ;

no rows selected

SQL> ALTER DISKGROUP DATA DROP DISK DB_ORCL_DATA_ASM_05;

Diskgroup altered.

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        340      33291       5431           6

SQL>

ORA-20005: object statistics are locked

Cause: Gather statistics on table that has statistics locked.

Solution: Find out the table of index and unlock the Gather statistics.


SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS ('CBS_USER','IDX_ACCOUNTS_ACC_NUM');
BEGIN DBMS_STATS.GATHER_INDEX_STATS ('CBS_USER','IDX_ACCOUNTS_ACC_NUM'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 18379
ORA-06512: at "SYS.DBMS_STATS", line 18402
ORA-06512: at line 1


SQL>  SELECT TABLE_OWNER,TABLE_NAME,STATTYPE_LOCKED
 FROM ALL_IND_STATISTICS
 WHERE INDEX_NAME='IDX_ACCOUNTS_ACC_NUM';  2    3

TABLE_OWNER                    TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
CBS_USER                  ACCOUNTS                       ALL

SQL> SELECT OWNER, TABLE_NAME, STATTYPE_LOCKED
FROM ALL_TAB_STATISTICS
WHERE TABLE_NAME='ACCOUNTS';  2    3

OWNER                          TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
CBS_USER                  ACCOUNTS                       ALL

SQL> EXEC DBMS_STATS.UNLOCK_TABLE_STATS ('CBS_USER','ACCOUNTS','ALL');

PL/SQL procedure successfully completed.

SQL>  EXEC DBMS_STATS.GATHER_INDEX_STATS ('CBS_USER','IDX_ACCOUNTS_ACC_NUM');

PL/SQL procedure successfully completed.

SQL>

Enable/Disable Automatic Startup Oracle HAS

[grid@OEL564ASMN1 ~]$ crsctl check has
CRS-4638: Oracle High Availability Services is online
[grid@OEL564ASMN1 ~]$
[grid@OEL564ASMN1 ~]$
[grid@OEL564ASMN1 ~]$ crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.
[grid@OEL564ASMN1 ~]$
[grid@OEL564ASMN1 ~]$ crsctl disable has
CRS-4621: Oracle High Availability Services autostart is disabled.
[grid@OEL564ASMN1 ~]$ crsctl config has
CRS-4621: Oracle High Availability Services autostart is disabled.
[grid@OEL564ASMN1 ~]$ crsctl enable has
CRS-4622: Oracle High Availability Services autostart is enabled.
[grid@OEL564ASMN1 ~]$ crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.
[grid@OEL564ASMN1 ~]$