签到成功

知道了

CNDBA社区CNDBA社区

11gR2 滚动升级到12C

2018-01-25 16:48 4822 2 原创 ORACLE
作者： Marvinn
原文链接：http://www.cndba.cn/Marvinn
本文链接：http://www.cndba.cn/Marvinn/article/2585

11Gr2DG滚动升级12C
第一章 升级准备
1.1 Precondition（先决条件）
1、主备库环境存在
2、闪回功能在主备库中被激活
3、如果data guard broker 管理被配置了，必须禁用这个配置。通过设置初始化参数
DG_BROKER_start=FALSE
4、确保初始化参数log_archive_dest_n配置正确，能进行主备切换
5、在主备库中配置静态listener.ora
6、12C软件安装

1.2升级环境
Primary Standby
Hostsname Rac1
Rac2 11gDG
Database_unique_name orcl standby
Instance_name orcl1
orcl1 standby
Scan_name scanip 单实例-无
Diskgroup +OCRVOTE
+DATA
+FLASH /home/
11Gr2 GI ORACLE_HOME=/g01/app/gria/11.2.0/
ORACLE_HOME=/g01/grid/ 无
12Cr1 GI ORACLE_HOME=/g01/12.1.0/grid
ORACLE_HOME=/g01/grid/ 无
11GR2 DB ORACLE_HOME=/u01/oracle/11.2.0/
ORACLE_BASE=/u01/oracle/ ORACLE_HOME=/u01/oracle/11.2.0/
ORACLE_BASE=/u01/oracle/
12CR1 DB ORACLE_HOME=/u01/oracle/12.1.0/
ORACLE_BASE=/u01/oracle/ ORACLE_HOME=/u01/oracle/12.1.0/
ORACLE_BASE=/u01/oracle/

第二章 升级步骤
2.1 升级检查
1、在升级之前确保所有数据库的组件以及对象是有效的
2、确保在sys和system用户下没有重复对象（dbupgdiag.sql脚本用于收集数据库升级/迁移诊断信息的脚本）位于NOTE 556610.1
该脚本将创建一个输出文件称为dbupg_diag <sid> _ <timestamp> .log
3、禁用在DDL之前/之后触发的自定义触发器，并在升级完成后之后启用它们
4、要么对源数据库进行冷或热备份（建议进行冷备份）（已创建还原点，可忽略）
5、升级前检查数据库服务器升级/降级兼容性
6、升级过程中设置存档登录。 Oracle建议您设置存档登录命令DBUA创建和更新升级过程的日志文件。
7、 对于Oracle RAC，如果使用DBUA升级群集数据库，则CLUSTER_DATABASE初始化参数必须设置为TRUE。
8、在升级数据库之前，请确保运行预升级实用程序。 脚本可在12c ORACLE_HOME/rdbms/admin中命名为preupgrd.sql。
9、源数据库中的物化视图应在升级前停止
10、禁用用户下自定义对象
11、在启动数据库升级助手之前，需要更改设置，如果当前设置未设置，则在当前版本上的“并发统计信息收集“设置为false
1、SQL> SELECT dbms_stats.get_prefs(‘CONCURRENT’) from dual;

DBMS_STATS.GET_PREFS(‘CONCURRENT’)

FALSE
若值不为false，则改变为false,运行如下过程：
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS(‘CONCURRENT’,’FALSE’);
END;
/
12、从12c家目录下运行用于收集升级前信息的工具
$ORACLE_HOME/rdbms/admin/preupgrd.sql
$ sqlplus ‘/ as sysdba’
SQL> spool upgrade_info.log
SQL> @preupgrd.sql
SQL> spool off
13、检查源数据库中的INVALID数据库组件和对象
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from
dba_registry order by comp_name;
select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from
dba_objects where status=’INVALID’ order by owner,object_type;
select owner,object_type,count(*) from dba_objects where status=’INVALID’ group by
owner,object_type order by owner,object_type ;

14、禁用Oracle Database Vault您必须在升级数据库之前执行此操作。 升级完成再次启用Oracle Database Vault

Note 453903.1 Enabling and Disabling Oracle Database Vault in UNIX
Note 453902.1 How To Enable And/Or Disable Oracle Database Vault
15、清除记录，避免花费太多时间在处理记录相关信息。清空aud$、fga_log$
Note1062993.1 11.2.0.1 Catupgrd.sql Hangs While Running Procedure POPULATE_DBID_AUDIT For 10.2 and later source versions there is now a pre-process script available: Note 1329590.1 How to Pre-Process SYS.AUD$ Records Pre-Upgrade From 10.1 or later to 11gR1 or later.
If do not want to keep the records collected before 12cR1 then you can just do in source environment (READ PREVIOUS NOTES before proceeding):
SQL> truncate table sys.aud$;
SQL> truncate table sys.fga_log$;
16、企业管理器控制’
sql> connect / as sysdba
sql> spool emremoval.log
sql>@emremove.sql
sql> spool off
17、Oracle Warehouse Builder
OWB不作为Oracle数据库12c和OWB组件的一部分安装
早期版本中可能存在的版本不会作为Oracle数据库升级的一部分进行升级
处理。 但是，您可以将OWB版本11.2.0.3与Oracle数据库12c一起使用。 注意
OWB版本早于版本11.2.0.3不适用于Oracle Database 12c
18、Oracle标签安全
如果要从使用Oracle的Oracle数据库版本12.1之前的数据库升级标签安全（OLS）和Database Vault，那么您必须首先运行OLS预处理脚本，
olspreupgrade.sql，以处理aud $ table内容。 OLS升级移动aud $表
从SYSTEM模式到SYS模式。 olspreupgrade.sql脚本是一个预处理
此举所需的脚本。
2.2 配置listener.ora
在RAC1节点添加如下：
GI用户下cd $ORACLE_HOME/network/admin/
vi listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME= orcl)
(ORACLE_HOME=/u01/oracle/11.2.0/)
(SID_NAME=orcl1)
)
)
在RAC2节点添加如下：步骤同上
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME= orcl)
(ORACLE_HOME=/u01/oracle/11.2.0/)
(SID_NAME=orcl2)
)
)

备库：
ORACLE用户添加如下：
vi listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = standby)
(ORACLE_HOME = /u01/oracle/11.2.0/)
(SID_NAME = standby)
)
)

2.3 配置tnsname.ora
在所有节点添加如下配置-有则不需要配置—注意standby备库中ORCL中得配置HOST 需要具体得scan_IP IP得填写，即rac-scan 改为172.41.176.105
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)http://www.cndba.cn/Marvinn/article/2585

ORCL2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.41.176.104)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(INSTANCE_NAME = orcl2)
)
)

ORCL1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.41.176.103)(PORT = 1521))

(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = orcl)
  (INSTANCE_NAME = orcl1)
)

)

standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.41.176.116)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
(INSTANCE_NAME = standby)
)
)

配置监听之后需要验证是否配置正确，用如下语句在所有节点运行看否能通

$sqlplus sys/密码@TNS连接字符串 as sysdba

2.4 关闭DG broker
检查所有节点DG broker是否开启，若开启则需要关闭
$sqlplus / as sysdba

SQL> show parameter broker

NAME TYPE VALUE

