Wednesday, December 17, 2014

ORA-01122: database file 22 failed verification check

Today when I tried to restart my test database I got strange but known error.

SQL> startup  
 ORA-32004: obsolete and/or deprecated parameter(s) specified  
 ORACLE instance started.  
 Total System Global Area 5344731136 bytes  
 Fixed Size         2163168 bytes  
 Variable Size      2298482208 bytes  
 Database Buffers     3036676096 bytes  
 Redo Buffers        7409664 bytes  
 Database mounted.  
 ORA-01122: database file 22 failed verification check  
 ORA-01110: data file 22: '/u01/app/oracle/oradata/???/archive_tbs01.dbf'  
 ORA-01200: actual file size of 65536 is smaller than correct size of 402176 


After researching it means inconsistency of controlfile. So, we need recreate or restore it from backup.

Do one of the following:

SQL > alter database backup controlfile to trace;  
 Database altered.

or

 SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/u01/app/oracle/admin/PROD/bdump/controlfile.bkp';  
 Database altered. 

or

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/u01/app/oracle/admin/PROD/bdump/controlfile.trc';  
 Database altered. 

shutdown database and start it on nomount state:

SQL> shut immediate  
 ORA-01109: database not open  
 Database dismounted.  
 ORACLE instance shut down.

 SQL> startup nomount  
 ORA-32004: obsolete and/or deprecated parameter(s) specified  
 ORACLE instance started.  
 Total System Global Area 5344731136 bytes  
 Fixed Size         2163168 bytes  
 Variable Size      2298482208 bytes  
 Database Buffers     3036676096 bytes  
 Redo Buffers        7409664 bytes  
 SQL>


edit your trace file and try to create  --I created createnewcontrolfile.sql file


 SQL > @createnewcontrolfile.sql  
 CREATE CONTROLFILE REUSE DATABASE "???" RESETLOGS ARCHIVELOG  
   MAXLOGFILES 16  
   MAXLOGMEMBERS 3  
   MAXDATAFILES 100  
   MAXINSTANCES 8  
   MAXLOGHISTORY 2921  
 LOGFILE  
  GROUP 1 '/u01/app/oracle/oradata/???/redo1.log' SIZE 400M,  
  GROUP 2 '/u01/app/oracle/oradata/???/redo2.log' SIZE 400M,  
  GROUP 3 '/u01/app/oracle/oradata/???/redo3.log' SIZE 400M,  
  GROUP 4 '/u01/app/oracle/oradata/???/redo4.log' SIZE 400M,  
  GROUP 5 '/u01/app/oracle/oradata/???/redo5.log' SIZE 400M,  
  GROUP 6 '/u01/app/oracle/oradata/???/redo6.log' SIZE 400M  
 -- STANDBY LOGFILE  
 DATAFILE  
  '/u01/app/oracle/oradata/???/system01.dbf',  
 ...  
  '/u01/app/oracle/oradata/???/FCJ_DATA_TS_13.dbf'  
 CHARACTER SET AL32UTF8; 


At the end recover database using new controlfile

 SQL> recover database using backup controlfile

Now open the database and enjoy

 SQL> alter database open; 

Thursday, November 27, 2014

SP2-0618: Cannot find the Session Identifier.

Today I could not enabled autorace and reason was SP2-0618.

After looking for in google I found solution and now want to share it.

 SQL> set autotrace traceonly;   
 SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled   
 SP2-0611: Error enabling STATISTICS report  

To enable autotrace PLUSTRACE role should be exists! If not exists create it.

Under $ORACLE_HOME\sqlplus\admin\ you may execute by SYS user

 SQL> @$ORACLE_HOME\sqlplus\admin\plustrce.sql; 

Another way you may just manually create plustrace role and assign select on necessary views;

SQL> create role plustrace;   
Role created.   
SQL> grant select on v_$sesstat to plustrace;   
Grant succeeded.   
SQL> grant select on v_$statname to plustrace;   
Grant succeeded.   
SQL> grant select on v_$mystat to plustrace;   
Grant succeeded.   
SQL> grant plustrace to dba with admin option;   
Grant succeeded. 

At the end grant plustrace role to needed user;
SQL> grant plustrace to ulfet;   
Grant succeeded. 

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.  

Wednesday, September 17, 2014

Forgot sysman password ?

Today I could not remember sysman password for database grid control. How carefully may I change sysman password ? Just follow.

1. Stop OMS (go to OMS dir)

 [oracle@gridrepo bin]$ ./emctl stop oms  
 Oracle Enterprise Manager 11g Release 1 Grid Control   
 Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.  
 Stopping WebTier...  
 WebTier Successfully Stopped  
 Stopping Oracle Management Server...  
 Oracle Management Server Successfully Stopped  
 Oracle Management Server is Down  

2. Config oms password with -change_repos_pwd parameter

[oracle@gridrepo bin]$ ./emctl config oms -change_repos_pwd  
 Oracle Enterprise Manager 11g Release 1 Grid Control   
 Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.  
 -change_in_db option not specified. Password not changed in backend.  
 Enter Repository User's New Password :   
 Updating repository password in Credential Store...  
 Repository password in Credential Store updated successfully.  
 Bounce the OMS.  
 Successfully changed repository password.  
 If you have multiple OMS's in your environment, run this command on all of them.  
 [oracle@gridrepo bin]$  

