Skip to content

Archive for

Recovering After loss of all members of the CURRENT redo log group

 

All of the members of a current online redo log group in your database have experience media failure.

You lose all members of a current online redo log group. Here are possible reasons,

  • Perform an incomplete recovery up to the last good SCN.
  • If flashback is enabled, flash your database back to the last good SCN.
  • If you’re using Oracle data guard, fail over to your physical or logical standby database.

SQL> select group#,status,archived,thread#,sequence# from v$log;

GROUP# STATUS           ARC    THREAD#  SEQUENCE#
———- —————- — ———- ———-
1 CURRENT          NO           1         17
2 INACTIVE         YES          1         15
3 INACTIVE         YES          1         16

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

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo01.log’
ORA-00312: online log 1 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo01a.log’

SQL> select group#,status,archived,thread#,sequence# from v$log;

GROUP# STATUS           ARC    THREAD#  SEQUENCE#
———- —————- — ———- ———-
1 CURRENT          NO           1         17
3 INACTIVE         YES          1         16
2 INACTIVE         YES          1         15

SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-01109: database not open

First determine the last good SCN by querying the first_change# column from v$log, you’re missing only the current online redo logs, therfore you can perform an incomplete recovery up tobut not including,

SQL> select group#,status,archived,thread#,sequence#,first_change# from v$log;

GROUP# STATUS           ARC    THREAD#  SEQUENCE# FIRST_CHANGE#
———- —————- — ———- ———- ————-
1 CURRENT          NO           1         17        574131
3 INACTIVE         YES          1         16        574125
2 INACTIVE         YES          1         15        574103

SQL>

In this case, you can restore and recover up to but not including, 574131

Operation :-

Losing  all members of your current online redo log group is arguably the worst thing taht can happen to your database. if you experience media failure with all members of the current online redo group, then you will lose any transactions contained in those logs. in this case, you will have to perform incomplete recovery before you can open your database.

[oracle@localhost root]$ rlwrap rman target sys/azar

Recovery Manager: Release 10.2.0.1.0 – Production on Mon May 31 10:51:10 2010

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

connected to target database: TEST (DBID=2015564632, not open)

RMAN> restore database until scn 574131;

Starting restore at 31-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=150 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/system01.dbf
restoring datafile 00002 to /home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/undotbs01.dbf
restoring datafile 00003 to /home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/sysaux01.dbf
restoring datafile 00004 to /home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/backupset/2010_05_25/o1_mf_nnndf_TAG20100525T103307_5zpz9nft_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/backupset/2010_05_25/o1_mf_nnndf_TAG20100525T103307_5zpz9nft_.bkp tag=TAG20100525T103307
channel ORA_DISK_1: restore complete, elapsed time: 00:01:37
Finished restore at 31-MAY-10

RMAN> recover database until scn 574131;

Starting recover at 31-MAY-10
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 7 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_25/o1_mf_1_7_5zpzh95q_.arc
archive log thread 1 sequence 8 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_8_606k8n7x_.arc
archive log thread 1 sequence 9 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_9_606lkvgy_.arc
archive log thread 1 sequence 10 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_10_606mfskg_.arc
archive log thread 1 sequence 11 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_11_606ndqvj_.arc
archive log thread 1 sequence 12 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_12_606szwxo_.arc
archive log thread 1 sequence 13 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_13_606t3zs8_.arc
archive log thread 1 sequence 14 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_14_606t777d_.arc
archive log thread 1 sequence 15 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_15_606t908r_.arc
archive log thread 1 sequence 16 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_16_606t9jd0_.arc
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_25/o1_mf_1_7_5zpzh95q_.arc thread=1 sequence=7
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_8_606k8n7x_.arc thread=1 sequence=8
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_9_606lkvgy_.arc thread=1 sequence=9
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_10_606mfskg_.arc thread=1 sequence=10
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_11_606ndqvj_.arc thread=1 sequence=11
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_12_606szwxo_.arc thread=1 sequence=12
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_13_606t3zs8_.arc thread=1 sequence=13
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_14_606t777d_.arc thread=1 sequence=14
media recovery complete, elapsed time: 00:00:06
Finished recover at 31-MAY-10

RMAN> alter database open resetlogs;

database opened.

SQL> select status from v$instance;

STATUS
————
OPEN

SQL> select group#,members,status from v$log;

GROUP#    MEMBERS STATUS
———- ———- —————-
1          2 INACTIVE
2          2 CURRENT
3          2 UNUSED

Recovering after loss of all members of the INACTIVE redo log group

You’re attempting to open your database and receive this message

ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo02.log’
ORA-00312: online log 2 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo02a.log’

