Skip to content

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. 🙂

 

Dec 29

Happy New Year 2011

Dec 19

Can we use Flashback database If noarchivelog exists

Can we use flashback database log after switching from archivelog mode to noarchivelog mode and back to archivelog mode?

According to  this below document reference

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#BRADV583

 

We cannot, because when we turn off flashback database, you lose the flashback logs unless they are guaranteed

If they guaranteed, We can’t alter no archivelog mode

Let us see some example :

Step 1: Ensure Flashback ON

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Step 2:

SQL> conn flashown/flashown;
Connected.
SQL> create table employee as select * from scott.emp;

Table created.

SQL> update employee set sal=sal+50;

14 rows updated.

SQL> commit;

Commit complete.

SQL> create restore point azar;

Restore point created.

SQL> update employee set sal=sal+50;

14 rows updated.

SQL> commit;

SQL>select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
19-DEC-10 09.53.55.726000 AM +03:00

Step 3: alter noarchivelog mode

SQL> shutdown immediate
ORACLE instance shut down.
SQL> startup mount
Database mounted.

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback database is enabled
SQL> alter database flashback off;

Database altered.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> update flashown.employee set sal=sal+50;

14 rows updated.

SQL> commit;

Commit complete.

Step 4: Again back to archivelog mode & Recover flashback database But we can’t.

SQL> shutdown immediate
ORACLE instance shut down.
SQL> startup mount
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> flashback database to restore point azar;
flashback database to restore point azar
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.
SQL> flashback database to timestamp to_date('19-DEC-10 09.54.00','DD-MM-YY HH24:MI:SS');
flashback database to timestamp to_date('19-DEC-10 09.54.00','DD-MM-YY HH24:MI:SS')
                                                             *
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.

Step 5: Create Guarantee Restore point

SQL> show parameter db_flashback_retention_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SQL> create restore point azardb guarantee flashback database;

Restore point created.

SQL> alter database open;

Database altered.

SQL> select sal from flashown.employee;

       SAL
----------
       955
      1755
      1405
      3130
      1405
      3005
      2605
      3255
      5155
      1655
      1255

       SAL
----------
      1105
      3155
      1455

14 rows selected.

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
19-DEC-10 10.11.43.716000 AM +03:00

SQL> update flashown.employee set sal=sal-5;

14 rows updated.

SQL> commit;

Commit complete.

SQL> shutdown immediate
ORACLE instance shut down.
SQL> startup mount
Database mounted.
SQL> alter database flashback off;

Database altered.

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38781: cannot disable media recovery - have guaranteed restore points
SQL> alter database flashback on;

Database altered.