Or you can also use "-change_in_db" parameter with above command which is optional.

But be aware of prompt will ask you enter existing sysman password. Change sysman password by using tradional command:

 SQL> alter user sysman identified by new_pass;


Add, enable, disable service using srvctl

A brief post about add||enable||disable service using SRVCTL.

[oracle@oralab1 dbhome_1]$ srvctl config database  
 orcl  
 [oracle@oralab1 dbhome_1]$ srvctl config database -d orcl  
 Database unique name: orcl  
 Database name: orcl  
 Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1  
 Oracle user: oracle  
 Spfile: +DATA/orcl/spfileorcl.ora  
 Domain:  
 Start options: open  
 Stop options: immediate  
 Database role: PRIMARY  
 Management policy: AUTOMATIC  
 Database instance: orcl  
 Disk Groups: DATA  
 Services: 

--There is no any service(s).

--create new service:

SQL> begin  
  2 dbms_service.create_service('NEWORCL','NEWORCL');  
  3 end;  
  4 /  
 PL/SQL procedure successfully completed.  
 SQL> select name, enabled from dba_services;  
 NAME                               ENA  
 ---------------------------------  ---------------------------
 SYS$BACKGROUND                     NO  
 SYS$USERS                          NO  
 NEWORCL                            NO  
 orclXDB                            NO  
 orcl                               NO  

[oracle@oralab1 dbhome_1]$ srvctl config database -d orcl  
 Database unique name: orcl  
 Database name: orcl  
 Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1  
 Oracle user: oracle  
 Spfile: +DATA/orcl/spfileorcl.ora  
 Domain:  
 Start options: open  
 Stop options: immediate  
 Database role: PRIMARY  
 Management policy: AUTOMATIC  
 Database instance: orcl  
 Disk Groups: DATA  
 Services: NEWORCL  
 [oracle@oralab1 dbhome_1]$ srvctl status service -d orcl  
 Service NEWORCL is not running.  


--service is not started, let`s start

 SQL> exec dbms_service.start_service('NEWORCL');  
 PL/SQL procedure successfully completed.  
 SQL>

[oracle@oralab1 dbhome_1]$ srvctl status service -d orcl  
 Service NEWORCL is running  

 [oracle@oralab1 dbhome_1]$ srvctl config database -d orcl  
 Database unique name: orcl  
 Database name: orcl  
 Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1  
 Oracle user: oracle  
 Spfile: +DATA/orcl/spfileorcl.ora  
 Domain:  
 Start options: open  
 Stop options: immediate  
 Database role: PRIMARY  
 Management policy: AUTOMATIC  
 Database instance: orcl  
 Disk Groups: DATA  
 Services: NEWORCL  

--disable service
 [oracle@oralab1 dbhome_1]$ srvctl disable service -d orcl -s NEWORCL  

 [oracle@oralab1 dbhome_1]$ srvctl config service -d orcl  
 Service name: NEWORCL  
 Service is disabled  
 Cardinality: SINGLETON  
 Disconnect: false  
 Service role: PRIMARY  
 Management policy: AUTOMATIC  
 DTP transaction: false  
 AQ HA notifications: false  
 Failover type: NONE  
 Failover method: NONE  
 TAF failover retries: 0  
 TAF failover delay: 0  
 Connection Load Balancing Goal: LONG  
 Runtime Load Balancing Goal: NONE  
 TAF policy specification: NONE  
 Edition:  

 --Enable service  
 [oracle@oralab1 dbhome_1]$ srvctl enable service -d orcl -s NEWORCL  
 [oracle@oralab1 dbhome_1]$ srvctl config service -d orcl  
 Service name: NEWORCL  
 Service is enabled  
 Cardinality: SINGLETON  
 Disconnect: false  
 Service role: PRIMARY  
 Management policy: AUTOMATIC  
 DTP transaction: false  
 AQ HA notifications: false  
 Failover type: NONE  
 Failover method: NONE  
 TAF failover retries: 0  
 TAF failover delay: 0  
 Connection Load Balancing Goal: LONG  
 Runtime Load Balancing Goal: NONE  
 TAF policy specification: NONE  
 Edition:  

--You may stop/start services using SRVCTL utility too.

 -- stop service  
 [oracle@oralab1 dbhome_1]$ srvctl stop service -s NEWORCL -d orcl  
 [oracle@oralab1 dbhome_1]$ srvctl status service -d orcl  
 Service NEWORCL is not running. 

--start service  
 Services: NEWORCL  
 [oracle@oralab1 dbhome_1]$ srvctl start service -s NEWORCL -d orcl  
 [oracle@oralab1 dbhome_1]$ srvctl status service -d orcl  
 Service NEWORCL is running  

Wednesday, April 30, 2014

Oracle 11g (FGA) Access Control List

After got API for sending SMS and developed packages we tried to send URL, but we faced below known error.

SQL> select alarmsender.pkg_sms_sender.send_sms('Ulfet','99450???????','test') from dual;  
 ORA-29273: HTTP request failed  
 ORA-06512: at "SYS.UTL_HTTP", line 1722  
 ORA-24247: network access denied by access control list (ACL)  
 ORA-06512: at line 1  

Starting Oracle 11g Oracle introduce FGA (Fine Grained Access) for using UTTL_HTTP, UTL_MAIL etc packages.

After reading Oracle notes, we need to create ACL and then assgin it.

Let`s check our db registery.

