Archive for March, 2013

I will be posting a series on installation and configuration of OFM 11g (11.1.1.6) on Oracle Linux 5 with focus of hosting a Oracle forms & reports application. Mainly, following steps will be done in different posts:

1) Weblogic 10.3.6 Installation
2) OFM 11.1.1.2 Installation
3) Applying 11.1.1.6 Patch
4) Configuring OFM for hosting forms and reports application

So, in first post, i will be installing weblogic 10.3.6. Certification Matrix for weblogic 10.3.6 for different platforms can be found here. Different platforms have different Prerequisites which can be found here. I am using a .bin installer for Weblogic for linux platform. There are several types of weblogic installers and details of which can be found here.

Start the installer for weblogic as screen shot given below.

web1

This will start the installer for weblogic in GUI mode.

web2

 

 

 

 

 

 

Click Next >

web3

 

 

 

 

 

 

Select the Middleware Home for weblogic installation and click next >

web4

 

 

 

 

 

 

Click next >

web5

 

 

 

 

 

 

Select the custom option and click next>

web6

 

 

 

 

 

 

De-select the evaluation database option and Oracle coherence option from this screen and click next >

web7

 

 

 

 

 

 

Select the version of java installed on machine, see the certification matrix link mentioned above to install the supported version of java.

web8

 

 

 

 

 

 

Select the weblogic home, which is usually given the default value.

web9

 

 

 

 

 

 

click next>

web10

 

 

 

 

 

 

Installation will start now.

web11

 

 

 

 

 

 

Uncleck the run quickstart and click Done. With this, the weblogic 10.3.6 installation is completed and we are ready for installing OFM 11.1.1.2 on this weblogic installation, which i will be covering in next post. Please let me know your queries and comments.

Advertisements

In this post, i will be recovering a standby to a particular SCN/Time to recover from a truncate table situation when we have a Physical standby implemented with some time delay between archive shipping and getting applied. I have a time delay of 120 Minutes.
Below are the steps i followed on PRIMARY database. Well, original blog from which i got these steps is of laurentschneider

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A_OB                           TABLE

SQL>
SQL> create table A_AB as select * from dba_objects;

Table created.

SQL> select count(*) from A_AB;

  COUNT(*)
----------
     13798

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> alter system switch logfile;

System altered.

SQL>
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   21661731

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
23-MAR-13 10.12.56.196491 AM +01:00

SQL> truncate table A_AB;

Table truncated.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   21661836

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
23-MAR-13 10.13.28.984307 AM +01:00

SQL> alter system switch logfile;

System altered.

SQL> select count(*) from A_AB;

  COUNT(*)
----------
         0

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

I have created table A_AB at 23-MAR-13 10:12:56 with SCN 21661731 and truncated by 10:13:38 with SCN 21661836. SO, i will be applying archived logs at standby till 10:12:56. At standby database, i have followed the following steps:

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> ALTER DATABASE RECOVER automatic standby database until time '2013-03-23 10:12:56';

Database altered.

Following is the output from standby alert.log file

Managed Standby Recovery Canceled (PLMQDBS)
Completed: alter database recover managed standby database cancel
Sat Mar 23 10:19:31 2013
ALTER DATABASE RECOVER automatic standby database until time '2013-03-23 10:12:56'
Media Recovery Start
 started logmerger process
Sat Mar 23 10:19:31 2013
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 8 slaves
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_23/o1_mf_1_3272_8ntv43gt_.arc
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_23/o1_mf_1_3273_8ntv43ht_.arc
Sat Mar 23 10:19:44 2013
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_23/o1_mf_1_3274_8ntv43kb_.arc
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_23/o1_mf_1_3275_8ntv6d5h_.arc
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_23/o1_mf_1_3276_8ntv6dyj_.arc
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_23/o1_mf_1_3277_8ntvmv0g_.arc
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_23/o1_mf_1_3278_8ntwg1fl_.arc
Sat Mar 23 10:19:48 2013
Incomplete Recovery applied until change 21661756 time 03/23/2013 10:12:56
Media Recovery Complete (PLMQDBS)
Completed: ALTER DATABASE RECOVER automatic standby database until time '2013-03-23 10:12:56'
Sat Mar 23 10:23:45 2013

Now, i just need to check the contents of table A_AB. I have converted standby into Snapshot standby and then back to physical standby.

SQL> select * from v$restore_point;

