Sunday, October 26, 2014

Enable Archivelog mode on Oracle11g R2 RAC environment

Today I want to show you how to enable archivelog mode on Oracle 11g R2 RAC environment.

1. Checking log_mode:
 [oracle@rac1 acfs]$ export ORACLE_SID=RAC1  
 [oracle@rac1 acfs]$ sqlplus "/as sysdba"  
 SQL> archive log list  
 Database log mode No Archive Mode  
 Automatic archival Disabled  
 Archive destination USE_DB_RECOVERY_FILE_DEST  
 Oldest online log sequence 9  
 Current log sequence 10  
 SQL>  

[oracle@rac2 acfs]$ export ORACLE_SID=RAC2  
 [oracle@rac2 acfs]$ sqlplus "/as sysdba"  
 SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 13 18:13:53 2014  
 Copyright (c) 1982, 2009, Oracle. All rights reserved.  
 Connected to:  
 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production  
 With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,  
 Data Mining and Real Application Testing options  
 SQL> select name, open_mode, log_mode from v$database;  
 NAME   OPEN_MODE      LOG_MODE    
 --------- -------------------- ------------  
 RAC    READ WRITE      NOARCHIVELOG  
 SQL>  

Using SRVCTL utility stop cluster database
 [oracle@rac1 ~]$ srvctl stop database -d RAC  

--checking statuses
[oracle@rac1 ~]$ srvctl status database -d RAC  
 Instance RAC1 is not running on node rac1  
 Instance RAC2 is not running on node rac2  

Connect to node 1 and startup instance on mount mode, enable archiving and at the end shutdown the instance:

 SQL> startup mount  
 ORACLE instance started.  
 Total System Global Area 849530880 bytes  
 Fixed Size         1339824 bytes  
 Variable Size       570429008 bytes  
 Database Buffers     272629760 bytes  
 Redo Buffers        5132288 bytes  
 Database mounted.  
 SQL> ALTER DATABASE ARCHIVELOG;  
 SQL> SHUTDOWN IMMEDIATE;  

As you know we have shared files between instances so, this operation only has to be done from a single node.

Now start the clustered database
 [oracle@rac1 ~]$ srvctl start database -d RAC  
 [oracle@rac1 ~]$ srvctl status database -d RAC  
 Instance RAC1 is running on node rac1  
 Instance RAC2 is running on node rac2 


--Check again on both nodes
 SQL> select name, open_mode, log_mode, instance_name from v$database, v$instance;  
 NAME   OPEN_MODE      LOG_MODE   INSTANCE_NAME  
 --------- -------------------- ------------ ----------------  
 RAC    READ WRITE      ARCHIVELOG  RAC1  

 SQL> select name, open_mode, log_mode, instance_name from v$database, v$instance;  
 NAME   OPEN_MODE      LOG_MODE   INSTANCE_NAME  
 --------- -------------------- ------------ ----------------  
 RAC    READ WRITE      ARCHIVELOG  RAC2  
 SQL>  

That`s all.

Wednesday, October 15, 2014

Error: ORA-16810: multiple errors or warnings detected

Today after restarting primary and standby database servers I faced with ORA-16810 error

DGMGRL> show configuration;

Configuration - DGMANAGER

  Protection Mode: MaxAvailability
  Databases:
    XXXPROD - Primary database
      Error: ORA-16810: multiple errors or warnings detected for the database

    XXXSTND - Physical standby database
      Error: ORA-01017: invalid username/password; logon denied

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

I did not understand what and why it happened...
After research I found that it was related password file of standby side.


DGMGRL> show database 'XXXPROD'  

Database - XXXPROD

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    FCDBPROD
      Error: ORA-16737: the redo transport service for standby database "XXXSTND" has an error

  Database Warning(s):
    ORA-16629: database reports a different protection level from the protection mode

Database Status:
ERROR

--On standby side
DGMGRL> show configuration

Configuration - DGMANAGER

  Protection Mode: MaxAvailability
  Databases:
    XXXPROD - Primary database
    XXXSTND - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ORA-01017: invalid username/password; logon denied
ORA-16625: cannot reach database "XXXPROD"
DGM-17017: unable to determine configuration status


But connection between servers were clean. Listeners were up and tnsping success.

Hm, interesting what will provide us v$archive_dest:

SQL> select error from v$archive_dest;

ERROR
-----------------------------------------------------------------

ORA-16191: Primary log shipping client not logged on standby

It looks like could not connect to my standby db. Both instance are up and tnsping were OK, Also using
sqlplus user/pass@'connection_stringPRIMARY'
sqlplus user/pass@'connection_stringSTANDBY'

on both side between were success.


On the forum I found that something related passwordfile.
But I did not change any thing...

On both side remote_login_passwordfile`s value was EXCLUSIVE