dg_broker_config_file1 string /u01/oracle/11.2.0/db_1/dbs/dr
1standby.dat
dg_broker_config_file2 string /u01/oracle/11.2.0/db_1/dbs/dr
2standby.dat
dg_broker_start boolean FALSE

若dg_broker_start 为true，则运行如下即可。
SQL>alter system set dg_broker_start=false;

2.5 激活闪回
激活闪回功能，创建还原点，确保数据库升级失败后能快速闪回到初始状态
11g:we can enable the flashback in mount/open status.
10g:we can enable the flashback in mount status only
主库：
SQL> select flashback_on from v$database;

FLASHBACK_ON

NO

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.
—设置闪回功能的时候需要先设置db_recovery_file_dest_size 闪回数据库的大小
SQL> alter system set db_recovery_file_dest_size=15G;

System altered.
—设置闪回功能的时候需要先设置db_recovery_file_dest 闪回数据库的位置，可自行创建，但需要ORACLE用户能访问
SQL> alter system set db_recovery_file_dest=’+flash’;

System altered.
SQL> alter database flashback on;

Database altered.
SQL> select flashback_on from v$database;

FLASHBACK_ON

YES

备库：
开启闪回功能操作步骤同上，若最后一步报错如下，则需要先关闭日志应用进程，再开启。
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database flashback on;

Database altered.

SQL> recover managed standby database disconnect from session parallel 2 using current logfile;
Media recovery complete.
SQL>select flashback_on from v$database;http://www.cndba.cn/Marvinn/article/2585

FLASHBACK_ON

YES
2.6 First exec physru.sh
主库 rac1节点执行，备库是mount状态，且打开日志实时应用
rac1:
Note: physru.sh is dos format, we need to convert it to Unix format
将physru.sh dos格式转换成unix格式
$dos2unix physru.sh
或者
vim physru.sh
然后
ESC
:set fileformat =unix
:wq
—执行
[oracle@rac1 ~]$ chmod 755 physru.sh
[oracle@rac1 ~]$ ls -l physru.sh
-rwxr-xr-x. 1 oracle oinstall 139458 Dec 6 16:48 physru.sh
[oracle@rac1 ~]$ cksum physru.sh
2437052056 139458 physru.sh
执行
[oracle@rac1 /u01]$./physru.sh sys orcl1 standby orcl standby 12.1.0.2.0
Please enter the sysdba password:

Initialize script to either start over or resume execution Dec 06 16:52:03 2016

[0-1] Identifying rdbms software version Dec 06 16:52:03 2016
[0-1] database prod is at version 11.2.0.4.0 Dec 06 16:52:03 2016
[0-1] database stdby is at version 11.2.0.4.0 Dec 06 16:52:03 2016
[0-1] verifying flashback database is enabled at prod and stdby Dec 06 16:52:04 2016
[0-1] verifying available flashback restore points Dec 06 16:52:04 2016
[0-1] verifying DG Broker is disabled Dec 06 16:52:04 2016
[0-1] looking up prior execution history Dec 06 16:52:04 2016
[0-1] purging script execution state from database prod Dec 06 16:52:04 2016
[0-1] purging script execution state from database stdby
.
.
省略
.
[2-2] waiting for apply lag to fall under 30 seconds Dec 06 16:54:34 2016
[2-2] apply lag measured at 7 seconds Dec 06 16:54:34 2016 [2-2] stopping media recovery on stdby Dec 06 16:54:35 2016
[2-2] executing dbms_logstdby.build on database prod Dec 06 16:55:01 2016
[2-2] converting physical standby into transient logical standby Dec 06 16:55:04 2016 [2-3] opening database stdby Dec 06 16:55:06 2016
[2-4] configuring transient logical standby parameters for rolling upgrade Dec 06 16:55:07 2016
[2-4] starting logical standby on database stdby Dec 06 16:55:13 2016
[2-4] waiting until logminer dictionary has fully loaded Dec 06 16:56:03 2016
[2-4] dictionary load 42% complete Dec 06 16:56:14 2016
[2-4] dictionary load 75% complete Dec 06 16:56:24 2016 [2-4] dictionary load is complete Dec 06 16:56:24 2016
[2-4] waiting for apply lag to fall under 30 seconds Dec 06 16:56:28 2016
[2-4] apply lag measured at 3 seconds

NOTE: Database stdby is now ready to be upgraded. This script has left the database open in case you want to perform any further tasks before upgrading the database. Once the upgrade is complete, the database must opened in READ WRITE mode before this script can be called to resume the rolling upgrade.

NOTE: Database stdby may be reverted back to a RAC database upon completion of the rdbms upgrade. This can be accomplished by performing the following steps: 

      1) On instance stdby1, set the cluster_database parameter to TRUE.

eg: SQL> alter system set cluster_database=true scope=spfile; 

      2) Shutdown instance stdby1.          
        eg: SQL> shutdown abort; 

      3) Startup and open all instances for database stdby.

eg: srvctl start database -d stdby
2.7 测试11g逻辑备库
oracle@rac1:/u01>sqlplus / as sysdba

SQL*Plus: Release11.2.0.4.0Production on Sun Aug 27 21:32:45 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select * from scott.test;

ID

 2
 1

SQL> insert into scott.test values (3);

1 row created.

SQL> commit;

Commit complete.

备库检验：
oracle@11gDG:/u01/ >./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0Production on Mon Aug 28 05:24:14 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release11.2.0.4.0- 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from scott.test;

ID

 2
 1
 3

——-检验测试成功
2.8 备库安装12C
图形化安装数据库12C软件，此处跳过
ORACLE_BASE=/u01/oracle/ — 不变
ORACLE_HOME=/u01/oracle/12.1.0/ —需要修改
2.9 运行preupgrd.sql预升级脚本
$cd /u01/oracle/12.1.0/rdbms/admin/
$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 31 19:22:43 2017

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> spool /tmp/upgrade2_12c.log
SQL> @preupgrd.sql
Loading Pre-Upgrade Package…

Executing Pre-Upgrade Checks in ORCL…

  ************************************************************

       ====>> ERRORS FOUND for ORCL <<====

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.http://www.cndba.cn/Marvinn/article/2585

1) Check Tag: JOB_QUEUE_PROCESS
Check Summary: Check JOB_QUEUE_PROCESSES value
Fixup Summary:
“Review and increase or remove the setting of job_queue_processes”
+++ Source Database Manual Action Required +++

    You MUST resolve the above error prior to upgrade

  ************************************************************

  ************************************************************

       ====>> PRE-UPGRADE RESULTS for ORCL <<====

ACTIONS REQUIRED:

  1. Review results of the pre-upgrade checks:
    /u01/oracle/cfgtoollogs/standby/preupgrade/preupgrade.log

  2. Execute in the SOURCE environment BEFORE upgrade:
    /u01/oracle/cfgtoollogs/standby/preupgrade/preupgrade_fixups.sql

  3. Execute in the NEW environment AFTER upgrade:
    /u01/oracle/cfgtoollogs/standby/preupgrade/postupgrade_fixups.sql

Pre-Upgrade Checks in ORCL Completed.

SQL>spool off

2.10 检查源库对象
SQL> col COMP_NAME for a30;
SQL> set pagesize500
SQL> set linesize 100
SQL> select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;

http://www.cndba.cn/Marvinn/article/2585

COMP_NAME STATUS VERSION