SQL> set linesize 400  
 SQL> col comp_name format a40  
 SQL> select comp_name, status from dba_registry;  
 COMP_NAME   STATUS  
 ---------------------------------------- --------------------------------------------  
 OWB   VALID  
 Oracle Application Express  VALID  
 Oracle Enterprise Manager  VALID  
 OLAP Catalog   VALID  
 Spatial   VALID  
 Oracle Multimedia  VALID  
 Oracle XML Database  VALID  
 Oracle Text   VALID  
 Oracle Expression Filter  VALID  
 Oracle Rules Manager  VALID  
 Oracle Workspace Manager  VALID  
 COMP_NAME   STATUS  
 ---------------------------------------- --------------------------------------------  
 Oracle Database Catalog Views  VALID  
 Oracle Database Packages and Types VALID  
 JServer JAVA Virtual Machine  VALID  
 Oracle XDK   VALID  
 Oracle Database Java Packages  VALID  
 OLAP Analytic Workspace  VALID  
 Oracle OLAP API   VALID  
 18 rows selected.  


--Creating new ACL

SQL> BEGIN  
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (  
   acl     => 'smssend_nowsms.xml', --acl  
   description => 'Permissions to access http://10.10.9.15', --needed host  
   principal  => 'ALARMSENDER', --my user  
   is_grant   => TRUE,  
   privilege  => 'connect');  
   COMMIT;  
 EN 2  3  4  5  6  7  8  9 D;  
 / 10  
 PL/SQL procedure successfully completed.  
 SQL>


--Assign it

 SQL> BEGIN  
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (  
   acl     => 'smssend_nowsms.xml',         
   host     => '10.10.9.15',  
   lower_port  => 8084,  
   upper_port  => 8084);   
   COMMIT;  
 END;  
 / 

PL/SQL procedure successfully completed.

SQL>

--Checking using select statement

SQL> SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('10.10.9.15'));  
 COLUMN_VALUE  
 10.10.9.15  
 10.10.9.*  
 10.10.*  
 10.*  
 *  


SQL> select acl , host , lower_port , upper_port from dba_network_acls;  
 ACL,HOST,LOWER_PORT,UPPER_PORT  
 /sys/acls/smssend_nowsms.xml,10.10.9.15,8084,8084  

SQL> select acl , principal , privilege , is_grant from dba_network_acl_privileges;  
 ACL,PRINCIPAL,PRIVILEGE,IS_GRANT  
 /sys/acls/smssend_nowsms.xml,ALARMSENDER,connect,true  



--Now try again
SQL> select alarmsender.pkg_sms_sender.send_sms('Ulfet','99450???????','test') from dual;
works fine!

Thursday, March 27, 2014

Purge trace, alert, incident files using adrci

Today suddenly I saw more disk usage. After investigation I found what used more space.

[oracle@fc-db-tst1 FPREPROD]$ du -sh *
9.4G alert
4.0K cdump
4.0K hm
4.0K incident
4.0K incpkg
4.0K ir
4.0K lck
3.5M metadata
4.0K metadata_dgif
4.0K metadata_pv
4.0K stage
4.0K sweep
7.2G  trace



[oracle@fc-db-tst1 alert]$ adrci

ADRCI: Release 11.2.0.3.0 - Production on Fri Mar 28 08:17:10 2014

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

ADR base = "/u01/app/oracle"
adrci> show homes
ADR Homes:
diag/rdbms/fpreprod/FPREPROD
adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/fpreprod/FPREPROD:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME                            
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1753316741           720                  8760                 2014-01-24 00:26:21.230527 +04:00        2014-03-24 15:02:28.535497 +04:00                                                 1                    2                    80                   1                    2014-01-24 00:26:21.230527 +04:00      
1 rows fetched


LONGP_POLICY and SHORTP_POLICY set`s via hour.
720 = (720/24 = 30 days)
8760 = (8760/24 = 365 days)

Let`s set new values.

adrci> set control (SHORTP_POLICY = 240)
adrci> set control (LONGP_POLICY = 1095)
adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/fpreprod/FPREPROD:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME                            
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1753316741           240                  1095                 2014-03-28 08:19:14.448120 +04:00        2014-03-24 15:02:28.535497 +04:00                                                 1                    2                    80                   1                    2014-01-24 00:26:21.230527 +04:00      
1 rows fetched


--Purging alert
adrci> purge -age 10080 -type ALERT

--Purging trace
adrci> purge -age 10080 -type TRACE

--Purging incident
adrci> purge -age 10080 -type incident

--Purging all
adrci> purge -age 10080



adrci> show alert

ADR Home = /u01/app/oracle/diag/rdbms/fpreprod/FPREPROD:
*************************************************************************
Output the results to file: /tmp/alert_27908_1397_FPREPROD_1.ado

2014-02-07 06:24:17.356000 +04:00
Thread 1 advanced to log sequence 347 (LGWR switch)
  Current log# 2 seq# 347 mem# 0: /u01/app/oracle/oradata/FPREPROD/redo02.log
2014-02-07 06:30:00.027000 +04:00
adrci>


