Recently I stepped on interesting issue.. I was suppose to restore database on the same host where source production database was running…
In general, not big deal…lots of procedures to follow, but what when source database uses OMF (Oracle Managed Files) ? Does situation changes then ?
As stated in documentation, and as I checked it , when using OMF adminstrator is no longer engaged in deleting files, when renaming it or dropping from tablespace.
What does it mean in our case ?
ALTER DATABASE RENAME FILE '+DATA_X1/psa_x1/onlinelog/group_3.334.905612369' TO '/u06/testdb/t2psa/redo03.log' WARNING: Cannot delete Oracle managed file +DATA_X1/psa_x1/onlinelog/group_3.334.905612369 Completed: ALTER DATABASE RENAME FILE '+DATA_X1/psa_x1/onlinelog/group_3.334.905612369' TO '/u06/testdb/t2psa/redo03.log' ALTER DATABASE RENAME FILE '+DATA_X1/psa_x1/onlinelog/group_2.329.905612279' TO '/u06/testdb/t2psa/redo02.log' WARNING: Cannot delete Oracle managed file +DATA_X1/psa_x1/onlinelog/group_2.329.905612279
When we try to change the location of redologs, what is one of steps in restoring to new or the same host, we are prone to mistake , that can cost us recovering of production database or in the worst case lost of data !
As you can see on snip of alert log, during renaming of redologs , database id trying to delete oryginal redo logs … Catastrophe !!!!… I was lucky, that files were on ASM, and primary database was running and using them …
So … How should we avoid it ?
First I realized, that I have to get rid of OMF managed redo logs. Found “magic” hidden parameter “_omf”=disabled/enabled …
So I tried it ! 🙂
SQL> alter system set "_omf"=disabled scope=spfile sid='*'; SQL> alter system reset db_create_file_dest scope=spfile sid='*'; SQL> alter system reset db_create_online_log_dest_1 scope=spfile sid='*';
This attitude was sufficient when I added on mounted database new OMF redo log and renamed it ..
ALTER DATABASE RENAME FILE '/u06/testdb/t2psa/T2PSA/onlinelog/o1_mf_8__1461687094729747_.log' TO '/u06/testdb/t2psa/redo08.log' Completed: ALTER DATABASE RENAME FILE '/u06/testdb/t2psa/T2PSA/onlinelog/o1_mf_8__1461687094729747_.log' TO '/u06/testdb/t2psa/redo08.log'
But…what was the result after restoring of controlfile and trying to rename redo log created on production database ?
ALTER DATABASE RENAME FILE '+DATA_X1/psa_x1/onlinelog/group_1.447.905612261' TO '/u06/testdb/t2psa/redo01.log' WARNING: Cannot delete Oracle managed file +DATA_X1/psa_x1/onlinelog/group_1.447.905612261 Completed: ALTER DATABASE RENAME FILE '+DATA_X1/psa_x1/onlinelog/group_1.447.905612261' TO '/u06/testdb/t2psa/redo01.log'
I started to see only one way … and metalink note, made me sure, that it is the only way … apart from using “RMAN DUPLICATE” instead of RMAN RESTORE ..
HowTo Restore RMAN Disk backups of RAC Database to Single Instance On Another Node (Doc ID 415579.1)
The answer is “after restoring and recovering database, but before opening it with resetlogs (what surely would delete your redo logs), recreate controlfile from trace…”
As you can see, automated new features, sometimes take some work from us, and sometimes gives us a lot more to do …:)
Below full restore procedure, have fun with testing it ! 🙂
References:
Using RMAN to Restore and Recover a Database When the Repository and Spfile/Init.ora Files Are Also Lost (Doc ID 372996.1)</label> HowTo Restore RMAN Disk backups of RAC Database to Single Instance On Another Node (Doc ID 415579.1) How To Create A Production (Full or Partial) Duplicate On The Same Host (Doc ID 388424.1)
Assumptions:
- Backups should be visible on host (the same host as production one) - Target db_name,instance_name,db_unique_name T2PSA
Steps of procedure:
- Create target directory /u06/testdb
mkdir -p /u06/testdb/t2psa
- Make a copy of RMAN catalog schema
create user RMAN_20160426 identified by oracle default tablespace RMAN_EX quota unlimited on RMAN_EX;
grant recovery_catalog_owner to RMAN_20160426;
impdp "'/ as sysdba'" directory=BACKUP_DIR SCHEMAS=RMAN_EX DUMPFILE=rman_ex_2016-04-26.dmp REMAP_SCHEMA=RMAN_EX:RMAN_20160426 TRANSFORM=oid:n
- Edit oratab and connect to rman
## TEST DATABASES
T2PSA:/u01/app/oracle/product/11.2.0.3/omh5:N # line added by Agent
- Because of BUG in version 11.2.0.3
RMAN-04014: startup failed: ORA-04031: unable to allocate 3981064 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","FileOpenBlock")
create temporary pfile
echo "db_name=PSA" > /u01/app/oracle/product/11.2.0.3/omh5/dbs/initT2PSA.ora
echo "db_unique_name=P2PSA" >> /u01/app/oracle/product/11.2.0.3/omh5/dbs/initT2PSA.ora
echo "sga_target=2G" >> /u01/app/oracle/product/11.2.0.3/omh5/dbs/initT2PSA.ora
. oraenv T2PSA
rman target=/ catalog=RMAN_20160426/oracle@RMAN
startup nomount
- On second TAB get DBID from production database
SQL> select dbid from v$database;
DBID
----------
4280568361
run {
set dbid 4280568361;
restore spfile;
shutdown immediate;
}
- Change db_unique_name before starting in nomount state, it protect from creating or overwriting trace files in diagnostic_dest
sqlplus "/ as sysdba"
create pfile from spfile;
- Edit pfile and change db_unique_name
*.db_unique_name=T2PSA
$ sqlplus "/ as sysdba"
SQL> create spfile from pfile;
- Restart to run from modified spfile;
SQL> startup nomount
- Get RESET parameters commands (as on test database which do not needs huge buffers and so on ..)
SQL> select 'alter system reset ' || name || ' scope=spfile sid=''' || SID || ''';' from v$spparameter where name in -
('shared_pool_size','db_cache_size','java_pool_size','large_pool_size','streams_pool_size','sga_max_size','sga_target','log_archive_dest_2','log_archive_dest_3','remote_listener','service_names','db_create_file_dest','db_create_online_log_dest_1');
Output should be sth like:
alter system reset sga_max_size scope=spfile sid='*';
alter system reset shared_pool_size scope=spfile sid='*';
alter system reset large_pool_size scope=spfile sid='*';
alter system reset java_pool_size scope=spfile sid='*';
alter system reset streams_pool_size scope=spfile sid='*';
alter system reset sga_target scope=spfile sid='*';
alter system reset db_cache_size scope=spfile sid='*';
alter system reset log_archive_dest_2 scope=spfile sid='*';
alter system reset log_archive_dest_3 scope=spfile sid='*';
alter system reset db_create_file_dest scope=spfile sid='*';
alter system reset db_create_online_log_dest_1 scope=spfile sid='*';
alter system reset service_names scope=spfile sid='*';
alter system reset remote_listener scope=spfile sid='*';
Merge it with SET commands
alter system set log_archive_dest_1='LOCATION=/u06/testdb/t2psa/fra' scope=spfile;
alter system set instance_name=t2psa scope=spfile;
alter system set DB_UNIQUE_NAME=T2PSA scope=spfile;
alter system set control_files='/u06/testdb/t2psa/control.ctl' scope=spfile;
alter system set job_queue_processes=0 scope=spfile;
alter system set aq_tm_processes=0 scope=spfile;
alter system set OPEN_LINKS=0 scope=spfile;
alter system set dg_broker_start=false scope=spfile;
alter system set use_large_pages=false scope=spfile;
alter system set max_dump_file_size=33554432 scope=spfile;
alter system set "_memory_imm_mode_without_autosga"=TRUE scope=spfile;
alter system set sga_target=2G scope=spfile;
alter system set db_create_file_dest-'/u06/testdb/t2psa'
Run both FOREGOING sections !
Example complete set of commands from output above !
-------------------------------------------------------------
alter system reset sga_max_size scope=spfile sid='*';
alter system reset shared_pool_size scope=spfile sid='*';
alter system reset large_pool_size scope=spfile sid='*';
alter system reset java_pool_size scope=spfile sid='*';
alter system reset streams_pool_size scope=spfile sid='*';
alter system reset sga_target scope=spfile sid='*';
alter system reset db_cache_size scope=spfile sid='*';
alter system reset log_archive_dest_2 scope=spfile sid='*';
alter system reset log_archive_dest_3 scope=spfile sid='*';
alter system reset db_create_file_dest scope=spfile sid='*';
alter system reset service_names scope=spfile sid='*';
alter system reset remote_listener scope=spfile sid='*';
alter system set log_archive_dest_1='LOCATION=/u06/testdb/t2psa/fra' scope=spfile;
alter system set instance_name=T2PSA scope=spfile;
alter system set DB_UNIQUE_NAME=T2PSA scope=spfile;
alter system set control_files='/u06/testdb/t2psa/control.ctl' scope=spfile;
alter system set job_queue_processes=0 scope=spfile;
alter system set aq_tm_processes=0 scope=spfile;
alter system set OPEN_LINKS=0 scope=spfile;
alter system set dg_broker_start=false scope=spfile;
alter system set use_large_pages=false scope=spfile;
alter system set max_dump_file_size=33554432 scope=spfile;
alter system set "_memory_imm_mode_without_autosga"=TRUE scope=spfile;
alter system set sga_target=2G scope=spfile;
alter system set db_create_file_dest-'/u06/testdb/t2psa'
-------------------------------------------------------------
- Restart to run from spfile with new parameters;
SQL> shutdown immediate
SQL> startup nomount
$ rman target=/ catalog=RMAN_20160426/oracle@RMAN
run {
set dbid 4280568361;
allocate channel c1 device type disk;
allocate channel c2 device type disk;
restore controlfile;
alter database mount;
}
- Get sequence of highest archivelog in controlfile, to prevent from recovering from production online redo logs
SQL> select max(sequence#) from v$archived_log order by 1 asc;
MAX(SEQUENCE#)
--------------
3263
- Proceed to restore and recovery
rman target=/
run {
set until logseq 3263;
set newname for database to '/u06/testdb/t2psa/%U';
allocate channel c1 device type disk;
allocate channel c2 device type disk;
restore database;
switch datafile all;
recover database;
}
- backup controlfile to trace to get rid of old DBID, old MOF redologs, and tempfiles
SQL> alter database backup controlfile to trace;
SQL> select tracefile
from v$session s,
v$process p
where s.paddr = p.addr
and s.audsid = sys_context('USERENV', 'SESSIONID');
/u01/app/oracle/diag/rdbms/t2psa/T2PSA/trace/T2PSA_ora_19194.trc
- Edit controlfile, leave only RESETLOGS section and change REUSE to SET database name ! ! !
example from T2PSA :
CREATE CONTROLFILE SET DATABASE "T2PSA" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXDATAFILES 512
MAXINSTANCES 1
MAXLOGHISTORY 2045
LOGFILE
GROUP 1 '/u06/testdb/t2psa/redo01.log' SIZE 100M BLOCKSIZE 512,
GROUP 2 '/u06/testdb/t2psa/redo02.log' SIZE 100M BLOCKSIZE 512,
GROUP 3 '/u06/testdb/t2psa/redo03.log' SIZE 100M BLOCKSIZE 512,
DATAFILE
'/u06/testdb/t2psa/data_D-PSA_TS-SYSTEM_FNO-1',
'/u06/testdb/t2psa/data_D-PSA_TS-SYSAUX_FNO-2',
'/u06/testdb/t2psa/data_D-PSA_TS-UNDOTBS1_FNO-3',
'/u06/testdb/t2psa/data_D-PSA_TS-USERS_FNO-4',
'/u06/testdb/t2psa/data_D-PSA_TS-XDB_FNO-5',
'/u06/testdb/t2psa/data_D-PSA_TS-HAL_AR_1_FNO-6',
'/u06/testdb/t2psa/data_D-PSA_TS-HAL_DANE_1_FNO-7',
'/u06/testdb/t2psa/data_D-PSA_TS-HAL_DANE_2_FNO-8',
'/u06/testdb/t2psa/data_D-PSA_TS-HAL_DANE_3_FNO-9',
'/u06/testdb/t2psa/data_D-PSA_TS-HAL_INDEKSY_1_FNO-10',
'/u06/testdb/t2psa/data_D-PSA_TS-HAL_INDEKSY_2_FNO-11',
'/u06/testdb/t2psa/data_D-PSA_TS-HAL_INDEKSY_3_FNO-12',
'/u06/testdb/t2psa/data_D-PSA_TS-HAL_JN_1_FNO-13',
'/u06/testdb/t2psa/data_D-PSA_TS-HAL_JN_2_FNO-14',
'/u06/testdb/t2psa/data_D-PSA_TS-HAL_JN_3_FNO-15',
'/u06/testdb/t2psa/data_D-PSA_TS-HAL_JN_4_FNO-16',
'/u06/testdb/t2psa/data_D-PSA_TS-HAL_JN_5_FNO-17',
'/u06/testdb/t2psa/data_D-PSA_TS-HAL_UZYTK_1_FNO-18',
'/u06/testdb/t2psa/data_D-PSA_TS-TEST_FNO-19'
CHARACTER SET EE8ISO8859P2;
$ cp /u01/app/oracle/diag/rdbms/t2psa/T2PSA/trace/T2PSA_ora_19194.trc /tmp/recreate_controlfile.sql
- Reset control_files parameter, and db_name in spfile . Recreating controlfile will update value of parameter in spfile
sqlplus / as sysdba
SQL> alter system reset control_files scope=spfile;
SQL> alter system set db_name='T2PSA' scope=spfile;
SQL> shutdown immediate
SQL> startup nomount
SQL> @/tmp/recreate_controlfile.sql
- Check logfile locations in controfile
SQL> Select group#,member form v$logfile;
3 /u06/testdb/t2psa/redo03.log
2 /u06/testdb/t2psa/redo02.log
1 /u06/testdb/t2psa/redo01.log
! NOW WE ARE SATISFIED , our production online logs are safe :)
- Open resetlogs, add tempfile to TEMP tablespace
SQL> alter database open resetlogs;
SQL> alter tablespace TEMP add tempfile size 200M;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
---------------------------------------------------------------------------
/u06/testdb/t2psa/T2PSA/datafile/o1_mf_temp__1461694197199668_.tmp