JServer JAVA Virtual Machine VALID 11.2.0.4.0
OLAP Analytic Workspace VALID 11.2.0.4.0
OLAP Catalog VALID 11.2.0.4.0
OWB VALID 11.2.0.4.0
Oracle Application Express VALID 3.2.1.00.1
Oracle Database Catalog Views VALID 11.2.0.4.0
Oracle Database Java Packages VALID 11.2.0.4.0
Oracle Database Packages and T VALID 11.2.0.4.0
ypes

Oracle Enterprise Manager VALID 11.2.0.4.0
Oracle Expression Filter VALID 11.2.0.4.0
Oracle Multimedia VALID 11.2.0.4.0
Oracle OLAP API VALID 11.2.0.4.0
Oracle Real Application Cluste VALID 11.2.0.4.0
rs

Oracle Rules Manager VALID 11.2.0.4.0
Oracle Text VALID 11.2.0.4.0
Oracle Workspace Manager VALID 11.2.0.4.0
Oracle XDK VALID 11.2.0.4.0
Oracle XML Database VALID 11.2.0.4.0
Spatial VALID 11.2.0.4.0
SQL> select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status=’INVALID’ order by owner,object_type;
0 rows

若有无效对象，则进行编译

SQL>@?/rdbms/admin/utlrp.sql
2.11 清除审计记录
如果不需要保留审计表记录就清楚，以提高升级速度
SQL> truncate table sys.aud$;
SQL> truncate table sys.fga_log$;

2.12 移除EM
SQL> @/u01/oracle/12.1.0/rdbms/admin/emremove.sql
old 69: IF (upper(‘&LOGGING’) = ‘VERBOSE’)
new 69: IF (upper(‘VERBOSE’) = ‘VERBOSE’)

declare
*
ERROR at line 1:
ORA-16224: Database Guard is enabled
ORA-06512: at line 249

SQL> SQL> select guard_status from v$database;http://www.cndba.cn/Marvinn/article/2585

GUARD_S

ALL
SQL> alter database guard none;
SQL> set serveroutput on
SQL> @/u01/oracle/12.1.0/rdbms/admin/emremove.sql

执行完上emremove脚本后，执行如下
SQL>exec dbms_java_dev.enable;

PL/SQL procedure successfully completed.

—若执行出现如下错误，则忽略
SQL> exec dbms_java_dev.enable;
BEGIN dbms_java_dev.enable; END;

  *

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier ‘DBMS_JAVA_DEV.ENABLE’ must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

2.13 运行preupgrade_fixups.sql脚本
$cd /u01/oracle/cfgtoollogs/standby/preupgrade

SQL> @preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2017-07-31 19:23:41 Version: 12.1.0.2 Build: 006
Beginning Pre-Upgrade Fixups…
Executing in container ORCL

Check Tag: DEFAULT_PROCESS_COUNT
Check Summary: Verify min process count is not too low
Fix Summary: Review and increase if needed, your PROCESSES value.

Fixup Returned Information:
WARNING: —> Process Count may be too low

 Database has a maximum process count of 150 which is lower than the
 default value of 300 for this release.
 You should update your processes value prior to the upgrade
 to a value of at least 300.
 For example:
    ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE
 or update your init.ora file.

Check Tag: EM_PRESENT
Check Summary: Check if Enterprise Manager is present
Fix Summary: Execute emremove.sql prior to upgrade.

Fixup Returned Information:
WARNING: —> Enterprise Manager Database Control repository found in the database

 In Oracle Database 12c, Database Control is removed during
 the upgrade. To save time during the Upgrade, this action
 can be done prior to upgrading using the following steps after
 copying rdbms/admin/emremove.sql from the new Oracle home

  • Stop EM Database Control:
    $> emctl stop dbconsole

  • Connect to the Database using the SYS account AS SYSDBA:

    SET ECHO ON;
    SET SERVEROUTPUT ON;
    @emremove.sql
    Without the set echo and serveroutput commands you will not
    be able to follow the progress of the script.

Check Tag: AMD_EXISTS
Check Summary: Check to see if AMD is present in the database
Fix Summary: Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.

Fixup Returned Information:
INFORMATION: —> OLAP Catalog(AMD) exists in database

 Starting with Oracle Database 12c, OLAP Catalog component is desupported.
 If you are not using the OLAP Catalog component and want
 to remove it, then execute the 
 ORACLE_HOME/olap/admin/catnoamd.sql script before or 
 after the upgrade.

Check Tag: JOB_QUEUE_PROCESS
Check Summary: Check JOB_QUEUE_PROCESSES value
Fix Summary: Review and increase or remove the setting of job_queue_processes

Fixup Returned Information:
ERROR: —> job_queue_processes set to zero

 Starting with Oracle Database 11g Release 2 (11.2), setting
 JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and
 DBMS_JOB jobs to not run. Previously, setting JOB_QUEUE_PROCESSES
 to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were
 unaffected and would still run.
 This parameter must be removed or updated to a value greater
 than 4 (default value if not defined is 1000) prior to upgrade.
 Not doing so will affect the running of utlrp.sql after the upgrade

 Update your init.ora or spfile to make this change.

Check Tag: APEX_UPGRADE_MSG
Check Summary: Check that APEX will need to be upgraded.
Fix Summary: Oracle Application Express can be manually upgraded prior to database upgrade.

Fixup Returned Information:
INFORMATION: —> Oracle Application Express (APEX) can be
manually upgraded prior to database upgrade

 APEX is currently at version 3.2.1.00.12 and will need to be
 upgraded to APEX version 4.2.5 in the new release.
 Note 1: To reduce database upgrade time, APEX can be manually
         upgraded outside of and prior to database upgrade.
 Note 2: See MOS Note 1088970.1 for information on APEX
         installation upgrades.

                  [Pre-Upgrade Recommendations]

                    *****************************************
                    ********* Dictionary Statistics *********
                    *****************************************

Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;

^^^ MANUAL ACTION SUGGESTED ^^^

       **************************************************
            ************* Fixup Summary ************

5 fixup routines generated INFORMATIONAL messages that should be reviewed.

**PL/SQL procedure successfully completed.

http://www.cndba.cn/Marvinn/article/2585

PL/SQL procedure successfully completed.

修改上述5建议
SQL>ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;

System altered.

SQL> alter system set job_queue_processes =10 scope=spfile;

System altered.

$cd /u01/oracle/11.2.0/rdbms/admin
$emctl stop dbconsole
Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name.

$export ORACLE_UNQNAME=standby
$emctl stop dbconsole
OC4J Configuration issue. /u01/oracle/11.2.0/oc4j/j2ee/OC4J_DBConsole_11gDG_standby not found.

oracle@11gDG:/u01/oracle/12.1.0/rdbms/admin>sqlplus / as sysdba

http://www.cndba.cn/Marvinn/article/2585

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 31 22:02:43 2017

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> @/u01/oracle/12.1.0/rdbms/admin/emremove.sql
old 69: IF (upper(‘&LOGGING’) = ‘VERBOSE’)
new 69: IF (upper(‘VERBOSE’) = ‘VERBOSE’)

PL/SQL procedure successfully completed.