--Display last 50 rows
adrci> show alert -TAIL 50

--Display last 10 rows and output appended data as the file grows
adrci> show alert -TAIL -F

For more detail please refer to :
http://docs.oracle.com/cd/B28359_01/server.111/b28319/adrci.htm#BGBHHBGB

Wednesday, March 19, 2014

Change db user`s default tablespace

Today I found on my friend`s database some application user`s default tablespace is SYSTEM which is not recommended by Oracle.

Select users list which default_tablespace is SYSTEM. In my case, application users prefix are APP.

 SELECT username, default_tablespace FROM dba_users WHERE username like 'APP%';  
 USERNAME   DEFAULT_TABLESPACE    
 APPLWEB    SYSTEM  
 APPLDESK   SYSTEM  
 APPLTEST    SYSTEM  

Now, use below little bit PLSQL block.

 BEGIN  
  FOR i IN (  
            SELECT username FROM dba_users   
            WHERE default_tablespace in ('SYSTEM') and username like 'APP%'  
          ) LOOP  
   EXECUTE IMMEDIATE 'ALTER USER ' || i.username || ' DEFAULT TABLESPACE USERS';  
  END LOOP;  
 END;  
 /  

Check

 SELECT username, default_tablespace FROM dba_users WHERE username like 'APP%';   
 USERNAME   DEFAULT_TABLESPACE    
 APPLWEB    USERS  
 APPLDESK   USERS  
 APPLTEST    USERS

Monday, March 3, 2014

ORA-03206: maximum file size of (5242880) blocks in AUTOEXTEND clause is out of range

Today when I want to change max size of datafile I got strange error.

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/MYDB/users01.dbf' 
AUTOEXTEND ON NEXT 1280K MAXSIZE 32GB  
ORA-03206: maximum file size of (5242880) blocks in AUTOEXTEND clause is out of range
Here is reason: The maximum file size for an autoextendable file has exceeded the maximum number of blocks allowed.

After research internet I found that instead of using 32GB we can use 32767M. (same values but in MB).
Or you may use 31GB too.

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/MYDB/users01.dbf' 
AUTOEXTEND ON NEXT 1280K MAXSIZE 32767M;

Saturday, February 22, 2014

True way to stop start primary and standby databases.

There are have a lot of ways to stop/start primary and standby databases. I will show you one of them.

We needed to migrate physically servers to new plaza. So, I had to stop all with clean.

Parameters:

Primary Stanby
db_name FCDBPROD FCDBPROD
instance_name FCDBPROD FCDBSTND
open_mode READ WRITE MOUNTED
database_role PRIMARY PHYSICAL STANDBY
ip 10.10.10.10 10.10.10.20


Data guard broker was configured.

DGMGRL> show configuration

Configuration - DGMANAGER

  Protection Mode: MaxPerformance
  Databases:
    FCDBPROD - Primary database
    FCDBSTND - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


1. Stop applying. # I do it on Standby side

DGMGRL> edit database "FCDBSTND" set state="APPLY-OFF";

2. Shutdown Primary database

[oracle@fcdbdb ~]$ export ORACLE_SID=FCDBPROD
[oracle@fcdbdb ~]$ 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> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
DGMGRL> 

Stop listener

lsnrctl stop

Shutdown OS


3. Shutdown Standby database

[oracle@fcdbdb_stby ~]$ 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> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
DGMGRL>

Stop listener

lsnrctl stop

Shutdown OS



After unmount servers, migrate to new place and mount follow below:

Standby side
1. After start OS and listener, startup database

[oracle@fcdbdb_stby ~]$ export ORACLE_SID=FCDBSTND
[oracle@fcdbdb_stby ~]$ 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> startup mount
ORACLE instance started.
Database mounted.
DGMGRL>

2. Start primary database

[oracle@fcdbdb ~]$ export ORACLE_SID=FCDBPROD
[oracle@fcdbdb ~]$ 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> startup
ORACLE instance started.
Database mounted.
Database opened.
DGMGRL>


3. Enable apply process

DGMGRL> edit database "FCDBSTND" set state="APPLY-ON";
Error: ORA-12543: TNS:destination host unreachable
Error: ORA-16625: cannot reach database "FCDBPROD"

Failed.
DGMGRL>

There is error. Let check tnsping. There is connection problem. In my situation, after OS restart firewall was enabled. Stop it

/etc/init.d/iptables status #check status

/etc/init.d/iptables stop

If you want to turn off firewall on boot do:

chkconfig iptables off


Try to enable again

DGMGRL> edit database "FCDBSTND" set state="APPLY-ON";
Succeeded.
DGMGRL> show configuration

Configuration - DGMANAGER

  Protection Mode: MaxPerformance
  Databases:
    FCDBPROD - Primary database
    FCDBSTND - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>  show database "FCDBSTND";

Database - FCDBSTND

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    FCDBSTND

Database Status:
SUCCESS

DGMGRL> show database "FCDBPROD";

Database - FCDBPROD

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    FCDBPROD

Database Status:
SUCCESS


Now check archive logs.

On primary:

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

SQL*Plus: Release 11.2.0.3.0 Production on Sat Feb 22 15:04:15 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

MAX(SEQUENCE#)
--------------
  722

SQL>


On Standby side

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

MAX(SEQUENCE#)
--------------
  722



now on primary switch logfile

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

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

MAX(SEQUENCE#)
--------------
  724

SQL>



check on standby

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

MAX(SEQUENCE#)
--------------
  722

SQL> /

MAX(SEQUENCE#)
--------------
  724

SQL>



At the end check alertlogs on both side

Check alertlog

stnaby alert

Thread 1 advanced to log sequence 725 (LGWR switch)
  Current log# 2 seq# 725 mem# 0: /u01/app/oracle/oradata/FCDBPROD/redo02.log
Sat Feb 22 15:05:42 2014
Archived Log entry 963 added for thread 1 sequence 724 ID 0xfb8f2b6 dest 1:
LNS: Standby redo logfile selected for thread 1 sequence 725 for destination LOG_ARCHIVE_DEST_2


primary alert

Sat Feb 22 15:05:45 2014
Archived Log entry 461 added for thread 1 sequence 724 ID 0xfb8f2b6 dest 1:
Media Recovery Waiting for thread 1 sequence 725 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 725 Reading mem 0
  Mem# 0: /u01/app/oracle/fast_recovery_area/FCDBSTND/onlinelog/o1_mf_5_9f753t2h_.log


That`s all.

