This blog is very helpful to everyone, in this blog we have demo of how to move database from RDBMS server to ASM STORAGE.
Very first we will take the full RMAN backup of Source database using below script code.
run {
allocate channel ch00 type disk;
backup
format '/scratch/MMTPR/full_db_%t_%sp%p'
filesperset 10
database plus archivelog;
release channel ch00;
allocate channel ch00 type disk;
backup
format '/scratch/MMTPR/cntrl_%s_%p_%t'
CURRENT CONTROLFILE;
release channel ch00;
}
On Target
And also copy the parameter file from source database to Target machine with all backups. Once you create the pfile on target machine check the directory path under the pfile. We need to create the same directories as per the parameter file.
Like we have adump/cdump/udump directory path in pfile which must be created before going to import the backup on target machine.
initmmtpr.ora
MMTPR.__db_cache_size=92274688
MMTPR.__java_pool_size=4194304
MMTPR.__large_pool_size=4194304
MMTPR.__shared_pool_size=197132288
MMTPR.__streams_pool_size=8388608
*.audit_file_dest='/u01/app/oracle/admin/MMTPR/adump'
*.background_dump_dest='/u01/app/oracle/admin/MMTPR/bdump'
*.compatible='10.2.0.3.0'
*.control_files='+DATA/abcd/controlfile/current.346.848248585','+FRA/abcd/controlfile/current.318.848248585'
*.core_dump_dest='/u01/app/oracle/admin/MMTPR/cdump'
*.db_create_file_dest='+DATA'
*.db_recovery_file_dest_size=2G
*.db_recovery_file_dest='+FRA'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='MMTPR'
*.job_queue_processes=10
#*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=example-ha)(PORT=1521)))'
#*.log_archive_dest='/archive/MMTPR'
#*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=314572800
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/MMTPR/udump'
And change some parameter in initmmtpr.ora which i have mention below according to ASM storage diskgroup name on target machine and save it.
*.control_files='+DATA','+FRA'
*.db_create_file_dest='+DATA'
*.db_recovery_file_dest_size=2G
*.db_recovery_file_dest='+FRA'
Now on target machine restore control file from backup which I have shipped from source top target.
2) restore controlfile from '/u01/app/oracle/disk_backup1/MMTPR/cntrl_6955_1_848307757';
3)alter database mount
Now catalog the backup into target RMAN repository. On target server we need to catalog the BACKUP into RMAN repository so that during the restore RMAN will read the details and mark the backup available in repository to restore and recover the backup files.
4) catalog start with '$BACKUP_LOCATION';
5) select * from v$logfile;And we need to rename the redo logfile to ASM Storage diskgroup suing the below commands.
SQL> alter database rename file '/u02/oradata/MMTPR/redo01.log' to '+DATA';
Database altered.
SQL> alter database rename file '/u02/oradata/MMTPR/redo02.log' to '+DATA';
Database altered.
SQL> alter database rename file '/u02/oradata/MMTPR/redo03.log' to '+DATA';
Database altered.
6. After that we need to restore and recover the database using the below script . Run it on RMAN prompt and monitor the log. Once it will be completed successfully we are good to open the database in read write mode.
run {
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';
set newname for datafile 5 to '+DATA';
set newname for datafile 6 to '+DATA';
set newname for datafile 7 to '+DATA';
set newname for datafile 8 to '+DATA';
set newname for datafile 9 to '+DATA';
set newname for datafile 10 to '+DATA';
set newname for datafile 11 to '+DATA';
set newname for datafile 12 to '+DATA';
set newname for datafile 13 to '+DATA';
restore database;
SWITCH DATAFILE ALL;
recover database;
}
7) alter database open resetlogs;
And also add the temp file into ASM STORAGE. check the detail from dba_temp_files view and rename the temp file in DATA diskgroup.
Note – ASM by default use the OMF(Oracle Managed File) method to create the files under the ASM disk and its automatically managed by OMF.
select * from dba_temp_files;
8) alter database rename file '/u02/oradata/MMTPR/temp01.dbf' to '+DATA';
9) shu immediate
—–>>>do changes in pfile—–
>*.control_files='+DATA/abcd/controlfile/current.346.848248585','+FRA/abcd/controlfile/current.318.848248585'
startup nomount pfile='pfile_location';
create spfile from pfile;
open database
Please share this Blog with your colleagues or friends. Your suggestions and feedback are very helpful for everyone who come to this site and learn it from oracleocpworld.com.
Please comment here for your any query related to above post. You can email me on : oracleocpworld@gmail.com.