执行如下语句
SQL> @/u01/oracle/11.2.0/olap/admin/catnoamd.sql
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
……
省略
Type dropped.
View dropped.
Type dropped.
Type dropped.
Type dropped.
Type dropped.
PL/SQL procedure successfully completed.
Role dropped.
PL/SQL procedure successfully completed.
1 row deleted.

2.14清空回收站
SQL>purge dba_recyclebin;

DBA Recyclebin purged.

再次运行
SQL> @/u01/oracle/cfgtoollogs/standby/preupgrade/preupgrade_fixups.sql
收集数据字典状态
SQL>EXECUTE dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

http://www.cndba.cn/Marvinn/article/2585

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

2.15 切换环境变量
$export ORCALE_SID=standby
$export ORACLE_HOME=/u01/oracle/12.1.0/
$cd /u01/oracle/12.1.0/bin/
开库并启动升级进程
$./sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 1 23:17:51 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 754977520 bytes
Database Buffers 75497472 bytes
Redo Buffers 5455872 bytes
SQL> alter database mount;

Database altered.

SQL> alter database open upgrade;

Database altered.
SQL>exit

2.16 运行升级脚本
$cd /u01/oracle/12.1.0/rdbms/admin/
$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql

中间屏幕打印出的省略，下面为最后一行

LOG FILES: (catupgrd*.log)

Grand Total Upgrade Time: [0d:0h:48m:35s]

2.17 重新启库
oracle@11gDG:/home/oracle>export ORACLE_HOME=/u01/oracle/12.1.0/
oracle@11gDG:/home/oracle>export ORACLE_SID=standby
oracle@11gDG:/home/oracle>cd $ORACLE_HOME/bin
oracle@11gDG:/u01/oracle/12.1.0/bin>./sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 2 04:44:06 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 754977520 bytes
Database Buffers 75497472 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.

2.18 运行postupgrade_fixups.sql脚本
启库之后，运行如下
SQL> @/u01/oracle/cfgtoollogs/standby/preupgrade/postupgrade_fixups.sql
Post Upgrade Fixup Script Generated on 2017-08-02 00:33:01 Version: 12.1.0.2 Build: 006
Beginning Post-Upgrade Fixups…

Check Tag: INVALID_OBJECTS_EXIST
Check Summary: Check for invalid objects
Fix Summary: Invalid objects are displayed and must be reviewed.

Fixup Returned Information:
WARNING: —> Database contains INVALID objects prior to upgrade

 The list of invalid SYS/SYSTEM objects was written to
 registry$sys_inv_objs.
 The list of non-SYS/SYSTEM objects was written to
 registry$nonsys_inv_objs unless there were over 5000.
 Use utluiobj.sql after the upgrade to identify any new invalid
 objects due to the upgrade.

Check Tag: OLD_TIME_ZONES_EXIST
Check Summary: Check for use of older timezone data file
Fix Summary: Update the timezone using the DBMS_DST package after upgrade is complete.

Fixup Returned Information:
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 12.1.0.2.0 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.

Check Tag: NOT_UPG_BY_STD_UPGRD
Check Summary: Identify existing components that will NOT be upgraded
Fix Summary: This fixup does not perform any action.

Fixup Returned Information:
This fixup does not perform any action.
If you want to upgrade those other components, you must do so manually.

                 [Post-Upgrade Recommendations]

                    *****************************************
                    ******** Fixed Object Statistics ********
                    *****************************************

Please create stats on fixed objects two weeks
after the upgrade using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

^^^ MANUAL ACTION SUGGESTED ^^^

       **************************************************
            ************* Fixup Summary ************

3 fixup routines generated INFORMATIONAL messages that should be reviewed.

* Post Upgrade Fixup Script Complete **
SQL>@/u01/oracle/12.1.0/rdbms/admin/utluiobj.sql

PL/SQL procedure successfully completed.
PUBLIC DBA_CUBE_DIMENSIONS SYNONYM
PUBLIC DBA_CUBE_DIMNL_MAPPINGS SYNONYM
PUBLIC DBA_CUBE_DIM_LEVELS SYNONYM
PUBLIC DBA_CUBE_DIM_MAPPINGS SYNONYM
PUBLIC DBA_CUBE_DIM_MODELS SYNONYM
PUBLIC DBA_CUBE_DIM_VIEWS SYNONYM
PUBLIC DBA_CUBE_DIM_VIEW_COLUMNS SYNONYM
PUBLIC DBA_CUBE_HIERARCHIES SYNONYM
PUBLIC DBA_CUBE_HIER_LEVELS SYNONYM
PUBLIC DBA_CUBE_HIER_VIEWS SYNONYM
PUBLIC DBA_CUBE_HIER_VIEW_COLUMNS SYNONYM
PUBLIC DBA_CUBE_MAPPINGS SYNONYM
PUBLIC DBA_CUBE_MEASURES SYNONYM
PUBLIC DBA_EDITIONING_VIEW_COLS_AE SYNONYM
PUBLIC DBA_EDITIONS
.
.
中间输出省略
XDB DBMS_XDB_ADMIN PACKAGE BODY
XDB DBMS_XDB_REPOS PACKAGE BODY
XDB DBMS_XMLSCHEMA_INT PACKAGE BODY
XDB DBMS_XMLSTORAGE_MANAGE PACKAGE BODY
XDB DEPTH OPERATOR
XDB DOCUMENT_LINKS VIEW
XDB GET_XDB_TABLESPACE FUNCTION
XDB PATH OPERATOR
XDB XDB$EXTNAME2INTNAME FUNCTION
XDB XDB$LINK_T TYPE
XDB XDB_PITRIG_PKG PACKAGE BODY
XDB XDB_PITRIG_PKG_01 PACKAGE BODY

PL/SQL procedure successfully completed.

2.19 升级时区
脚本需要下载，并放入指定目录下
-SQL>@/u01/oracle/12.1.0/rdbms/admin/upg_tzv_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues …
INFO: Database version is 12.1.0.2 .
INFO: Database RDBMS DST version is DSTv14 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv18 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen …
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.

SQL> @/u01/oracle/12.1.0/rdbms/admin/upg_tzv_apply.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv18 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 738200304 bytes
Database Buffers 92274688 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen …
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 738200304 bytes
Database Buffers 92274688 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen …
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: “GSMADMIN_INTERNAL”.”AQ$_CHANGE_LOG_QUEUE_TABLE_S”
Number of failures: 0
Table list: “GSMADMIN_INTERNAL”.”AQ$_CHANGE_LOG_QUEUE_TABLE_L”
Number of failures: 0
Table list: “APEX_040200”.”WWV_FLOW_WORKSHEET_NOTIFY”
Number of failures: 0
Table list: “APEX_040200”.”WWV_FLOW_FEEDBACK_FOLLOWUP”
Number of failures: 0
Table list: “APEX_040200”.”WWV_FLOW_FEEDBACK”
Number of failures: 0
Table list: “APEX_040200”.”WWV_FLOW_DEBUG_MESSAGES2”
Number of failures: 0
Table list: “APEX_040200”.”WWV_FLOW_DEBUG_MESSAGES”
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv18 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.

收集对象状态
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.
运行如下语句
SQL>@?/rdbms/admin/catuppst.sql
2.20 重新编译
SQL>@?/rdbms/admin/utlrp.sql

第三章 测试已升级得备库
3.1关闭原11g监听
$lsnrctl stop

