Ulfet`s Oracle Blog: 2014

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.

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

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

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

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

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

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;

----------

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;

----------

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

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

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;

----------

111

SQL> select * from test2;

no rows selected

SQL>

Thats all