Skip to content

Archive for

Jan 23

14 Comments

How to export from oracle 11.2 and import into 10.2 version

I think many OTN users asked repeately this question How do i export from higher version and import into lower version.I just show here How do we do.

First : Using Datapump from Oralce 11.2 and import into 10.2 version

Source DB 11.2 Version :

Step 1: Create Directory

SQL> create or replace directory test_dir as 'D:\backupnew\dump';

Directory created.

SQL> grant read,write on directory test_dir to scott;

Grant succeeded.

SQL> conn scott/tiger@azardb
Connected.
SQL> create table testversion(version varchar2(20));

Table created.

SQL> insert into testversion values('oralce11gr2');

1 row created.

SQL> commit;

Commit complete.

Step 2: Export Table using Datapump

C:\Users\mazar>expdp scott/tiger@azardb directory=test_dir dumpfile=testver.dmp tables=testversion

Export: Release 11.2.0.1.0 - Production on Sun Jan 23 15:54:13 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/********@azardb directory=test_dir dumpfile=testver.dmp tables=testversion
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."TESTVERSION"                       5.031 KB       1 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  D:\BACKUPNEW\DUMP\TESTVER.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 15:54:40

 Now Go to Target DB 10.2 Version

Step 3: Create Directory for Scott  User.

SQL> create or replace directory test_dir as 'd:\newdump';

Directory created.

SQL> grant read,write on directory test_dir to scott;

Grant succeeded.

Step 3: I just Copied this TESTVER.DMP file into target DB 10.2 Directory and import it

D:\oracle\product\10.2.0\db_2\BIN>impdp scott/tiger@ace directory=test_dir dumpf
ile=testversion.dmp tables=testversion

Import: Release 10.2.0.1.0 - 64bit Production on Sunday, 23 January, 2011 16:04:
31

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39143: dump file "d:\newdump\testversion.dmp" may be an original export dump
 file

It showing error, So you need to export Data in source db 11.2 using Version parameter

Step 4:

C:\Users\mazar>expdp scott/tiger@azardb directory=test_dir dumpfile=testver.dmp tables=testversion
 version=10.2 reuse_dumpfiles=yes

Export: Release 11.2.0.1.0 - Production on Sun Jan 23 16:06:47 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/********@azardb directory=test_dir dumpfile=testver.dmp tables=testversion 
version=10.2 reuse_dumpfiles=yes
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."TESTVERSION"                       4.968 KB       1 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  D:\BACKUPNEW\DUMP\TESTVER.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 16:06:54

Step 5: Again I copied this dump file to target DB 10.2 directory and import it.

D:\oracle\product\10.2.0\db_2\BIN>impdp scott/tiger@ace directory=test_dir dumpf
ile=testver.dmp tables=testversion remap_tablespace=users_tbs:users

Import: Release 10.2.0.1.0 - 64bit Production on Sunday, 23 January, 2011 16:08:
37

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/********@ace directory=test_dir d
umpfile=testver.dmp tables=testversion remap_tablespace=users_tbs:users
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."TESTVERSION"                       4.968 KB       1 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 16:08:39
D:\oracle\product\10.2.0\db_2\BIN>

Now I successfully import it.

Same Like If you want Import oracle 9i version from export taking of 10g version dumpfile, you just use to export 9.2 export client of Source DB 10.2 data and then import it.

Sourcedb : azardb , Version :11.2

Unfortunately I don’t have Oracle 9i software.So i jsut used 11.2 and 10.2

D:\oracle\product\10.2.0\db_2\BIN>exp scott/tiger@azardb file=d:\newdump\testver
sion.dmp tables=test

Export: Release 10.2.0.1.0 - Production on Sun Jan 23 15:10:32 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AR8MSWIN1256 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                           TEST          1 rows exported
Export terminated successfully without warnings.

Target DB : ACE Version :10.2

D:\oracle\product\10.2.0\db_2\BIN>set oracle_sid=ace

D:\oracle\product\10.2.0\db_2\BIN>imp scott/tiger@ace file=d:\newdump\testversio
n.dmp tables=test

Import: Release 10.2.0.1.0 - Production on Sun Jan 23 15:11:03 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AR8MSWIN1256 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                         "TEST"          1 rows imported
Import terminated successfully without warnings.

Jan 21

4 Comments

Grid infrastructure installation standalone server for windows video guide

Here I just demonstrate for grid infrastructure installation standalone server for windows and also include How do we create asmdisk for windows 7.

Watch Now:

Just a Good quality watch from here also

http://www.youtube.com/watch?v=1aKPY0Zi_rc

For Grid infrastructure installation standalone server for Linux

https://mohamedazar.wordpress.com/2010/11/29/grid-infrastructure-installation-guide-for-standalone-server/

Jan 8

7 Comments

Recover missing datafile using RMAN Data Recoveray advisor

This is one Oracle 11g New feature. Here I’m go to show How to recover missing datafile using RMAN data recovery advisor without taking previous backup.

SQL> create tablespace datarec datafile 'd:\backupnew\datarec01.dbf' size 50m;

Tablespace created.

SQL> create user datarec identified by datarec default tablespace datarec;

User created.

SQL> grant connect,resource to datarec;

Grant succeeded.

SQL> conn datarec/datarec;
Connected.
SQL> create table test(empno number,name varchar2(20));

Table created.

SQL> insert into test values(001,'azar');

1 row created.

SQL> insert into test values(002,'kareem');

1 row created.

SQL> commit;

Commit complete.
SQL> conn datarec/datarec;
Connected.
SQL> select * from test;

     EMPNO NAME
---------- --------------------
         1 azar
         2 kareem

SQL>

Shutdown Database

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Delete particular DBF file using OS command manualy.

d:\backupnew>dir
Volume in drive D has no label.
Volume Serial Number is 3861-730C

Directory of d:\backupnew

08-Jan-11 11:35 AM .
08-Jan-11 11:35 AM ..
08-Jan-11 11:33 AM 2,002,329,600 ACE01.DBF
08-Jan-11 10:13 AM 130,838 archive.txt
08-Jan-11 10:12 AM 148,590 archivelog.txt
01-Jan-11 04:50 PM AZARDB
08-Jan-11 11:35 AM 52,436,992 DATAREC01.DBF
03-Jan-11 10:06 AM 1,116 initazar.ora
03-Jan-11 10:19 AM 1,115 initazardb.ora
03-Jan-11 09:11 AM New folder
03-Jan-11 09:16 AM 104,865,792 TESTTBS01.DBF
01-Jan-11 05:12 PM 86,016 TSPITR_EINS_53941.DMP
01-Jan-11 05:57 PM 86,016 TSPITR_LBFD_89576.DMP
9 File(s) 2,160,086,075 bytes
4 Dir(s) 106,103,545,856 bytes free

d:\backupnew>del datarec01.dbf

Startup database

SQL> startup
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 335544784 bytes
Database Buffers 192937984 bytes
Redo Buffers 5804032 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 15 - see DBWR trace file
ORA-01110: data file 15: 'D:\BACKUPNEW\DATAREC01.DBF'

Recover missing datafile using RMAN Data Recovery Advisor

C:\Users\mazar>set oracle_sid=azardb

C:\Users\mazar>rman target sys/Admin123

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 8 11:40:07 2011

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

connected to target database: AZARDB (DBID=1652383192, not open)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
67468 HIGH OPEN 08-JAN-11 One or more non-system datafiles are missing

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
67468 HIGH OPEN 08-JAN-11 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file D:\BACKUPNEW\DATAREC01.DBF was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 15
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\oracle\mazar\diag\rdbms\azardb\azardb\hm\reco_3190867713.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\oracle\mazar\diag\rdbms\azardb\azardb\hm\reco_3190867713.hm

contents of repair script:
# restore and recover datafile
restore datafile 15;
recover datafile 15;

Do you really want to execute the above repair (enter YES or NO)? y
executing repair script

Starting restore at 08-JAN-11
using channel ORA_DISK_1

creating datafile file number=15 name=D:\BACKUPNEW\DATAREC01.DBF
restore not done; all files read only, offline, or already restored
Finished restore at 08-JAN-11

Starting recover at 08-JAN-11
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 08-JAN-11
repair failure complete
RMAN>SQL> alter database open;

Database altered.

SQL> select name,status from v$datafile where file#=15;

NAME
--------------------------------------------------------------------------------
STATUS
-------
D:\BACKUPNEW\DATAREC01.DBF
ONLINE

SQL>SQL> conn datarec/datarec;
Connected.
SQL> select * from test;

EMPNO NAME
---------- --------------------
1 azar
2 kareem

SQL>

Controfile Recover :

And also Checking For Control File. I just moved controlfile and startup database ,It shows error,
then i just recovered Controlfile using RMAN data Recovery Advisor.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\CONTROL01.CTL
C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\CONTROL02.CTL

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
C:\app\oracle\mazar\oradata\azardb>move control01.ctl C:\APP\ORACLE\MAZAR\ORADATA\
        1 file(s) moved. 
SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             335544784 bytes
Database Buffers          192937984 bytes
Redo Buffers                5804032 bytes
ORA-00205: error in identifying control file, check alert log for more info

 

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
75867      CRITICAL OPEN      08-JAN-11     Control file C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\CONTROL01.CTL is missing

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
75867      CRITICAL OPEN      08-JAN-11     Control file C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\CONTROL01.CTL is missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Use a multiplexed copy to restore control file C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\CONTROL01.CTL
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: c:\app\oracle\mazar\diag\rdbms\azardb\azardb\hm\reco_2373618729.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\app\oracle\mazar\diag\rdbms\azardb\azardb\hm\reco_2373618729.hm

contents of repair script:
   # restore control file using multiplexed copy
   restore controlfile from 'C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\CONTROL02.CTL';
   sql 'alter database mount';

Do you really want to execute the above repair (enter YES or NO)? y
executing repair script

Starting restore at 08-JAN-11
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\CONTROL01.CTL
output file name=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\CONTROL02.CTL
Finished restore at 08-JAN-11

sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete

RMAN>
SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\CONTROL01.CTL
C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\CONTROL02.CTL

SQL>
Just for My understanding
http://forums.oracle.com/forums/thread.jspa?threadID=2155786&tstart=0

Jan 4

4 Comments

How do i connect remotely Linux desktop through windows using NX machine

Very bore when i connect linux terminal session  through telnet. I need to see linux desktop also , so I just configured and Here i’m go to show How can we connect remotely linux desktop through by windows usins NX machine.

Requirements :

Download NX machine software

Check your Linux x86 or x86_64, Mine x86 So I just downloaded linux NX server edition from here

http://www.nomachine.com/download-package.php?Prod_Id=2248

You need to download  ” Client, Node, Server ”

& then if you want linux desktop from Windows, you must install ‘Client NX Software for Windows ”

http://www.nomachine.com/download-client-windows.php

Now Let’s Start Installation Process.

Go Linux Machine

Copied RPM packages from USB Drive or where downloaded rpm copy avilable.

Install RPM copy like below


[root@azardb tmp]# ls
gconfd-root            nxclient-3.4.0-7.i386.rpm   ssh-TxRrrm2695
gedit.root.1943051511  nxnode-3.4.0-14.i386.rpm    VBoxLinuxAdditions-x86.run
keyring-hSXRFi         nxserver-3.4.0-14.i386.rpm  virtual-root.wx4sy5
mapping-root           orbit-root
[root@azardb tmp]# rpm -Uvh nxclient-3.4.0-7.i386.rpm
Preparing...                ########################################### [100%]
   1:nxclient               ########################################### [100%]
Showing file: /usr/NX/share/documents/client/cups-info

 CUPS Printing Backend

 The NX Client set-up procedure detected that your "IPP CUPS" printing
 backend doesn't allow printing from the NX session. In order to have
 printing support in your NX system, you need to set proper permissions
 on the IPP backend. Please execute:

   chmod 755 /usr/lib/cups/backend/ipp
[root@azardb tmp]# chmod 755 /usr/lib/cups/backend/ipp
[root@azardb tmp]# rpm -Uvh nxnode-3.4.0-14.i386.rpm
Preparing...                ########################################### [100%]
   1:nxnode                 ########################################### [100%]
NX> 700 Starting: install node operation at: Tue Jan 04 03:57:35 2011.
NX> 700 Autodetected system 'redhat'.
NX> 700 Install log is '/usr/NX/var/log/install'.
NX> 700 Creating configuration in /usr/NX/etc/node.cfg.
NX> 700 Inspecting local CUPS environment.
NX> 700 Generating CUPS entries in: /usr/NX/etc/node.cfg.
NX> 700 Installation of version: 3.4.0-14 completed.
NX> 700 Bye.

[root@azardb tmp]# rpm -Uvh nxserver-3.4.0-14.i386.rpm
Preparing...                ########################################### [100%]
   1:nxserver               ########################################### [100%]
NX> 700 Installing: server at: Tue Jan 04 03:57:57 2011.
NX> 700 Autodetected system: redhat.
NX> 700 Install log is: /usr/NX/var/log/install.
NX> 700 Creating configuration file: /usr/NX/etc/server.cfg.
NX> 723 Cannot start NX statistics:
NX> 709 NX statistics are disabled for this server.
NX> 700 Version '3.4.0-14' installation completed.
NX> 700 Showing file: /usr/NX/share/documents/server/install-notices

Server keys

The initial login between client and server happens through a DSA key
pair, i.e. a couple of specially generated cryptographic keys, called
the private key and the public key, which allow you to establish a
secure connection, by means of SSL encryption, between NX client and
NX server.

..... Blah Blah blah

Documentation

For further information on how to manage the configuration of your
NX system, please refer to the System Administrator's Guide available
on the NoMachine Web site at:

http://www.nomachine.com/documentation/admin-guide.php

The NoMachine Team.
NX> 700 Bye.

After Installed

We need to check SSH Key generation .


http://www.nomachine.com/ar/view.php?ar_id=AR01C00126


 [root@azardb ~]# cat  /usr/NX/home/nx/.ssh/default.id_dsa.pub
no-port-forwarding,no-agent-forwarding,command="/usr/NX/bin/nxserver --login" ssh-dss AAAAB3NzaC1kc3MAAACBAJe/0DNBePG9dYLWq7cJ0SqyRf1iiZN/IbzrmBvgPTZnBa5FT/0Lcj39sRYt1paAlhchwUmwwIiSZaON5JnJOZ6jKkjWIuJ9MdTGfdvtY1aLwDMpxUVoGwEaKWOyin02IPWYSkDQb6cceuG9NfPulS9iuytdx0zIzqvGqfvudtufAAAAFQCwosRXR2QA8OSgFWSO6+kGrRJKiwAAAIEAjgvVNAYWSrnFD+cghyJbyx60AAjKtxZ0r/Pn9k94Qt2rvQoMnGgt/zU0v/y4hzg+g3JNEmO1PdHh/wDPVOxlZ6Hb5F4IQnENaAZ9uTZiFGqhBO1c8Wwjiq/MFZy3jZaidarLJvVs8EeT4mZcWxwm7nIVD4lRU2wQ2lj4aTPcepMAAACANlgcCuA4wrC+3Cic9CFkqiwO/Rn1vk8dvGuEQqFJ6f6LVfPfRTfaQU7TGVLk2CzY4dasrwxJ1f6FsT8DHTNGnxELPKRuLstGrFY/PR7KeafeFZDf+fJ3mbX5nxrld3wi5titTnX+8s4IKv29HJguPvOK/SI7cjzA+SqNfD7qEo8=
[root@azardb ~]#

And also Give NX permission for this file.


[root@azardb ~]# chown nx:root /usr/NX/home/nx/.ssh/authorized_keys2
[root@azardb ~]# chmod 0644 /usr/NX/home/nx/.ssh/authorized_keys2

[root@azardb ~]# chown nx:root /usr/NX/home/nx/.ssh/default.id_dsa.pub
[root@azardb ~]# chmod 0644 /usr/NX/home/nx/.ssh/default.id_dsa.pub

Now Install NX Windows client for Windows

Double Click *.exe file, I just showing some screenshots here

After Installed , Go to Start –> NX client for Windows –> Click NX Connection Wizard

 Session Name : Put any name

Hostname : linux machine hostname or ipaddress. Default port 22

Select Internet connection type also Like LAN, WAN


 

 

 Now Client software also successfully installed, But if you’re trying to login, It should show error.

You must copy SSH generation key from linux machine to Windows machine


[root@azardb ~]# cat  /usr/NX/home/nx/.ssh/default.id_dsa.pub

no-port-forwarding,no-agent-forwarding,command="/usr/NX/bin/nxserver --login" ssh-dss AAAAB3NzaC1kc3MAAACBAJe/0DNBePG9dYLWq7cJ0SqyRf1iiZN/IbzrmBvgPTZnBa5FT/0Lcj39sRYt1paAlhchwUmwwIiSZaON5JnJOZ6jKkjWIuJ9MdTGfdvtY1aLwDMpxUVoGwEaKWOyin02IPWYSkDQb6cceuG9NfPulS9iuytdx0zIzqvGqfvudtufAAAAFQCwosRXR2QA8OSgFWSO6+kGrRJKiwAAAIEAjgvVNAYWSrnFD+cghyJbyx60AAjKtxZ0r/Pn9k94Qt2rvQoMnGgt/zU0v/y4hzg+g3JNEmO1PdHh/wDPVOxlZ6Hb5F4IQnENaAZ9uTZiFGqhBO1c8Wwjiq/MFZy3jZaidarLJvVs8EeT4mZcWxwm7nIVD4lRU2wQ2lj4aTPcepMAAACANlgcCuA4wrC+3Cic9CFkqiwO/Rn1vk8dvGuEQqFJ6f6LVfPfRTfaQU7TGVLk2CzY4dasrwxJ1f6FsT8DHTNGnxELPKRuLstGrFY/PR7KeafeFZDf+fJ3mbX5nxrld3wi5titTnX+8s4IKv29HJguPvOK/SI7cjzA+SqNfD7qEo8=

Copy this Key, and paste it in your windows machine, Where ?


C:\Program Files\NX Client for Windows\share\keys\server.id_dsa

Replace old key Adn save with new key.

Now You can go to login As Root user & password but before login as Root user, you need to add some lines in linux machine, Otherwise you can get error like  “User ‘root’ cannot be used as an NX user”


[root@azardb ~]# gedit /usr/NX/etc/server.cfg

EnableAdministratorLogin = "1"

And restart Nxserver[root@azardb ~]# /etc/init.d/nxserver restart
Trying to restart NX server:
NX> 123 Service stopped.
NX> 153 Stopping NX server monitor.
NX> 153 NX server monitor already stopped.
NX> 122 Service started.
NX> 999 Bye.
Trying to restart NX statistics:
NX> 723 Cannot start NX statistics:
NX> 709 NX statistics are disabled for this server.
NX> 999 Bye.
[root@azardb ~]#

Now You can Successully login as a Root user through windows and Linux Desktop should be appear in your windows Desktop.

Jan 3

3 Comments

How to recover datafile not backed up

Recently i just added one datafile for a tablespace and later, had a media failure occur before I backuped recently datafile.In this case , How do we recover.

We have previous Good Database backup.

Redo files needed upto Datafiles created.

If you have a current control file, we can restore , recover datafile & tablespace or database level.

Now See Below Example :

before I just backuped database with archivelogs.

Now I’m go to Add datafile to a existing tablespace.

SQL> alter tablespace testtbs add datafile 'D:\BACKUP\RESTORENEW\TESTTBS02.dbf' SIZE 50M;

Tablespace altered. 

SQL> select file_name from dba_data_files where tablespace_name='TESTTBS';

FILE_NAME
--------------------------------------------------------------------------------
D:\BACKUP\RESTORENEW\TESTTBS01.DBF
D:\BACKUP\RESTORENEW\TESTTBS02.DBF

Now I moved Recently added DBF file to other location

d:\backup\restorenew>move testtbs02.dbf d:\backup
        1 file(s) moved.

d:\backup\restorenew>

Shutdown Database & Startup

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             331350480 bytes
Database Buffers          197132288 bytes
Redo Buffers                5804032 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: 'D:\BACKUP\RESTORENEW\TESTTBS02.DBF'

Now Recover tablespace using RMAN

RMAN> restore tablespace testtbs;

Starting restore at 03-JAN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=135 device type=DISK

creating datafile file number=12 name=D:\BACKUP\RESTORENEW\TESTTBS02.DBF
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to D:\BACKUP\RESTORENEW\TESTTBS01.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_03\O1_MF_NNNDF_TAG20110103T122037_6L357C6K_.BKP
channel ORA_DISK_1: piece handle=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_03\O1_MF_NNNDF_TAG20110103T122037_6L357C6K_.BKP tag=TAG201101
03T122037
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 03-JAN-11

RMAN> recover tablespace testtbs;

Starting recover at 03-JAN-11
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 03-JAN-11

RMAN> alter database open;

database opened

Let me check datafile

SQL> select file_name from dba_data_files where tablespace_name='TESTTBS';

FILE_NAME
--------------------------------------------------------------------------------
D:\BACKUP\RESTORENEW\TESTTBS01.DBF
D:\BACKUP\RESTORENEW\TESTTBS02.DBF

Using A backup Control file

If we’re using a backup control file that has no information about the datafile, then we must restore and recover at the database level.

I just added another datafile also.

SQL> alter tablespace testtbs add datafile 'D:\BACKUP\RESTORENEW\TESTTBS03.dbf' SIZE 50M;

Tablespace altered.

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area     535662592 bytes

Fixed Size                     1375792 bytes
Variable Size                331350480 bytes
Database Buffers             197132288 bytes
Redo Buffers                   5804032 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 03-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

recovery area destination: C:\app\oracle\mazar\flash_recovery_area
database name (or database unique name) used for search: AZARDB
channel ORA_DISK_1: AUTOBACKUP C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2011_01_03\O1_MF_S_739455960_6L35KBTC_.BKP found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20110103
channel ORA_DISK_1: restoring control file from AUTOBACKUP C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2011_01_03\O1_MF_S_739455960_6L35KBTC_.BKP

channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\CONTROL01.CTL
output file name=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\CONTROL02.CTL
Finished restore at 03-JAN-11

RMAN> alter database mount;

database mounted

RMAN> restore database;

Starting restore at 03-JAN-11
Starting implicit crosscheck backup at 03-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 03-JAN-11

Starting implicit crosscheck copy at 03-JAN-11
using channel ORA_DISK_1
Crosschecked 17 objects
Finished implicit crosscheck copy at 03-JAN-11

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\ARCHIVELOG\2011_01_03\O1_MF_1_29_6L2X3V22_.ARC
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\ARCHIVELOG\2011_01_03\O1_MF_1_29_6L2XL3M4_.ARC
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2010_12_26\O1_MF_S_738773797_6KGCCPPK_.BKP
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2010_12_26\O1_MF_S_738777392_6KGGW2R7_.BKP
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2010_12_27\O1_MF_N_738851503_6KJQ80HG_.BKP
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2010_12_27\O1_MF_N_738851640_6KJY64RH_.BKP
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2010_12_27\O1_MF_S_738813797_6KHLFP6B_.BKP
File Name: C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2011_01_03\O1_MF_S_739455960_6L35KBTC_.BKP

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\BACKUP\ADDME01.DBF
channel ORA_DISK_1: restoring datafile 00004 to C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\EXAMPLE01.DBF
channel ORA_DISK_1: restoring datafile 00006 to D:\BACKUP\RCRMAN01.DBF
channel ORA_DISK_1: restoring datafile 00007 to D:\BACKUP\USERSTBS01.DBF
channel ORA_DISK_1: restoring datafile 00008 to D:\BACKUP\UNDOTBS02.DBF
channel ORA_DISK_1: restoring datafile 00009 to D:\BACKUP\RESTORENEW\TESTTBS01.DBF
channel ORA_DISK_1: restoring datafile 00010 to D:\BACKUP\TESTDB001.DBF
channel ORA_DISK_1: restoring datafile 00011 to D:\BACKUP\TESTTB1.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_03\O1_MF_NNNDF_TAG20110103T122037_6L357C6K_.BKP
channel ORA_DISK_1: piece handle=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_03\O1_MF_NNNDF_TAG20110103T122037_6L357C6K_.BKP tag=TAG201101
03T122037
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:05
Finished restore at 03-JAN-11

RMAN> recover database;

Starting recover at 03-JAN-11
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 34 is already on disk as file C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\ARCHIVELOG\2011_01_03\O1_MF_1_34_6L35K2PO_
.ARC
archived log for thread 1 with sequence 35 is already on disk as file C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\REDO03.LOG
archived log file name=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\ARCHIVELOG\2011_01_03\O1_MF_1_34_6L35K2PO_.ARC thread=1 sequence=34
archived log file name=C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\REDO03.LOG thread=1 sequence=35
creating datafile file number=12 name=D:\BACKUP\RESTORENEW\TESTTBS02.DBF
archived log file name=C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\REDO03.LOG thread=1 sequence=35
creating datafile file number=13 name=D:\BACKUP\RESTORENEW\TESTTBS03.DBF
archived log file name=C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\REDO03.LOG thread=1 sequence=35
media recovery complete, elapsed time: 00:00:06
Finished recover at 03-JAN-11

RMAN> alter database open resetlogs;

database opened
SQL> select file_name from dba_data_files where tablespace_name='TESTTBS';

FILE_NAME
--------------------------------------------------------------------------------
D:\BACKUP\RESTORENEW\TESTTBS02.DBF
D:\BACKUP\RESTORENEW\TESTTBS03.DBF
D:\BACKUP\RESTORENEW\TESTTBS01.DBF

Jan 3

12 Comments

ORA-30012: undo tablespace ‘UNDOTBS02’ does not exist or of wrong type

When i try startup my test db , the following error will be occur.

SQL> startup
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 331350480 bytes
Database Buffers 197132288 bytes
Redo Buffers 5804032 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS02' does not exist or of wrong type
Process ID: 14328
Session ID: 125 Serial number: 5

Actually what i’am wrong here, I created new undo tablespace for existing tablespace & then I dropped existing undo tablespace. I startup using pfile and worked, later i shutdown database without creating spfile for existing db. SO I just startup using spfile, the above error will be shown.

Solution :

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 331350480 bytes
Database Buffers 197132288 bytes
Redo Buffers 5804032 bytes
Database mounted.
SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
20 ADDME YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
9 RCRMAN YES NO YES
10 USERS_TBS YES NO YES
5 UNDOTBS2 YES NO YES
19 TESTTBS YES NO YES
14 TESTTB YES NO YES

TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
15 TESTDB YES NO YES

12 rows selected.

SQL> create pfile='d:\backupnew\initazardb.ora' from spfile;

File created.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Edit Pfile UNDO_TABLESPACE PARAMETER
SQL> startup pfile='d:\backupnew\initazardb.ora';
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 331350480 bytes
Database Buffers 197132288 bytes
Redo Buffers 5804032 bytes
Database mounted.
Database opened.
SQL>
SQL> create spfile from pfile;

File created.

SQL> create spfile from pfile='d:\backupnew\initazardb.ora';

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 331350480 bytes
Database Buffers 197132288 bytes
Redo Buffers 5804032 bytes
Database mounted.
Database opened.
SQL> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL>

Jan 2

2010 in review

Thanks Everyone 🙂  Happy New Year &  Again I’m ready to start my post for this year & I’m plan to post more usefull information about Oracle Technology & Linux.  It should be easy demonstration.The stats helper monkeys at WordPress.com mulled over how this blog did in 2010, and here’s a high level summary of its overall blog health:

Healthy blog!

The Blog-Health-o-Meter™ reads Wow.

Crunchy numbers

Featured image

A helper monkey made this abstract painting, inspired by your stats.

The average container ship can carry about 4,500 containers. This blog was viewed about 21,000 times in 2010. If each view were a shipping container, your blog would have filled about 5 fully loaded ships.

In 2010, there were 78 new posts, growing the total archive of this blog to 94 posts. There were 87 pictures uploaded, taking up a total of 33mb. That’s about 2 pictures per week.

The busiest day of the year was December 22nd with 591 views. The most popular post that day was Recover dropped user using Flashback database.

Where did they come from?

The top referring sites in 2010 were forums.oracle.com, oraclecommunity.net, facebook.com, jonathanlewis.wordpress.com, and google.co.in.

Some visitors came searching, mostly for ora-28365: wallet is not open, ora-01078: failure in processing system parameters, ora-00119: invalid specification for system parameter local_listener, ora-00119, and ora-28365.

Attractions in 2010

These are the posts and pages that got the most views in 2010.

1

Recover dropped user using Flashback database December 2010
4 comments

2

ORA-00119: invalid specification for system parameter LOCAL_LISTENER April 2010
4 comments

3

ORA-01078: failure in processing system parameters April 2010
2 comments

4

ORA-28365: wallet is not open March 2010

5

create duplicate database using rman May 2010
15 comments

Jan 2

11 Comments

Recover dropped tablespace using RMAN tablespace point in time recovery

This is new feature for Oracle 11g R2.Here I’m go to demonstrate How to recover dropped Tablespace using RMAN Tablespace point in time recovery.

Download PDF Document from here

Recover Dropped tablespace using RMAN TSPITR pdf

Right Click on this URL, Save Target as  & then Download it.

Step 1: Create Tablespace

SQL> conn / as sysdba

Connected.

SQL> create tablespace testtbs datafile 'd:\backup\testtbs01.dbf' size 100m; 

Tablespace created. 

SQL> create user testtbs identified by testtbs default tablespace testtbs; 

User created. 

SQL> grant connect,resource to testtbs; 

Grant succeeded. 

SQL> conn testtbs/testtbs;

Connected.

SQL> create table test(empname varchar2(20),city varchar2(20)); 

Table created.

SQL> insert into test values('azar','riyadh'); 

1 row created. 

SQL> insert into test values('jabar','chennai'); 

1 row created.

SQL> commit; 

Commit complete.

 SQL> conn testdb/testdb;

Connected. 

(Note : This table allocated for other tablespace , i just insert data for this table for check data consistent after doing TBPITR)

SQL> create table d(empname varchar2(20)); 

Table created. 

SQL> insert into d values('kareem'); 

1 row created. 

SQL> insert into d values('syed'); 

1 row created. 

SQL> commit; 

Commit complete.

Step 2:  Backup database Plus archivelog


C:\Users\mazar>set oracle_sid=azardb 

C:\Users\mazar>rman target sys/Admin123 

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 1 15:39:34 2011 

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

connected to target database: AZARDB (DBID=1652383192) 

RMAN> backup database plus archivelog;

Starting backup at 01-JAN-11

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=30 device type=DISK

Step 3: Note Current SCN


SQL> conn / as sysdba

Connected.

SQL> select current_scn from v$database; 

CURRENT_SCN

-----------

    8448197

Step 4: Drop tablespace 


SQL> drop tablespace testtbs including contents and datafiles; 

Tablespace dropped.

Step 5: I just added data for another table allocated for other tablespace due to  just check for data consistent.


SQL> conn testdb/testdb;

Connected.

SQL> insert into d values('azmi'); 

1 row created. 

SQL> commit; 

Commit complete.

Step 6: Create auxiliary & Recover tablespace using tablespace point in time recovery.

C:\Windows\system32>rman target sys/Admin123 

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 1 14:45:08 2011 

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

connected to target database: AZARDB (DBID=1652383192)

 RMAN> run{

2> recover tablespace testtbs

3> until scn 8448197

4> auxiliary destination 'D:\backupnew'; 

5> } 

executing command: SET NEWNAME 

Starting recover at 01-JAN-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=31 device type=DISK

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time 

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS2

Creating automatic instance, with SID='Bopd' 

initialization parameters used for automatic instance:

db_name=AZARDB

db_unique_name=Bopd_tspitr_AZARDB

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=D:\backupnew

log_archive_dest_1='location=D:\backupnew'

#No auxiliary parameter file used

starting up automatic instance AZARDB

Oracle instance started

Total System Global Area     292933632 bytes 

Fixed Size                     1374164 bytes

Variable Size                100665388 bytes

Database Buffers             184549376 bytes

Redo Buffers                   6344704 bytes

Automatic instance created

List of tablespaces that have been dropped from the target database:

Tablespace testtbs

contents of Memory Script:

{

# set requested point in time

set until  scn 8448197;

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

# avoid unnecessary autobackups for structural changes during TSPITR

sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';

}

executing Memory Script

executing command: SET until clause

Starting restore at 01-JAN-11

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=59 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2011_01_01\O1_MF_N_739286122_6KXZOWTT_.BKP

channel ORA_AUX_DISK_1: piece handle=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2011_01_01\O1_MF_N_739286122_6KXZOWTT_.BKP tag=TAG20110101T13152

1

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:00

output file name=D:\BACKUPNEW\AZARDB\CONTROLFILE\O1_MF_6KY50OH2_.CTL

Finished restore at 01-JAN-11

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:

{

# set requested point in time

set until  scn 8448197;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  8 to new;

set newname for clone datafile  2 to new;

set newname for clone tempfile  1 to new;

set newname for datafile  3 to

 "D:\BACKUP\TESTTBS01.DBF";

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 8, 2, 3;

switch clone datafile all;

}

executing Memory Script 

executing command: SET until clause 

executing command: SET NEWNAME 

executing command: SET NEWNAME 

executing command: SET NEWNAME 

executing command: SET NEWNAME 

executing command: SET NEWNAME 

renamed tempfile 1 to D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_TEMP_%U_.TMP in control file 

Starting restore at 01-JAN-11

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_SYSTEM_%U_.DBF

channel ORA_AUX_DISK_1: restoring datafile 00008 to D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_UNDOTBS2_%U_.DBF

channel ORA_AUX_DISK_1: restoring datafile 00002 to D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_SYSAUX_%U_.DBF

channel ORA_AUX_DISK_1: restoring datafile 00003 to D:\BACKUP\TESTTBS01.DBF

channel ORA_AUX_DISK_1: reading from backup piece C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_01\O1_MF_NNNDF_TAG20110101T131020_6KXZDFVB_.

BKP

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:06

Finished restore at 01-JAN-11

datafile 1 switched to datafile copy

input datafile copy RECID=388 STAMP=739291881 file name=D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_SYSTEM_6KY53Y6C_.DBF

datafile 8 switched to datafile copy

input datafile copy RECID=389 STAMP=739291881 file name=D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_UNDOTBS2_6KY53YJT_.DBF

datafile 2 switched to datafile copy

input datafile copy RECID=390 STAMP=739291881 file name=D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_SYSAUX_6KY53YG4_.DBF

contents of Memory Script:

{

# set requested point in time

set until  scn 8448197;

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  8 online";

sql clone "alter database datafile  2 online";

sql clone "alter database datafile  3 online";

# recover and open resetlogs

recover clone database tablespace  "TESTTBS", "SYSTEM", "UNDOTBS2", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script 

executing command: SET until clause 

sql statement: alter database datafile  1 online 

sql statement: alter database datafile  8 online 

sql statement: alter database datafile  2 online 

sql statement: alter database datafile  3 online 

Starting recover at 01-JAN-11

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 17 is already on disk as file C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\ARCHIVELOG\2011_01_01\O1_MF_1_17_6KY53B3J_

.ARC

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=16

channel ORA_AUX_DISK_1: reading from backup piece C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_01\O1_MF_ANNNN_TAG20110101T131514_6KXZOPGN_.

BKP

channel ORA_AUX_DISK_1: piece handle=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_01\O1_MF_ANNNN_TAG20110101T131514_6KXZOPGN_.BKP tag=TAG20

110101T131514

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=D:\BACKUPNEW\ARC0000000016_0738861267.0001 thread=1 sequence=16

channel clone_default: deleting archived log(s)

archived log file name=D:\BACKUPNEW\ARC0000000016_0738861267.0001 RECID=777 STAMP=739291886

archived log file name=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\ARCHIVELOG\2011_01_01\O1_MF_1_17_6KY53B3J_.ARC thread=1 sequence=17

media recovery complete, elapsed time: 00:00:06

Finished recover at 01-JAN-11

database opened

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace  TESTTBS read only';

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

D:\backupnew''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

D:\backupnew''";

}

executing Memory Script

sql statement: alter tablespace  TESTTBS read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''D:\backupnew''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''D:\backupnew''

Performing export of metadata...

   EXPDP> Starting "SYS"."TSPITR_EXP_Bopd":

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

   EXPDP> Master table "SYS"."TSPITR_EXP_Bopd" successfully loaded/unloaded

   EXPDP> ******************************************************************************

   EXPDP> Dump file set for SYS.TSPITR_EXP_Bopd is:

   EXPDP>   D:\BACKUPNEW\TSPITR_BOPD_37645.DMP

   EXPDP> ******************************************************************************

   EXPDP> Datafiles required for transportable tablespace TESTTBS:

   EXPDP>   D:\BACKUP\TESTTBS01.DBF

   EXPDP> Job "SYS"."TSPITR_EXP_Bopd" successfully completed at 15:12:24

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone immediate

}

executing Memory Script

database closed

database dismounted

Oracle instance shut down

Performing import of metadata...

   IMPDP> Master table "SYS"."TSPITR_IMP_Bopd" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_Bopd":

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

   IMPDP> Job "SYS"."TSPITR_IMP_Bopd" successfully completed at 15:16:47

Import completed

contents of Memory Script:

{

# make read write and offline the imported tablespaces

sql 'alter tablespace  TESTTBS read write';

sql 'alter tablespace  TESTTBS offline';

# enable autobackups after TSPITR is finished

sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';

}

executing Memory Script 

sql statement: alter tablespace  TESTTBS read write 

sql statement: alter tablespace  TESTTBS offline 

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end; 

Removing automatic instance

Automatic instance removed

auxiliary instance file D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_TEMP_6KY5C761_.TMP deleted

auxiliary instance file D:\BACKUPNEW\AZARDB\ONLINELOG\O1_MF_4_6KY5BT82_.LOG deleted

auxiliary instance file D:\BACKUPNEW\AZARDB\ONLINELOG\O1_MF_3_6KY5BQSZ_.LOG deleted

auxiliary instance file D:\BACKUPNEW\AZARDB\ONLINELOG\O1_MF_2_6KY5BOYS_.LOG deleted

auxiliary instance file D:\BACKUPNEW\AZARDB\ONLINELOG\O1_MF_1_6KY5BMV3_.LOG deleted

auxiliary instance file D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_SYSAUX_6KY53YG4_.DBF deleted

auxiliary instance file D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_UNDOTBS2_6KY53YJT_.DBF deleted

auxiliary instance file D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_SYSTEM_6KY53Y6C_.DBF deleted

auxiliary instance file D:\BACKUPNEW\AZARDB\CONTROLFILE\O1_MF_6KY50OH2_.CTL deleted

Finished recover at 01-JAN-11

RMAN>

Step 7: Check tablespace status


SQL> select status,tablespace_name from dba_tablespaces where tablespace_name like 'TESTTBS%'; 

STATUS    TABLESPACE_NAME

--------- ------------------------------

OFFLINE   TESTTBS 

11 rows selected. 

Alter tablespace online

SQL> alter tablespace testtbs online; 

Tablespace altered.

Step 8: Check table 

SQL> conn testtbs/testtbs;

Connected.

SQL> select * from tab; 

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

TEST                           TABLE 

SQL> select * from test; 

EMPNAME              CITY

-------------------- --------------------

azar                 riyadh

jabar                chennai

SQL> 

And also I'm go to check other table for data consistent. 

SQL> conn testdb/testdb;

Connected.

SQL> select * from d; 

EMPNAME

--------------------

azmi

kareem

syed

Now successfully recovered dropped tablespace using RMAN point in time recovery.

Jan 1

1 Comment

ORA-19852: error creating services for auxiliary instance

During Auxiliary instance creation , the following error appeared.

RMAN> run{
2> set newname for datafile 12 to ‘D:\backup\%f’;
3> recover tablespace testtbs
4> until scn 8448197
5> auxiliary destination ‘D:\backupnew’;
6> }

executing command: SET NEWNAME

Starting recover at 01-JAN-11
using channel ORA_DISK_1
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS2

Creating automatic instance, with SID=’CBka’
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/01/2011 14:43:56
ORA-19852: error creating services for auxiliary instance CBka (error 0)
ORA-27302: failure occurred at:
ORA-27303: additional information: failed to start instance

 

You need to run following command in sql prompt.

SQL> exec dbms_backup_restore.manageauxinstance (‘TSPITR’,1);

PL/SQL procedure successfully completed.

Now you can try to re-exeucte , It should be work. 🙂