Tuesday, November 29, 2016

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>

Tuesday, November 22, 2016

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>

Monday, November 21, 2016

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$

Sunday, November 20, 2016

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;

Thursday, November 17, 2016

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>

Saturday, November 12, 2016

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.