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.
Mathijs Bruggink 