Archive

Archive for the ‘RMAN Backup and Recovery’ Category

let’s meet at TROUG High Availability SIG 2016 – Galatasaray University!

February 22, 2016 Leave a comment

he16-1

For detailed information and registration;

High Availability SIG Meeting 2016

RMAN-20202 & RMAN-06019 Errors During RMAN Tablespace Restore

August 23, 2012 2 comments

These errors are occured when we try to restore a dropped tablespace. If we drop a tablespace then the controlfile will not keep any records related to the dropped tablespace. If we try to use the RMAN RESTORE or RECOVER TABLESPACE command, it will get the RMAN-20202, RMAN-06019 errors.

Let’s do a test

# sqlplus / as sysdba

SQL> create tablespace TS_TEST datafile ‘/data/test11g/ts_test.dbf’ size 10M autoextend on;

# rman target /

RMAN> backup tablespace TS_TEST;

Starting backup at 23-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00031 name=/data/test11g/ts_test.dbf
channel ORA_DISK_1: starting piece 1 at 23-AUG-12
channel ORA_DISK_1: finished piece 1 at 23-AUG-12
piece handle=/oracle/yedek/bck_test11g/a8njcggd_1_1.bck tag=TAG20120823T153645 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-AUG-12
Starting Control File Autobackup at 23-AUG-12
piece handle=/oracle/yedek/bck_test11g/cf_sp_file_c-1532875336-20120823-01 comment=NONE
Finished Control File Autobackup at 23-AUG-12

# sqlplus / as sysdba

SQL> drop tablespace TS_TEST including contents and datafiles;

# rman target /

RMAN> restore tablespace TS_TEST;

Starting restore at 23-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=136 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/23/2012 15:38:40
RMAN-20202: tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name “TS_TEST”

To recover a dropped tablespace, we must do a point in time recovery of the whole database until the time the tablespace was dropped.
A tablespace backup is good if you have a media error related to tablespace.

Now let’s do another test

# sqlplus / as sysdba

SQL> create tablespace TS_TEST datafile ‘/data/test11g/ts_test.dbf’ size 10M autoextend on;

# rman target /

RMAN> backup tablespace TS_TEST;

Starting backup at 23-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00031 name=/data/test11g/ts_test.dbf
channel ORA_DISK_1: starting piece 1 at 23-AUG-12
channel ORA_DISK_1: finished piece 1 at 23-AUG-12
piece handle=/oracle/yedek/bck_test11g/acnjch45_1_1.bck tag=TAG20120823T154717 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-AUG-12
Starting Control File Autobackup at 23-AUG-12
piece handle=/oracle/yedek/bck_test10g/cf_sp_file_c-1532875336-20120823-04 comment=NONE
Finished Control File Autobackup at 23-AUG-12

Remove datafile belong to TS_TEST tablespace in operating system

# rm /data/test11g/ts_test.dbf

Now, let’s try to create a table in TS_TEST tablespace

SQL> create table talip_test (id number) tablespace TS_TEST;
create table talip_test (id number) tablespace TS_TEST
*
ERROR at line 1:
ORA-01116: error in opening database file 31
ORA-01110: data file 31: ‘/data/test10g/ts_test.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

There is a media error and we must restore tablespace backup.

# rman target /

RMAN> sql “alter database datafile 31 offline”;

sql statement: alter database datafile 31 offline

RMAN> restore tablespace TS_TEST;

Starting restore at 23-AUG-12
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00031 to /data/test11g/ts_test.dbf
channel ORA_DISK_1: reading from backup piece /oracle/yedek/bck_test11g/acnjch45_1_1.bck
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/yedek/bck_test11g/acnjch45_1_1.bck tag=TAG20120823T154717
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 23-AUG-12

RMAN> recover tablespace TS_TEST;

Starting recover at 23-AUG-12
using channel ORA_DISK_1

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

Finished recover at 23-AUG-12

RMAN> sql “alter database datafile 31 online”;

sql statement: alter database datafile 31 online

Yes! now you can create a table in this tablespace.

