How To Duplicate A Filesystem Database to an ASM Database

Introduction

I have been asked to explore a scenario in which a database that is currently on a file systems is recovered with RMAN into ASM. In this web log the steps i have performed are shared.

Environment:

11.20.3.0

On Linux

For Demo sake source database is called MYFSDB

Database we will create in ASM called MYASMDB

First step make a proper backup of MYFSDB:

Make sure your ORACLE_SID is pointing to the correct Environment.

Commands:

rman

connect target /

backup database plus archivelog;

 

Preparation Listener:

For the new database make sure listener is set up to listen. Since this is 1120.30 Db should register automatically. But let’s check if My source database has registered already there

lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 03-AUG-2012 10:51:41

Service “+ASM” has 1 instance(s).

Instance “+ASM”, status READY, has 1 handler(s) for this service…

Service “MYFSDB” has 1 instance(s).

Instance “MYFSDB”, status READY, has 1 handler(s) for this service…

Service “MYFSDBXDB” has 1 instance(s).

Instance “MYFSDB”, status READY, has 1 handler(s) for this service…

Service “NLRMAN” has 1 instance(s).

Instance “NLRMAN”, status READY, has 1 handler(s) for this service…

Service “NLRMANXDB” has 1 instance(s).

Instance “NLRMAN”, status READY, has 1 handler(s) for this service…

The command completed successfully

 

So that is a yes

Preparation tnsnames.ora for the auxiliary  database

The database that is going to be in ASM is referred to as an auxiliary database. It needs to have proper tnsnames entry

 

###  tnsnames. ora

MYASMDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = prod1)(PORT = 33000))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = myasmdb)

)

)

 

Preparation make a link for the init.ora to $ORACLE_HOME/dbs

 

###  make link  so Oracle will know about settings , cause I will be setting up a init.ora manually and make changes needed in that one. I know there are more options  but this one I like.. so I stick to  a known  step by step approach.

 

ln -s /opt/oracle/MYASMDB/admin/pfile/initMYASMDB.ora initMYASMDB.ora

 

 

Preparation alter the Init.ora  for the Auxiliary database

 

Copy the  init.ora of the source database to  the environment where the new db should be able to access it and start making changes .  I will mark them and explain.

 

Did  1,$s/MYFSDB/MYASMDB/g

 

Then I started working with the file.

 

initMYASMDB.ora

MYASMDB.__db_cache_size=22682796032

MYASMDB.__java_pool_size=134217728

MYASMDB.__large_pool_size=134217728

MYASMDB.__pga_aggregate_target=17716740096

MYASMDB.__sga_target=26440892416

MYASMDB.__shared_io_pool_size=0

MYASMDB.__shared_pool_size=3221225472

MYASMDB.__streams_pool_size=0

*.audit_file_dest=’/opt/oracle/admin/MYASMDB/adump’

##made sure this directory exists on OS

*.audit_trail=’db’

*.compatible=’11.2.0.0.0′

*.control_files = ‘+NLRMAN_DATA01/MYASMDB/control01.ctl’,’+NLRMAN_FRA01/MYASMDB/control02.ctl’

## Make sure DISKGROUPS exist AND that you have manually create the subdirs in asmcmd for the NEW db (here MYASMDB)

*.db_block_size=8192

*.db_domain=”

*.db_file_name_convert = (‘/opt/oracle/db/MYFSDB/MYFSDB/’,’+NLRMAN_DATA01/MYASMDB/’)

## Make sure DISKGROUPS exist AND that you have manually create the subdirs in asmcmd

## ALL datafiles of MYFSDB where in same location. If that is not case consider .. set newname  in ##Rman.

*.db_name=’MYASMDB’

*.db_create_file_dest=’+NLRMAN_DATA01/’

*.db_create_online_log_dest_1=’+NLRMAN_DATA01/’

*.db_create_online_log_dest_2=’+NLRMAN_FRA01/’

*.db_recovery_file_dest=’/opt/oracle/db/fast_recovery_area’

*.db_recovery_file_dest_size=4322230272

*.diagnostic_dest=’/opt/oracle’

*.dispatchers=’(PROTOCOL=TCP) (SERVICE=MYASMDBXDB)’

*.local_listener=’LISTENER_MYASMDB’

##  Forgot to add  entry to the tnsnames.ora that felt  awkward when I saw  that Oracle saw that too

 

*.log_file_name_convert=’/opt/oracle/db/MYFSDB/data/’,’+NLRMAN_DATA01/MYASMDB/’,’/opt/oracle/db/MYFSDB/fra/’,’+NLRMAN_FRA01/MYASMDB’

