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:
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;
2) Stop weekend oracle default maintenance windows
Thanks!!
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;
-- 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!!