复制监听文件以及密码文件/控制文件/参数文件到12C对应目录中
$cp /u01/oracle/11.2.0/dbs/orapwstandby /u01/oracle/12.1.0/dbs/

$cp /u01/oracle/11.2.0/dbs/stdby.ora /u01/oracle/12.1.0/dbs/
需要修改控制文件所在路径，若审计文件所在路径没变更则不需要，修改如下
*.control_files=’/u01/oracle/12.1.0/dbs/control01.ctl’

$cp /u01/oracle/11.2.0/dbs/control01.ctl /u01/oracle/12.1.0/dbs/
注意，若备库为RAC，listener.ora文件在grid中得$ORACLE_HOME/network/admin下,也需要放入对应12CRAC目录下

$cp /u01/oracle/11.2.0/network/admin/listener.ora /u01/oracle/12.1.0/network/admin/
需要修改为12C得对应ORACLE_HOME

listener.ora Network Configuration File: /u01/oracle/12.1.0/network/admin/listener.ora

Generated by Oracle configuration tools.

$cp /u01/oracle/11.2.0/network/admin/tnsnames.ora /u01/oracle/12.1.0/network/admin/
需要修改为12C得对应ORACLE_HOME

tnsnames.ora Network Configuration File: /u01/oracle/12.1.0/network/admin/tnsnames.ora

Generated by Oracle configuration tools.

3.2打开现12C监听
$export ORACLE_HOME=/u01/oracle/12.1.0/
$export ORACLE_SID=standby
$lsnrctl start
$lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-AUG-2017 23:17:21

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.41.176.116)(PORT=1521)))

STATUS of the LISTENER

Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 28-AUG-2017 18:39:57
Uptime 0 days 4 hr. 37 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/12.1.0/network/admin/listener.ora
Listener Log File /u01/oracle/diag/tnslsnr/11gDG/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.41.176.116)(PORT=1521)))
Services Summary…
Service “standby” has 2 instance(s).
Instance “standby”, status UNKNOWN, has 1 handler(s) for this service…
Instance “standby”, status READY, has 1 handler(s) for this service…
Service “standbyXDB” has 1 instance(s).
Instance “standby”, status READY, has 1 handler(s) for this service…
The command completed successfully

3.3重新生成spfile文件
oracle@11gDG:/u01/oracle/12.1.0/bin>./sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 28 23:20:39 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>shutdown immediate
SQL> startup nomount pfile=’/u01/oracle/12.1.0/dbs/stdby.ora’;
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 754977520 bytes
Database Buffers 75497472 bytes
Redo Buffers 5455872 bytes

SQL>create spfile=’/u01/oracle/12.1.0/dbs/spfilestandby.ora’ from pfile=’/u01/oracle/12.1.0/dbs/stdby.ora’;

FILE CREATED

SQL>shutdown immediate
SQL>startup
开启数据库日志应用
SQL> alter database start logical standby apply immediate;

3.3 测试12C逻辑备库
主库插入：
oracle@rac1:/u01>sqlplus / as sysdba

SQL*Plus: Release11.2.0.4.0Production on Sun Aug 27 21:32:45 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select * from scott.test;

ID

 2
 1
 3

SQL> insert into scott.test values (4);

1 row created.

SQL> commit;

Commit complete.

备库检验：
oracle@11gDG:/u01/ >./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0Production on Mon Aug 28 05:24:14 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release11.2.0.4.0- 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from scott.test;

ID

 2
 1
 3
 4

——-检验测试成功

3.4 Second exec physru.sh
[oracle@rac1 /u01]$ ./physru.sh sys orcl1 standby orcl standby 12.1.0.2.0
Please enter the sysdba password:

Initialize script to either start over or resume execution

Aug 01 21:28:11 2017 [0-1] Identifying rdbms software version
Aug 01 21:28:11 2017 [0-1] database orcl is at version 11.2.0.4.0
Aug 01 21:28:11 2017 [0-1] database standby is at version 12.1.0.2.0
Aug 01 21:28:11 2017 [0-1] verifying flashback database is enabled at orcl and standby
Aug 01 21:28:11 2017 [0-1] verifying available flashback restore points
Aug 01 21:28:12 2017 [0-1] verifying DG Broker is disabled
Aug 01 21:28:12 2017 [0-1] looking up prior execution history
Aug 01 21:28:12 2017 [0-1] last completed stage [2-4] using script version 0001
Aug 01 21:28:12 2017 [0-1] resuming execution of script

Stage 3: Validate upgraded transient logical standby

Aug 01 21:28:12 2017 [3-1] database standby is no longer in OPEN MIGRATE mode
Aug 01 21:28:12 2017 [3-1] database standby is at version 12.1.0.2.0

Stage 4: Switch the transient logical standby to be the new primary

Aug 01 21:28:12 2017 [4-1] waiting for standby to catch up (this could take a while)
Aug 01 21:28:14 2017 [4-1] starting logical standby on database standby
Aug 01 21:28:20 2017 [4-1] waiting for apply lag to fall under 30 seconds
Aug 01 21:28:23 2017 [4-1] apply lag measured at 3 seconds
Aug 01 21:28:24 2017 [4-2] switching orcl to become a logical standby
Aug 01 21:29:17 2017 [4-2] orcl is now a logical standby
Aug 01 21:29:17 2017 [4-3] waiting for standby standby to process end-of-redo from primary
Aug 01 21:29:17 2017 [4-4] switching standby to become the new primary
Aug 01 21:29:22 2017 [4-4] standby is now the new primary

Stage 5: Flashback former primary to pre-upgrade restore point and convert to physical

Aug 01 21:29:22 2017 [5-1] verifying instance orcl1 is the only active instance

WARN: orcl is a RAC database. Before this script can continue, you
must manually reduce the RAC to a single instance. This can be
accomplished with the following step:

    1) Shutdown all instances other than instance orcl1.
       eg: srvctl stop instance -d orcl -i orcl2 -o abort

  Once these steps have been performed, enter 'y' to continue the script.
  If desired, you may enter 'n' to exit the script to perform the required
  steps, and recall the script to resume from this point.

Are you ready to continue? (y/n): Y

到一节点执行下面语句，关闭二节点，再按Y继续执行
grid@rac1:/home/grid>srvctl stop instance -d orcl -i orcl2 -o abort
grid@rac1:/home/grid>exit
logout
oracle@rac1:/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 1 21:31:43 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> exit
Disconnected

Enter your selection (1/2/3): 1

Aug 21 09:29:11 2017 [0-1] resuming execution of script

Stage 5: Flashback former primary to pre-upgrade restore point and convert to physical

Aug 21 09:29:11 2017 [5-1] verifying instance orcl1 is the only active instance
Aug 21 09:29:11 2017 [5-1] shutting down database orcl
Aug 21 09:29:31 2017 [5-1] mounting database orcl
Aug 21 09:29:48 2017 [5-2] flashing back database orcl to restore point PRU_0000_0001
Aug 21 09:29:51 2017 [5-3] converting orcl into physical standby
Aug 21 09:29:52 2017 [5-4] shutting down database orcl

NOTE: Database orcl has been shutdown, and is now ready to be started
using the newer version Oracle binary.This script requires the
database to be mounted (on all active instances, if RAC) before calling
this script to resume the rolling upgrade.