no rows selected

SQL> alter database convert to snapshot standby;

Database altered.

SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                             RESTORE_POINT_TIME  PRE NAME
---------- --------------------- --- ------------ -------------------------------- ------------------- --- ------------------------------------
  21661755                     1 YES     15941632 23-MAR-13 10.23.45.000000000 AM  10:23:45            YES SNAPSHOT_STANDBY_REQUIRED_03/23/2013 

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4175568896 bytes
Fixed Size                  2213384 bytes
Variable Size             805308920 bytes
Database Buffers         3355443200 bytes
Redo Buffers               12603392 bytes
Database mounted.
Database opened.
SQL> conn backup/backup
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A_AB                           TABLE
A_OB                           TABLE

SQL> select count(*) from A_AB;

  COUNT(*)
----------
     13798

SQL> select count(*) from A_OB;

  COUNT(*)
----------
     13795

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 4175568896 bytes
Fixed Size                  2213384 bytes
Variable Size             805308920 bytes
Database Buffers         3355443200 bytes
Redo Buffers               12603392 bytes
Database mounted.
SQL> alter database convert to physical standby;

Database altered.

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


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

Total System Global Area 4175568896 bytes
Fixed Size                  2213384 bytes
Variable Size             805308920 bytes
Database Buffers         3355443200 bytes
Redo Buffers               12603392 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

Below are lines from alert.log file of standby database.

alter database convert to snapshot standby
Starting background process RVWR
Sat Mar 23 10:23:45 2013
RVWR started with pid=24, OS id=29360282
Allocated 15937344 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/23/2013 10:23:45
krsv_proc_kill: Killing 2 processes (all RFS)
Sat Mar 23 10:23:48 2013
Deleted Oracle managed file /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_23/o1_mf_1_3279_8ntwl8sd_.arc
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 21661756
Resetting resetlogs activation ID 165702244 (0x9e06a64)
Online log /u02/oradata/CORE/PLMQDBS/redo01a.log: Thread 1 Group 1 was previously cleared
Online log /u03/oradata/CORE/PLMQDBS/redo01b.log: Thread 1 Group 1 was previously cleared
Online log /u02/oradata/CORE/PLMQDBS/redo02a.log: Thread 1 Group 2 was previously cleared
Online log /u03/oradata/CORE/PLMQDBS/redo02b.log: Thread 1 Group 2 was previously cleared
Online log /u02/oradata/CORE/PLMQDBS/redo03a.log: Thread 1 Group 3 was previously cleared
Online log /u03/oradata/CORE/PLMQDBS/redo03b.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 21661754
Sat Mar 23 10:23:49 2013
Setting recovery target incarnation to 2
WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is
not set to the value "AUTO".
This may cause recovery of the standby database to terminate
prior to applying all available redo data.
It may be necessary to use the ALTER DATABASE CREATE DATAFILE
command to add datafiles created on the primary database.
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby

I got the following error while applying the incremental backup to the standby database “ORA-19573: cannot obtain exclusive enqueue for datafile 1”. This was very annoying as i have cross checked everything many times. Here is few lines from RMAN sessions from which applying incremental backup:


channel ORA_DISK_4: reading from backup piece /oraclebackup/PLMQDBS/inc/incr_for_stdby_f7o3u54k_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/07/2013 12:39:20
ORA-19870: error while restoring backup piece /oraclebackup/PLMQDBS/inc/incr_for_stdby_f4o3u54k_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 1

Well, error was because of very silly mistake, i forgot to cancel the media recovery process before starting the RMAN incremental apply session. Once i have cancelled the recovery process as below, incremental backup ran fine.

oracore@myhost $ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 7 12:41:16 2013

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

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select name from v$database;

NAME
---------
PLMQDBS

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database recover managed standby database cancel;

Database altered.

what a small mistake !!!!!!!!

In my previous post (Restoring Table From Recyclebin), i have shown that how the constraint name is lost forever when we restore table from recyclebin. I feel it very strange that i can’t get the original constraint name back, so i asked this question in OTN forum and i came to know that there is way to get the name back using flashback queries. All thanks to “Tom Kyte” for this and original post link can be found here Tom Kyte.

I am just trying to replicate his work and trying to see if i am able to get the same result or not. I am using TIMESTAMP_TO_SCN function to get the SCN number as in real situations we usually know the appworx time when tables dropped from database.

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
17-MAR-13 05.40.51.553391 PM +01:00