SQL> show parameter remote_login_passwordfile

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile     string EXCLUSIVE
SQL>

And

SQL> select * from v$pwfile_users;

USERNAME       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS       TRUE  TRUE  FALSE

SQL>

I just copied Primary`s password file to standby side and then checked.
before it renamed old password file: mv orapwXXX.old

scp orapwXXX oracle@XX.XX.XX.X:/u01/app/oracle/product/11.2.0.3/db_1/dbs
--renamed it also for stanby



now everything is OK

[oracle@fcdbdb dbs]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration

Configuration - DGMANAGER

  Protection Mode: MaxAvailability
  Databases:
    XXXPROD - Primary database
    XXXSTND - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

--primary
SQL>  select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
 7819

--standby
SQL>  select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
 7819



Sunday, October 12, 2014

Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Today when I tested dataguard configuration I saw ORA-16826 error.

DGMGRL> show configuration;

Configuration - DGMANAGER

  Protection Mode: MaxPerformance
  Databases:
    PROD   - Primary database
    PRODST - Physical standby database
      Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Fast-Start Failover: DISABLED

Configuration Status:
WARNING

DGMGRL>


This is because I managed my standby database with NODELAY option.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  NODELAY;

To fix this cancel recover managed:

SQL> alter database recover managed standby database cancel;

Database altered.

Start with current logfile option:

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

Try again


DGMGRL> show configuration;

Configuration - DGMANAGER

  Protection Mode: MaxPerformance
  Databases:
    PROD   - Primary database
    PRODST - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Wednesday, October 8, 2014

Online Patch Apply

Hello,

Today I want to share my experience about online patching.

Download needed patch : https://support.oracle.com
For tutorial I got p16928674_112030_Generic.zip file.


1. Go to $ORACLE_HOME/Opatch dir and call opatch with parameters

[oracle@dbserver OPatch]$ pwd  
 /u01/app/oracle/product/11.2.0/db_1/OPatch  
 [oracle@dbserver OPatch]$ ./opatch lsinventory  
 Invoking OPatch 11.2.0.1.7  
 Oracle Interim Patch Installer version 11.2.0.1.7  
 Copyright (c) 2011, Oracle Corporation. All rights reserved.  
 Oracle Home    : /u01/app/oracle/product/11.2.0/db_1  
 Central Inventory : /u01/app/oraInventory  
   from      : /etc/oraInst.loc  
 OPatch version  : 11.2.0.1.7  
 OUI version    : 11.2.0.3.0  
 Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-10-08_11-53-07AM.log  
 Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-10-08_11-53-07AM.txt  
 --------------------------------------------------------------------------------  
 Installed Top-level Products (1):   
 Oracle Database 11g                         11.2.0.3.0  
 There are 1 products installed in this Oracle Home.  
 There are no Interim patches installed in this Oracle Home.  
 --------------------------------------------------------------------------------  
 OPatch succeeded.  

--Check version
 [oracle@dbserver OPatch]$ ./opatch version  
 Invoking OPatch 11.2.0.1.7  
 OPatch Version: 11.2.0.1.7  
 OPatch succeeded.  


2. Take backup of OPatch dir but it will be good to take entire ORACLE_HOME`s backup.
[tar, mv, etc]

3. export path
[oracle@dbserver db_1]$ export PATH=$PATH:$ORACLE_HOME/OPatch

4. unzip patch file
[oracle@dbserver Desktop]$ unzip p16928674_112030_Generic.zip


If you want you can check applicable using CheckApplicable parameter.