SQL> flashback database to timestamp to_date('19-DEC-10 10.11.44','DD-MM-YY HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select sal from flashown.employee;

       SAL
----------
       955
      1755
      1405
      3130
      1405
      3005
      2605
      3255
      5155
      1655
      1255

       SAL
----------
      1105
      3155
      1455

14 rows selected.
SQL>

So we can’t alter noarchivelog mode While Guarantee restore point exists in DB.

And the If flashback database off,  we can’t recover flashback database using flashback logs.

Dec 18

4 Comments

Flashback Data Archive

Flashback Data archive is one of the Oracle 11g feature (Oracle Total Recall).

A Flashback Data Archive (Oracle Total Recall) provides the ability to track and store all transactional changes to a table over its lifetime. It is no longer necessary to build this intelligence into your application. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.

 

How to Enable Flashback Data Archive;

Step 1:  Conn AS sys user & create tablespace

SQL> select flashback_on from v$database;

FLASHBACK_ON

SQL> create tablespace flashbacktbs datafile 'D:\backup\flbtbs01.dbf' size 50m autoextend on;

Tablespace created.
------------------
YES

Step 2: Create Flashback Data Archive with retention period

SQL> create flashback archive fla1 tablespace flashbacktbs retention 1 year;

Flashback archive created.

Step 3:

SQL> conn scott/tiger;
Connected.
SQL> alter table emp flashback archive fla1;
alter table emp flashback archive fla1
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive

So Need to give privilege for scott user

Step 4: Grant privilege

SQL> conn sys/Admin123 as sysdba
Connected.

SQL> grant flashback archive on fla1 to scott;

Grant succeeded.

Step 5: 

SQL> conn scott/tiger;
Connected.
SQL> alter table emp flashback archive fla1;

Table altered.

Step 6:

SQL> select empno,ename,sal from emp where empno=7788;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7788 SCOTT            3000

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
18-DEC-10 10.53.41.521000 AM +03:00

SQL>  update emp set sal=sal+100 where empno=7788;

1 row updated.

SQL>  select sal from emp as of timestamp to_timestamp('18-DEC-10 10:53:41','DD-MM-YY HH24:MI:SS')  WHERE EMPNO=7788;

       SAL
----------
      3000

SQL> select empno,ename,sal from emp where empno=7788;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7788 SCOTT            3100

Some Other operations:

Modify Retention Period:

SQL>  alter flashback archive FLA1 modify retention 2 year;

Flashback archive altered.

Purge all data

SQL> alter flashback archive FLA1 purge all;

Flashback archive altered.

Disable Falshback data archive for table

SQL> alter table scott.emp no flashback archive;

Table altered.

ADD/MODIFY/REMOVE tablespace

SQL> alter flashback archive fla1 add tablespace testdb;

Flashback archive altered.

Ref :

https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#BJFIEEII

 

Dec 17

5 Comments

Duplicate database from ASM to non ASM

Download PDF Document from here


Duplicate Database from ASM to Non pdf

Right click on thir URL, Save Target as & then Download it.

Step 1:

Determine how much disk space will be required.

SQL> select name from v$database;

NAME
---------
AZARDB

Calculate total space for all datafiles within database

SQL> select DF.TOTAL/1048576 "DataFile Size Mb",LOG.TOTAL/1048576 "Redo Log Size Mb",CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,(select sum(a.bytes) TOTAL from dba_data_files a) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL ;

DataFile Size Mb Redo Log Size Mb
---------------- ----------------
Control File Size Mb Total Size Mb
-------------------- -------------
1310              150
18.59375    1478.59375

Calculate space for list of datafiles within primary database

SQL> select DF.TOTAL/1048576 "DataFile Size Mb",
LOG.TOTAL/1048576 "Redo Log Size Mb",
CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a where tablespace_name in ('SYSTEM','SYSAUX','UNDO','USERS')) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL ;

DataFile Size Mb Redo Log Size Mb
---------------- ----------------
Control File Size Mb Total Size Mb
-------------------- -------------
1165              150
18.59375    1333.59375

Step 2: backup Source database

RMAN> run{
2> allocate channel d1 type disk;
3> backup format '/backups/df_t%t_s%s_p%p' database;
4> sql 'alter system archive log current';
5> backup format '/backups/al_t%t_s%s_p%p' archivelog all;
6> release channel d1;
7> }

released channel: ORA_DISK_1
allocated channel: d1
channel d1: SID=44 device type=DISK

Starting backup at 17-DEC-10
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/azardb/datafile/system.256.736179685
input datafile file number=00002 name=+DATA/azardb/datafile/sysaux.257.736179685
input datafile file number=00005 name=+DATA/azardb/datafile/example.269.736179971
input datafile file number=00003 name=+DATA/azardb/datafile/undotbs1.258.736179685
input datafile file number=00004 name=+SPTEST/azardb/datafile/users.256.737964169
channel d1: starting piece 1 at 17-DEC-10
channel d1: finished piece 1 at 17-DEC-10
piece handle=/backups/df_t737969795_s6_p1 tag=TAG20101217T073635 comment=NONE
channel d1: backup set complete, elapsed time: 00:02:27
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel d1: starting piece 1 at 17-DEC-10
channel d1: finished piece 1 at 17-DEC-10
piece handle=/backups/df_t737969943_s7_p1 tag=TAG20101217T073635 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-DEC-10

sql statement: alter system archive log current