The message indicates that two members of an online redo log group in your database have experienced a media failure.

To recoveer when you’ve lost all members of an inactive redo log group, perform the following steps

Verify that all members of a group have been damaged.

Verify that the log group status is INACTIVE.

Recreate the log group with the clear logfile command.

If the recreated log group has not been archived,then immediately backup up your database.

SQL> select group#,members,status from v$log;
GROUP#    MEMBERS STATUS
———- ———- —————-
1          2 CURRENT
2          2 INACTIVE
3          2 ACTIVE

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

(Here I manually deleted redolog files members of group 2)
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo02.log’
ORA-00312: online log 2 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo02a.log’

If all members of an online redo log group are damaged, you won’t be able to open your database, oracle will allow you to only mount your database.

SQL> select status from v$instance;

STATUS
————
MOUNTED

SQL> select group#,status,archived,thread#,sequence# from v$log;

GROUP# STATUS           ARC    THREAD#  SEQUENCE#
———- —————- — ———- ———-
1 CURRENT          NO           1         11
3 INACTIVE         YES          1         10
2 INACTIVE         YES          1          9

If the status is INACTIVE ,then this log group is no longer needed for crash recovery , therefore ,you can use the clear logfile command to recreate all members of a log group.

SQL> alter database clear logfile group 2;

Database altered.

If the group has not been archived, then you will need to use the clear unarchived logfile command as follows.

SQL> alter database clear unarchived logfile group 2;

Database altered.

If the cleared log group had not been previously archived, it’s critical that you immediately create a backup of your database.

SQL> alter database open;

Database altered.

SQL> select group#,status,archived,thread#,sequence# from v$log;

GROUP# STATUS           ARC    THREAD#  SEQUENCE#
———- —————- — ———- ———-
1 INACTIVE         YES          1         11
2 CURRENT          NO           1         12
3 INACTIVE         YES          1         10

SQL>

Transport the tablespace on Same OS platform

You want to transport tablespaces using RMAN backups instead of performing the transportable tablespaces operation  on the live production database.

Source Database : MYTEST

Target Database : MYDB

Transportable Tablespace : TRANSPORT

  • You can also transport tablespaces from a “live” database using an alternative transport tablespace technique explained in the Oracle manula,The disadvantages in that method is that the transportable tablespces must be put in  a read only mode, thus affecting database availablity. it not only may be time consuming to put the tablespace into read only mode but users cann’t write to those tablespaces for the duration of the tablespace transport. You don’t have any of these limitations when you use RMAN backups as the basis of your transportable tablespace operation.
  • RMAN backups to create transportable tablespaces is that your RMAN backups must be recoverable to the SCN at which you want the transportable tablespaces.
  • RMAN creates an auxiliary database instance through which it creates the transportable tablespace sets. RMAN does quites a few things in order to prepare the transportable table set.

The following example shows how to transport tablespaces on identical operating system platforms by utilizing RMAN backups.

Step 1: Make sure the tablespaces you plan to transport are self contained. To be considered self contained, the tablespaces se you want to transport
muchn’t contain references pointing outside those tablespaces, such as an index on a table that doesn’t belong to one of the tablespaces you’re transporting.

SQL> execute sys.dbms_tts.transport_set_check(‘transport’,TRUE);

PL/SQL procedure successfully completed.

Step 2: Generate the transportable tablespace set by issuing the transport tablespace command.

RMAN> transport tablespace ‘TRANSPORT’
2> tablespace destination ‘/home/oracle/oracle/transporttbs’
3> auxiliary destination ‘/home/oracle/oracle/auxdest’;

Creating automatic instance, with SID=’gjEd’

initialization parameters used for automatic instance:
db_name=MYTEST
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_MYTEST_gjEd
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/home/oracle/oracle/auxdest
control_files=/home/oracle/oracle/auxdest/cntrl_tspitr_MYTEST_gjEd.f

starting up automatic instance MYTEST

Oracle instance started

Total System Global Area     201326592 bytes

Fixed Size                     1218508 bytes
Variable Size                146802740 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2973696 bytes
Automatic instance created

