Wednesday, June 29, 2011

Recover table from RMAN backup after droping it? is it possible? how can i do it?

Recover table from RMAN backup after droping it? is it possible? how can i do it?
=========================================================


Recovery will always rollforward yours backup from the time you crash the database.Like

Step 1
You created table

Step 2
Full backup

Step 3
Drop table

Step 1 will goes to yours backup file but step 3 goes to redo log

When you recover database

Step 1
Recover table again due to yours backup file

Step 3
Drop table will due to apply yours redo.



incomplete recovery ( just before to the drop table) can recover your table from RMAN backup.

or

so from RMAN backup one table recovery has No direct methods available.
You can recover /duplcate the database to anther database with RMAN backup
or incomplete recovery before the drop table command
Export the concerned table from the restored/duplicated database to the database you want.

or

use flashback (in oracle 10g) for indivisual table recovery purpose.

or

RMAN TSPITR is most useful for recovering the following:

An erroneous DROP TABLE or TRUNCATE TABLE statement
A table that has become logically corrupted
An incorrect batch job or other DML statement that has affected only a subset of the database
A logical schema to a point different from the rest of the physical database when multiple schemas exist in separate tablespaces of one physical database
-------------------------------------***--------------------------------

RMAN is suitable for media recovery.

imp dump file is a data and metadata backup not database backup
it will no longer be furitful when you will have corruption in yours datafiles.

HOW (IMPORT) IMPDP DATA from onE schema to another schema IN ORACLE DATABASE 10G

HOW to (IMPORT) IMPDP DATA from onE schema to another schema IN ORACLE DATABASE 10G
====================================================================================

impdp scott/tiger@stlbas_105 schemas=SCOTT directory=dir_stlbas dumpfile=stelar.dmp logfile=stelar_tables.log remap_schema=SCOTT:HR

-----table from one schema to another schema

impdp scott/tiger@stlbas_105 tables=SCOTT.EMP directory=dir_stlbas dumpfile=stelar.dmp logfile=stelar_tables.log remap_schema=SCOTT:HR

see more here
http://www.oracle-base.com/articles/10g/OracleDataPump10g.php

How to expdp or impdp some tables from one of the schema out of many schema IN ORACLE DATABASE 10G

How to expdp or impdp some tables from one of the schema out of many schema IN ORACLE DATABASE 10G
==================================================================================================

expdp stlbas/stlbas@stlbas_105 tables=stlbas.stfacmas,stlbas.stfalimt,stlbas.stprodct,stlbas.stfetran directory=dir_stlbas dumpfile=stelar.dmp logfile=stelar_tables.log

impdp stlbas/stlbas@stlbas_105 tables=stlbas.stfacmas,stlbas.stfalimt,stlbas.stprodct,stlbas.stfetran directory=dir_stlbas table_exists_action=replace dumpfile=stelar.dmp logfile=stelar_tables.log

or

expdp scott/tiger@stlbas_105 schemas=Stlbas include=TABLE:"IN ('STFACMAS', 'STFALIMT',STPRODCT,STFETRAN)" directory=dir_stlbas dumpfile=stelar.dmp logfile=stelar_tables.log

IMPdp scott/tiger@stlbas_105 schemas=Stlbas include=TABLE:"IN ('STFACMAS', 'STFALIMT',STPRODCT,STFETRAN)" directory=dir_stlbas table_exists_action=replace dumpfile=stelar.dmp logfile=stelar_tables.log


see more here http://www.oracle-base.com/articles/10g/OracleDataPump10g.php

Monday, June 27, 2011

Secure External Password Store or SSO or password wallet

Secure External Password Store or SSO or password wallet
------------------------------------------------------------

see here:-

http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/cnctslsh.htm#i1006413

examples:-

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>mkstore
mkstore [-wrl wrl] [-create] [-createSSO] [-createALO] [-delete] [-deleteSSO] [-
list] [-createEntry alias secret] [-viewEntry alias] [-modifyEntry alias secret]
[-deleteEntry alias] [-help]

C:\Documents and Settings\Administrator>mkstore -wrl G:\app\Administrator\produc
t\11.1.0\db_1\NETWORK\ADMIN\ -create
Enter password:

Invalid Password....
PASSWORD_POLICY : Passwords must have a minimum length of eight characters and
contain alphabetic characters combined with numbers or special characters.
Enter password:

Invalid Password....
PASSWORD_POLICY : Passwords must have a minimum length of eight characters and
contain alphabetic characters combined with numbers or special characters.