To configure data guard broker and to post this post I got some best practices video and strongly recommend to every one to watch my friend Mahir`s video series about data guard broker.

Tuesday, February 18, 2014

Data Guard Broker Configuration

Today I decided to use Oracle`s magic dgmgrl utility and configure my standby database.

Below you can see my environment:

PRIMARY

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS     INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
OPEN     FCDBPROD      PRIMARY       MAXIMUM PERFORMANCE

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

MAX(SEQUENCE#)
--------------
  674

SQL> 


STANBDY

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS     INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      FCDBSTND      PHYSICAL STANDBY MAXIMUM PERFORMANCE

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

MAX(SEQUENCE#)
--------------
  674

SQL> 

Now, set dataguard broker process on both primary and standby databases.

SQL> alter system set dg_broker_start=true;

System altered.

SQL> 


Add a below lines in the listener.ora file on both the primary and standby servers.


On Primary:

   (SID_DESC=
      (GLOBAL_DBNAME=FCDBPROD_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1)
      (SID_NAME=FCDBPROD)
   )

On Standby

   (SID_DESC=
      (GLOBAL_DBNAME=FCDBSTND_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1)
      (SID_NAME=FCDBSTND)
   )

[oracle@fcdbdb admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.178)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC=
      (GLOBAL_DBNAME=FCDBPROD_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1)
      (SID_NAME=FCDBPROD)
   )
   (SID_DESC =
     (SID_NAME = FCDBPROD)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
   )
 )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON





[oracle@fcdbdb_stby admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.179)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC=
      (GLOBAL_DBNAME=FCDBSTND_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1)
      (SID_NAME=FCDBSTND)
   )
   (SID_DESC =
     (SID_NAME = FCDBSTND)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
   )
 )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
[oracle@fcdbdb_stby admin]$ 



Or, instead of you may add

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = FCDBPROD)
     (GLOBAL_DBNAME=FCDBPROD_DGMGRL)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
   )
 )


reload listener on both side

[oracle@fcdbdb_stby admin]$ lsnrctl reload




On the primary server, create the Dataguard Broker configuration.


[oracle@fcdbdb admin]$ 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.
DGMGRL> connect sys/oracle@FCDBPROD
Connected.
DGMGRL> create configuration 'DGMANAGER' 
> as primary database is 'FCDBPROD'
> connect identifier is FCDBPROD;
Configuration "DGMANAGER" created with primary database "FCDBPROD"
DGMGRL> 

Check status

DGMGRL> show configuration;

Configuration - DGMANAGER

  Protection Mode: MaxPerformance
  Databases:
    FCDBPROD - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> 



Add the standby database "FCDBSTND" to the configuration created above.


DGMGRL> add database 'FCDBSTND'
> as connect identifier is FCDBSTND
> maintained as physical;      
Database "FCDBSTND" added
DGMGRL> 


DGMGRL> show configuration;

Configuration - DGMANAGER

  Protection Mode: MaxPerformance
  Databases:
    FCDBPROD - Primary database
    FCDBSTND - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> 



The configuration is added, now needs to be enabled.

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - DGMANAGER

  Protection Mode: MaxPerformance
  Databases:
    FCDBPROD - Primary database
    FCDBSTND - Physical standby database
      Error: ORA-16797: database is not using a server parameter file

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

DGMGRL> 

We have to restart standby database, create spfile and start from spfile.

SQL> show parameter pfile

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile     string
SQL> 

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shut immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@fcdbdb_stby dbs]$ pwd
/u01/app/oracle/product/11.2.0.3/db_1/dbs

[oracle@fcdbdb_stby dbs]$ ls -lrt
total 9632
-rw-r--r--. 1 oracle oinstall    1610 Jan 13 10:01 initFCDBSTND.ora
-rw-r-----. 1 oracle oinstall    1536 Jan 13 10:17 orapwFCDBSTND
-rw-r-----. 1 oracle oinstall      24 Jan 13 10:17 lkFCDBSTND
-rw-r-----. 1 oracle oinstall 9814016 Jan 13 11:32 snapcf_FCDBSTND.f
-rw-r-----. 1 oracle oinstall   16384 Feb 18 10:41 dr1FCDBSTND.dat
-rw-r-----. 1 oracle oinstall   16384 Feb 18 10:41 dr2FCDBSTND.dat
-rw-rw----. 1 oracle oinstall    1544 Feb 18 10:44 hc_FCDBSTND.dat

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

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 18 10:45:16 2014

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

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> exit
Disconnected