# sqlplus / as sysdba

SQL> create table talip_test (id number) tablespace TS_TEST;

Table created.

Talip Hakan Ozturk

How To Restore 10g RMAN Backup to 11g?

July 20, 2012 21 comments

In this article we will discuss about restoring 10g database backup to 11g software using RMAN. Suppose our 10g database is running on the ext3 file system and we want to restore it to 11g software running on the Oracle ASM.

After the database restoration using RMAN ,we have to upgrade this database into 11g by catupgrd.sql script.

The steps for 10g database:

1- Run the utlu112i.sql Pre-upgrade script. You can find this script under @$ORACLE_HOME/rdbms/admin/. It must be copied from the 11g database software.

SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql

This script adds a column named tz_version to table named registry$database. Pre-upgrade script updates this column with the value of the following query.

SQL> select version from v$timezone_file;

So it performs following operation.

SQL> ALTER TABLE registry$database ADD (tz_version NUMBER);
SQL> UPDATE registry$database set tz_version =4;

SQL> ALTER  PACKAGE “SYS”.”DBMS_REGISTRY”  COMPILE BODY;
SQL> ALTER VIEW “SYS”.”DBA_REGISTRY_DATABASE”  COMPILE;

2- Connect to 10g database and take RMAN full backup.

#rman target /
RMAN> backup as backupset database;

3- Copy 10g database backup files and archive files to 11g database server.

The steps for 11g database:

1- Create temporary pfile in $ORACLE_HOME/dbs

*.audit_file_dest=’/oracle/admin/TALIPDB/adump’
*.compatible=’11.2.0.0.0′
*.control_files=’+DATA/talipdb/controlfile/current.257.787742981′,’+DATA/talipdb/controlfile/current.258.787742983′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_create_online_log_dest_1=’+RECO’
*.db_domain=”
*.db_name=’TALIPDB’
*.diagnostic_dest=’/oracle’
*.job_queue_processes=0
*.open_cursors=300
*.pga_aggregate_target=1G
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=2G
*.undo_tablespace=’UNDOTBS1′

2- Open the database in NOMOUNT state.

# rman target /

RMAN> startup nomount;

3- Restore controlfile from backup.

RMAN> restore controlfile from ‘/oracle/ora11g/talipdb/backup/c-784951186-20120620-02’;

4- Open the database in MOUNT state.

RMAN> alter database mount;

5- Catalog RMAN backup files and archive log files.

RMAN> catalog start with ‘/oracle/ora11g/talipdb/backup’;
RMAN> catalog start with ‘/oracle/ora11g/talipdb/archive’;

6- Restore 10g database backup to +DATA diskgroup and perform incomplete recovery.

RMAN> run
{
allocate channel c1 device type disk;
SET NEWNAME FOR DATAFILE 1 TO ‘+DATA’;
SET NEWNAME FOR DATAFILE 2 TO ‘+DATA’;
SET NEWNAME FOR DATAFILE 3 TO ‘+DATA’;
SET NEWNAME FOR DATAFILE 4 TO ‘+DATA’;
restore database until sequence 4;
switch datafile all;
recover database until sequence 4;
}

7-  Open the database with RESETLOGS UPGRADE.

# sqlplus / as sysdba
SQL> alter database open resetlogs upgrade;

8- Run the upgrade script.

SQL> SPOOL upgrade.log
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
SQL> SPOOL off

9- If our 10g and 11g database os platforms are different then you must run utlmmig.sql script.

————–Changing 32 bit to 64 bit————–
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE
SQL> SPOOL migrate.log
SQL> @$ORACLE_HOME/rdbms/admin/utlmmig.sql
SQL> SPOOL off
——————————————- 

10- Now, you can open the database.

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

11- Run the Post-Upgrade script to check problems.

SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql

12- Compile invalid objects.

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

13- Drop the temporary file and create new one in +DATA diskgroup.

SQL> alter tablespace temp drop tempfile ‘/data_TALIPDB/temp01.dbf’;
SQL> alter tablespace temp add tempfile ‘+DATA’ size 1024M;

