Friday, January 29, 2016
SYED DATAGUARD UPGRADE STEPS
Traditional Method
=====================
1)make sure both primary and stanndby are in sync
2)stop mrp
3)install oracle higher binaryone new oracle home both primary and standby
4)stop both primary and standby
5)restart the standby pointing to new oracle home
6) start media recovery
7) start primary database from newhome startup upgarde mode
8)run the catupgrade.sql
9)make sure the redo is applied on the standby
1)make sure both primary and stanndby are in sync
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 99
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 99
SQL>
2)stop mrp
3)install oracle higher binaryone new oracle home both primary and standby
Installing oracle binaries on both primary and standy one new home
create new oraclehome in both primary standby
[oracle@primary ~]$ mkdir -p /u01/app/oracle/product/11.2.0/db_2
copy
unzip software on both primary and standby database
[oracle@standby software]$ ls -altr
total 2489648
-rw-r--r-- 1 oracle oinstall 1395582860 Jan 23 13:16 p13390677_112040_Linux-x86-64_1of7.zip
-rw-r--r-- 1 oracle oinstall 1151304589 Jan 23 13:26 p13390677_112040_Linux-x86-64_2of7.zip
drwxr-xr-x 4 oracle oinstall 4096 Jan 27 19:56 ..
drwxr-xr-x 2 oracle oinstall 4096 Jan 27 21:25 .
[oracle@standby software]$ unzip p13390677_112040_Linux-x86-64_1of7.zip
[oracle@primary software]$ unzip p13390677_112040_Linux-x86-64_2of7.zip
total 2489644
drwxr-xr-x 7 oracle oinstall 4096 Aug 27 2013 database
-rw-r--r-- 1 oracle oinstall 1395582860 Jan 23 13:16 p13390677_112040_Linux-x86-64_1of7.zip
-rw-r--r-- 1 oracle oinstall 1151304589 Jan 23 13:26 p13390677_112040_Linux-x86-64_2of7.zip
[oracle@primary software]$ cd database
[oracle@primary database]$ ls -ltr
total 60
drwxr-xr-x 2 oracle oinstall 4096 Aug 27 2013 sshsetup
-rwxr-xr-x 1 oracle oinstall 3267 Aug 27 2013 runInstaller
drwxr-xr-x 2 oracle oinstall 4096 Aug 27 2013 rpm
drwxr-xr-x 2 oracle oinstall 4096 Aug 27 2013 response
-rw-r--r-- 1 oracle oinstall 30016 Aug 27 2013 readme.html
drwxr-xr-x 14 oracle oinstall 4096 Aug 27 2013 stage
-rw-r--r-- 1 oracle oinstall 500 Aug 27 2013 welcome.html
drwxr-xr-x 4 oracle oinstall 4096 Aug 27 2013 install
[oracle@primary database]$ xhost +
access control disabled, clients can connect from any host
[oracle@primary database]$ ./runInstaller
select next
skip software updates
select next
Install database software only
select next
Single instance
select next
English
select next
Enterprize Edition
select next
software location:/u01/app/oracle/product/11.2.0/db_2
select next
select next
save response file:/u01/db.rsp
select next
click install
run roo.sh after completion
[root@standby ~]# cd /u01/app/oracle/product/11.2.0/db_2/
[root@standby db_2]# ./root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_2
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin ...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
[root@standby db_2]#
make and entry oratab on both primary and standby
primary:/u01/app/oracle/product/11.2.0/db_1:N
primary:/u01/app/oracle/product/11.2.0/db_2:N
standby:/u01/app/oracle/product/11.2.0/db_1:N
standby:/u01/app/oracle/product/11.2.0/db_2:N
checking
primary:/u01/app/oracle/product/11.2.0/db_2:N
[oracle@primary syed]$ cd /u01/app/oracle/product/11.2.0/db_2
[oracle@primary db_2]$ cd bin
[oracle@primary bin]$ ./sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 28 13:11:49 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
[oracle@standby syed]$ . oraenv
ORACLE_SID = [standby] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/ oracle
[oracle@standby syed]$ ps -ef | grep smon
oracle 4483 1 0 08:46 ? 00:00:02 ora_smon_standby
oracle 6298 6183 0 13:14 pts/1 00:00:00 grep smon
[oracle@standby syed]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 28 13:14:55 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
PRIMARY MOUNTED
SQL> !
[oracle@standby syed]$ ps -eaf|grep mrp
oracle 4654 1 0 08:58 ? 00:00:12 ora_mrp0_standby
oracle 6347 6314 0 13:15 pts/1 00:00:00 grep mrp
[oracle@standby syed]$ ps -eaf|grep dmon
oracle 4496 1 0 08:46 ? 00:00:04 ora_dmon_standby
oracle 6360 6314 0 13:16 pts/1 00:00:00 grep dmon
DISABLE BROKER CONFIGURATION ON BOTH PRIMARY AND STANDBY
[oracle@standby syed]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/india123
Connected.
DGMGRL> show configuration
Configuration - dg_config
Protection Mode: MaxPerformance
Databases:
primary - Primary database
standby - Physical standby database
Warning: ORA-16826: apply service state is inconsistent with the DelayMins property
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
DGMGRL> disable configuration;
Disabled.
DGMGRL> show configuration
ORA-16541: database is not enabled
Configuration
on primary
[oracle@primary db_1]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/india123
Connected.
DGMGRL> show configuration
Configuration - dg_config
Protection Mode: MaxPerformance
Databases:
primary - Primary database
standby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> disable configuration;
Disabled.
DGMGRL> show configuration
Configuration - dg_config
Protection Mode: MaxPerformance
Databases:
primary - Primary database
standby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 100
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /syed/arch
Oldest online log sequence 99
Next log sequence to archive 101
Current log sequence 101
SQL>
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 100
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL>
2)stop mrp on stadby and shutdown
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shutdown immediate;
4)shutdown primary and if require do pre requite check
Current log sequence 101
SQL> @/u01/app/oracle/product/11.2.0/db_2/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 01-28-2016 14:07:45
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: PRIMARY
--> version: 11.2.0.1.0
--> compatible: 11.2.0.0.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V11
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 892 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 613 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 78 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle Application Express [upgrade] VALID
... APEX will only be upgraded if the version of APEX in
... the target Oracle home is higher than the current one.
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 11.2.0.1.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Sync standby database prior to upgrade.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER APEX_030200 has dependent objects.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'
Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
SQL> PURGE DBA_RECYCLEBIN;
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
SQL>
SQL> create pfile='/tmp/initprimary.ora' from spfile;
SQL>
keep the standby in mount and let mrp to work
SQL> alter database recover managed standby database disconnect from session;
Database altered.
on primary
NOW CHANGE ORACLE_HOME IN PRIAMRY AND STANDY IN ORATAB COMMENT OLD ONE
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /syed/arch
Oldest online log sequence 100
Next log sequence to archive 102
Current log sequence 102
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 101
SQL>
on standby
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 101
SQL>
shutdown primary
SQL> shutdown immediate;
/u01/app/oracle/diag/rdbms/primary/primary/trace
SHUTDOWN PRIMARY FIRST AND THEN STANDBY
WHILE OPENING
START STANDBY FIRST THEN PRIMARY
copy following files from old oraclehome to new oraclehome
[oracle@primary admin]$ cp listener.ora /u01/app/oracle/product/11.2.0/db_2/network/admin/
[oracle@primary admin]$ cp tnsnames.ora /u01/app/oracle/product/11.2.0/db_2/network/admin/
[oracle@primary dbs]$ cp initprimary.ora /u01/app/oracle/product/11.2.0/db_2/dbs/
[oracle@primary dbs]$ cp orapwprimary /u01/app/oracle/product/11.2.0/db_2/dbs/
[oracle@primary dbs]$ cp spfileprimary.ora /u01/app/oracle/product/11.2.0/db_2/dbs/
[oracle@prima
[oracle@standby dbs]$ cp orapwprimary /u01/app/oracle/product/11.2.0/db_2/dbs/
[oracle@standby dbs]$ cp orapwstandby /u01/app/oracle/product/11.2.0/db_2/dbs/
[oracle@standby dbs]$ cp initstandby.ora /u01/app/oracle/product/11.2.0/db_2/dbs/
[oracle@standby dbs]$ cp spfilestandby.ora /u01/app/oracle/product/11.2.0/db_2/dbs/
[oracle@standby admin]$ cp listener.ora /u01/app/oracle/product/11.2.0/db_2/network/admin/
[oracle@standby admin]$ cp tnsnames.ora /u01/app/oracle/product/11.2.0/db_2/network/admin/
mount standby from neworacle home
[oracle@standby admin]$ . oraenv
ORACLE_SID = [standby] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_2 is /u01/app/oracle
[oracle@standby admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 28 17:06:45 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 726540288 bytes
Fixed Size 2256792 bytes
Variable Size 465567848 bytes
Database Buffers 251658240 bytes
Redo Buffers 7057408 bytes
Database mounted.
SQL>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> !ps -eaf|grep mrp
oracle 10242 1 0 17:10 ? 00:00:00 ora_mrp0_standby
oracle 10270 10082 0 17:11 pts/3 00:00:00 /bin/bash -c ps -eaf|grep mrp
oracle 10272 10270 0 17:11 pts/3 00:00:00 grep mrp
SQL>
start primary database from new oracle home
and make sure you are able to connect to newstandby from primary
[oracle@primary dbs]$ . oraenv
ORACLE_SID = [primary] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_2 is /u01/app/oracle
[oracle@primary dbs]$ sqlplus sys/india123@standby as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 28 17:23:04 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
[oracle@primary dbs]$ . oraenv
ORACLE_SID = [primary] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@primary dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 28 17:35:50 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 726540288 bytes
Fixed Size 2256792 bytes
Variable Size 452984936 bytes
Database Buffers 264241152 bytes
Redo Buffers 7057408 bytes
Database mounted.
Database opened.
SQL> spool '/tmp/upgrade.log';
SQL>@$ORACLE_HOME/rdbms/admin/catupgrd.sql
L> SET ERRORLOGGING OFF;
SQL>
SQL> REM END OF CATUPGRD.SQL
SQL>
SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
SQL> REM This forces user to start a new sqlplus session in order
SQL> REM to connect to the upgraded db.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@primary dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 28 19:28:35 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 726540288 bytes
Fixed Size 2256792 bytes
Variable Size 662700136 bytes
Database Buffers 54525952 bytes
Redo Buffers 7057408 bytes
Database mounted.
Database opened.
SQL> @/u01/app/oracle/product/11.2.0/db_2/rdbms/admin/catuppst.sql;
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2016-01-28 19:31:03
PL/SQL procedure successfully completed.
This script will migrate the Baseline data on a pre-11g database
to the 11g database.
... ...
... Completed Moving the Baseline Data ...
... ...
... If there are no Move BL Data messages ...
... above, then there are no renamed ...
... baseline tables in the system. ...
... ...
... ...
... Completed the Dropping of the ...
... Renamed Baseline Tables ...
... ...
... If there are no Drop Table messages ...
... above, then there are no renamed ...
... baseline tables in the system. ...
... ...
PL/SQL procedure successfully completed.
0 rows created.
Commit complete.
Table created.
2 rows created.
1 row updated.
2 rows updated.
0 rows updated.
Table dropped.
Commit complete.
0 rows updated.
Commit complete.
0 rows updated.
Commit complete.
0 rows updated.
Commit complete.
0 rows created.
Commit complete.
0 rows created.
Commit complete.
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2016-01-28 19:31:05
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Generating apply and rollback scripts...
Check the following file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_PRIMARY_GENERATE_2016Jan28_19_31_16.log
Apply script: /u01/app/oracle/product/11.2.0/db_2/rdbms/admin/catbundle_PSU_PRIMARY_APPLY.sql
Rollback script: /u01/app/oracle/product/11.2.0/db_2/rdbms/admin/catbundle_PSU_PRIMARY_ROLLBACK.sql
PL/SQL procedure successfully completed.
Executing script file...
SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/u01/app/oracle/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;
SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')
PL/SQL procedure successfully completed.
SQL> ALTER SESSION SET current_schema = SYS;
Session altered.
SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, 'APPLY',
7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
8 '11.2.0.4',
9 0,
10 'PSU',
11 'Patchset 11.2.0.2.0');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_PRIMARY_APPLY_2016Jan28_19_31_18.log
SQL> @/u01/app/oracle/product/11.2.0/db_2/rdbms/admin/utlrp.sql;
SQL> @/u01/app/oracle/product/11.2.0/db_2/rdbms/admin/utlrp.sql;
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2016-01-28 19:33:12
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2016-01-28 19:35:28
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL> SQL> alter system set compatible="11.2.0.4.0" scope=spfile;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_2/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/primary/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary.gmail.com)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=primary.gmail.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 28-JAN-2016 22:32:07
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_2/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/primary/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary.gmail.com)(PORT=1521)))
Services Summary...
Service "primary_DGMGRL.gmail.com" has 1 instance(s).
Instance "primary", status UNKNOWN, has 1 handler(s) for this service...
Service "standby_DGMGRL.gmail.com" has 1 instance(s).
Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@primary dbs]$
oracle@primary dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 28 22:34:57 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 726540288 bytes
Fixed Size 2256792 bytes
Variable Size 452984936 bytes
Database Buffers 264241152 bytes
Redo Buffers 7057408 bytes
Database mounted.
Database opened.
SQL> select comp_name,version,status from dba_registry;
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
OWB
11.2.0.1.0 VALID
Oracle Application Express
3.2.1.00.10 VALID
Oracle Enterprise Manager
11.2.0.4.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
OLAP Catalog
11.2.0.4.0 VALID
Spatial
11.2.0.4.0 VALID
Oracle Multimedia
11.2.0.4.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle XML Database
11.2.0.4.0 VALID
Oracle Text
11.2.0.4.0 VALID
Oracle Expression Filter
11.2.0.4.0 VALID
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
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
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
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
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle Database Java Packages
11.2.0.4.0 VALID
OLAP Analytic Workspace
11.2.0.4.0 VALID
Oracle OLAP API
11.2.0.4.0 VALID
18 rows selected.
SQL>
[oracle@primary dbs]$ cd /u01/app/oracle/product/11.2.0/db_1/deinstall/
[oracle@primary deinstall]$ ls -altr
total 76
-rwxr-xr-x 1 oracle oinstall 31544 Mar 31 2009 sshUserSetup.sh
-rw-r--r-- 1 oracle oinstall 3154 Jul 13 2009 deinstall.xml
drwxr-xr-x 2 oracle oinstall 4096 Dec 31 15:24 response
drwxr-xr-x 2 oracle oinstall 4096 Dec 31 15:24 jlib
-rwxr-xr-x 1 oracle oinstall 20680 Dec 31 15:30 deinstall
drwxr-xr-x 4 oracle oinstall 4096 Dec 31 15:30 .
drwxrwxr-x 74 oracle oinstall 4096 Dec 31 16:38 ..
[oracle@primary deinstall]$ ./deinstall
Checking for required files and bootstrapping ...
Please wa
Location of logs /u01/app/oraInventory/logs/
############ ORACLE DEINSTALL & DECONFIG TOOL START ############
######################## CHECK OPERATION START ########################
Install check configuration START
Checking for existence of the Oracle home location /u01/app/oracle/product/11.2.0/db_1
Oracle Home type selected for de-install is: SIDB
Oracle Base selected for de-install is: /u01/app/oracle
Checking for existence of central inventory location /u01/app/oraInventory
Install check configuration END
Network Configuration check config START
Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_check6262605909094203008.log
Specify all Single Instance listeners that are to be de-configured [LISTENER]:
Network Configuration check config END
Database Check Configuration START
Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_check4488507332348741516.log
Use comma as separator when specifying list of values as input
Specify the list of database names that are configured in this Oracle home []:
Database Check Configuration END
Enterprise Manager Configuration Assistant START
EMCA de-configuration trace file location: /u01/app/oraInventory/logs/emcadc_check.log
Enterprise Manager Configuration Assistant END
Oracle Configuration Manager check START
OCM check log file location : /u01/app/oraInventory/logs//ocm_check2285.log
Oracle Configuration Manager check END
######################### CHECK OPERATION END #########################
####################### CHECK OPERATION SUMMARY #######################
Oracle Home selected for de-install is: /u01/app/oracle/product/11.2.0/db_1
Inventory Location where the Oracle home registered is: /u01/app/oraInventory
Following Single Instance listener(s) will be de-configured: LISTENER
No Enterprise Manager configuration to be updated for any database(s)
No Enterprise Manager ASM targets to update
No Enterprise Manager listener targets to migrate
Checking the config status for CCR
Oracle Home exists with CCR directory, but CCR is not configured
CCR check is finished
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2016-01-28_10-42-13-PM.out'
Any error messages from this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2016-01-28_10-42-13-PM.err'
######################## CLEAN OPERATION START ########################
Enterprise Manager Configuration Assistant START
EMCA de-configuration trace file location: /u01/app/oraInventory/logs/emcadc_clean.log
Updating Enterprise Manager ASM targets (if any)
Updating Enterprise Manager listener targets (if any)
Enterprise Manager Configuration Assistant END
Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_clean7145715995041509164.log
Network Configuration clean config START
Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_clean4679813692535944203.log
De-configuring Single Instance listener(s): LISTENER
De-configuring listener: LISTENER
Stopping listener: LISTENER
Listener stopped successfully.
Deleting listener: LISTENER
Listener deleted successfully.
Listener de-configured successfully.
De-configuring Listener configuration file...
Listener configuration file de-configured successfully.
De-configuring Naming Methods configuration file...
Naming Methods configuration file de-configured successfully.
De-configuring Local Net Service Names configuration file...
Local Net Service Names configuration file de-configured successfully.
De-configuring backup files...
Backup files de-configured successfully.
The network configuration has been cleaned up successfully.
Network Configuration clean config END
Oracle Configuration Manager clean START
OCM clean log file location : /u01/app/oraInventory/logs//ocm_clean2285.log
Oracle Configuration Manager clean END
Oracle Universal Installer clean START
Detach Oracle home '/u01/app/oracle/product/11.2.0/db_1' from the central inventory on the local node : Done
Delete directory '/u01/app/oracle/product/11.2.0/db_1' on the local node : Done
The Oracle Base directory '/u01/app/oracle' will not be removed on local node. The directory is in use by Oracle Home '/u01/app/oracle/product/11.2.0/db_2'.
Oracle Universal Installer cleanup was successful.
Oracle Universal Installer clean END
Oracle install clean START
Clean install operation removing temporary directory '/tmp/install' on node 'primary'
Oracle install clean END
######################### CLEAN OPERATION END #########################
####################### CLEAN OPERATION SUMMARY #######################
Following Single Instance listener(s) were de-configured successfully: LISTENER
Cleaning the config for CCR
As CCR is not configured, so skipping the cleaning of CCR configuration
CCR clean is finished
Successfully detached Oracle home '/u01/app/oracle/product/11.2.0/db_1' from the central inventory on the local node.
Successfully deleted directory '/u01/app/oracle/product/11.2.0/db_1' on the local node.
Oracle Universal Installer cleanup was successful.
Oracle install successfully cleaned up the temporary directories.
#######################################################################
############# ORACLE DEINSTALL & DECONFIG TOOL END #############
[oracle@primary deinstall]$
/u01/app/oracle/product/11.2.0/db_1/deinstall
[oracle@standby deinstall]$ ls -altr
total 76
-rwxr-xr-x 1 oracle oinstall 31544 Mar 31 2009 sshUserSetup.sh
-rw-r--r-- 1 oracle oinstall 3154 Jul 13 2009 deinstall.xml
drwxr-xr-x 2 oracle oinstall 4096 Dec 31 15:24 response
drwxr-xr-x 2 oracle oinstall 4096 Dec 31 15:24 jlib
-rwxr-xr-x 1 oracle oinstall 20680 Dec 31 15:30 deinstall
drwxr-xr-x 4 oracle oinstall 4096 Dec 31 15:30 .
drwxrwxr-x 72 oracle oinstall 4096 Dec 31 15:32 ..
[oracle@standby deinstall]$ ./deinstall
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /u01/app/oraInventory/logs/
############ ORACLE DEINSTALL & DECONFIG TOOL START ############
######################## CHECK OPERATION START ########################
Install check configuration START
Checking for existence of the Oracle home location /u01/app/oracle/product/11.2.0/db_1
Oracle Home type selected for de-install is: SIDB
Oracle Base selected for de-install is: /u01/app/oracle
Checking for existence of central inventory location /u01/app/oraInventory
Install check configuration END
Network Configuration check config START
Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_check1270051826216256347.log
Specify all Single Instance listeners that are to be de-configured [LISTENER]:
Network Configuration check config END
Database Check Configuration START
Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_check7743180026519387660.log
Use comma as separator when specifying list of values as input
Specify the list of database names that are configured in this Oracle home []:
Database Check Configuration END
Enterprise Manager Configuration Assistant START
EMCA de-configuration trace file location: /u01/app/oraInventory/logs/emcadc_check.log
Enterprise Manager Configuration Assistant END
Oracle Configuration Manager check START
OCM check log file location : /u01/app/oraInventory/logs//ocm_check3859.log
Oracle Configuration Manager check END
######################### CHECK OPERATION END #########################
####################### CHECK OPERATION SUMMARY #######################
Oracle Home selected for de-install is: /u01/app/oracle/product/11.2.0/db_1
Inventory Location where the Oracle home registered is: /u01/app/oraInventory
Following Single Instance listener(s) will be de-configured: LISTENER
No Enterprise Manager configuration to be updated for any database(s)
No Enterprise Manager ASM targets to update
No Enterprise Manager listener targets to migrate
Checking the config status for CCR
Oracle Home exists with CCR directory, but CCR is not configured
CCR check is finished
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2016-01-28_11-31-55-PM.out'
Any error messages from this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2016-01-28_11-31-55-PM.err'
######################## CLEAN OPERATION START ########################
Enterprise Manager Configuration Assistant START
EMCA de-configuration trace file location: /u01/app/oraInventory/logs/emcadc_clean.log
Updating Enterprise Manager ASM targets (if any)
Updating Enterprise Manager listener targets (if any)
Enterprise Manager Configuration Assistant END
Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_clean306945389757752590.log
Network Configuration clean config START
Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_clean2701405976048655944.log
De-configuring Single Instance listener(s): LISTENER
De-configuring listener: LISTENER
Stopping listener: LISTENER
Listener stopped successfully.
Deleting listener: LISTENER
Listener deleted successfully.
Listener de-configured successfully.
De-configuring Listener configuration file...
Listener configuration file de-configured successfully.
De-configuring Local Net Service Names configuration file...
Local Net Service Names configuration file de-configured successfully.
De-configuring backup files...
Backup files de-configured successfully.
The network configuration has been cleaned up successfully.
Network Configuration clean config END
Oracle Configuration Manager clean START
OCM clean log file location : /u01/app/oraInventory/logs//ocm_clean3859.log
Oracle Configuration Manager clean END
Oracle Universal Installer clean START
Detach Oracle home '/u01/app/oracle/product/11.2.0/db_1' from the central inventory on the local node : Done
Delete directory '/u01/app/oracle/product/11.2.0/db_1' on the local node : Done
The Oracle Base directory '/u01/app/oracle' will not be removed on local node. The directory is in use by Oracle Home '/u01/app/oracle/product/11.2.0/db_2'.
Oracle Universal Installer cleanup was successful.
Oracle Universal Installer clean END
Oracle install clean START
Clean install operation removing temporary directory '/tmp/install' on node 'standby'
Oracle install clean END
######################### CLEAN OPERATION END #########################
####################### CLEAN OPERATION SUMMARY #######################
Following Single Instance listener(s) were de-configured successfully: LISTENER
Cleaning the config for CCR
As CCR is not configured, so skipping the cleaning of CCR configuration
CCR clean is finished
Successfully detached Oracle home '/u01/app/oracle/product/11.2.0/db_1' from the central inventory on the local node.
Successfully deleted directory '/u01/app/oracle/product/11.2.0/db_1' on the local node.
Oracle Universal Installer cleanup was successful.
Oracle install successfully cleaned up the temporary directories.
#######################################################################
############# ORACLE DEINSTALL & DECONFIG TOOL END #############
[oracle@standby deinstall]$
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment