6. Activating standby
Following steps can be taken to activate the standby and opening db instance in read write mode
SQL> select name,open_mode,database_role ,SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS from v$database;
NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR GUARD_S
------------ ------------ -------------------- -------- -------
TESTDBPRD MOUNTED PHYSICAL STANDBY NOT ALLOWED DISABLED NONE
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> SQL> alter database recover managed standby database finish;
Database altered.
SQL> alter database activate physical standby database;
Database altered.
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
------------ ----------------- ----------------
TESTDBPRD MOUNTED PRIMARY
SQL> alter database open;
Database altered.
7. Pre-requisites of 12c upgrade
srvctl stop instance -d TESTDBPRD -i NEWDBPRD2
Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) (Doc ID 1503653.1)
[oracle@testlabdb005a backup]$ srvctl start instance -d TESTDBPRD -i NEWDBPRD2
SQL> select count(1),owner from dba_objects where status <> 'VALID' group by owner;
On new primary testlabdb005a (matching)
COUNT(1) OWNER
---------- -------------
15 CORET
2 ASC_ACE_IP
5 DMSYS
1 QS_ADM
89 PUBLIC
7 ASC_ACE_TX
20 NETX
10 NETXDEV
1 ASC_SIA
3 SYSTEM
2 ASC_ACE_TEMP_TABLE
16 DATALOAD
4 OE
16 SYSMAN
3 NETXDR
1 ODM
7 CORETAQ1
3 OMA
1442 SYS
143 MSPAK
15 ASC_RF
21 rows selected.
On testoldlabdb1:
COUNT(1) OWNER
---------- --------------
15 CORET
2 ASC_ACE_IP
5 DMSYS
1 QS_ADM
89 PUBLIC
8 ASC_ACE_TX
20 NETX
10 NETXDEV
2 ASC_SIA
3 SYSTEM
2 ASC_ACE_TEMP_TABLE
16 DATALOAD
4 OE
16 SYSMAN
3 NETXDR
2 ASC_ACE_TEMP_TABLE
16 DATALOAD
4 OE
16 SYSMAN
3 NETXDR
1 ODM
7 CORETAQ1
3 OMA
1442 SYS
143 MSPAK
15 ASC_RF
21 rows selected.
i. wri$_optstat_histgrm_history purging
select segment_name,bytes/1024/1024 from dba_segments where segment_name in ('WRI$_OPTSTAT_HISTHEAD_HISTORY','WRI$_OPTSTAT_HISTGRM_HISTORY');
SEGMENT_NAME BYTES/1024/1024
---------------------------- ---------------
WRI$_OPTSTAT_HISTHEAD_HISTORY 7431
WRI$_OPTSTAT_HISTGRM_HISTORY 19379
select count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY; -- 124808149
select count(1) from WRI$_OPTSTAT_HISTGRM_HISTORY; -- 460134614
select count(1) from dba_tab_partitions where table_name='WRI$_OPTSTAT_HISTGRM_HISTORY';
select count(1) from dba_tab_partitions where table_name='WRI$_OPTSTAT_HISTHEAD_HISTORY';
-- for 15 days older purging
exec dbms_stats.purge_stats(trunc(sysdate-15));
select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
select count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY;
select count(1) from WRI$_OPTSTAT_HISTGRM_HISTORY;
ii. Executed sysaux partitioning (to fix sysaux high growth issue)select 1 from dual;
alter session set "_swrf_test_action" = 72;
iii. Gather dictionary stats
EXECUTE dbms_stats.gather_dictionary_stats;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
iv. Purge dba_recyclebin
sys@TESTDBPRD> purge dba_recyclebin;
3 OMA
1442 SYS
143 MSPAK
15 ASC_RF
21 rows selected.
i. wri$_optstat_histgrm_history purging
select segment_name,bytes/1024/1024 from dba_segments where segment_name in ('WRI$_OPTSTAT_HISTHEAD_HISTORY','WRI$_OPTSTAT_HISTGRM_HISTORY');
SEGMENT_NAME BYTES/1024/1024
---------------------------- ---------------
WRI$_OPTSTAT_HISTHEAD_HISTORY 7431
WRI$_OPTSTAT_HISTGRM_HISTORY 19379
select count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY; -- 124808149
select count(1) from WRI$_OPTSTAT_HISTGRM_HISTORY; -- 460134614
select count(1) from dba_tab_partitions where table_name='WRI$_OPTSTAT_HISTGRM_HISTORY';
select count(1) from dba_tab_partitions where table_name='WRI$_OPTSTAT_HISTHEAD_HISTORY';
-- for 15 days older purging
exec dbms_stats.purge_stats(trunc(sysdate-15));
select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
select count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY;
select count(1) from WRI$_OPTSTAT_HISTGRM_HISTORY;
ii. Executed sysaux partitioning (to fix sysaux high growth issue)select 1 from dual;
alter session set "_swrf_test_action" = 72;
iii. Gather dictionary stats
EXECUTE dbms_stats.gather_dictionary_stats;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
iv. Purge dba_recyclebin
sys@TESTDBPRD> purge dba_recyclebin;
DBA Recyclebin purged.
v. Pre-upgrade script execution
SQL>@/golden/app/oracle/11.2.0.3.0/db/cfgtoollogs/TESTDBPRD/preupgrade/preupgrade_fixups.sql
$ORACLE_HOME/olap/admin/catnoamd.sql
to drop olapsys
sys@TESTDBPRD> @/rdbms/app/oracle/12.1.0.2/db/rdbms/admin/preupgrd.sql
Loading Pre-Upgrade Package...
************************************************************
Executing Pre-Upgrade Checks in TESTDBPRD...
************************************************************
************************************************************
====>> ERRORS FOUND for TESTDBPRD <<====
The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
prior to attempting your upgrade.
Failure to do so will result in a failed upgrade.
You MUST resolve the above errors prior to upgrade
************************************************************
************************************************************
====>> PRE-UPGRADE RESULTS for TESTDBPRD <<====
ACTIONS REQUIRED:
1. Review results of the pre-upgrade checks:
/golden/app/oracle/11.2.0.3.0/db/cfgtoollogs/TESTDBPRD/preupgrade/preupgrade.log
2. Execute in the SOURCE environment BEFORE upgrade:
/golden/app/oracle/11.2.0.3.0/db/cfgtoollogs/TESTDBPRD/preupgrade/preupgrade_fixups.sql
3. Execute in the NEW environment AFTER upgrade:
/golden/app/oracle/11.2.0.3.0/db/cfgtoollogs/TESTDBPRD/preupgrade/postupgrade_fixups.sql
************************************************************
************************************************************
Pre-Upgrade Checks in TESTDBPRD Completed.
************************************************************
************************************************************
INFORMATION: --> Older Timezone in use
Database is using a time zone file older than version 18.
After the upgrade, it is recommended that DBMS_DST package
be used to upgrade the 11.2.0.3.0 database time zone version
to the latest version which comes with the new release.
Please refer to My Oracle Support note number 1509653.1 for details.
sys@TESTDBPRD> SELECT version FROM v$timezone_file;
14
Note : 1665676.1
If your current timezone version is lower than 18 (typically 4, 11 or 14) when upgrading from 10.2.0.5, 11.1.0.7, 11.2.0.2, 11.2.0.3 , 11.2.0.4 or 12.1.0.1 to 12.1.0.2 :
If the source database is using a timezone file older than version 18 then the preupgrd.sql (Pre-Upgrade Information Tool) will generate the following warning in the preupgrd.log (this is normal)
Example output:
Database is using a time zone file older than version 18.
After the upgrade, it is recommended that DBMS_DST package
be used to upgrade the -db version here- database time zone version
to the latest version which comes with the new release.
Please refer to My Oracle Support note number 977512.1 for details.
The 12.1.0.2 RDBMS DST version after the upgrade to 12.1.0.2 will be the same DST version as used in 10.2.0.5 , 11.1.0.7, 11.2.0.2, 11.2.0.3 ,11.2.0.4 or 12.1.0.2.
After the upgrade to 12.1.0.2 you can:
* (recommended) update the 12.1.0.2 database(s) to DSTv18 (standard DST version of 12.1.0.2) by :
or using the upg_tzv_check.sql and upg_tzv_apply.sql scripts in note 1585343.1 Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database .
or following note 1509653.1 Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST from step 3a) using "18" as (<the new DST version number>) in that note.
* (optional) update to a higher DST than 18 version if this is needed.
The latest DST patch and related note on how to apply this is found in Note 412160.1 Updated Time Zones in Oracle Time Zone File patches under "C) Notes covering the current DST available updates".
Simply follow the note for the latest DST update
vi. file status
sys@TESTDBPRD> SELECT * FROM v$recover_file;
no rows selected
sys@TESTDBPRD> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
no rows selected
sys@TESTDBPRD> SELECT * FROM dba_2pc_pending;
no rows selected
sys@TESTDBPRD> SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM'); 2 3
USERNAME DEFAULT_TABLESPACE
---------------- -------------------------
SYSTEM SYSTEM
SYS SYSTEM
SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT group#, member FROM v$logfile;
SQL> Alter system set "_system_trig_enabled"=false scope=spfile;
-- to be enabled post upgrade
sys@TESTDBPRD> alter trigger sys.GGS_DDL_TRIGGER_BEFORE disable ;
Trigger altered.
SQL> ALTER SYSTEM SET java_jit_enabled=FALSE;
-- to be enabled post upgrade
System altered.
SQL> alter system set java_pool_size=5G scope=spfile sid='*';
System altered.
SQL>alter system set job_queue_processes=0;
SQL> alter system set db_recovery_file_dest_size=300g;
-- to be set to 10 post upgrade
vi. Verify free temp/undo space.. Add 20-30G space in TEMP
vii. Drop DB links post script backup
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;
select OWNER,DB_LINK,USERNAME,HOST from dba_db_links
drop database link CMSPROD_TEST;
viii. Set job_queue_process to Zero
ix. SQL>ALTER SYSTEM SET COMPATIBLE = '12.1.0' SCOPE=SPFILE;Disable archive log & cluster:
sys@TESTDBPRD> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TESTDBPRD> startup mount
ORACLE instance started.
Total System Global Area 4.8103E+10 bytes
Fixed Size 2239376 bytes
Variable Size 4563403888 bytes
Database Buffers 4.3487E+10 bytes
Redo Buffers 50479104 bytes
Database mounted.
sys@TESTDBPRD> alter database noarchivelog;
Database altered.
sys@TESTDBPRD> alter database open;
Database altered.
sys@TESTDBPRD> alter system set cluster_database=FALSE scope=spfile;
System altered.
sys@TESTDBPRD> alter system set resumable_timeout=36000 scope=spfile;
--To be removed post upgrade
System altered.
SQL> alter system set db_securefile=PERMITTED scope=spfile;
v. Pre-upgrade script execution
SQL>@/golden/app/oracle/11.2.0.3.0/db/cfgtoollogs/TESTDBPRD/preupgrade/preupgrade_fixups.sql
$ORACLE_HOME/olap/admin/catnoamd.sql
to drop olapsys
sys@TESTDBPRD> @/rdbms/app/oracle/12.1.0.2/db/rdbms/admin/preupgrd.sql
Loading Pre-Upgrade Package...
************************************************************
Executing Pre-Upgrade Checks in TESTDBPRD...
************************************************************
************************************************************
====>> ERRORS FOUND for TESTDBPRD <<====
The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
prior to attempting your upgrade.
Failure to do so will result in a failed upgrade.
You MUST resolve the above errors prior to upgrade
************************************************************
************************************************************
====>> PRE-UPGRADE RESULTS for TESTDBPRD <<====
ACTIONS REQUIRED:
1. Review results of the pre-upgrade checks:
/golden/app/oracle/11.2.0.3.0/db/cfgtoollogs/TESTDBPRD/preupgrade/preupgrade.log
2. Execute in the SOURCE environment BEFORE upgrade:
/golden/app/oracle/11.2.0.3.0/db/cfgtoollogs/TESTDBPRD/preupgrade/preupgrade_fixups.sql
3. Execute in the NEW environment AFTER upgrade:
/golden/app/oracle/11.2.0.3.0/db/cfgtoollogs/TESTDBPRD/preupgrade/postupgrade_fixups.sql
************************************************************
************************************************************
Pre-Upgrade Checks in TESTDBPRD Completed.
************************************************************
************************************************************
INFORMATION: --> Older Timezone in use
Database is using a time zone file older than version 18.
After the upgrade, it is recommended that DBMS_DST package
be used to upgrade the 11.2.0.3.0 database time zone version
to the latest version which comes with the new release.
Please refer to My Oracle Support note number 1509653.1 for details.
sys@TESTDBPRD> SELECT version FROM v$timezone_file;
14
Note : 1665676.1
If your current timezone version is lower than 18 (typically 4, 11 or 14) when upgrading from 10.2.0.5, 11.1.0.7, 11.2.0.2, 11.2.0.3 , 11.2.0.4 or 12.1.0.1 to 12.1.0.2 :
If the source database is using a timezone file older than version 18 then the preupgrd.sql (Pre-Upgrade Information Tool) will generate the following warning in the preupgrd.log (this is normal)
Example output:
Database is using a time zone file older than version 18.
After the upgrade, it is recommended that DBMS_DST package
be used to upgrade the -db version here- database time zone version
to the latest version which comes with the new release.
Please refer to My Oracle Support note number 977512.1 for details.
The 12.1.0.2 RDBMS DST version after the upgrade to 12.1.0.2 will be the same DST version as used in 10.2.0.5 , 11.1.0.7, 11.2.0.2, 11.2.0.3 ,11.2.0.4 or 12.1.0.2.
After the upgrade to 12.1.0.2 you can:
* (recommended) update the 12.1.0.2 database(s) to DSTv18 (standard DST version of 12.1.0.2) by :
or using the upg_tzv_check.sql and upg_tzv_apply.sql scripts in note 1585343.1 Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database .
or following note 1509653.1 Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST from step 3a) using "18" as (<the new DST version number>) in that note.
* (optional) update to a higher DST than 18 version if this is needed.
The latest DST patch and related note on how to apply this is found in Note 412160.1 Updated Time Zones in Oracle Time Zone File patches under "C) Notes covering the current DST available updates".
Simply follow the note for the latest DST update
vi. file status
sys@TESTDBPRD> SELECT * FROM v$recover_file;
no rows selected
sys@TESTDBPRD> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
no rows selected
sys@TESTDBPRD> SELECT * FROM dba_2pc_pending;
no rows selected
sys@TESTDBPRD> SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM'); 2 3
USERNAME DEFAULT_TABLESPACE
---------------- -------------------------
SYSTEM SYSTEM
SYS SYSTEM
SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT group#, member FROM v$logfile;
SQL> Alter system set "_system_trig_enabled"=false scope=spfile;
-- to be enabled post upgrade
sys@TESTDBPRD> alter trigger sys.GGS_DDL_TRIGGER_BEFORE disable ;
Trigger altered.
SQL> ALTER SYSTEM SET java_jit_enabled=FALSE;
-- to be enabled post upgrade
System altered.
SQL> alter system set java_pool_size=5G scope=spfile sid='*';
System altered.
SQL>alter system set job_queue_processes=0;
SQL> alter system set db_recovery_file_dest_size=300g;
-- to be set to 10 post upgrade
vi. Verify free temp/undo space.. Add 20-30G space in TEMP
vii. Drop DB links post script backup
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;
select OWNER,DB_LINK,USERNAME,HOST from dba_db_links
drop database link CMSPROD_TEST;
viii. Set job_queue_process to Zero
ix. SQL>ALTER SYSTEM SET COMPATIBLE = '12.1.0' SCOPE=SPFILE;Disable archive log & cluster:
sys@TESTDBPRD> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TESTDBPRD> startup mount
ORACLE instance started.
Total System Global Area 4.8103E+10 bytes
Fixed Size 2239376 bytes
Variable Size 4563403888 bytes
Database Buffers 4.3487E+10 bytes
Redo Buffers 50479104 bytes
Database mounted.
sys@TESTDBPRD> alter database noarchivelog;
Database altered.
sys@TESTDBPRD> alter database open;
Database altered.
sys@TESTDBPRD> alter system set cluster_database=FALSE scope=spfile;
System altered.
sys@TESTDBPRD> alter system set resumable_timeout=36000 scope=spfile;
--To be removed post upgrade
System altered.
SQL> alter system set db_securefile=PERMITTED scope=spfile;
No comments:
Post a Comment