[oracle@dbserver OPatch]$ ./opatch prereq CheckApplicable -ph /home/oracle/Desktop/16928674  
 Invoking OPatch 11.2.0.1.7  
 Oracle Interim Patch Installer version 11.2.0.1.7  
 Copyright (c) 2011, Oracle Corporation. All rights reserved.  
 PREREQ session  
 Oracle Home    : /u01/app/oracle/product/11.2.0/db_1  
 Central Inventory : /u01/app/oraInventory  
   from      : /etc/oraInst.loc  
 OPatch version  : 11.2.0.1.7  
 OUI version    : 11.2.0.3.0  
 Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-10-08_14-13-31PM.log  
 Invoking prereq "checkapplicable"  
 Prereq "checkApplicable" for patch 16928674 passed.  
 OPatch succeeded.   

5. Apply patch

[oracle@dbserver 16928674]$ opatch apply  
 Invoking OPatch 11.2.0.1.7  
 Oracle Interim Patch Installer version 11.2.0.1.7  
 Copyright (c) 2011, Oracle Corporation. All rights reserved.  
 Oracle Home    : /u01/app/oracle/product/11.2.0/db_1  
 Central Inventory : /u01/app/oraInventory  
   from      : /etc/oraInst.loc  
 OPatch version  : 11.2.0.1.7  
 OUI version    : 11.2.0.3.0  
 Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-10-08_14-22-10PM.log  
 Applying interim patch '16928674' to OH '/u01/app/oracle/product/11.2.0/db_1'  
 Verifying environment and performing prerequisite checks...  
 Do you want to proceed? [y|n]  
 y  
 User Responded with: Y  
 All checks passed.  
 Backing up files...  
 Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...  
 Copying file to "/u01/app/oracle/product/11.2.0/db_1/sqlpatch/16928674/postinstall.sql"  
 Patch 16928674 successfully applied  
 Log file location: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-10-08_14-22-10PM.log  
 OPatch succeeded.  

To check result you can check log file :
[oracle@dbserver Desktop]$ more /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-10-08_14-22-10PM.log  
 ....  
 [Oct 8, 2014 2:22:21 PM]   Files check OK: Files from Patch ID 16928674 are present in Oracle Home.  
 [Oct 8, 2014 2:22:21 PM]   Finish applying patch to local system at Wed Oct 08 14:22:21 AZST 2014  
 [Oct 8, 2014 2:22:21 PM]   Patch 16928674 successfully applied  
 [Oct 8, 2014 2:22:21 PM]   Finishing ApplySession at Wed Oct 08 14:22:21 AZST 2014  
 [Oct 8, 2014 2:22:21 PM]   Total time spent waiting for user-input is 3 seconds. Finish at Wed Oct 08 14:22:21 AZST 2014  
 ....  


If everythins are OK, then execute postinstallion script

 SQL> @/home/oracle/Desktop/16928674/postinstall;  
 Calling rdbms/admin/prvtbpci.plb on 08-OCT-14 02.28.02.958883 PM +05:00  
 Package body created.  
 Package body created.  
 SQL>   


Now you can check opatch lsinventory again:
[oracle@dbserver 16928674]$ opatch lsinventory  
 Invoking OPatch 11.2.0.1.7  
 Oracle Interim Patch Installer version 11.2.0.1.7  
 Copyright (c) 2011, Oracle Corporation. All rights reserved.  
 Oracle Home    : /u01/app/oracle/product/11.2.0/db_1  
 Central Inventory : /u01/app/oraInventory  
   from      : /etc/oraInst.loc  
 OPatch version  : 11.2.0.1.7  
 OUI version    : 11.2.0.3.0  
 Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-10-08_14-23-31PM.log  
 Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-10-08_14-23-31PM.txt  
 --------------------------------------------------------------------------------  
 Installed Top-level Products (1):   
 Oracle Database 11g                         11.2.0.3.0  
 There are 1 products installed in this Oracle Home.  
 Interim patches (1) :  
 Patch 16928674   : applied on Wed Oct 08 14:22:20 AZST 2014  
 Unique Patch ID: 17483843  
   Created on 2 Apr 2014, 04:20:57 hrs PST8PDT  
   Bugs fixed:  
    16928674  
 --------------------------------------------------------------------------------  
 OPatch succeeded.  

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...