contents of Memory Script:
{
# set the until clause
set until  scn 580515;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone ‘alter database mount clone database’;
# archive current online log for tspitr to a resent until time
sql ‘alter system archive log current’;
# avoid unnecessary autobackups for structural changes during TSPITR
sql ‘begin dbms_backup_restore.AutoBackupFlag(FALSE); end;’;
# resync catalog after controlfile restore
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 29-MAY-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/autobackup/2010_05_29/o1_mf_s_720285190_60216pdz_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/autobackup/2010_05_29/o1_mf_s_720285190_60216pdz_.bkp tag=TAG20100529T151310
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/home/oracle/oracle/auxdest/cntrl_tspitr_MYTEST_gjEd.f
Finished restore at 29-MAY-10

sql statement: alter database mount clone database

sql statement: alter system archive log current

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

starting full resync of recovery catalog
full resync complete
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until  scn 580515;
# set an omf destination filename for restore
set newname for clone datafile  1 to new;
# set an omf destination filename for restore
set newname for clone datafile  2 to new;
# set an omf destination filename for restore
set newname for clone datafile  3 to new;
# set an omf destination tempfile
set newname for clone tempfile  1 to new;
# set a destination filename for restore
set newname for datafile  8 to
“/home/oracle/oracle/transporttbs/transport01.dbf”;
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile  1, 2, 3, 8;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone “alter database datafile  1 online”;
#online the datafiles restored or flipped
sql clone “alter database datafile  2 online”;
#online the datafiles restored or flipped
sql clone “alter database datafile  3 online”;
#online the datafiles restored or flipped
sql clone “alter database datafile  8 online”;
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace  “TRANSPORT”, “SYSTEM”, “UNDOTBS1”, “SYSAUX” delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone “create tablespace aux_tspitr_tmp
#           datafile ”/tmp/aux_tspitr_tmp.dbf” size 500K”;
}
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 temporary file 1 to /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 29-MAY-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=39 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_undotbs1_%u_.dbf
restoring datafile 00003 to /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_sysaux_%u_.dbf
restoring datafile 00008 to /home/oracle/oracle/transporttbs/transport01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/backupset/2010_05_29/o1_mf_nnndf_TAG20100529T154405_60230pbp_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/backupset/2010_05_29/o1_mf_nnndf_TAG20100529T154405_60230pbp_.bkp tag=TAG20100529T154405
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:38
Finished restore at 29-MAY-10

datafile 1 switched to datafile copy
input datafile copy recid=9 stamp=720287348 filename=/home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_system_602372kr_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=10 stamp=720287348 filename=/home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_undotbs1_602372qx_.dbf
datafile 3 switched to datafile copy
input datafile copy recid=11 stamp=720287348 filename=/home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_sysaux_602372o0_.dbf
datafile 8 switched to datafile copy
input datafile copy recid=12 stamp=720287348 filename=/home/oracle/oracle/transporttbs/transport01.dbf

sql statement: alter database datafile  1 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  8 online

Starting recover at 29-MAY-10
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 15 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_29/o1_mf_1_15_6022vddo_.arc
archive log thread 1 sequence 16 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_29/o1_mf_1_16_60235d3p_.arc
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_29/o1_mf_1_15_6022vddo_.arc thread=1 sequence=15
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_29/o1_mf_1_16_60235d3p_.arc thread=1 sequence=16
media recovery complete, elapsed time: 00:00:03
Finished recover at 29-MAY-10

database opened

contents of Memory Script:
{
#mark read only the tablespace that will be exported
sql clone “alter tablespace TRANSPORT read only”;
# create directory for datapump export
sql clone “create or replace directory STREAMS_DIROBJ_DPDIR as ”
/home/oracle/oracle/transporttbs””;
# export the tablespaces in the recovery set
host ‘expdp userid=\”/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/home/oracle/oracle/product/10.2.0/db_1/bin/oracle\)\(ARGV0=oraclegjEd\)\(ARGS=^’\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^’\)\(ENVS=^’ORACLE_SID=gjEd^’\)\)\(CONNECT_DATA=\(SID=gjEd\)\)\) as sysdba\” transport_tablespaces=
TRANSPORT dumpfile=
dmpfile.dmp directory=
STREAMS_DIROBJ_DPDIR logfile=
explog.log’;
}
executing Memory Script

sql statement: alter tablespace TRANSPORT read only

sql statement: create or replace directory STREAMS_DIROBJ_DPDIR as ”/home/oracle/oracle/transporttbs”

