Shulou(Shulou.com)06/01 Report--

Upgrade key points

1. It can be upgraded directly from 11.2.0.4, 12.1.0.2, 12.2.0.1 and 18c to 19c.

two。 Compatibility parameter should be at least 11.2.0

3. After the upgrade, the Oracle default account (whose password was not reset prior to the upgrade) will be locked and set to NO AUTHENICATE MODE.

4. After the upgrade, due to the new authentication method, you may not be able to log in to existing users with a password. To resolve this problem, you need to update the sqlnet.ora file.

Software preparation

Database software

Database upgrade version:

Oracle Database 19.3.0.0

Current environment details:

DATABASE TYPE-single

DATABASE NAME-TESTDB

DATABASE VESION-12.1.0.2

CURRENT ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1

NEW ORACLE_HOME = / oracle/app/oracle/product/19.0.0.0/dbhome_1

* * install DB**

Extract the installation package to create the installation directory

Unzip the binary and run runInstaller.sh

Mkdir-p/oracle/ app/oracle/product/19.0.0.0/dbhome_1

Install database software

Pre-upgrade check

Run the pre-upgrade tool script

The oracle database binaries provide preupgrade.jar tool files. Run this to pre-check

Export ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1

$ORACLE_HOME/jdk/bin/java-jar / oracle/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/preupgrade.jar

=

PREUPGRADE SUMMARY

/ oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade.log

/ oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql

/ oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups

@ / oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups

@ / oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2019-08-26T13:09:51

Run the pre-upgrade fix script:

SQL > @ / oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql

Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script

Version: 19.0.0.0.0 Build: 1

Generated on: 2019-08-26 13:09:37

For Source Database: TESTDB

Source Database Version: 12.1.0.2.0

For Upgrade to Version: 19.0.0.0.0

Preup Preupgrade

Action Issue Is

Number Preupgrade Check Name Remedied Further DBA Action

1. Invalid_objects_exist NO Manual fixup recommended.2. Exclusive_mode_auth NO Manual fixup recommended.3. Case_insensitive_auth NO Manual fixup recommended.4. Underscore_events NO Informational only. Further action is optional.5. Dictionary_stats YES None.6. Parameter_deprecated NO Informational only. Further action is optional.7. Min_archive_dest_size NO Informational only. Further action is optional.8. Rman_recovery_version NO Informational only. Further action is optional.

The fixup scripts have been run and resolved what they can. However

There are still issues originally identified by the preupgrade that

Have not been remedied and are still present in the database.

Depending on the severity of the specific issue, and the nature of

The issue itself, that could mean that your database is not ready

For upgrade. To resolve the outstanding issues, start by reviewing

The preupgrade_fixups.sql and searching it for the name of

The failed CHECK NAME or Preupgrade Action Number listed above.

There you will find the original corresponding diagnostic message

From the preupgrade which explains in more detail what still needs

To be done.

PL/SQL procedure successfully completed.

Run utlrp.sql: (compile invalid objects)

SQL > @ $ORACLE_HOME/rdbms/admin/utlrp.sql

SQL > select count (*) from dba_objects where status='INVALID'

COUNT (*)

0

Check the status of database components

Set pagesize500

Set linesize 100

Select substr (comp_name,1,40) comp_name, status, substr (version,1,10) version from dba_registry order by comp_name

COMP_NAME

STATUS VERSION

JServer JAVA Virtual Machine

VALID 12.1.0.2.0

Oracle Database Catalog Views

VALID 12.1.0.2.0

Oracle Database Java Packages

VALID 12.1.0.2.0

Oracle Database Packages and Types

VALID 12.1.0.2.0

Oracle Multimedia

VALID 12.1.0.2.0

Oracle Text

VALID 12.1.0.2.0

Oracle Workspace Manager

VALID 12.1.0.2.0

Oracle XDK

VALID 12.1.0.2.0

Oracle XML Database

VALID 12.1.0.2.0

SQL > SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand (s.mflags, 8) = 8

No rows selected

Check the time zone version:

SQL > select * from v$timezone_file

FILENAME VERSION CON_ID

Timezlrg_18.dat 18 0

Check the file in backup mode: (zero line should be returned)

SQL > SELECT * FROM v$backup WHERE status! = 'NOT ACTIVE'

No rows selected

SQL > SELECT * FROM v$recover_file

No rows selected

Clear the Recycle Bin

SQL > SELECT * FROM v$backup WHERE status! = 'NOT ACTIVE'

No rows selected

SQL > SELECT * FROM v$recover_file

No rows selected

Upgrade the database

Enable the flashback on the database.

To enable restore, in case of failure, enable flashback option.

Alter system set db_recovery_file_dest_size=20G scope=both

Alter system set db_recovery_file_dest='/dumparea/FRA/' scope=both

Alter database flashback on

Export ORACLE_HOME=/oracle/app/oracle/product/19.0.0.0/dbhome_1/

Cd $ORACLE_HOME/bin

. / dbua

Upgrade completed successfully.

Check after upgrade

SQL > select comp_id,status from dba_registry

COMP_ID STATUS

CATALOG VALID

CATPROC VALID

JAVAVM VALID

XML VALID

CATJAVA VALID

RAC OPTION OFF

XDB VALID

OWM VALID

CONTEXT VALID

ORDIM VALID

10 rows selected.

SQL > select * from v$timezone_file

FILENAME VERSION CON_ID

Timezlrg_32.dat 32 0

Update sqlnet.ora files

Post upgrade, you might not be able to connect to the existing users with the passwords. So to fix this add SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 to sqlnet.ora file

Export ORACLE_HOME=/oracle/app/oracle/product/19.0.0.0/dbhome_1

Cd $ORACLE_HOME/network/admin

Cat sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

Once you confirm that the upgrade was successful and that there was no rollback, you can delete the restore point.

Select * from v$restore_point

Drop restore point

Update compatible parameters after upgrade.

After the upgrade is successful, test the database. After the test is successful, you can update the compatibility parameters. However, once the compatibility parameters are updated, you cannot downgrade the database. Therefore, be sure to test properly and make a full backup before updating the compatibility parameters.

Alter system set compatible='19.0.0' scope=spfile

Shutdown immediate

Startup

SELECT name, value FROM v$parameter

WHERE name = 'compatible'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.