C:\Documents and Settings\Administrator>mkstore -wrl G:\app\Administrator\produc
t\11.1.0\db_1\NETWORK\ADMIN\ -create
Enter password:

Enter password again:


C:\Documents and Settings\Administrator>
C:\Documents and Settings\Administrator>
C:\Documents and Settings\Administrator>cd ..

C:\Documents and Settings>cd ..

C:\>
C:\>
C:\>mkstore -wrl G:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\ -create
Credential test123 stlbas stlbas
Enter wallet password:

Create credential oracle.security.client.connect_string1

C:\>mkstore -wrl G:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\ -create
Credential test123 sys sys
Enter wallet password:

Create credential Secret Store error occured: oracle.security.pki.OracleSecretSt
oreException: Credential already exists

C:\>
C:\>mkstore -wrl G:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\ -create
Credential atm19 sys sys
Enter wallet password:

Create credential oracle.security.client.connect_string2

C:\>
C:\>mkstore -wrl G:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\ -create
Credential report39 sys reportsys
Enter wallet password:

Create credential oracle.security.client.connect_string3

C:\>
C:\>
C:\>conn sys@report39
'conn' is not recognized as an internal or external command,
operable program or batch file.

C:\>sqplus /nolog
'sqplus' is not recognized as an internal or external command,
operable program or batch file.

C:\>
C:\>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 27 14:02:48 2011

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

SQL>
SQL> conn /@report39
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> conn sys@report39
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied


SQL>
SQL> conn / @atm19
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> conn / @test123
Connected.
SQL>
SQL> show user
USER is "STLBAS"
SQL>
SQL> conn sys@test123
Enter password:
ERROR:
ORA-01005: null password given; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn / @test123
Connected.
SQL>
SQL>
SQL> conn / @test123
Connected.
SQL>
SQL>
SQL>
SQL> conn / @test123
Connected.
SQL>
SQL>
SQL> conn / @test123
Connected.
SQL> select host_name from v$instance ;

HOST_NAME
----------------------------------------------------------------
TESTDB

SQL> conn / @test123
Connected.
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>
C:\>mkstore -wrl G:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\ -listCr
edential
Enter wallet password:

List credential (index: connect_string username)
3: report39 sys
2: atm19 sys
1: test123 stlbas

C:\>
C:\>mkstore -wrl G:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\ -delete
Credential atm19
Enter wallet password:

Delete credential
Delete 2

C:\>
C:\>
C:\>
C:\>mkstore -wrl G:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\ -listCr
edential
Enter wallet password:

List credential (index: connect_string username)
3: report39 sys
1: test123 stlbas

C:\>
C:\>
C:\>mkstore -wrl G:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\ -create
Credential atm19 atmutl atmutl
Enter wallet password:

Create credential oracle.security.client.connect_string2

C:\>
C:\>
C:\>mkstore -wrl G:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\ -create
Credential atm19 stlbas stlbas
Enter wallet password:

Create credential Secret Store error occured: oracle.security.pki.OracleSecretSt
oreException: Credential already exists

C:\>
C:\>
C:\>mkstore -wrl G:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\sso\ -cr
eateCredential atm19 stlbas stlbas
Enter wallet password:

Could not open wallet. Wallet not found at G:\app\Administrator\product\11.1.0\d
b_1\NETWORK\ADMIN\sso\ewallet.p12

C:\>
C:\>
C:\>
C:\>
C:\>
C:\>
C:\>
C:\>mkstore -wrl G:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\ssowalle
t\ -create
Enter password:

Enter password again:


C:\>
C:\>
C:\>
C:\>mkstore -wrl G:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\sso\ -cr
eateCredential atm19 stlbas stlbas
Enter wallet password:

Could not open wallet. Wallet not found at G:\app\Administrator\product\11.1.0\d
b_1\NETWORK\ADMIN\sso

C:\>
C:\>
C:\>mkstore -wrl G:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\ssowalle
t\ -createCredential atm19 stlbas stlbas
Enter wallet password:

Create credential oracle.security.client.connect_string1

C:\>
C:\>
C:\>
C:\>mkstore -wrl G:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\ssowalle
t\ -createCredential test123 sys sys
Enter wallet password:

Create credential oracle.security.client.connect_string2

C:\>
C:\>
C:\>
C:\>
C:\>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 27 14:28:06 2011

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

SQL> conn /@test123
Connected.
SQL>
SQL>
SQL> show user
USER is "STLBAS"
SQL>
SQL> conn /@test123
Connected.
SQL>
SQL>