[oracle@fcdbdb_stby dbs]$ ls -lrt
total 9640
-rw-r--r--. 1 oracle oinstall    1610 Jan 13 10:01 initFCDBSTND.ora
-rw-r-----. 1 oracle oinstall    1536 Jan 13 10:17 orapwFCDBSTND
-rw-r-----. 1 oracle oinstall      24 Jan 13 10:17 lkFCDBSTND
-rw-r-----. 1 oracle oinstall 9814016 Jan 13 11:32 snapcf_FCDBSTND.f
-rw-r-----. 1 oracle oinstall   16384 Feb 18 10:41 dr1FCDBSTND.dat
-rw-r-----. 1 oracle oinstall   16384 Feb 18 10:41 dr2FCDBSTND.dat
-rw-rw----. 1 oracle oinstall    1544 Feb 18 10:44 hc_FCDBSTND.dat
-rw-r-----. 1 oracle oinstall    4608 Feb 18 10:45 spfileFCDBSTND.ora

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

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 18 10:45:32 2014

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 5394837504 bytes
Fixed Size    2237896 bytes
Variable Size 1275071032 bytes
Database Buffers 4110417920 bytes
Redo Buffers    7110656 bytes
Database mounted.

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

Database altered.


SQL> show parameter pfile

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile     string /u01/app/oracle/product/11.2.0
.3/db_1/dbs/spfileFCDBSTND.ora
SQL> 

recheck again

DGMGRL> show configuration;

Configuration - DGMANAGER

  Protection Mode: MaxPerformance
  Databases:
    FCDBPROD - Primary database
    FCDBSTND - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> 

Friday, February 14, 2014

Fix ORA-30009: Not enough memory for CONNECT BY operation

How to fix ORA-30009:

SQL> create table too_big_tbl as
select ROWNUM n, dbms_random.value(100000, 999999) n2  from dual connect by level <= 100000000; 2
 select ROWNUM n from dual connect by level <= 100000000
                      *
ERROR at line 2:
ORA-30009: Not enough memory for CONNECT BY operation