NOTE: Database orcl is no longer limited to single instance operation since
the database has been successfully converted into a physical standby.
For increased availability, Oracle recommends starting all instances in
the RAC on the newer binary by performing the following step:

    1) Startup and mount all instances for database orcl
    eg: srvctl start database -d orcl -o mount

第四章 主库升级
4.1 安装12c GI软件
图形化安装GI，选择第三个选项，升级已有得GI和ASM

跳过更新，其他选项下一步
Install Locations 主目录不变
HOME目录 选择 /g01/12.1.0/grid
其他默认安装

最后在两个节点执行弹框出现得升级脚本
完成安装
4.2 安装12c DB软件
图形化安装跟11g一样，此处省略
4.3 主库配置修改
mount所有节点得数据库
oracle@rac1:/home/oracle>srvctl start database -d orcl -o mount

一节点：
oracle@rac1:/home/oracle>cp /u01/oracle/11.2.0/dbs/orapworcl1 /u01/oracle/12.1.0/dbs/

oracle@rac1:/u01/oracle/11.2.0/dbs>ls
hc_orcl1.dat init.ora initorcl1.ora initorcl1.ora.bak.rac1 orapworcl1 PRU_0001_orcl_f.f snapcf_orcl1.f

oracle@rac1:/u01/oracle/11.2.0/dbs>cp initorcl1.ora /u01/oracle/12.1.0/dbs/
oracle@rac1:/u01/oracle/11.2.0/dbs>cp -rp /u01/oracle/11.2.0/network/admin/tnsnames.ora /u01/oracle/12.1.0/network/admin/

oracle@rac1:/u01/oracle/11.2.0/dbs>cd /u01/oracle/12.1.0/network/admin/
oracle@rac1:/u01/oracle/12.1.0/network/admin>ls
samples shrept.lst tnsnames.ora

oracle@rac1:/u01/oracle/12.1.0/network/admin>vi tnsnames.ora
—-修改下面得TNS中ORACLE_HOME

http://www.cndba.cn/Marvinn/article/2585

tnsnames.ora Network Configuration File: /u01/oracle/12.1.0/network/admin/tnsnames.ora

Generated by Oracle configuration tools.

二节点：

oracle@rac2:/home/oracle>cp /u01/oracle/11.2.0/dbs/orapworcl2 /u01/oracle/12.1.0/dbs/

oracle@rac2:/home/oracle>cp /u01/oracle/11.2.0/dbs/initorcl2.ora /u01/oracle/12.1.0/dbs/

oracle@rac2:/home/oracle>cp -rp /u01/oracle/11.2.0/network/admin/tnsnames.ora /u01/oracle/12.1.0/network/admin/

oracle@rac2:/home/oracle>cd /u01/oracle/12.1.0/network/admin/

oracle@rac2:/u01/oracle/12.1.0/network/admin>vi tnsnames.ora
—-修改下面得TNS中ORACLE_HOME

tnsnames.ora Network Configuration File: /u01/oracle/12.1.0/network/admin/tnsnames.ora

Generated by Oracle configuration tools.

4.4 修改12clistener.ora文件
一节点：
grid@rac1:/home/grid>cd /g01/11.2.0/grid/network/admin/

grid@rac1:/g01/11.2.0/grid/network/admin>ls
endpoints_listener.ora listener1708266PM5529.bak listener.ora listener.ora.bak.rac1 samples shrept.lst sqlnet.ora

grid@rac1:/g01/11.2.0/grid/network/admin>cp listener.ora /g01/12.1.0/grid/network/admin/
cp: overwrite `/g01/12.1.0/grid/network/admin/listener.ora’? yes

grid@rac1:/g01/11.2.0/grid/network/admin>vi /g01/12.1.0/grid/network/admin/listener.ora

grid@rac1:/g01/11.2.0/grid/network/admin>cat listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME= orcl)
(ORACLE_HOME=/u01/oracle/11.2.0/)
(SID_NAME=orcl1)
)
)

二节点：
grid@rac2:/home/grid>cd /g01/11.2.0/grid/network/admin/

grid@rac2:/g01/11.2.0/grid/network/admin>ls
endpoints_listener.ora listener.ora listener.ora.bak.rac2 samples shrept.lst sqlnet.orahttp://www.cndba.cn/Marvinn/article/2585

grid@rac2:/g01/11.2.0/grid/network/admin>cp listener.ora /g01/12.1.0/grid/network/admin/
cp: overwrite `/g01/12.1.0/grid/network/admin/listener.ora’? yes

grid@rac2:/g01/11.2.0/grid/network/admin>vi /g01/12.1.0/grid/network/admin/listener.ora
grid@rac2:/g01/11.2.0/grid/network/admin>cat /g01/12.1.0/grid/network/admin/listener.ora
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME= orcl )
(ORACLE_HOME=/u01/oracle/12.1.0/)
(SID_NAME= orcl2 )
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent

启动12C监听
一节点：
grid@rac1:/g01/11.2.0/grid/network/admin>srvctl stop listener
grid@rac1:/g01/11.2.0/grid/network/admin>export ORACLE_HOME=/g01/12.1.0/grid/
grid@rac1:/g01/11.2.0/grid/network/admin>srvctl start listener
rid@rac1:/g01/11.2.0/grid/network/admin>lsnrctl status
二节点：
grid@rac2:/g01/11.2.0/grid/network/admin>srvctl stop listener
grid@rac2:/g01/11.2.0/grid/network/admin>export ORACLE_HOME=/g01/12.1.0/grid/
grid@rac2:/g01/11.2.0/grid/network/admin>srvctl start listener
grid@rac2:/g01/12.1.0/grid/network/admin>lsnrctl status
4.5 移除11g数据库
oracle@rac1:/home/oracle> srvctl config database -d orcl
Database unique name: orcl
Database name: orcl
Oracle home: /u01/oracle/11.2.0
Oracle user: oracle
Spfile: +DATA/orcl/spfileorcl.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: orcl
Database instances: orcl1,orcl2
Disk Groups: DATA,FLASH
Mount point paths:
Services:
Type: RAC
Database is administrator managed

oracle@rac1:/home/oracle>srvctl remove database -d orcl
PRKO-3141 : Database orcl could not be removed because it was running

oracle@rac1:/home/oracle>srvctl stop database -d orcl
oracle@rac1:/home/oracle>srvctl remove database -d orcl
Remove the database orcl? (y/[n]) y
oracle@rac1:/home/oracle>
4.6 编辑环境变量
一节点：
$export ORACLE_SID=orcl1
$export ORACLE_HOME=/u01/oracle/12.1.0/
$export PATH=$ORACLE_HOME/bin:$PATH
$source ~/.bash_profile
$which srvctl
$export ORACLE_HOME=/u01/oracle/12.1.0/

二节点：
$export ORACLE_SID=orcl2
$export ORACLE_HOME=/u01/oracle/12.1.0/
$export PATH=$ORACLE_HOME/bin:$PATH
$source ~/.bash_profile
$which srvctl
$export ORACLE_HOME=/u01/oracle/12.1.0/

4.7添加12C数据库
oracle@rac1:/home/oracle>srvctl add database -d orcl -o /u01/oracle/12.1.0/ -p +DATA/orcl/spfileorcl.ora -a DATA,FLASH
PRCD-1025 : Failed to create database orcl
PRCT-1402 : Attempt to retrieve version of SRVCTL from Oracle Home /u01/oracle/12.1.0//bin failed. Detailed error:
PRKC-1137 : Unable to find Version object with string value 12.1.0.2.0

