Steps to create active data guard in 11g and up-gradation to 12c using manual method - II



<< PREV_PAGE                                                                 Page   1   2   3   4


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
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;

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;



>> NEXT_PAGE                                                                        Page   1   2   3   4

No comments:

Post a Comment

Popular Posts