Check pga_aggregate_target`s value. In my test instance it was not set.     

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 0
SQL> alter system set pga_aggregate_target = 100 scope = both;

System altered.


Then try again

SQL> create table too_big_tbl as
select ROWNUM n, dbms_random.value(100000, 999999) n2  from dual connect by level <= 100000000; 

Table created.

Done

Tuesday, February 4, 2014

Fix /var/adm/wtmp: Value too large to be stored in data type.

Today morning while I checked who last loged on my dbserver I saw strange error.

bash-3.00# last
/var/adm/wtmp: Value too large to be stored in data type.

My OS is AiX

bash-3.00# uname -a
AIX ?????? 1 6 00F6A54E4C00

Check size of file:

-bash-3.00$ ls -lrt /var/adm/wtmp
-rw-rw-r--    1 adm      adm      2148154327 Feb 05 08:47 /var/adm/wtmp
-bash-3.00$

It is around 2GB.

For next analyze copy file to your backup location

bash-3.00# cp /var/adm/wtmp /u01/BackUpFiles/

Now you may reset file.

bash-3.00# cat </dev/null >/var/adm/wtmp

Check again the last command

-bash-3.00$ last
oracle    pts/1        xx.xx.xx.xx            Feb 05 09:13   still logged in.

wtmp begins     Feb 05 09:09


If you want to see the last 10 failed logins.

bash-3.00#  who /etc/security/failedlogin | tail -10
oracle      ssh         Dec 27 17:10     (xx.xx.xx.xx)
oracle      ssh         Dec 31 13:54     (xx.xx.xx.xx)
UNKNOWN_    ssh         Jan 08 14:12     (xx.xx.xx.xx)
UNKNOWN_    ssh         Jan 14 13:12     (xx.xx.xx.xx)
UNKNOWN_    ssh         Jan 14 13:12     (xx.xx.xx.xx)
UNKNOWN_    ssh         Jan 22 15:29     (xx.xx.xx.xx)
UNKNOWN_    ssh         Jan 24 12:26     (xx.xx.xx.xx)
UNKNOWN_    ssh         Jan 24 12:26     (xx.xx.xx.xx)
UNKNOWN_    ssh         Jan 24 12:26     (xx.xx.xx.xx)
oracle      ssh         Feb 03 17:09     (xx.xx.xx.xx)
bash-3.00#


P.S: you have to login as root to execute those commands.


Monday, February 3, 2014

Changing diagnostic_dest path

Hi,

after duplicated test databases I found out that location of diagnostic_dest is under $ORACLE_HOME/log/.
To change it just type alter system set diagnostic_dest=new_path. It will create related folders under there.

Firstly check current path:

[oracle@fc-db-tst1 rdbms]$ export ORACLE_SID=FCDBDEV
[oracle@fc-db-tst1 rdbms]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 4 09:53:35 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  show parameter diag

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest     string /u01/app/oracle/product/11.2.0
.3/db_1/log


Change path. In my case I use Oracle recommended path

SQL> alter system set diagnostic_dest='/u01/app/oracle';

System altered.

It can take few seconds. (while creating folders)

So, check

SQL> show parameter diagnostic_dest

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest     string /u01/app/oracle


[oracle@fc-db-tst1 rdbms]$ pwd
/u01/app/oracle/diag/rdbms

[oracle@fc-db-tst1 rdbms]$ ls
fcdbdev

[oracle@fc-db-tst1 rdbms]$ cd fcdbdev/

[oracle@fc-db-tst1 fcdbdev]$ ls
FCDBDEV  i_1.mif



Tuesday, January 21, 2014

Fix RMAN-06214

Today I found in my backup log file strange error.

RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy   /u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f


RMAN> Report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 3 days
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     1      13-JAN-14          /u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f

--try to delete obsolete backup

RMAN> Delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 3 days
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     1      13-JAN-14          /u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f

Do you really want to delete the above objects (enter YES or NO)? YES
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_DISK_1 channel at 01/21/2014 08:56:58
ORA-19606: Cannot copy or restore to snapshot control file

RMAN> exit

--I tried to delete it with noprompt too but result was same

delete noprompt obsolete;


RMAN> SHOW SNAPSHOT CONTROLFILE NAME;

RMAN configuration parameters for database with db_unique_name FCDBPROD are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f'; # default

--provide new location

RMAN> configure snapshot controlfile name to '/home/oracle/scripts/snapcf_FCDBPROD.f';

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/scripts/snapcf_FCDBPROD.f';
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name FCDBPROD are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/scripts/snapcf_FCDBPROD.f';

--now crosscheck and delete

RMAN> crosscheck controlfilecopy '/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f';

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
validation failed for control file copy
control file copy file name=/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f RECID=1 STAMP=836734569
Crosschecked 1 objects


RMAN> delete expired controlfilecopy '/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f';

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
List of Control File Copies
===========================

Key     S Completion Time Ckp SCN    Ckp Time    
------- - --------------- ---------- ---------------
1       X 13-JAN-14       10156665859609 13-JAN-14    
        Name: /u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f
        Tag: TAG20140113T101609


Do you really want to delete the above objects (enter YES or NO)? yes
deleted control file copy
control file copy file name=/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f RECID=1 STAMP=836734569
Deleted 1 EXPIRED objects


--now set old location, default

RMAN> configure snapshot controlfile name to '/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f';

old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/scripts/snapcf_FCDBPROD.f';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f';
new RMAN configuration parameters are successfully stored

--check

RMAN> show SNAPSHOT CONTROLFILE NAME;

RMAN configuration parameters for database with db_unique_name FCDBPROD are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f';


Check location if file not created yet, take current control file backup for creating snapshot control file

RMAN> backup current controlfile;

That`s all.

Wednesday, January 8, 2014

ORA-38754: FLASHBACK DATABASE not started & ORA-38761

Yesterday I suddenly faced ORA-38761 and ORA-38754 errors.
I checked all logs and backups which were available, but why I could not flashback db.