Talip Hakan Ozturk

How to Resolve Primary/Standby Log GAP In Case of Deleting Archivelogs From Primary?

April 11, 2012 2 comments

Hi Friends,

I will write about resolving the Primary/Standby log gap in case of we deleted some archive log files from primary. Suppose that we don’t have the backup of the deleted archive files. Normally we (DBAs) should not allow such a situation but such a situation can happen to us. In this case,  we need to learn the current SCN number of Primary and standby databases.

1- let’s learn current SCN number with the following query on the Primary.

SQL> select current_scn from v$database;

CURRENT_SCN

———–

1289504966

2- let’s learn current SCN number with the following query on the Standby

SQL> select current_scn from v$database;

CURRENT_SCN

———–

1289359962

using the function scn_to_timestamp(SCN_NUMBER) you can check the time difference between primary and standby.

3- Stop apply process on the Standby database.

SQL> alter database recover managed standby database cancel;

4– Shutdown the Standby database.

SQL> shutdown immediate;

5- Take incremental backup from the latest SCN number of the Standby database on the Primary database. And copy backup to the standby server.

RMAN> backup incremental from scn 1289359962 database;

# scp /backup_ISTANBUL/dun52q66_1_1 oracle@192.168.2.3:/oracle/ora11g

6- Create new standby control file on the Primary database. And copy this file to standby server.

SQL> alter database create standby controlfile as ‘/oracle/ora11g/standby.ctl’;

# scp /oracle/ora11g/standby.ctl oracle@192.168.2.3:/oracle/ora11g

7- Open the Standby database on NOMOUNT state to learn control files location.

SQL> startup nomount

SQL> show parameter control_files

8- Replace new standby control file with old files.

# cp /oracle/ora11g/standby.ctl /oracle/ora11g/ISTANBUL/data1/control01.ctl

# cp /oracle/ora11g/standby.ctl /oracle/ora11g/ISTANBUL/data2/control02.ctl

9- Open the Standby database on MOUNT state.

SQL> alter database mount standby database;

10- Connect to the RMAN and register backup to catalog.

# rman target /

RMAN> catalog start with ‘/oracle/ora11g’;

It will ask for confirmation. Click “y” .

11- Now, you can recover the Standby database. Start recover database.

RMAN> recover database;

When recover of database is finished, it searches the latest archive file. And it gives an ORA-00334 error. In this case, don’t worry about it. Exit from RMAN and start apply process on the standby database.

SQL> alter database recover managed standby database disconnect from session;

We solved the Primary/Standby log gap with RMAN incremental backup . When we faced with such a situation we don’t need to think about re-installing standby database. Because time is very valuable for us.

Talip Hakan Öztürk

Comparing of RMAN Backup Compression Levels

April 7, 2012 1 comment

Hi Friends,

There are different compression levels with Oracle 11g R2. BASIC, LOW, MEDIUM and HIGH are the four different compression levels. We must have “Advanced Compression” option license to use LOW, MEDIUM and HIGH levels of compression. In this article, I will do the tests at 4 compression levels. We will compare compression levels for backup duration and backup size.

So let’s just our test.

I wrote a shell script like the following. Thus, you can watch your spare time.

# vi rman_compression_test.sh

Add following lines to rman_compression_test.sh script and save it.

echo “RMAN Backup Start Date :” `date ‘+%d.%m.%Y %H:%M:%S’`

StartTime=$(date +%s)

export NLS_LANG=AMERICAN export NLS_DATE_FORMAT=’DD-MON-YYYY HH24:MI:SS’

rman target / << EOSQL

backup as compressed backupset database;

EOSQL

EndTime=$(date +%s)

DiffTime=$(( $EndTime – $StartTime ))

echo “RMAN Backup Finished.”

echo “Backup End Date :” `date ‘+%d.%m.%Y %H:%M:%S’`

echo “RMAN Backup Duration :” $DiffTime

Let us set the location of our backup files.