Export: Release 10.2.0.1.0 – Production on Saturday, 29 May, 2010 15:49:28

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″:  userid=”/********@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/home/oracle/oracle/product/10.2.0/db_1/bin/oracle)(ARGV0=oraclegjEd)(ARGS=\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\))(ENVS=ORACLE_SID=gjEd))(CONNECT_DATA=(SID=gjEd))) AS SYSDBA” transport_tablespaces= TRANSPORT dumpfile=dmpfile.dmp directory=STREAMS_DIROBJ_DPDIR logfile=explog.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/oracle/transporttbs/dmpfile.dmp
Job “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully completed at 15:49:48

host command complete
/*
The following command may be used to import the tablespaces.
Substitute values for <logon> and <directory>.
impdp <logon> directory=<directory> dumpfile= ‘dmpfile.dmp’ transport_datafiles= /home/oracle/oracle/transporttbs/transport01.dbf
*/
————————————————————–
— Start of sample PL/SQL script for importing the tablespaces
————————————————————–
— creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS  ‘/home/oracle/oracle/transporttbs/’;
CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS  ‘/home/oracle/oracle/transporttbs’;
/* PL/SQL Script to import the exported tablespaces */
DECLARE
— the datafiles
tbs_files     dbms_streams_tablespace_adm.file_set;
cvt_files     dbms_streams_tablespace_adm.file_set;
— the dumpfile to import
dump_file     dbms_streams_tablespace_adm.file;
dp_job_name   VARCHAR2(30) := NULL;
— names of tablespaces that were imported
ts_names       dbms_streams_tablespace_adm.tablespace_set;
BEGIN
— dump file name and location
dump_file.file_name :=  ‘dmpfile.dmp’;
dump_file.directory_object := ‘STREAMS$DIROBJ$DPDIR’;
— forming list of datafiles for import
tbs_files( 1).file_name :=  ‘transport01.dbf’;
tbs_files( 1).directory_object :=  ‘STREAMS$DIROBJ$1’;
— import tablespaces
dbms_streams_tablespace_adm.attach_tablespaces(
datapump_job_name      => dp_job_name,
dump_file              => dump_file,
tablespace_files       => tbs_files,
converted_files        => cvt_files,
tablespace_names       => ts_names);
— output names of imported tablespaces
IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
FOR i IN ts_names.first .. ts_names.last LOOP
dbms_output.put_line(‘imported tablespace ‘|| ts_names(i));
END LOOP;
END IF;
END;
/
— dropping directory objects
DROP DIRECTORY STREAMS$DIROBJ$1;
DROP DIRECTORY STREAMS$DIROBJ$DPDIR;
————————————————————–
— End of sample PL/SQL script
————————————————————–

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /home/oracle/oracle/auxdest/cntrl_tspitr_MYTEST_gjEd.f deleted
auxiliary instance file /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_system_602372kr_.dbf deleted
auxiliary instance file /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_undotbs1_602372qx_.dbf deleted
auxiliary instance file /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_sysaux_602372o0_.dbf deleted
auxiliary instance file /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_temp_6023bh4x_.tmp deleted
auxiliary instance file /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/onlinelog/o1_mf_1_6023b963_.log deleted
auxiliary instance file /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/onlinelog/o1_mf_2_6023bb7g_.log deleted
auxiliary instance file /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/onlinelog/o1_mf_3_6023bc3y_.log deleted

Operation :

  • RMAN starts an auxiliary instance in nomount mode, you don’t have to specify a parameter file for this auixliary instance, RMAN automatically creates the file.
  • RMAN restores a backup of the target database control file and uses it to mount the auxiliary database.
  • Using the switch operation, RMAN restores all datafiles from the target database for the auxiliary instance. These files are restored to the location specified by the auxiliary destination clause in the transportable tablespace command.
  • RMAN also stores the files pertaining to the tablepsaces in the transportable tablspace set in the location specified by the tablespace destination parameter in the transport tablespace command.
  • Once the datafile from the target database are all restored to the auxiliary database location, RMAN performs a point in time recovery of the auxiliary instance.
  • Rman invoked the data pump Export utility to create teh export dump file containg the tablespaces in the transportable tablespace set.
  • Export dump file is places in the location specified by the tablespace destination clause of the transport tablespace command.
  • RMAN alsp generates a Data pump import script you can use to plug in the transported tablespaces into target database.
  • RMAN shuts down the auxiliary instance and automatically deletes all the files created and used during the transport tablespace process. The only files that remain are the transportable set files,
    data pump export log and sample Datapump Import the tablespaces.

Step 4:  Run import script files to target database. The script files generated when you’re done tablespace transport command used by rman , source database.

RMAN> SQL> select name from v$database;

NAME
———
MYDB

SQL> conn system/azar@mydb
Connected.
SQL> CREATE DIRECTORY STREAMS$DIROBJ$1 AS  ‘/home/oracle/oracle/transporttbs/’;

Directory created.

SQL> CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS  ‘/home/oracle/oracle/transporttbs’;

Directory created.