SQL> create table yya_test_New (name varchar2(10) constraint upper_name check (name=upper(name)));

Table created.

SQL> create index yya_idx1 on yya_test_New(name);

Index created.

SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,TS_NAME,DROPTIME,CAN_UNDROP from user_recyclebin;

no rows selected

SQL> select CONSTRAINT_NAME from user_constraints;

CONSTRAINT_NAME
------------------------------
UPPER_NAME

SQL> insert into yya_test values('yogesh');
insert into yya_test values('yogesh')
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> insert into yya_test_New values('YOGESH');

1 row created.

SQL> commit;

Commit complete.

SQL> drop table YYA_TEST_NEW;

Table dropped.

Now, let’s find out the original constraint name. Now, i have appwrox time of table dropped and let’s find out the SCN.

SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,TS_NAME,DROPTIME,CAN_UNDROP from user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    TYPE                      TS_NAME                        DROPTIME            CAN
------------------------------ -------------------------------- ------------------------- ------------------------------ ------------------- ---
BIN$2CK5+mwxAELgQwotDFQAQg==$0 YYA_IDX1                         INDEX                     USERS                          2013-03-17:17:54:32 NO
BIN$2CK5+mwyAELgQwotDFQAQg==$0 YYA_TEST_NEW                     TABLE                     USERS                          2013-03-17:17:54:32 YES

SQL> select CONSTRAINT_NAME from user_constraints;

CONSTRAINT_NAME
------------------------------
BIN$2CK5+mwwAELgQwotDFQAQg==$0

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select timestamp_to_scn('17-MAR-13 05:42:00 PM') from dual;

TIMESTAMP_TO_SCN('17-MAR-1305:42:00PM')
---------------------------------------
                                 236338

SQL> select CONSTRAINT_NAME from dba_constraints as of scn 236338 where owner='BACKUP';

CONSTRAINT_NAME
------------------------------
UPPER_NAME

So, we got the name back…..

Recently there is situation where user has dropped around 25 tables from a test environment and i need to recover those tables. Since, recyclebin is “ON”, it seems to be a easy task. Of couse, recovery of table is easy but recovery of dependent is a real pain. On checking, i see that there are about total 113 objects which got dropped. It includes (Tables, Triggers, Indexes). Here are few lines and query against my recyclebin.


SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,DROPTIME,CAN_UNDROP from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME CAN
------------------------------ ------------------- ------ ------------------- ---
BIN$12jgaeSlAJLgQwotC20Akg==$0 PROJ_CREATED_INDEX INDEX 2013-03-08:12:10:55 NO
BIN$12jgaeSkAJLgQwotC20Akg==$0 PROJECT_NAME_INDEX INDEX 2013-03-08:12:10:55 NO
BIN$12jgaeScAJLgQwotC20Akg==$0 M1WEB_MODULE_MAP12 TABLE 2013-03-08:12:10:55 YES
BIN$12jgaeSbAJLgQwotC20Akg==$0 SYS_C0055476123456 INDEX 2013-03-08:12:10:55 NO
BIN$12jgaeSaAJLgQwotC20Akg==$0 MOD_MODEL_INDEX123 INDEX 2013-03-08:12:10:55 NO
BIN$12jgaeSZAJLgQwotC20Akg==$0 MOD_MAP_ID_INDEX12 INDEX 2013-03-08:12:10:55 NO
BIN$12jgaeSVAJLgQwotC20Akg==$0 M1WEB_MODULE123456 TABLE 2013-03-08:12:10:55 YES
BIN$12jgaeSUAJLgQwotC20Akg==$0 SYS_C0055483123456 INDEX 2013-03-08:12:10:55 NO
BIN$12jgaeSPAJLgQwotC20Akg==$0 M1WEB_MODEL_TAGS12 TABLE 2013-03-08:12:10:55 YES
BIN$12jgaeSOAJLgQwotC20Akg==$0 MOD_NAME_INDEX1234 INDEX 2013-03-08:12:10:55 NO
BIN$12jgaeSLAJLgQwotC20Akg==$0 M1WEB_MOD_STORAGE3 TABLE 2013-03-08:12:10:55 YES

To recover table simply flashback table to before drop as:


SQL> flashback table M1WEB_MODULE_MAP12 to before drop;

Flashback complete.