# rman target /

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/oracle/yedek/bck_test11g/%U’;

1- BASIC compression level test. let’s see our recent compression level.

# rman target /

RMAN> SHOW COMPRESSION ALGORITHM ;

RMAN configuration parameters for database with db_unique_name DBARGE are:

CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE;

We are using BASIC level. Now let’s back up  of our 10GB database as compressed.

# . rman_compression_test.sh

RMAN Backup Start Date : 13.03.2012 16:19:33

…..

Recovery Manager complete.

RMAN Backup Finished.

Backup End Date : 13.03.2012 16:26:32

RMAN Backup Duration : 419

The load average was during the backup:

# top load average: 1.12, 0.82, 0.75

Backup Size: 636M

2- LOW compression level test.

# rman target /

RMAN> CONFIGURE COMPRESSION ALGORITHM ‘LOW’;

old RMAN configuration parameters:

CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE;

new RMAN configuration parameters:

CONFIGURE COMPRESSION ALGORITHM ‘LOW’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE;

new RMAN configuration parameters are successfully stored

Now we are using the LOW level. let’s start backup.

# . rman_compression_test.sh

RMAN Backup Start Date : 13.03.2012 16:30:36

…..

Recovery Manager complete.

RMAN Backup Finished.

Backup End Date : 13.03.2012 16:33:45 RMAN

Backup Duration : 189

The load average was during the backup:

# top load average: 1.34, 0.85, 0.74

Backup Size: 797M

3- MEDIUM compression level test.

# rman target /

RMAN> CONFIGURE COMPRESSION ALGORITHM ‘MEDIUM’;

old RMAN configuration parameters:

CONFIGURE COMPRESSION ALGORITHM ‘LOW’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE;

new RMAN configuration parameters:

CONFIGURE COMPRESSION ALGORITHM ‘MEDIUM’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE;

new RMAN configuration parameters are successfully stored

Now we are using the MEDIUM level. let’s start backup.

# . rman_compression_test.sh

RMAN Backup Start Date : 13.03.2012 16:36:21

…..

Recovery Manager complete.

RMAN Backup Finished.

Backup End Date : 13.03.2012 16:40:19

RMAN Backup Duration : 238

The load average was during the backup:

# top load average: 1.38, 0.93, 0.77

Backup Size: 674M

4- HIGH compression level test.

# rman target /

RMAN> CONFIGURE COMPRESSION ALGORITHM ‘HIGH’;

old RMAN configuration parameters:

CONFIGURE COMPRESSION ALGORITHM ‘MEDIUM’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE;

new RMAN configuration parameters:

CONFIGURE COMPRESSION ALGORITHM ‘HIGH’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE;

new RMAN configuration parameters are successfully stored

Now we are using the HIGH level. let’s start backup.

# . rman_compression_test.sh

RMAN Backup Start Date : 13.03.2012 16:42:21

…..

Recovery Manager complete.

RMAN Backup Finished.

Backup End Date : 13.03.2012 17:34:30

RMAN Backup Duration : 3129

The load average was during the backup:

# top load average: 1.20, 1.07, 0.88

Backup Size: 485M

5- Normal backup test. Change content of our rman_compression_test.sh script with following lines

# vi rman_compression_test.sh

echo “RMAN Backup Start Date :” `date ‘+%d.%m.%Y %H:%M:%S’`

StartTime=$(date +%s)

export NLS_LANG=AMERICAN export NLS_DATE_FORMAT=’DD-MON-YYYY HH24:MI:SS’

rman target / << EOSQL

backup as backupset database;

EOSQL

EndTime=$(date +%s)

DiffTime=$(( $EndTime – $StartTime ))

echo “RMAN Backup Finished.”

echo “Backup End Date :” `date ‘+%d.%m.%Y %H:%M:%S’`

echo “RMAN Backup Duration :” $DiffTime

Execute script.

#. rman_compression_test.sh

RMAN Backup Start Date : 13.03.2012 17:37:51

…..

Recovery Manager complete.

RMAN Backup Finished.

Backup End Date : 13.03.2012 17:42:30

