How to Recover oracle Database until a Previous position by RMAN

How to back Database until a Previous position by RMAN

Database Point-in-time recovery (Time based incomplete recovery)

# This scenario assumes that all initialization files,RMAN backup of database and the current
# controlfile are in place and you want to recover to a point in time '03-07-2010:14:20:00' .
#

RMAN> shutdown immediate

RMAN>STARTUP MOUNT FORCE;

RMAN> RUN
{
SET UNTIL TIME "TO_DATE('03-07-2010:14:20:00','dd-mm-yyyy:hh24:mi:ss')";
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

connect to the database without TNS configure

1. with TNS In replace of Service name

sqlplus> conn scott/tiger@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(service_name=orcl)))'

--need to write in one line

2. Easy connection Method

In sqlnet.ora

NAMES.DIRECTORY_PATH=(TNSNAMES, EZCONNECT)

Then

sqlplus> conn scott/tiger@//localhost:1521/halim

---here halim is sid

ORA-02429: cannot drop index used for enforcement of unique/primary key

ORA-02429: cannot drop index used for enforcement of unique/primary key
========================================================================

---when I execute

SQL> drop index IND_ON_HALIM_TEST ; ----its reply

ORA-02429: cannot drop index used for enforcement of unique/primary key


If it is an unique index that enforces unique constraint
you cannot simply drop it



You have to drop unique constraint BY

alter table tablename drop constraint constraintname;



=================================================
Hands on Example
=================================================

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jun 30 16:21:01 2010

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

SQL> CONN STLBAS/STLBAS@JUNCLS
Connected.
SQL>
SQL>
SQL> DROP TABLE HALIM_TEST ;

Table dropped.

SQL>
SQL> CREATE TABLE halim_test (id number,name varchar2(30)) ;

Table created.

SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (100,'Mr. Halim');

1 row created.

SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (100,'Mr Cadot');

1 row created.

SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (200,'Mr. Arif');

1 row created.

SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (300,'Mr. Akash');

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL>
SQL> create index IND_ON_HALIM_TEST on halim_test(ID);

Index created.

SQL> ALTER TABLE halim_test ADD PRIMARY KEY (ID) NOVALIDATE ;

Table altered.

SQL>
SQL>
SQL>
SQL> DROP index IND_ON_HALIM_TEST ;
DROP index IND_ON_HALIM_TEST
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL>
SQL>
SQL>
SQL>
SQL> SELECT owner, constraint_name, constraint_type, table_name
2 FROM user_constraints
3 WHERE table_name = 'HALIM_TEST';

OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ----------------

STLBAS SYS_C0084077 P HALIM_TEST

SQL>
SQL>
SQL> alter table halim_test drop constraint SYS_C0084077 ;

Table altered.

SQL>
SQL>
SQL>
SQL>
SQL> SELECT index_name, index_type, table_owner, table_name, table_type,
2 uniqueness
3 FROM user_indexes
4 WHERE table_name = 'HALIM_TEST';

INDEX_NAME INDEX_TYPE TABLE_OWNER
TABLE_NAME TABLE_TYPE UNIQUENES
------------------------------ --------------------------- ---------------------
--------- ------------------------------ ----------- ---------
IND_ON_HALIM_TEST NORMAL STLBAS
HALIM_TEST TABLE NONUNIQUE

SQL>
SQL>
SQL> drop index IND_ON_HALIM_TEST;

Index dropped.

SQL>
SQL>
SQL>
SQL>

ORA-02437: cannot validate - primary key violated

ORA-02437: cannot validate - primary key violated
====================================================



SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jun 30 16:21:01 2010

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

SQL> CONN STLBAS/STLBAS@JUNCLS
Connected.
SQL>
SQL>
SQL> DROP TABLE HALIM_TEST ;

Table dropped.

SQL>
SQL> CREATE TABLE halim_test (id number,name varchar2(30)) ;

Table created.

SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (100,'Mr. Halim');

1 row created.

SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (100,'Mr Cadot');

1 row created.

SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (200,'Mr. Arif');

1 row created.

SQL>
SQL>
SQL> INSERT INTO halim_test VALUES (300,'Mr. Akash');

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> ALTER TABLE halim_test ADD PRIMARY KEY (ID) NOVALIDATE ;
ALTER TABLE halim_test ADD PRIMARY KEY (ID) NOVALIDATE
*
ERROR at line 1:
ORA-02437: cannot validate (STLBAS.SYS_C0084074) - primary key violated


SQL>
SQL>
SQL> ALTER TABLE halim_test ADD PRIMARY KEY (ID) DISABLE ;

Table altered.

SQL>
SQL> ALTER TABLE halim_test ENABLE NOVALIDATE PRIMARY KEY;
ALTER TABLE halim_test ENABLE NOVALIDATE PRIMARY KEY
*
ERROR at line 1:
ORA-02437: cannot validate (STLBAS.SYS_C0084075) - primary key violated

SQL>
SQL> ALTER TABLE halim_test ENABLE NOVALIDATE PRIMARY KEY;
ALTER TABLE halim_test ENABLE NOVALIDATE PRIMARY KEY
*
ERROR at line 1:
ORA-02437: cannot validate (STLBAS.SYS_C0084076) - primary key violated



======================
CAUSE & sOLUTION:
======================

This error occurs because it tries to create a unique index for the unique or primary
key index. The uniqueness is checked during the creation of the unique index.


Solution
=============
------- First, create a NON-UNIQUE index on the same fields that you want to
--------include in the constraint.
SQL>
SQL>
SQL> alter table HALIM_TEST drop constraint SYS_C0084076 ;

Table altered.

SQL>
SQL>
SQL> create index IND_ON_HALIM_TEST on halim_test(ID);

Index created.


-------- Then add the unique or primary key constraint.

SQL>
SQL> ALTER TABLE halim_test ADD PRIMARY KEY (ID) NOVALIDATE ;

Table altered.

oR

SQL>
SQL>
SQL> ALTER TABLE HALIM_TEST ENABLE NOVALIDATE PRIMARY KEY;

Table altered.

SQL>
SQL>