## Make sure DISKGROUPS exist AND that you have manually create the subdirs in asmcmd  for  ##MYASMDB in this example

## and we see two entries  here  cause most times  redolog member are in separate  mountpoints at ## this site.

 

*.log_archive_format=’%t_%s_%r.dbf’

*.memory_target=44045434880

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile=’EXCLUSIVE’

*.undo_tablespace=’UNDOTBS1′

## For my checks I would look into the source database to see where stuff is located:

SQL> select name from v$datafile;

 

NAME

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

/opt/oracle/db/MYFSDB/system01.dbf

/opt/oracle/db/MYFSDB/sysaux01.dbf

/opt/oracle/db/MYFSDB/undotbs01.dbf

/opt/oracle/db/MYFSDB/users01.dbf

/opt/oracle/db/MYFSDB/example01.dbf

 

SQL> select member from v$logfile;

 

MEMBER

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

/opt/oracle/db/MYFSDB/redo03.log

/opt/oracle/db/MYFSDB/redo02.log

/opt/oracle/db/MYFSDB/redo01.log

 

## And  I used an existing DB in ASm to see the naming convent of the diskgroups.

 

SQL> select name from v$datafile;

 

NAME

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

+NLRMAN_DATA01/nlrman/datafile/system.260.787068089

+NLRMAN_DATA01/nlrman/datafile/sysaux.261.787068097

+NLRMAN_DATA01/nlrman/datafile/undotbs1.262.787068105

+NLRMAN_DATA01/nlrman/datafile/rman_data01.264.787068117

+NLRMAN_DATA01/nlrman/datafile/rman_data02.265.787068151

+NLRMAN_DATA01/nlrman/datafile/rman_data03.266.787068187

+NLRMAN_DATA01/nlrman/datafile/rman_data04.267.787068223

+NLRMAN_DATA01/nlrman/datafile/users.268.787068243

 

 

SQL> select member from v$logfile;

 

MEMBER

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

+NLRMAN_DATA01/nlrman/onlinelog/group_1.257.787068083

+NLRMAN_FRA01/nlrman/onlinelog/group_1.257.787068085

+NLRMAN_DATA01/nlrman/onlinelog/group_2.258.787068085

+NLRMAN_FRA01/nlrman/onlinelog/group_2.258.787068087

+NLRMAN_DATA01/nlrman/onlinelog/group_3.259.787068087

+NLRMAN_FRA01/nlrman/onlinelog/group_3.259.787068087

Starting the auxiliary database.

 

As best practice (and has to be done if your aux is on a different server .Create a password file for the aux database in the $ORACLE_HOME/dbs:

orapwd file=orapwMyASMDB password=<same_as_sys_on_source>  entries=5

 

According to the books and scenarios the auxiliary database (MYASMDB) needs to be started in nomount:

 

export ORACLE_SID=myasmdb

$ sqlplus /nolog

SQL> connect / as sysdba

Connected to an idle instance

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initMYASMDB.ora

 

And now for the real Deal. The duplicate

 

First would have done this cause this was way I connected on other occasions:

rman

connect target sys/<sys_password>@myfsdb

 

connect auxiliary sys/<sys_password>@myasmdb

 

duplicate target database to myasmdb;

 

BUT then  I came across  (and that on my Friday afternoon)

ORA-12528: TNS:listener: all appropriate instances are blocking new connections.

Found  note:  [ID 419440.1] so I implemented workaround

 

rman

 

connect target sys/<sys_password>@myfsdb

 

connect auxiliary /

 

 

duplicate target database to myasmdb;

 

That worked out just fine so  indeed this is a valid scenario to move a  Database into  ASM.

After the process  finished I shutdown  the MyASMDB and  created an spfile.

Then I started the database again .

Final touch was that i had to create the temp tablespace again cause my alertfile kept screaming I AM EMPTY ..

(Fri Aug 03 16:19:15 2012

Errors in file /opt/oracle/diag/rdbms/myasmdb/MYASMDB/trace/MYASMDB_m001_4012.trc:

ORA-25153: Temporary Tablespace is Empty).

This is how I did it:

 

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE SIZE 50M eXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

 

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

 

drop tablespace temp including contents;

 

CREATE TEMPORARY TABLESPACE temp TEMPFILE SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

Tablespace created.

 

Checked it , it was ok .

 

SQL> select * from dba_temp_files;

 

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

 

drop tablespace temp2 including contents;

 

 

………………………..   End of Line ……………..

 

Happy Dba.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s