RMAN Backup Duration : 279

The load average was during the backup:

# top load average: 2.42, 1.56, 1.17

Backup Size: 4.0G

Compression Level Backup Size Backup Duration
NORMAL 4.0G 279 sec
BASIC 636M 419 sec
LOW 797M 189 sec
MEDIUM 674M 238 sec
HIGH 485M 3129 sec

Moving Datafiles and Redo Log Files From Filesystem To ASM Storage

March 27, 2012 1 comment

Hi Friends,

In previous article , we learned  moving a datafile from file system to ASM storage. In this article, We will learn step by step moving a database (datafiles, control file and online redo log files) from file system to ASM storage with RMAN. .

1- If Block Change Tracking is enabled then you must disable it.

# sqlplus / as sysdba

SQL> alter database disable block change tracking;

2- Change default location of datafiles and control files as  +DATA disk group.

SQL> alter system set db_create_file_dest=’+DATA’ scope=spfile;

Change also default location of online redo log files as +RECO disk group.

SQL> alter system set db_create_online_log_dest_1=’+RECO’ scope=spfile;

3- Reset control_files parameter in the spfile parameter file.

SQL> alter system reset control_files scope=spfile sid=’*’;

4- Now, you can move datafiles to ASM disk group with RMAN. First, open the database on NOMOUNT state and restore control file. The control file will locate on the +DATA disk group.

$ rman target /

RMAN> startup nomount

RMAN> restore controlfile from ‘/data_TALIPDB/control01.ctl’;

5- Change status of the database to MOUNT state and take image copy backup to +DATA disk group.

RMAN> alter database mount;

RMAN> backup as copy database format ‘+DATA’;

6- After finished backup switch database to copy.

RMAN> switch database to copy;

7- Open the database.

RMAN> alter database open;

8- Now you can enable block change tracking.

SQL> alter database enable block change tracking using file ‘/oracle/ora11g/bct_file.log’;

9- You must add new tempfile to create tempfile on the +DATA disk group.

# sqlplus / as sysdba

SQL> alter tablespace temp add tempfile size 500M;

SQL> alter tablespace temp drop tempfile ‘/data_TALIPDB/temp01.dbf’

10- Let’s move online redo log files to ASM storage.

# sqlplus / as sysdba

SQL>select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP#           STATUS                 MEMBER

————          ————              —————————

1                            INACTIVE             /data1/redo01.log

2                            INACTIVE            /data1/redo02.log

3                            CURRENT             /data1/redo03.log

You can drop redo log files which are INACTIVE or UNUSED. You can not drop a redo log file which status is CURRENT or ACTIVE. Let’s drop online redo log groups and create on the +RECO disk group.

SQL> alter database drop logfile group 1;

Our new online redo log files will be created on the +RECO disk group because I set db_create_online_log_dest_1 parameter to ‘+RECO’ disk group.

SQL> alter database add logfile group 1 size 50M;

Drop/create second redo log group in the same way.

SQL> alter database drop logfile group 2;

SQL> alter database add logfile group 2 size 50M;

11- Rerun query to see status of online redo log files.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP#           STATUS                 MEMBER

————          ————              —————————

1                            INACTIVE            +RECO/talipdb/online log/group_1.257.7782 52603

2                            INACTIVE            +RECO/talipdb/online log/group_2.258.7782 52715

3                            CURRENT             /data1/redo03.log

We can not drop 3th redo log group because it is CURRENT log file. In this situation, switch log file.

SQL> alter system switch logfile;

Rerun above query. You will see its status as ACTIVE because of the redo log file is needed for crash recovery.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP#           STATUS                 MEMBER

————          ————              —————————

1                            CURRENT             +RECO/talipdb/online log/group_1.257.7782 52603

2                            UNUSED               +RECO/talipdb/online log/group_2.258.7782 52715

3                            ACTIVE                 /data1/redo03.log

To change status to INACTIVE, you must checkpoint.

SQL> alter system checkpoint;

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP#           STATUS                 MEMBER