SQL>DECLARE
— the datafiles
tbs_files     dbms_streams_tablespace_adm.file_set;
cvt_files     dbms_streams_tablespace_adm.file_set;
— the dumpfile to import
dump_file     dbms_streams_tablespace_adm.file;
dp_job_name   VARCHAR2(30) := NULL;
— names of tablespaces that were imported
ts_names       dbms_streams_tablespace_adm.tablespace_set;
BEGIN
— dump file name and location
dump_file.file_name :=  ‘dmpfile.dmp’;
dump_file.directory_object := ‘STREAMS$DIROBJ$DPDIR’;
— forming list of datafiles for import
tbs_files( 1).file_name :=  ‘transport01.dbf’;
tbs_files( 1).directory_object :=  ‘STREAMS$DIROBJ$1’;
— import tablespaces
dbms_streams_tablespace_adm.attach_tablespaces(
datapump_job_name      => dp_job_name,
dump_file              => dump_file,
tablespace_files       => tbs_files,
converted_files        => cvt_files,
tablespace_names       => ts_names);
— output names of imported tablespaces
IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
FOR i IN ts_names.first .. ts_names.last LOOP
dbms_output.put_line(‘imported tablespace ‘|| ts_names(i));
END LOOP;
END IF;
END;
31  /

PL/SQL procedure successfully completed.

SQL> select tablespace_name from dba_data_files;

TABLESPACE_NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
USERS
TRANSPORT

SQL>

Possible Error :-

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of tranport tablespace command at 05/29/2010 15:36:00
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20202: tablespace not found in the recovery catalog

If you don’t have backup copy of your transportable tablespace in RMAN backupsets, the above error will be shown.

create duplicate database using rman

You want to use RMAN to create a duplicate database on the same server by using RMAN backups.

My Source Database Name is MYTEST

Target (duplicate) Database name is MYDB 

Step 1: Configure Listener.ora and tnsnames.ora files

tnsnames.ora file

MYTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mytest)
)
)

MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mydb)
)
)

listener.ora file

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mytest)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = mytest)
)
(SID_DESC =
(GLOBAL_DBNAME= mydb)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = mydb)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)

Step 2: create pfile for target database from source database

SQL> create pfile from spfile;

File created.

Step 3: modified parameter file for target database (duplicate) here log_file_name_convert and db_file_name_convert and control file location is important, further your choice.

*.audit_file_dest=’/home/oracle/oracle/product/10.2.0/db_1/admin/mydb/adump’
*.compatible=’10.2.0.1.0′
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’mydb’
*.control_files=’/home/oracle/oracle/product/10.2.0/oradata/mydb/control01.ctl’,’/home/oracle/oracle/product/10.2.0/oradata/mydb/control02.ctl’,’/home/oracle/oracle/product/10.2.0/oradata/mydb/control03.ctl’
*.db_file_name_convert=’/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest’,’/home/oracle/oracle/product/10.2.0/oradata/mydb’
*.log_file_name_convert=’/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest’,’/home/oracle/oracle/product/10.2.0/oradata/mydb’
*.db_recovery_file_dest=’/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mydbXDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=285212672
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′

Step 4: Create password file

[oracle@localhost ~]$orapwd file=orapwmydb password=oracle entries=5

Step 5: start your target database nomount stage

[oracle@localhost ~]$ export ORACLE_SID=mydb
[oracle@localhost ~]$ rlwrap sqlplus

SQL*Plus: Release 10.2.0.1.0 – Production on Tue May 25 18:22:09 2010

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

Enter user-name: sys/oracle@mydb as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> startup nomount pfile=/home/oracle/oracle/product/10.2.0/db_1/dbs/initmydb.ora
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
SQL> exit

Step 6: From source database

SQL> alter system switch logfile;

System altered.

SQL>

Step 7: Backup database and archivelog Source database

[oracle@localhost root]$ rlwrap rman target sys/azar@mytest

Recovery Manager: Release 10.2.0.1.0 – Production on Tue May 25 19:56:10 2010

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

connected to target database: MYTEST (DBID=2419076664)

RMAN> backup database plus archivelog;

Starting backup at 25-MAY-10
current log archived

input datafile fno=00001 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/system01.dbf
input datafile fno=00003 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/sysaux01.dbf
input datafile fno=00002 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/undotbs01.dbf
input datafile fno=00004 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAY-10
channel ORA_DISK_1: finished piece 1 at 25-MAY-10

(skipped)

Starting Control File and SPFILE Autobackup at 25-MAY-10
piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/autobackup/2010_05_25/o1_mf_s_719956316_5zr01f85_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAY-10

Step 8: Connect auxiliary database

RMAN> connect auxiliary sys/oracle@mydb;

connected to auxiliary database: MYDB (not mounted)

Step 9: Create duplicate database.

RMAN> duplicate target database to mydb;

Starting Duplicate Db at 25-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:
{
set until scn  470310;
set newname for datafile  1 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf”;
set newname for datafile  2 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf”;
set newname for datafile  3 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf”;
set newname for datafile  4 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf”;
restore
check readonly
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

Starting restore at 25-MAY-10
using channel ORA_AUX_DISK_1

skipping datafile 1; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf
skipping datafile 2; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
skipping datafile 3; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
skipping datafile 4; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 25-MAY-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “MYDB” RESETLOGS ARCHIVELOG
MAXLOGFILES     16
MAXLOGMEMBERS      3
MAXDATAFILES      100
MAXINSTANCES     8
MAXLOGHISTORY      292
LOGFILE
GROUP  1 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log’ ) SIZE 50 M  REUSE,
GROUP  2 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log’ ) SIZE 50 M  REUSE,
GROUP  3 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log’ ) SIZE 50 M  REUSE
DATAFILE
‘/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf’
CHARACTER SET WE8ISO8859P1

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

released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf

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

executing command: SET until clause

Starting recover at 25-MAY-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

starting media recovery

archive log thread 1 sequence 9 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_25/o1_mf_1_9_5zr01bl8_.arc
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_25/o1_mf_1_9_5zr01bl8_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:02
Finished recover at 25-MAY-10

contents of Memory Script:
{
shutdown clone;
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     285212672 bytes

Fixed Size                     1218992 bytes
Variable Size                 92276304 bytes
Database Buffers             188743680 bytes
Redo Buffers                   2973696 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “MYDB” RESETLOGS ARCHIVELOG
MAXLOGFILES     16
MAXLOGMEMBERS      3
MAXDATAFILES      100
MAXINSTANCES     8
MAXLOGHISTORY      292
LOGFILE
GROUP  1 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log’ ) SIZE 50 M  REUSE,
GROUP  2 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log’ ) SIZE 50 M  REUSE,
GROUP  3 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log’ ) SIZE 50 M  REUSE
DATAFILE
‘/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf’
CHARACTER SET WE8ISO8859P1

contents of Memory Script:
{
set newname for tempfile  1 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/temp01.dbf”;
switch clone tempfile all;
catalog clone datafilecopy  “/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf”;
catalog clone datafilecopy  “/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf”;
catalog clone datafilecopy  “/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /home/oracle/oracle/product/10.2.0/oradata/mydb/temp01.dbf in control file

cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf recid=1 stamp=719956593

cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf recid=2 stamp=719956593

cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf recid=3 stamp=719956593

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf

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

database opened
Finished Duplicate Db at 25-MAY-10

RMAN>

Step 10: My duplicate database succesfully created.

Enter user-name: sys/oracle@mydb as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> select status from v$instance;

STATUS
————
OPEN

SQL> select name from V$database;

NAME
———
MYDB

SQL>

ORA-19566: exceeded limit of 0 corrupt blocks

 

When i try to take backup, it will shows block corrupted errors,

RMAN> backup database;

Starting backup at 25-MAY-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/home/oracle/oracle/product/10.2.0/oradata/orcl/system01.dbf
input datafile fno=00003 name=/home/oracle/oracle/product/10.2.0/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/home/oracle/oracle/product/10.2.0/oradata/orcl/example01.dbf
input datafile fno=00006 name=/home/oracle/oracle/mytest/test01.dbf
input datafile fno=00002 name=/home/oracle/oracle/product/10.2.0/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/home/oracle/oracle/product/10.2.0/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAY-10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/25/2010 13:22:00
ORA-19566: exceeded limit of 0 corrupt blocks for file /home/oracle/oracle/product/10.2.0/oradata/orcl/system01.dbf

In this case, i have checked my alert log file and find out which block corrupted and reocvered using rman.

My alertlog file

Hex dump of (file 1, block 61441) in trace file /home/oracle/oracle/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_17556.trc
Corrupt block relative dba: 0x0040f001 (file 1, block 61441)
Completely zero block found during backing up datafile
Reread of blocknum=61441, file=/home/oracle/oracle/product/10.2.0/oradata/orcl/system01.dbf. found same corrupt data

RMAN> blockrecover datafile 1 block 61441;

Starting blockrecover at 25-MAY-10
using channel ORA_DISK_1

(skipped)

Finished blockrecover at 25-MAY-10.

TNS-01201: Listener cannot find executable

After configured the listener file, i tried to start my listener following error occurs, 

LSNRCTL> start
Starting /home/oracle/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait…  

TNSLSNR for Linux: Version 10.2.0.1.0 – Production
System parameter file is /home/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /home/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
TNS-01201: Listener cannot find executable /home/oracle/oracle/product/10.2.0/db_1/bin/orcl for SID orcl Listener failed to start. See the error message(s) above… 

 This is my listener.ora file 

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(PROGRAM= orcl)
)
(SID_DESC =
(SID_NAME = mydb)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(PROGRAM= mydb)
)
) LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)

 Tnsnames.ora file 

MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mydb)
)
) ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)

My mistake was when i configured the listener file, I couldnot change the SID_NAME where PROGRAM located in listener file. 

 (SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(PROGRAM= orcl)
)
(SID_DESC =
(SID_NAME = mydb)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(PROGRAM= mydb)

Here We should be put it SID_NAME here.
 

(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME= orcl)
)
(SID_DESC =
(SID_NAME = mydb)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME= mydb)

Now my listener started and I could be able to connect with string. 

Status: 

Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “mydb” has 1 instance(s).
Instance “mydb”, status UNKNOWN, has 1 handler(s) for this service…
Service “orcl” has 1 instance(s).
Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

Character Set migration Oracle 11g

When i tried to transport the tablespace from linux to windows, during import it shows charachter set error, so I just looked on linux database and windows database character set is different .

Linux is WE8IS08859P1

Windows is AR8ISO8859P6

So i plan to migrate the charater set of windows and change to WE8IS08859P1 character set. Here I provided step by step guide , How to migrate character set. I play with CSSCAN

Step1 : On windows Env

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
—————————— ————————————
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               AR8ISO8859P6
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

21 rows selected.

Step 2:

C:\Users\mazar>CSSCAN
Character Set Scanner v2.2 : Release 11.2.0.1.0 – Production on Tue May 18 11
:43 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Username: SYS AS SYSDBA

Password:

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

CSS-00107: Character set migration utility schema not installed

So CSSCAN utility doesnot installed on your computer, so we want to install the CSSCAN utility. Go to csminst.sql script

Step 3:

SQL> @C:\app\mazar\product\11.2.0\dbhome_1\RDBMS\ADMIN\csminst.sql
Synonym created.
View created.
View created.
View created.
View created.
Grant succeeded.
Grant succeeded.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\mazar>

Step 4:

C:\Users\mazar>CSSCAN
Character Set Scanner v2.2 : Release 11.2.0.1.0 – Production on Tue May 18 11:38
:37 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Username: SYS AS SYSDBA

Password:

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

(1)Full database, (2)User, (3)Table, (4)Column: 1 > 1

Current database character set is  AR8ISO8859P6.

Enter new database character set name: > WE8ISO8859P1

Enter array fetch buffer size: 1024000 >

Enter number of scan processes to utilize(1..64): 1 > 64

. process 51 scanning EXFSYS.RLM$EVENTSTRUCT
. process 63 scanning EXFSYS.RLM$RULESETSTCODE
. process 59 scanning EXFSYS.RLM$RULESETPRIVS
. process 27 scanning EXFSYS.RLM$INCRRRSCHACT
. process 62 scanning EXFSYS.RLM$ORDERCLSALS
. process 21 scanning EXFSYS.RLM$RSPRIMEVENTS
. process 31 scanning EXFSYS.RLM$VALIDPRIVS
. process 10 scanning EXFSYS.RLM$DMLEVTTRIGS
. process 60 scanning EXFSYS.RLM$COLLGRPBYSPEC
. process 32 scanning EXFSYS.RLM$PRIMEVTTYPEMAP
. process 5 scanning EXFSYS.RLM$EQUALSPEC

Creating Database Scan Summary Report…

Creating Individual Exception Report…

Scanner terminated successfully.

C:\Users\mazar>

Step 5:

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             301990352 bytes
Database Buffers          226492416 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.

Step 6: Run the csalter.plb script

SQL> @C:\app\mazar\product\11.2.0\dbhome_1\RDBMS\ADMIN\csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.

This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old   6:     if (UPPER(‘&conf’) <> ‘Y’) then
new   6:     if (UPPER(‘Y’) <> ‘Y’) then
Checking data validity…
begin converting system objects

PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.

SQL>

Step 7:

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             301990352 bytes
Database Buffers          226492416 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.

Step 8: Check the current character set.

SQL> select * from nls_database_parameters where parameter=’NLS_CHARACTERSET’;

PARAMETER                                  VALUE
—————————— —————————————-
NLS_CHARACTERSET               WE8ISO8859P1

SQL>

datapump reterive original data using flashback scn

Retrieve original data:-

It may be useful to export the image of a table the way it existed before a change was committed.if the database is properly configured, the database flashback query facility also integrated with expdp may be used.

SQL> conn / as sysdba
Connected.
SQL> create restore point original_emp;

Restore point created.

SQL> select scn,name from v$restore_point;