Let`s simulate.

-- First I will create new user and will create table under new user. This is for checking purpose after flashback.
SQL> create user ulfet identified by ulfet;

User created.

SQL> grant dba to ulfet;

Grant succeeded.

SQL> conn ulfet/ulfet
Connected.

-- Note date. If you desire you may note SCN
SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
09-JAN-14 09.13.23.721393 AM +04:00

SQL>


SQL> create table test (id number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
09-JAN-14 09.15.10.917401 AM +04:00

SQL>



-- Now create restore point for flashback db, but you may flashaback db using timestamp or scn too.
SQL> create restore point first_point_09102014_0917;

Restore point created.

-- Now drop table
SQL> drop table test;

Table dropped.



SQL> conn /as sysdba
Connected.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size    2217992 bytes
Variable Size  499124216 bytes
Database Buffers  335544320 bytes
Redo Buffers    2396160 bytes
Database mounted.

SQL> flashback database to restore point first_point_09102014_0917;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

--After flashback db we have to open db with resetlogs option.

-- Now check table and data

SQL> conn ulfet/ulfet
Connected.

SQL> select * from test;

ID
----------
1

SQL> 



-- Take backup of db and archivelog all and delete input

RMAN> backup incremental level 0 device type disk tag 'FULL_BACKUP_0' database;

Starting backup at 09-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/DB11GR2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/DB11GR2/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DB11GR2/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DB11GR2/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DB11GR2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 09-JAN-14
channel ORA_DISK_1: finished piece 1 at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_nnnd0_FULL_BACKUP_0_9dwdltvh_.bkp tag=FULL_BACKUP_0 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26
Finished backup at 09-JAN-14

Starting Control File and SPFILE Autobackup at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/autobackup/2014_01_09/o1_mf_s_836385904_9dwdojxc_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-JAN-14


RMAN> backup  device type disk tag "ARCHIVELOG_BACKUP" archivelog all not backed up delete all input;

Starting backup at 09-JAN-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=3 STAMP=836385564
input archived log thread=1 sequence=3 RECID=4 STAMP=836385564
input archived log thread=1 sequence=4 RECID=2 STAMP=836385562
channel ORA_DISK_1: starting piece 1 at 09-JAN-14
channel ORA_DISK_1: finished piece 1 at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwdtrhj_.bkp tag=ARCHIVELOG_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_2_9dwdbtld_.arc RECID=3 STAMP=836385564
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_3_9dwdbwp4_.arc RECID=4 STAMP=836385564
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_3_9dwcdmhp_.arc RECID=1 STAMP=836384595
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_4_9dwdbtjj_.arc RECID=2 STAMP=836385562
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=5 STAMP=836386072
channel ORA_DISK_1: starting piece 1 at 09-JAN-14
channel ORA_DISK_1: finished piece 1 at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwdtvmf_.bkp tag=ARCHIVELOG_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_1_9dwdtr7r_.arc RECID=5 STAMP=836386072
Finished backup at 09-JAN-14

Starting Control File and SPFILE Autobackup at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/autobackup/2014_01_09/o1_mf_s_836386076_9dwdtww2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-JAN-14

RMAN> 



-- Now make dml and ddl statement 

SQL> conn ulfet/ulfet
Connected.

SQL> select * from test;

ID
----------
1

SQL> update test set id=111 where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> create table test2 as select * from test where 1=2;

Table created.

--Generate Archivelogs

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> 


-- Take again archivelogs backup
[oracle@dbserver Desktop]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 9 09:33:39 2014

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

connected to target database: DB11GR2 (DBID=1660417640)

RMAN> backup  device type disk tag "ARCHIVELOG_BACKUP" archivelog all not backed up delete all input;

Starting backup at 09-JAN-14
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=6 STAMP=836386263
input archived log thread=1 sequence=3 RECID=7 STAMP=836386270
input archived log thread=1 sequence=4 RECID=8 STAMP=836386427
channel ORA_DISK_1: starting piece 1 at 09-JAN-14
channel ORA_DISK_1: finished piece 1 at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwf5w15_.bkp tag=ARCHIVELOG_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_2_9dwf0qnp_.arc RECID=6 STAMP=836386263
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_3_9dwf0y20_.arc RECID=7 STAMP=836386270
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_4_9dwf5vld_.arc RECID=8 STAMP=836386427
Finished backup at 09-JAN-14

Starting Control File and SPFILE Autobackup at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/autobackup/2014_01_09/o1_mf_s_836386429_9dwf5xcm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-JAN-14

RMAN> 

[oracle@dbserver 2014_01_09]$ pwd
/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09

[oracle@dbserver 2014_01_09]$ ls
[oracle@dbserver 2014_01_09]$ 

-- Now try to restore using flashback db

Jan 9 09:32:00 2014 -- before backup of archivelog



SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size    2217992 bytes
Variable Size  499124216 bytes
Database Buffers  335544320 bytes
Redo Buffers    2396160 bytes
Database mounted.

SQL> FLASHBACK DATABASE TO TIMESTAMP timestamp '2014-01-09 09:32:00';
FLASHBACK DATABASE TO TIMESTAMP timestamp '2014-01-09 09:32:00'
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 989641 to SCN 990218
ORA-38761: redo log sequence 2 in thread 1, incarnation 3 could not be accessed


SQL> 


[oracle@dbserver ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 9 09:43:22 2014

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

connected to target database: DB11GR2 (DBID=1660417640, not open)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB11GR2  1660417640       PARENT  1          15-AUG-09
2       2       DB11GR2  1660417640       PARENT  945184     11-SEP-13
3       3       DB11GR2  1660417640       CURRENT 989483     09-JAN-14

RMAN> 



-- Take which archivelog need

SQL> SELECT NAME
  FROM V$ARCHIVED_LOG A, V$DATABASE_INCARNATION I 
 WHERE A.RESETLOGS_ID = I.RESETLOGS_ID
   AND A.SEQUENCE# = 2 
   AND A.THREAD# = 1
   AND I.INCARNATION# = 3  2    3    4    5    6  ;

NAME
--------------------------------------------------------------------------------



-- Try restore archivelog and select again

RMAN> restore archivelog from sequence 1;

Starting restore at 09-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwdtvmf_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwdtvmf_.bkp tag=ARCHIVELOG_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=3
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwf5w15_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwf5w15_.bkp tag=ARCHIVELOG_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 09-JAN-14

RMAN> 


SQL> SELECT NAME
  FROM V$ARCHIVED_LOG A, V$DATABASE_INCARNATION I 
 WHERE A.RESETLOGS_ID = I.RESETLOGS_ID
   AND A.SEQUENCE# = 2 
   AND A.THREAD# = 1
   AND I.INCARNATION# = 3  2    3    4    5    6  ;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_2_9dwg
1v3r_.arc



-- Needed archivelog is available now try to flashback

SQL> FLASHBACK DATABASE TO TIMESTAMP timestamp '2014-01-09 09:32:00';

Flashback complete.


SQL> alter database open resetlogs;

Database altered.

SQL> 

-- Now check 
SQL> conn ulfet/ulfet
Connected.

SQL> select * from tab;

TNAME       TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST       TABLE
TEST2       TABLE

SQL> select * from test;

ID
----------
       111

SQL> select * from test2;

no rows selected

SQL> 

Thats all