————          ————              —————————

1                            CURRENT             +RECO/talipdb/online log/group_1.257.7782 52603

2                            UNUSED               +RECO/talipdb/online log/group_2.258.7782 52715

3                            INACTIVE             /data1/redo03.log

Now, you can drop/create 3th redo log group.

SQL> alter database drop logfile group 3;

SQL> alter database add logfile group 3 size 50M;

Let’s verify our online redo log files.

SQL> set lines 50

SQL> select member from v$logfile;

MEMBER

————————————————–

+RECO/talipdb/onlinelog/group_3.259.778253083

+RECO/talipdb/onlinelog/group_2.258.778252715

+RECO/talipdb/onlinelog/group_1.257.778252603

Let’s verify our datafiles.

SQL> select name from v$datafile;

NAME

——————————————————————————–

+DATA/talipdb/datafile/system.256.778251403

+DATA/talipdb/datafile/sysaux.257.778251487

+DATA/talipdb/datafile/undotbs1.258.778251553

+DATA/talipdb/datafile/users.260.778251563

SQL> select name from v$tempfile;

NAME

——————————————————————————–

+DATA/talipdb/tempfile/temp.262.778252097

We moved our datafiles and online redo log files to ASM.  Hope to see you happy to be working with ASM databases … 🙂

Talip Hakan Öztürk

How to Move a Datafile From File System To ASM Disk Group?

March 21, 2012 Leave a comment

Hi Friends,

In this article, I will write about moving a datafile from file system to ASM disk group with RMAN.

1- First, let’s create a tablespace which datafile locates on the file system (/data1).

SQL> CREATE TABLESPACE TOASM DATAFILE ‘/data1/toasm01.dbf’ SIZE 100M AUTOEXTEND ON NEXT 1M ;

2- Check for datafiles. So where are our datafiles?

SQL> select name from v$datafile;

NAME

——————————————————————————–

+DATA/talipdb/datafile/system.257.778261279

+DATA/talipdb/datafile/sysaux.258.778261375

+DATA/talipdb/datafile/undotbs1.259.778261441

+DATA/talipdb/datafile/users.260.778261447

/data1/toasm01.dbf

3- let’s connect to RMAN.

[oracle@DBTALIP /oracle/ora11g]# rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Sun Mar 18 16:05:09 2012

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

connected to target database: TALIPDB (DBID=4043281188)

4- Take tablespace offline which you want to move datafiles.

RMAN> sql “alter tablespace toasm offline”;

using target database control file instead of recovery catalog

sql statement: alter tablespace toasm offline

5- Copy datafile with RMAN Copy command to +DATA disk group.

RMAN> copy datafile ‘/data1/toasm01.dbf’ to ‘+DATA’;

Starting backup at 18-MAR-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=58 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/data1/toasm01.dbf

output file name=+DATA/talipdb/datafile/toasm.263.778262769 tag=TAG20120318T160609 RECID=13 STAMP=778262779

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

Finished backup at 18-MAR-12

RMAN> exit

Recovery Manager complete.

6- Connect to SQL*Plus and rename the old datafile to created ASM file .

[oracle@DBTALIP /oracle/ora11g]# sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 18 16:09:12 2012

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

SQL> alter database rename file ‘/data1/toasm01.dbf’ to ‘+DATA/talipdb/datafile/toasm.263.778262769’;

Database altered.

7- Take tablespace again online.

SQL> alter tablespace toasm online;

Tablespace altered.

8- Query v$datafile view to see location of our datafile.

SQL> select name from v$datafile;

NAME ——————————————————————————–

+DATA/talipdb/datafile/system.257.778261279

+DATA/talipdb/datafile/sysaux.258.778261375

+DATA/talipdb/datafile/undotbs1.259.778261441

+DATA/talipdb/datafile/users.260.778261447

+DATA/talipdb/datafile/toasm.263.778262769

That is finished. Our Tablepsace is moved to ASM disk group with RMAN copy command.  🙂

Talip Hakan Öztürk

%d bloggers like this: