upgrade database from 11.2.0.3 to 11.2.0.4 when recovered using standby controlfile

All DBAs are hands on with backup/restore and upgrade scenarios. Here we will discuss how we will take backup without any corrupted block and to restore as is or corrupt free process. Rather than backup control-file we will take backup of standby control file and restore from this.

Someone ask why? This way we ll transfer archivelogs and keep on applying manually with will of dataguad commands which are common to all DBAs. So that we can take very less downtime to finish the upgrade process.

So, here are the step-by-step process :

Phase-1 :-- Restore and Recover ( Using standby controlfile)

a) Restore database
Precautions to restore to avoid block corruption:

1) Enable force-logging the primary database.

-- To enable
SQL> ALTER DATABASE FORCE LOGGING;

-- To verify
SQL> select force_logging from v$database;

FOR
---
YES

Note: The FORCE LOGGING option is the safest method to ensure that all the changes made in the database will be captured and available for recovery in the redo logs. Force logging is the new feature added to the family of logging attributes.

2) validate the database and backups before restore.
-- To validate database:

RMAN> validate database;
OR
RMAN> BACKUP CHECK LOGICAL VALIDATE DATABASE;

To Take logically valid backup:

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

-- To validate specific datafiles:
RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
backup check logical validate datafile x,y,z;
release channel d1;
release channel d2;
}

-- To validate backup piece:
RMAN> VALIDATE BACKUPSET <n>;
e.g.,
RMAN> VALIDATE BACKUPSET 22;

Now in standby restore the database.
RMAN> restore database;

You can follow the below secure process also:

RMAN> RESTORE DATABASE VALIDATE;

Note : use set new name if any datafile path is change.

b) Recover database
Precautions to restore to avoid block corruption:

in standby:

-- To enable 
alter system set DB_BLOCK_CHECKING = TRUE ;
alter system set DB_BLOCK_CHECKSUM = TRUE ;
alter system set "_DB_BLOCK_CHECK_FOR_DEBUG" = TRUE;

-- To disable ( must be disable when standby is open for production in r/w operation)

alter system set DB_BLOCK_CHECKING = FALSE ;
alter system set DB_BLOCK_CHECKSUM = FALSE ;
alter system set "_DB_BLOCK_CHECK_FOR_DEBUG" = FALSE;

-- now start MRP
SQL> alter database recover managed standby database disconnect from session;

-- Catalog archivelog location to recover
RMAN> catalog '/u03/backup_logs/archivelogs';

Phase-2 :-- Stop synch

-- To see the current database status, role

SQL> select name,open_mode,database_role from v$database;

-- Cancel the recovery after applying all redo logs

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;


Phase-3 :-- Convert to primary

SQL> select name,open_mode,database_role from v$database;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

SQL> select name,open_mode ,database_role from v$database;
SQL> select count(1) from dba_objects where status='INVALID';

Phase-4 :-- Upgrade
SQL> connect / as sysdba
SQL> shut immediate;
SQL> startup upgrade;
----------verify the registry before catupgrd--------
SQL> set line 120;
SQL> column COMP_NAME format a35;
SQL> column VERSION format a10;
SQL> column status format a10;
SQL> select COMP_NAME, VERSION,status from dba_registry;
----------------------------------------------------
SQL> @?/rdbms/admin/catupgrd.sql   (default)
SQL> @?/rdbms/admin/utlrp.sql     (default)
SQL> @/home/oracle/work_dir/dbupgdiag.sql (downloaded)
SQL> @?/rdbms/admin/utlrp.sql     (default) -- once more
SQL> shut immediate;
SQL> startup;

----------verify the registry after catupgrd--------
SQL> set line 120;
SQL> column COMP_NAME format a35;
SQL> column VERSION format a10;
SQL> column status format a10;
SQL> select COMP_NAME, VERSION,status from dba_registry;
----------------------------------------------------

SQL> select name,open_mode ,database_role from v$database;
SQL> select count(1) from dba_objects where status='INVALID';

------------------commands ------------------------------------

-- standby

SQL> select name,open_mode ,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD  MOUNTED              PHYSICAL STANDBY

SQL> select max(sequence#),thread# 
from v$archived_log 
where applied='YES' group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
         39691          1


-- primary


SQL> select max(sequence#),thread# from v$archived_log where group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
         39691          1

SQL> select count(1) from dba_objects where status='INVALID';

  COUNT(1)
----------
      1264

-- standby

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD      MOUNTED              PHYSICAL STANDBY

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD      MOUNTED              PRIMARY

SQL>
SQL> shut immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup upgrade;
ORACLE instance started.

.....
Database mounted.
Database opened.
SQL>

SQL> set line 120;
column COMP_NAME format a35;
column VERSION format a10;
column status format a10;
select COMP_NAME, VERSION,status from dba_registry;
SQL>
COMP_NAME                           VERSION    STATUS
----------------------------------- ---------- ----------
Oracle Enterprise Manager           11.2.0.3.0 VALID
Oracle XML Database                 11.2.0.3.0 VALID
Oracle Expression Filter            11.2.0.3.0 VALID
Oracle Rules Manager                11.2.0.3.0 VALID
Oracle Workspace Manager            11.2.0.3.0 VALID
Oracle Database Catalog Views       11.2.0.3.0 VALID
Oracle Database Packages and Types  11.2.0.3.0 VALID
JServer JAVA Virtual Machine        11.2.0.3.0 VALID
Oracle XDK                          11.2.0.3.0 VALID
Oracle Database Java Packages       11.2.0.3.0 VALID

10 rows selected.

SQL>

SQL> select count(1) from dba_objects where status='INVALID';  -- this count will vary

  COUNT(1)
----------
      1328

SQL> @?/rdbms/admin/catupgrd.sql
SQL> @?/rdbms/admin/catuppst.sql

SQL> startup;
ORACLE instance started.
.....
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> set line 120;
SQL> column COMP_NAME format a35;
SQL> column VERSION format a10;
SQL> column status format a10;
SQL> select COMP_NAME, VERSION,status from dba_registry;
COMP_NAME                           VERSION    STATUS
----------------------------------- ---------- ----------
Oracle Enterprise Manager           11.2.0.4.0 VALID
Oracle XML Database                 11.2.0.4.0 VALID
Oracle Expression Filter            11.2.0.4.0 VALID
Oracle Rules Manager                11.2.0.4.0 VALID
Oracle Workspace Manager            11.2.0.4.0 VALID
Oracle Database Catalog Views       11.2.0.4.0 VALID
Oracle Database Packages and Types  11.2.0.4.0 VALID
JServer JAVA Virtual Machine        11.2.0.4.0 VALID
Oracle XDK                          11.2.0.4.0 VALID
Oracle Database Java Packages       11.2.0.4.0 VALID

10 rows selected.
SQL>
and then,

@?/rdbms/admin/utlrp.sql
@/home/oracle/work_dir/dbupgdiag.sql
SQL> select count(1) from dba_objects where status='INVALID';  
-- this count will vary

  COUNT(1)
----------
       393

1 row selected.

SQL>

Note: Invalid objects counts must be less or equal after upgrade. Check owner-wise valid objects counts also before and after upgrade.

-- Post upgrade activity -- if live for production

a) create/ reconfigure listener
b) create/ modify tnsnames.ora
c) gather stats:
   1) gather all schema's stats:
        SQL> exec dbms_stats.gather_table_stats('owner','table_name',cascade => TRUE);
Best practice after upgrade- gather tables like below:
SQL>
exec dbms_stats.gather_table_stats(ownname=>'user_name', 
tabname=>'table_name', 
estimate_percent => 100, 
cascade=>true, 
method_opt=>'for all columns size AUTO'); 

   2) gather sys views and fixed objects:
   SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; 
    SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; 

d) disable below parameters:( enable if you compromise performance):
-- To disable ( must be disable when standby is open for production in r/w operation)

alter system set DB_BLOCK_CHECKING = FALSE ;
alter system set DB_BLOCK_CHECKSUM = FALSE ;
alter system set "_DB_BLOCK_CHECK_FOR_DEBUG" = FALSE;

e) Take FULL rman backup before handover as production.

Note: Some Oracle 11.2.0.x bug fixes for AIX platform:

1) to get better execution plan: If your performance degraded, then OFF it and try.

SQL> ALTER session "_fix_control"='5483301:OFF'; 

2) Stop weekend oracle default maintenance windows

Thanks!!