Steps to migrate database machines by mounting existing ASM disks on new machines

Philosophy behind this machine migration is: ASM disk groups could be mounted to any machine, that see the underlying disk luns.  And there is so much to do for OS admins then DBAs while migrating your database to new servers(machines).

Let's tell about our environment and requirements before dive into steps.

Both our database and grid infra versions are 11.2.0.2, running on IBM AIX 6.1 and migration/ upgrade from ibm p5 series machine to p6.

There are a single database and a RAC database in our RAC environment needed to migrate.

In our case, IP adress and VLAN are needed to be changed. That is why we need install grid and Oracle database software instead of only mounting u01 disk. Also we need to deal with  scan name change side effects.

You could find the steps for changing database machines followingly:

1. Install grid infra, and Oracle database software and do not create data disk groups after then.

This step could be done at any time, even days before following steps.

2. Call application admins before starting.

[Steps 3,4,5 will be executed in old database node]

3. disable&stop listeners

--run below two lines in all nodes of cluster
srvctl stop listener -l listener
srvctl disable listener -l listener

--disable&stop scan_listeners in one node
srvctl disable scan_listener -i 1
srvctl disable scan_listener -i 2
srvctl disable scan_listener -i 3
srvctl stop scan_listener -i 1
srvctl stop scan_listener -i 2
srvctl stop scan_listener -i 3

4. Backup your archive logs to tape, in order to rollback the operation.

5. Stop database, asm, cluster.

crsctl stop database
crsctl stop cluster
crsctl stop crs

[Below steps will be executed in old database node]
6. Add database, instance to cluster
srvctl add database -d MYDB -c RAC -o /u01/app/oracle/product/11.2.0/dbhome_1
srvctl add database -d APEX -c SINGLE -x istanbul1 -o /u01/app/oracle/product/11.2.0/dbhome_1

srvctl add instance -d MYDB -i MYDB1 -n istanbul1
srvctl add instance -d MYDB -i MYDB2 -n istanbul2
srvctl add instance -d MYDB -i APEX -n istanbul1

8. Change asm search string as it was in older machines
alter system set asm_diskstring='/dev/ORACLE/*/*' scope=both;
9. Mount ASM diskgroups
alter diskgroup data1dg mount;
alter diskgroup data2dg mount;

10. Create init.ora files as in old servers.

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initMYDBDB1.ora and
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initMYDBDB2.ora

12. Create adump folder, give oracle user required OS rights

Check for audit_file_dest parameter in old system, create folder in all new nodes like below:

mkdir /u01/app/oracle/admin/MYDB/adump
chown oracle:oinstall mkdir /u01/app/oracle/admin/MYDB/adump

13. Start database [check alert.log while starting database]


srvctl start database -d MYDB
srvctl start database -d APEX

14. Add & start services to cluster [be sure you started MYDB database as in step 13]

srvctl add service -d MYDB -s MYSVC1 -r MYDB1  -a MYDB2
srvctl add service -d MYDB -s MYSVC2 -r MYDB1 -a MYDB2

srvctl start service -d MYDB -s MYSVC1
srvctl start service -d MYDB -s MYSVC2

15. Change remote_listener parameter in init.ora

ALTER SYSTEM SET remote_listener='mydb-scan.turkcell.tgc:1521' SCOPE=BOTH;

How to open a database while restoring and recovering without archivelogs

In other words, how can I open a database, in a case that database is successfully restored, but cannot be recovered due to absence of  archivelogs?

•         The heart of opening the "wounded" database is based on using below init.ora parameters:

_ALLOW_RESETLOGS_CORRUPTION = TRUE    

_ALLOW_READ_ONLY_CORRUPTION = TRUE     

UNDO_MANAGEMENT = MANUAL

Beware of using above parameters that it will be last resort for opening the database.

Further, we asked for _corrupted_rollback_segments parameter to Oracle ACS, thanks Osman Abi:),  he told that we could have been used that parameter in case of corruption on undo segments which may prevent opening database.

•          Restore environment is 11.2.0.3, and source database is 11.2.0.2, therefore we opened the db as if making upgrade by:

alter database open resetlogs upgrade;

 

•          Lesson learnt from the case: 

Monitor archivelog backup health by following:

select sequence#,archived,deleted,backup_count, status from gv$archived_log where sequence#=111430 and inst_id=2;

 

 

DETAILS

While initiating recovery from RMAN, we got below errors:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/09/2012 12:31:11
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 2 with sequence 111430 and starting SCN of 11345139269565 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111428 and starting SCN of 11344754878547 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111426 and starting SCN of 11344754846270 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111420 and starting SCN of 11344754612059 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111418 and starting SCN of 11344754547555 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111416 and starting SCN of 11344754483883 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111415 and starting SCN of 11344754472813 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111414 and starting SCN of 11344754461578 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111413 and starting SCN of 11344754450683 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111412 and starting SCN of 11344754438403 found to restore
...
 

After investigating the cause of above messages by following:

1-      Whether archive log sequence 111430  exists in controlfile from RMAN connected to target database controlfile by:

oracle@kiev:/zfssa/restore1>rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Oct 9 16:52:27 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: MYDB (DBID=1032014565, not open)
RMAN> list backup of archivelog FROM SEQUENCE 111245 UNTIL SEQUENCE 111247;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN>