oracle@rac1:/home/oracle>export ORACLE_HOME=/u01/oracle/12.1.0/
oracle@rac1:/home/oracle>echo $ORACLE_HOME
/u01/oracle/12.1.0/

oracle@rac1:/home/oracle>srvctl add database -d orcl -o /u01/oracle/12.1.0/ -p +DATA/orcl/spfileorcl.ora -a DATA,FLASH
oracle@rac1:/home/oracle>srvctl config database -d orcl
Database unique name: orcl
Database name:
Oracle home: /u01/oracle/12.1.0/
Oracle user: oracle
Spfile: +DATA/orcl/spfileorcl.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,FLASH
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oinstall
Database instances:
Configured nodes:
Database is administrator managed

oracle@rac1:/home/oracle> srvctl add instance -d orcl -i orcl1 -n rac1
oracle@rac1:/home/oracle> srvctl add instance -d orcl -i orcl2 -n rac2

检查二节点配置是否正常
[root@rac2 ~]#su - oracle

You login as oracle,Please ask somebody to double check!*

oracle@rac2:/home/oracle> srvctl config database -d orcl
PRCD-1027 : Failed to retrieve database orcl
PRCD-1229 : An attempt to access configuration of database orcl was rejected because its version 12.1.0.2.0 differs from the program version 11.2.0.4.0. Instead run the program from /u01/oracle/12.1.0/.
oracle@rac2:/home/oracle>export ORACLE_SID=orcl2
oracle@rac2:/home/oracle>export ORACLE_HOME=/u01/oracle/12.1.0/
oracle@rac2:/home/oracle>export PATH=$ORACLE_HOME/bin:$PATH
oracle@rac2:/home/oracle>source ~/.bash_profile

You login as oracle,Please ask somebody to double check!*

oracle@rac2:/home/oracle>which srvctl
/u01/oracle/12.1.0/bin/srvctl
oracle@rac2:/home/oracle>export ORACLE_HOME=/u01/oracle/12.1.0/
oracle@rac2:/home/oracle>export ORAC srvctl config database -d orcl
Database unique name: orcl
Database name:
Oracle home: /u01/oracle/12.1.0/
Oracle user: oracle
Spfile: +DATA/orcl/spfileorcl.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,FLASH
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oinstall
Database instances: orcl1,orcl2
Configured nodes: rac1,rac2
Database is administrator managed

4.8 mount所有数据库
oracle@rac1:/home/oracle>srvctl start database -d orcl -o mount
oracle@rac1:/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 27 20:57:15 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select name,database_role,open_mode from gv$database
2 /

NAME DATABASE_ROLE

OPEN_MODE

ORCL PHYSICAL STANDBY
MOUNTED

ORCL PHYSICAL STANDBY
MOUNTED

SQL> select banner from v$version;

BANNER

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 – Production
4.9 Third Run phyrsu.sh
oracle@rac1:/u01> ./physru.sh sys orcl1 standby orcl standby 12.1.0.2.0
Please enter the sysdba password:

Initialize script to either start over or resume execution

Aug 27 20:59:52 2017 [0-1] Identifying rdbms software version
Aug 27 20:59:52 2017 [0-1] database orcl is at version 12.1.0.2.0
Aug 27 20:59:53 2017 [0-1] database standby is at version 12.1.0.2.0
Aug 27 20:59:53 2017 [0-1] verifying flashback database is enabled at orcl and standby
Aug 27 20:59:53 2017 [0-1] verifying available flashback restore points
Aug 27 20:59:54 2017 [0-1] verifying DG Broker is disabled
Aug 27 20:59:54 2017 [0-1] looking up prior execution history
Aug 27 20:59:54 2017 [0-1] last completed stage [5-4] using script version 0001
Aug 27 20:59:54 2017 [0-1] resuming execution of script

Stage 6: Run media recovery through upgrade redo

Aug 27 20:59:55 2017 [6-1] upgrade redo region identified as scn range [1174008, 2328741]
Aug 27 20:59:55 2017 [6-1] starting media recovery on orcl
Aug 27 21:00:02 2017 [6-1] confirming media recovery is running
Aug 27 21:00:03 2017 [6-1] waiting for media recovery to initialize v$recovery_progress
Aug 27 21:00:25 2017 [6-1] monitoring media recovery’s progress
Aug 27 21:00:26 2017 [6-3] recovery of upgrade redo at 45% - estimated complete at Aug 27 21:04:24
Aug 27 21:00:42 2017 [6-3] recovery of upgrade redo at 49% - estimated complete at Aug 27 21:04:15
Aug 27 21:00:58 2017 [6-3] recovery of upgrade redo at 55% - estimated complete at Aug 27 21:03:25
Aug 27 21:01:15 2017 [6-3] recovery of upgrade redo at 65% - estimated complete at Aug 27 21:02:43
Aug 27 21:01:31 2017 [6-3] recovery of upgrade redo at 71% - estimated complete at Aug 27 21:02:45
Aug 27 21:01:47 2017 [6-3] recovery of upgrade redo at 82% - estimated complete at Aug 27 21:02:29
Aug 27 21:02:03 2017 [6-3] recovery of upgrade redo at 88% - estimated complete at Aug 27 21:02:30
Aug 27 21:02:19 2017 [6-3] recovery of upgrade redo at 94% - estimated complete at Aug 27 21:02:32
Aug 27 21:02:35 2017 [6-3] recovery of upgrade redo at 99% - estimated complete at Aug 27 21:02:36
Aug 27 21:02:51 2017 [6-4] media recovery has finished recovering through upgrade

Stage 7: Switch back to the original roles prior to the rolling upgrade

NOTE: At this point, you have the option to perform a switchover
which will restore orcl back to a primary database and
standby back to a physical standby database. If you answer ‘n’
to the question below, orcl will remain a physical standby
database and standby will remain a primary database.

Do you want to perform a switchover? (y/n): y

Aug 27 21:14:21 2017 [7-1] continuing
Aug 27 21:14:23 2017 [7-2] waiting for apply lag to fall under 30 seconds
Aug 27 21:14:26 2017 [7-2] apply lag measured at 3 seconds
Aug 27 21:14:26 2017 [7-3] switching standby to become a physical standby
Aug 27 21:14:29 2017 [7-3] standby is now a physical standby
Aug 27 21:14:29 2017 [7-3] shutting down database standby
Aug 27 21:14:32 2017 [7-3] mounting database standby
Aug 27 21:14:40 2017 [7-4] waiting for standby orcl to process end-of-redo from primary
Aug 27 21:14:41 2017 [7-5] switching orcl to become the new primary
Aug 27 21:14:43 2017 [7-5] orcl is now the new primary
Aug 27 21:14:43 2017 [7-5] opening database orcl
Aug 27 21:15:03 2017 [7-6] starting media recovery on standby
Aug 27 21:15:09 2017 [7-6] confirming media recovery is running

NOTE: Database orcl has completed the switchover to the primary role, but
instance orcl1 is the only open instance. For increased availability,
Oracle recommends opening the remaining active instances which are
currently in mounted mode by performing the following steps:

    1) Shutdown all instances other than instance orcl1.
    eg: srvctl stop instance -d orcl -i orcl2

    2) Startup and open all inactive instances for database orcl.
    eg: srvctl start database -d orcl

Stage 8: Statistics

script start time: 27-Aug-17 20:39:47
script finish time: 28-Aug-17 05:17:03
total script execution time: +00 08:37:16
wait time for user upgrade: +00 02:04:51
active script execution time: +00 06:32:25
transient logical creation start time: 27-Aug-17 20:39:53
transient logical creation finish time: 27-Aug-17 22:39:23
primary to logical switchover start time: 28-Aug-17 00:45:59
logical to primary switchover finish time: 28-Aug-17 00:46:28
primary services offline for: +00 00:00:29
total time former primary in physical role: +00 04:27:17
time to reach upgrade redo:
time to recover upgrade redo: +00 00:02:24
primary to physical switchover start time: 28-Aug-17 05:16:13
physical to primary switchover finish time: 28-Aug-17 05:16:54
primary services offline for: +00 00:00:41

SUCCESS: The physical rolling upgrade is complete
—-升级完成
oracle@rac1:/u01>srvctl stop instance -d orcl -i orcl2
oracle@rac1:/u01>srvctl start database -d orcl
第五章 备库standby
5.1 同步日志
oracle@11gDG:/home/oracle>export ORACLE_HOME=/u01/oracle/12.1.0/
oracle@11gDG:/home/oracle>cd $ORACLE_HOME/bin
oracle@11gDG:/u01/oracle/12.1.0/bin>./sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 28 05:24:14 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select open_mode,database_role from gv$database;

OPEN_MODE DATABASE_ROLE

MOUNTED PHYSICAL STANDBY

SQL> alter system set “_transient_logical_clear_hold_mrp_bit”=true;

System altered.

SQL> alter database recover managed standby database using current logfile disconnect;
alter database recover managed standby database using current logfile disconnect
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select open_mode,database_role from gv$database;

OPEN_MODE DATABASE_ROLE

MOUNTED PHYSICAL STANDBY

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

——主备库执行如下语句

主库是否有lgwr进程状态为writing
select process,status,GROUP# , SEQUENCE# , THREAD# from v$managed_standby;
备库是否有MRP进程状态为apply_log
select process,status,GROUP# , SEQUENCE# , THREAD# from v$managed_standby;

5.2 测试同步
主库插入测试：
oracle@rac1:/u01>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 27 21:32:45 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select * from scott.test;

ID

 2
 1

SQL> insert into scott.test values (3);

1 row created.

SQL> commit;

Commit complete.

备库检验：
oracle@11gDG:/u01/oracle/12.1.0/bin>./sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 28 05:24:14 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from scott.test;

ID

 2
 1
 3

——-检验测试成功
第七章 环境变量更改与卸载
7.1 环境变量更改
7.1.1主库
一节点

GRID用户：
grid@rac1:/g01>vi ~/.bash_profile
ORACLE_HOME=/g01/12.1.0/grid
grid@rac1:/g01>source ~/.bash_profile

You login as grid,Please ask somebody to double check!*

grid@rac1:/g01>echo $ORACLE_HOME
/g01/12.1.0/grid
grid@rac1:/home/grid>which sqlplus
/g01/12.1.0/grid/bin/sqlplus

ORACLE用户：
oracle@rac1:/home/oracle>vi ~/.bash_profile
oracle@rac1:/home/oracle>source ~/.bash_profile
oracle@rac1:/home/oracle>echo $ORACLE_HOME
/u01/oracle/12.1.0/
oracle@rac1:/u01/oracle>which sqlplus
/u01/oracle/11.2.0/bin/sqlplus ——错误显示
oracle@rac1:/u01/oracle>cp ~/.bash_profile ~/.bash_profile1
oracle@rac1:/u01/oracle>vi ~/.bash_profile
/u01/oracle/11.2.0/
oracle@rac1:/u01/oracle>source ~/.bash_profile
oracle@rac1:/u01/oracle>mv ~/.bash_profile ~/.bash_profile.bak
oracle@rac1:/u01/oracle>vi ~/.bash_profile1
/u01/oracle/12.1.0/
oracle@rac1:/u01/oracle>source ~/.bash_profile1

You login as oracle,Please ask somebody to double check!*

oracle@rac1:/u01/oracle>exit
logout
[root@rac1 ~]# su - oracle
-bash-4.1$ mv ~/.bash_profile1 ~/.bash_profile
-bash-4.1$ source ~/.bash_profile

You login as oracle,Please ask somebody to double check!*

oracle@rac1:/home/oracle>echo $ORACLE_HOME
/u01/oracle/12.1.0/
oracle@rac1:/home/oracle>which sqlplus
/u01/oracle/12.1.0/bin/sqlplus ——-正确显示

二节点
GRID用户：
grid@rac2:/home/grid>vi ~/.bash_profile
ORACLE_HOME=/g01/12.1.0/grid
grid@rac2:/home/grid>source ~/.bash_profile

You login as grid,Please ask somebody to double check!*

grid@rac2:/home/grid>echo $ORACLE_HOME
/g01/12.1.0/grid
grid@rac2:/home/grid>which sqlplus
/g01/12.1.0/grid/bin/sqlplus

ORACLE用户：
oracle@rac2:/home/oracle>cp ~/.bash_profile ~/.bash_profile1
oracle@rac2:/home/oracle>mv ~/.bash_profile ~/.bash_profile.bak
oracle@rac2:/home/oracle>exit
logout
[root@rac2 ~]#su - oracle
-bash-4.1$ vi ~/.bash_profile1
/u01/oracle/12.1.0/
-bash-4.1$ mv ~/.bash_profile1 ~/.bash_profile
-bash-4.1$ source ~/.bash_profile

You login as oracle,Please ask somebody to double check!*

oracle@rac2:/home/oracle>echo $ORACLE_HOME
/u01/oracle/12.1.0
oracle@rac2:/home/oracle>which sqlplus
/u01/oracle/12.1.0/bin/sqlplus ——正确显示

7.1.2备库
oracle@11gDG:/home/oracle>cp ~/.bash_profile ~/.bash_profile.bak
oracle@11gDG:/home/oracle>mv ~/.bash_profile ~/.bash_profile1
oracle@11gDG:/home/oracle>exit
logout
[root@11gDG ~]# su - oracle
-bash-4.1$ vi ~/.bash_profile1
ORACLE_HOME=/u01/oracle/12.1.0/
-bash-4.1$ mv ~/.bash_profile1 ~/.bash_profile
-bash-4.1$ source ~/.bash_profile

You login as oracle,Please ask somebody to double check!*

oracle@11gDG:/home/oracle>echo $ORACLE_HOME
/u01/oracle/12.1.0/
oracle@11gDG:/home/oracle>which sqlplus
/u01/oracle/12.1.0/bin/sqlplus ——正确显示
7.2 卸载软件
备库卸载数据库软件
主库卸载数据库软件

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
Marvinn

Marvinn

关注

路漫漫其修远兮、吾将上下而求索

  • 99
    原创
  • 0
    翻译
  • 2
    转载
  • 36
    评论
  • 访问：458502次
  • 积分：449
  • 等级：中级会员
  • 排名：第12名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