SQL> flashback table M1WEB_MODULE123456 to before drop;

Flashback complete.

SQL> flashback table M1WEB_MODEL_TAGS12 to before drop;

Flashback complete.

SQL> flashback table M1WEB_MOD_STORAGE3 to before drop;

Flashback complete.

Once, tables are back now we need to rename indexes and triggers to the original names. Mapping of recyclebin names to original name for tables and triggers is fine as above query has given the original names. So, we can rename indexes and triggers as:


SQL> alter index "BIN$12jgaeSlAJLgQwotC20Akg==$0" rename to PROJ_CREATED_INDEX;

Index altered.

SQL> alter index "BIN$12jgaeSkAJLgQwotC20Akg==$0" rename to PROJECT_NAME_INDEX;

Index altered.

SQL> alter index "BIN$12jgaeSbAJLgQwotC20Akg==$0" rename to SYS_C0055476;

Index altered.

Here is one interesting thing i noticed is that oracle generated nearly identicals names for idexes and tables with difference only in CAPS. Also no two objects of same type have identical names. Like, table – index have identical name not the table – table. Here is example from my first query.


SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,DROPTIME,CAN_UNDROP from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME CAN
------------------------------ ------------------- ------ ------------------- ---
BIN$12jgaeSlAJLgQwotC20Akg==$0 PROJ_CREATED_INDEX INDEX 2013-03-08:12:10:55 NO
BIN$12jgaeSLAJLgQwotC20Akg==$0 M1WEB_MOD_STORAGE3 TABLE 2013-03-08:12:10:55 YES

See the names are identical except 12th letter “l” is small in first name and in CAPS in second name. This was very confusing at first look but since we have original name with us, so no problem in rename of these objects.

Now, the real pain starts when it comes to rename constraints. Although, you can leave the constraint name in recyclebin format, but it’s not advisable. Now, the above query doesn’t contain the constraints, so we need to check the user_constraints and see the names of constraints which got renamed as well. Here is a small example, which is different from above one. In this example, i just wanted to show that how painful it is to rename a constraint.


SQL> create table yya_test (name varchar2(10) constraint upper_name check (name=upper(name)));

Table created.

SQL> create index yya_idx on yya_test(name);

Index created.

SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,TS_NAME,DROPTIME,CAN_UNDROP from user_recyclebin;

no rows selected

SQL> select CONSTRAINT_NAME from user_constraints;

CONSTRAINT_NAME
------------------------------
UPPER_NAME

SQL> insert into yya_test values('yogesh');
insert into yya_test values('yogesh')
*
ERROR at line 1:
ORA-02290: check constraint (BACKUP.UPPER_NAME) violated

SQL> insert into yya_test values('YOGESH');

1 row created.

SQL> commit;

Commit complete.

Now, as soon as i drop the table, i got entries for table & index into recyclebin and constraint got renamed and original name is lost for constraint. I would love to raise this point to oracle (if i know where to raise), that constraint name should not be lost. Here are the line that shows that constraint name is lost:


SQL> drop table yya_test;

Table dropped.

SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,TS_NAME,DROPTIME,CAN_UNDROP from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE TS_NAME DROPTIME CAN
------------------------------ -------------- ------- -------- ------------------- ---
BIN$1+WNXaW1AGTgQwotC20AZA==$0 YYA_IDX INDEX USERS 2013-03-14:17:34:02 NO
BIN$1+WNXaW2AGTgQwotC20AZA==$0 YYA_TEST TABLE USERS 2013-03-14:17:34:02 YES

SQL> select CONSTRAINT_NAME from user_constraints;

CONSTRAINT_NAME
------------------------------
BIN$1+WNXaW0AGTgQwotC20AZA==$0

I don’t know the solution for constraints name, if anyone does, please let me know too. Also, few restrictions on objects which can’t be restored from recyclebin. Here are lines from Oracle documentation:

When you drop a table, the table and all of its dependent objects go into the recycle bin together. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together. When you restore a table from the recycle bin, dependent objects such as indexes do not get their original names back; they retain their system-generated recycle bin names. Oracle Database retrieves all indexes defined on the table except for bitmap join indexes, and all triggers and constraints defined on the table except for referential integrity constraints that reference other tables.

Some dependent objects such as indexes may have been reclaimed because of space pressure. In such cases, the reclaimed dependent objects are not retrievable from the recycle bin.