2-      Whether archive log sequence 111430  exists in recovery catalog querying from CATALOG DB metadata by:
RMAN> list backup of archivelog all completed between '2012-09-23 00:00:00' AND '2012-09-24 00:00:00';
List of Backup Sets
===================
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
199745  11.25M     SBT_TAPE    00:00:10     23.09.2012 00:00:47
BP Key: 205852   Status: AVAILABLE  Compressed: NO  Tag: TAG20120923T000036
Handle: a3nlsh95_1_1   Media: J30643
List of Archived Logs in backup set 199745
Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
---- ------- ---------- ------------------- ---------- ---------
1    83839   11343439796011 22.09.2012 23:30:05 11343439805018 23.09.2012 00:00:03
...
3- Whether the sequence number exists in RMAN log file in /nsr/rman/log.
Not surpsrisingly, with parallel to above checking results,
there is not any entry for relevant archivelog sequence in RMAN log file.

4-  Whether the each archivelog was archived, backed up before deleted; by querying production database:
select sequence#,archived,deleted,backup_count, status
from gv$archived_log where sequence#=111430 and inst_id=2;

SEQUENCE#	ARCHIVED	DELETED	BACKUP_COUNT	STATUS
111430	YES	YES	0	D

As a result, we achieve a conclusion that archivelog file was deleted but not backed up because backup_count is 0.

  

And here comes, incomplete Inconsistent Recovery steps, make recovery without archivelogs:

 Restore controlfile and datafile as usual.

 Change parameter file as below.            

oracle@kiev:/tmp>more /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initMYDB.ora
          *._ALLOW_RESETLOGS_CORRUPTION= TRUE
*._ALLOW_READ_ONLY_CORRUPTION=TRUE
*.cluster_database=false
*.audit_file_dest='/u01/app/oracle/admin/MYDB/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_file_record_keep_time=14
*.control_files='/tmp/ctl_mydb_01.dbf'
*.cursor_sharing='FORCE'
*.db_block_size=32768
*.db_cache_size=0
*.db_create_file_dest='/zfssa/restore1'
*.db_domain=''
*.db_name='MYDB'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MYDBXDB)'
*.log_archive_dest_1='LOCATION=/zfssa/restore1'
*.memory_target=0
*.open_cursors=300
*.parallel_force_local=FALSE
*.parallel_max_servers=256
*.pga_aggregate_target=1212254720
*.processes=1000
*.remote_login_passwordfile='exclusive'
*.sessions=1105
*.sga_max_size=107374182400
*.sga_target=107374182400
*.shared_pool_size=34359738368
*.undo_tablespace='UNDOTBS1'
*.UNDO_MANAGEMENT=MANUAL

oracle@kiev:/tmp>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 9 19:41:37 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initMYDB.ora';

ORACLE instance started.

Total System Global Area 1,0689E+11 bytes
Fixed Size                2240984 bytes
Variable Size             3,4897E+10 bytes
Database Buffers          7,1941E+10 bytes
Redo Buffers              55267328 bytes

SQL> @bckcf.ctl
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
Database altered.

SQL> alter database open resetlogs upgrade;
Database altered.

Controlfile change content is like below:

oracle@kiev:/tmp>cat bckcf.ctl
-- End of tempfile additions.
--
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- WARNING! The current control file needs to be checked against
-- the datafiles to insure it contains the correct files. The
-- commands printed here may be missing log and/or data files.
-- Another report should be made after the database has been
-- successfully opened.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MYDB" RESETLOGS  ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 34824
LOGFILE
GROUP 5 '/zfssa/restore1/redolog5.dbf'  SIZE 500M BLOCKSIZE 512,
GROUP 6 '/zfssa/restore1/redolog6.dbf'  SIZE 500M BLOCKSIZE 512,
GROUP 7 '/zfssa/restore1/redolog7.dbf'  SIZE 500M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/zfssa/restore1/SYSTEM1.dbf',
'/zfssa/restore1/SYSAUX2.dbf',
'/zfssa/restore1/UNDOTBS3.dbf',
'/zfssa/restore1/UNDOTBS9.dbf',
'/zfssa/restore1/UNDOTBS4.dbf',
'/zfssa/restore1/UNDOTBS7.dbf',
'/zfssa/restore1/UNDOTBS8.dbf',
'/zfssa/restore1/UNDOTBS6.dbf',
'/zfssa/restore1/SYSAUX1.dbf',
'/zfssa/restore1/UNDOTBS2.dbf',
'/zfssa/restore1/UNDOTBS1.dbf',
'/zfssa/restore1/TBS_DWH_NODI_B.dbf'
CHARACTER SET WE8ISO8859P9;
-- Create log files for threads other than thread one.
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 8 '/zfssa/restore1/redolog8.dbf' SIZE 500M BLOCKSIZE 512 REUSE,
GROUP 9 '/zfssa/restore1/redolog9.dbf' SIZE 500M BLOCKSIZE 512 REUSE,
GROUP 10 '/zfssa/restore1/redolog10.dbf' SIZE 500M BLOCKSIZE 512 REUSE;
-- Database can now be opened zeroing the online logs.
--ALTER DATABASE OPEN RESETLOGS;
- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.