Starting backup at 17-DEC-10
current log archived
channel d1: starting archived log backup set
channel d1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=736268182
input archived log thread=1 sequence=4 RECID=2 STAMP=736345263
input archived log thread=1 sequence=5 RECID=3 STAMP=736438711
input archived log thread=1 sequence=6 RECID=4 STAMP=737551583
input archived log thread=1 sequence=7 RECID=5 STAMP=737880523
input archived log thread=1 sequence=8 RECID=6 STAMP=737897929
input archived log thread=1 sequence=9 RECID=7 STAMP=737962069
input archived log thread=1 sequence=10 RECID=8 STAMP=737969217
input archived log thread=1 sequence=11 RECID=9 STAMP=737969220
input archived log thread=1 sequence=12 RECID=10 STAMP=737969948
input archived log thread=1 sequence=13 RECID=11 STAMP=737969948
channel d1: starting piece 1 at 17-DEC-10
channel d1: finished piece 1 at 17-DEC-10
piece handle=/backups/al_t737969948_s8_p1 tag=TAG20101217T073908 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:15
Finished backup at 17-DEC-10

released channel: d1

RMAN>

Step 3: Create pfile & edit some required parameters

SQL> create pfile='/backups/initAUX.ora' from spfile;

File created.

SQL>

Edit& Save

*.audit_file_dest =/u01/app/oracle/AUX/adump
*.core_dump_dest =/u01/app/oracle/AUX/cdump
*.db_name ="AUX"
*.instance_name =AUX
*.control_files =/u01/app/oracle/AUX/control01.ctl
*.db_file_name_convert =("+DATA","/u01/app/oracle/AUX","+SPTEST","/u01/app/oracle/AUX")
*.log_file_name_convert =("+DATA","/u01/app/oracle/AUX")
*.undo_management =AUTO
*.undo_retention =10800
*.undo_tablespace='UNDOTBS1'
*.db_block_size=8192
*.compatible='11.2.0.0.0'

Step 4: startup nomount for AUX database

[oracle@localhost ~]$ export ORACLE_SID=AUX
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 17 09:14:09 2010

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, OLAP, Data Mining and Real Application Testing options

SQL> startup nomount pfile='/backups/initAUX.ora';
ORACLE instance started.

Total System Global Area  146472960 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
SQL>

Step 5:Create duplicate database from here

[oracle@localhost ~]$ export ORACLE_SID=AUX
[oracle@localhost ~]$ rman target sys/Admin123@azardb auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 17 09:18:34 2010

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

connected to target database: AZARDB (DBID=1655311948)
connected to auxiliary database: AUX (not mounted)

RMAN> run{
2> allocate auxiliary channel C1 device type disk;
3> duplicate target database to AUX;
4> }

using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=20 device type=DISK

Starting Duplicate Db at 17-DEC-10

contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     146472960 bytes

Fixed Size                     1335080 bytes
Variable Size                 92274904 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK

contents of Memory Script:
{
sql clone "alter system set  db_name =
''AZARDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set  db_unique_name =
''AUX'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''AZARDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     146472960 bytes

Fixed Size                     1335080 bytes
Variable Size                 92274904 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK

Starting restore at 17-DEC-10

channel C1: starting datafile backup set restore
channel C1: restoring control file
channel C1: reading from backup piece /backups/df_t737969943_s7_p1
channel C1: piece handle=/backups/df_t737969943_s7_p1 tag=TAG20101217T073635
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/AUX/control01.ctl
Finished restore at 17-DEC-10

database mounted

contents of Memory Script:
{
set until scn  971599;
set newname for datafile  1 to
"/u01/app/oracle/AUX/azardb/datafile/system.256.736179685";
set newname for datafile  2 to
"/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685";
set newname for datafile  3 to
"/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685";
set newname for datafile  4 to
"/u01/app/oracle/AUX/azardb/datafile/users.256.737964169";
set newname for datafile  5 to
"/u01/app/oracle/AUX/azardb/datafile/example.269.736179971";
restore
clone database
;
}
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

Starting restore at 17-DEC-10

channel C1: starting datafile backup set restore
channel C1: specifying datafile(s) to restore from backup set
channel C1: restoring datafile 00001 to /u01/app/oracle/AUX/azardb/datafile/system.256.736179685
channel C1: restoring datafile 00002 to /u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
channel C1: restoring datafile 00003 to /u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
channel C1: restoring datafile 00004 to /u01/app/oracle/AUX/azardb/datafile/users.256.737964169
channel C1: restoring datafile 00005 to /u01/app/oracle/AUX/azardb/datafile/example.269.736179971
channel C1: reading from backup piece /backups/df_t737969795_s6_p1
channel C1: piece handle=/backups/df_t737969795_s6_p1 tag=TAG20101217T073635
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:01:27
Finished restore at 17-DEC-10

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/system.256.736179685
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971

contents of Memory Script:
{
set until scn  971599;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 17-DEC-10

starting media recovery

archived log for thread 1 with sequence 12 is already on disk as file +DATA/azardb/archivelog/2010_12_17/thread_1_seq_12.279.737969947
archived log for thread 1 with sequence 13 is already on disk as file +DATA/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949
archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_12.279.737969947 thread=1 sequence=12
archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949 thread=1 sequence=13
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-DEC-10

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set  db_name =
''AUX'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset  db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     146472960 bytes

Fixed Size                     1335080 bytes
Variable Size                 92274904 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK

sql statement: alter system set  db_name =  ''AUX'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     146472960 bytes

Fixed Size                     1335080 bytes
Variable Size                 92274904 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES     16
MAXLOGMEMBERS      3
MAXDATAFILES      100
MAXINSTANCES     8
MAXLOGHISTORY      292
LOGFILE
GROUP  1 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_1.262.736179919', '/u01/app/oracle/AUX/azardb/onlinelog/group_1.263.736179919' ) SIZE 50 M  REUSE,
GROUP  2 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_2.264.736179921', '/u01/app/oracle/AUX/azardb/onlinelog/group_2.265.736179925' ) SIZE 50 M  REUSE,
GROUP  3 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_3.266.736179927', '/u01/app/oracle/AUX/azardb/onlinelog/group_3.267.736179929' ) SIZE 50 M  REUSE
DATAFILE
'/u01/app/oracle/AUX/azardb/datafile/system.256.736179685'
CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
set newname for tempfile  1 to
"/u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955";
switch clone tempfile all;
catalog clone datafilecopy  "/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685",
"/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685",
"/u01/app/oracle/AUX/azardb/datafile/users.256.737964169",
"/u01/app/oracle/AUX/azardb/datafile/example.269.736179971";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955 in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 RECID=1 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 RECID=2 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169 RECID=3 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971 RECID=4 STAMP=737976113

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=737976113 file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971

[oracle@localhost ~]$ export ORACLE_SID=AUX
[oracle@localhost ~]$ rman target sys/Admin123@azardb auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 17 09:18:34 2010

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

connected to target database: AZARDB (DBID=1655311948)
connected to auxiliary database: AUX (not mounted)

RMAN> run{
2> allocate auxiliary channel C1 device type disk;
3> duplicate target database to AUX;
4> }

using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=20 device type=DISK

Starting Duplicate Db at 17-DEC-10

contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     146472960 bytes

Fixed Size                     1335080 bytes
Variable Size                 92274904 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK

contents of Memory Script:
{
sql clone "alter system set  db_name =
''AZARDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set  db_unique_name =
''AUX'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''AZARDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     146472960 bytes

Fixed Size                     1335080 bytes
Variable Size                 92274904 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK

Starting restore at 17-DEC-10

channel C1: starting datafile backup set restore
channel C1: restoring control file
channel C1: reading from backup piece /backups/df_t737969943_s7_p1
channel C1: piece handle=/backups/df_t737969943_s7_p1 tag=TAG20101217T073635
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/AUX/control01.ctl
Finished restore at 17-DEC-10

database mounted

contents of Memory Script:
{
set until scn  971599;
set newname for datafile  1 to
"/u01/app/oracle/AUX/azardb/datafile/system.256.736179685";
set newname for datafile  2 to
"/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685";
set newname for datafile  3 to
"/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685";
set newname for datafile  4 to
"/u01/app/oracle/AUX/azardb/datafile/users.256.737964169";
set newname for datafile  5 to
"/u01/app/oracle/AUX/azardb/datafile/example.269.736179971";
restore
clone database
;
}
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

Starting restore at 17-DEC-10

channel C1: starting datafile backup set restore
channel C1: specifying datafile(s) to restore from backup set
channel C1: restoring datafile 00001 to /u01/app/oracle/AUX/azardb/datafile/system.256.736179685
channel C1: restoring datafile 00002 to /u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
channel C1: restoring datafile 00003 to /u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
channel C1: restoring datafile 00004 to /u01/app/oracle/AUX/azardb/datafile/users.256.737964169
channel C1: restoring datafile 00005 to /u01/app/oracle/AUX/azardb/datafile/example.269.736179971
channel C1: reading from backup piece /backups/df_t737969795_s6_p1
channel C1: piece handle=/backups/df_t737969795_s6_p1 tag=TAG20101217T073635
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:01:27
Finished restore at 17-DEC-10

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/system.256.736179685
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971

contents of Memory Script:
{
set until scn  971599;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 17-DEC-10

starting media recovery

archived log for thread 1 with sequence 12 is already on disk as file +DATA/azardb/archivelog/2010_12_17/thread_1_seq_12.279.737969947
archived log for thread 1 with sequence 13 is already on disk as file +DATA/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949
archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_12.279.737969947 thread=1 sequence=12
archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949 thread=1 sequence=13
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-DEC-10

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set  db_name =
''AUX'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset  db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     146472960 bytes

Fixed Size                     1335080 bytes
Variable Size                 92274904 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK

sql statement: alter system set  db_name =  ''AUX'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     146472960 bytes

Fixed Size                     1335080 bytes
Variable Size                 92274904 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES     16
MAXLOGMEMBERS      3
MAXDATAFILES      100
MAXINSTANCES     8
MAXLOGHISTORY      292
LOGFILE
GROUP  1 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_1.262.736179919', '/u01/app/oracle/AUX/azardb/onlinelog/group_1.263.736179919' ) SIZE 50 M  REUSE,
GROUP  2 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_2.264.736179921', '/u01/app/oracle/AUX/azardb/onlinelog/group_2.265.736179925' ) SIZE 50 M  REUSE,
GROUP  3 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_3.266.736179927', '/u01/app/oracle/AUX/azardb/onlinelog/group_3.267.736179929' ) SIZE 50 M  REUSE
DATAFILE
'/u01/app/oracle/AUX/azardb/datafile/system.256.736179685'
CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
set newname for tempfile  1 to
"/u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955";
switch clone tempfile all;
catalog clone datafilecopy  "/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685",
"/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685",
"/u01/app/oracle/AUX/azardb/datafile/users.256.737964169",
"/u01/app/oracle/AUX/azardb/datafile/example.269.736179971";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955 in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 RECID=1 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 RECID=2 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169 RECID=3 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971 RECID=4 STAMP=737976113

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=737976113 file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened

Duplicate database was successfully created

Step 6: Check Duplicate Database

[oracle@localhost ~]$ export ORACLE_SID=AUX
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 17 09:25:45 2010

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

Enter user-name: / as sysdba

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

SQL> select name from v$database;

NAME
---------
AUX

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/AUX/azardb/datafile/system.256.736179685
/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
/u01/app/oracle/AUX/azardb/datafile/example.269.736179971

SQL>

Possible Errors:

You can get some errors , cann’t restore datafile & onlineredo log files, in this case you need to create directory ,It should be indicate.

azardb/datafile

azardb/onlinelog

Dec 16

1 Comment

How to move ASM spfile from one diskgroup to another diskgroup

Here , I just showed you ,How to move ASM spfile from one diskgroup to another diskgroup.

before I have currently using one diskgroup, so i need to create another diskgroup using ASMCA, I just formated disk using fdisk command.

After created new diskgroup, follow the steps

[root@localhost ~]# su - oracle
[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
[oracle@localhost ~]$ export PATH=/u01/app/oracle/product/11.2.0/grid/bin
[oracle@localhost ~]$ export ORACLE_SID=+ASM
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 16 11:46:32 2010

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

Enter user-name: sys/Admin123 as sysasm

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/asm/asmparameterfile/reg
istry.253.736177541
SQL> create pfile='/install/initASM.ora' from spfile;

File created.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/asm/asmparameterfile/reg
istry.253.736177541
SQL> create spfile='+SPTEST' from pfile='/install/initASM.ora';

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option

[oracle@localhost ~]$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'localhost'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'localhost'
CRS-2673: Attempting to stop 'ora.SPTEST.dg' on 'localhost'
CRS-2673: Attempting to stop 'ora.azardb.db' on 'localhost'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'localhost' succeeded
CRS-2677: Stop of 'ora.SPTEST.dg' on 'localhost' succeeded
CRS-2677: Stop of 'ora.azardb.db' on 'localhost' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'localhost'
CRS-2677: Stop of 'ora.DATA.dg' on 'localhost' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'localhost'
CRS-2677: Stop of 'ora.asm' on 'localhost' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'localhost'
CRS-2677: Stop of 'ora.cssd' on 'localhost' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'localhost'
CRS-2677: Stop of 'ora.diskmon' on 'localhost' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'localhost' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[oracle@localhost ~]$

[oracle@localhost ~]$ crsctl start has
CRS-4123: Oracle High Availability Services has been started.

[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
[oracle@localhost ~]$ export PATH=/u01/app/oracle/product/11.2.0/grid/bin
[oracle@localhost ~]$ export ORACLE_SID=+ASM
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 16 11:57:30 2010

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

Enter user-name: sys/Admin123 as sysasm

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +SPTEST/asm/asmparameterfile/r
egistry.253.737898615
SQL> select name,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
SPTEST                         MOUNTED
DATA                           MOUNTED

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option

you can verify using asmcmd command

[oracle@localhost ~]$ asmcmd spget
+SPTEST/asm/asmparameterfile/registry.253.737898615

just remove old spfile from here

[[oracle@localhost ~]$ asmcmd rm +DATA/asm/asmparameterfile/registry.253.736177541

[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
[oracle@localhost ~]$ export PATH=/u01/app/oracle/product/11.2.0/grid/bin
[oracle@localhost ~]$ export ORACLE_SID=+ASM
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 16 12:00:08 2010

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

Enter user-name: sys/Admin123 as sysasm

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +SPTEST/asm/asmparameterfile/r
egistry.253.737898615
SQL>

 

Dec 11

Datapump REUSE_DUMPFILES parameter

REUSE_DUMPFILES parameter is using for overwriting preexisting dump file.

It  default parameter is NO.

Data Pump Export will return an error if you specify a dump file name that already exists. The REUSE_DUMPFILES parameter allows you to override that behavior and reuse a dump file name.

This is compatabile for Oracle 11g Version, Not in Oracle 10g version.

see below Example :


C:\Users\mazar>expdp scott/tiger@azardb dumpfile=reusedump.dmp directory=data_pump_dir

Export: Release 11.2.0.1.0 - Production on Sat Dec 11 12:17:12 2010

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_SCHEMA_01":  scott/********@azardb dumpfile=reusedump.dmp directory=data_pump_dir
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.945 KB       4 rows
. . exported "SCOTT"."EMP"                               8.578 KB      14 rows
. . exported "SCOTT"."MYTEST"                            5.429 KB       1 rows
. . exported "SCOTT"."SALGRADE"                          5.875 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\REUSEDUMP.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:18:05

C:\Users\mazar>expdp scott/tiger@azardb dumpfile=reusedump.dmp directory=data_pump_dir

Export: Release 11.2.0.1.0 - Production on Sat Dec 11 12:18:17 2010

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
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "C:\app\oracle\mazar\admin\azardb\dpdump\reusedump.dmp"
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

 




C:\Users\mazar>expdp scott/tiger@azardb dumpfile=reusedump.dmp directory=data_pump_dir reuse_dumpfiles=y

Export: Release 11.2.0.1.0 - Production on Sat Dec 11 12:18:31 2010

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_SCHEMA_01":  scott/********@azardb dumpfile=reusedump.dmp directory=data_pump_dir reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.945 KB       4 rows
. . exported "SCOTT"."EMP"                               8.578 KB      14 rows
. . exported "SCOTT"."MYTEST"                            5.429 KB       1 rows
. . exported "SCOTT"."SALGRADE"                          5.875 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\REUSEDUMP.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:19:10

Ref :

http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_export.htm

← Older Entries
Newer Entries →