SCN
———-
NAME
——————————————————————————–
518527
ORIGINAL_EMP

SQL> conn scott/tiger;
Connected.

SQL> select sum(sal) from emp;

SUM(SAL)
———-
29025

SQL> update test set sal=sal*1.1;

14 rows updated.

Here Test table is copy of emp table.

SQL> commit;

Commit complete.

SQL> select sum(sal) from test;

SUM(SAL)
———-
31927.5

[oracle@localhost rlwrap-0.30]$ expdp scott/tiger dumpfile=original directory=test_dir tables=test flashback_scn=518527

Export: Release 10.2.0.1.0 – Production on Friday, 07 May, 2010 13:33:02

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “SCOTT”.”SYS_EXPORT_TABLE_01″:  scott/******** dumpfile=original directory=test_dir tables=test flashback_scn=518527
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”.”TEST”                              7.820 KB      14 rows
Master table “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/datapumptest/original.dmp
Job “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully completed at 13:33:11

SQL> drop table test;

Table dropped.

SQL> commit;

Commit complete.

[oracle@localhost rlwrap-0.30]$ impdp scott/tiger dumpfile=original directory=test_dir tables=test

Import: Release 10.2.0.1.0 – Production on Friday, 07 May, 2010 13:34:57

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 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/******** dumpfile=original directory=test_dir tables=test
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “SCOTT”.”TEST”                              7.820 KB      14 rows
Job “SCOTT”.”SYS_IMPORT_TABLE_01″ successfully completed at 13:35:00

[oracle@localhost rlwrap-0.30]$

SQL> select sum(sal) from test;

SUM(SAL)
———-
29025

deleting Archive Redo logs file

Deleting Archived Redo logs :

You can delete any eligible archived redo log by using the delete archivelog or backup…delete input command.

RMAN>delete archivelog all;

The delete archivelog all command deletes all archived redo logs on disk that aren’t necessary to meet the configured archived redo log
deletion policy,It’s more likely that you’d want to use the following delete command, which deletes archvied redo logs from disk based on whether
they have been first backed up to tape a certain number of times.

RMAN>delete archivelog until sequence – 999;

the backup…delete command lets you first backup an archived redo log and then delete the source archived redo log file.
In order to delete the source file, you use the additional clause delete input,

RMAN>backup device type sbt
     archivelog all
     delete all input;

The previous backup…delete command backs up all the archived redo logs and then deletes all those archvied redo logs (input files).
the delete all input clause results in the deletion of all backed up archived redo logs from all archived redo log destinations.

if you want to delete only the specified archived redo log that you’ve just backed up to a backup set, use the delete input clause instead,

RMAN>backup archiveloglike ‘/arch%’ delete input;

backup…delete command to back up archived redo logs to tape and then delete the source files.

How?

RMAN uses the configured archived redo log deletion policy to determine which of the archvied redo logs are eligible for deletion, including those archived redo logs that
are stored in the FRA, RMAN automatically deletes the eligible archived redo logs from the flash recovery area.

An  archived redo logs is considered eligible from deletion when the flash recovery area becomes full.
Suppose you have configured the following archived redo log deletion policy:

RMAN>configure archivelog deletion policy
     to backed up 2 times to device type sbt;

The above command that all archived redo log files will be eligible for deletion from all locations when those files have been backed up twice or more to tape.

Once you set the archvied redo log deletion policy shown here, a delete archivelog all or backup…delete input command will delete all archived redo logs that satisfy the requirements of your configured deletion policy,which requires that rman back up all archived redo logs to tape twice.
If you haven’t configured an archived redo log deletion policy( by default there is no policy set), RMAN will deem any archied redo log file in the flash recovery area eligible for deletion, If  following true,

  • The archived redo logs have been successfully sent to all the destinations specified by the log_archive_dest_n parameter
  • you have copied the archvied redo lgos to disk or tape at least once or the archived redo logs are obsolete per hour configured retention policy.

Use the configure archivelog deletion policy command to specify your own archive redo log deletion critieria of leaving the deletion timing to RMAN.

Once you configure an archived redo log deletion policy,it applies to all archived redo log locations, including FRA,if you’ve configured one.

RMAN stores the archived redo logs as long as possible in the FRA. When the flash recovery area is under space pressure, RMAN tries to ensure that any flashback retention time you’ve set is being satisfied before automatically deleting the archvied redo logs.

RMAN deletes eliggible archived redo lgos stored in all areas other than the flash recovery area when you execute one of the two deletion commands shown in the solution section of the receipe, backup…delete input or delete archivelog.

*If you execute the delete command with the force option, RMAN will ignore any configured archived redo log retention policies and deletes all the specified archived redo logs.