#how to check patch version in oracle 12c
Explore tagged Tumblr posts
Text
Check the Patch Applied to the Oracle Database
multiple ways to find Patch is Applied or not in the Database. Here i give all the ways to get the details about the same. #oracle #oracledba #databaseadmin #oracledatabase
In Oracle Database we have multiple ways to find Patch is Applied or not in the Database. Here i give all the ways to get the details about the same. how to check patch is applied or not in oracle, sql query to find patches applied in oracle 11g, How do I know if a patch is applied in Oracle 19c? Check applied patch list Here we have two ways to find the patch list and patch list with…
View On WordPress
#how to check latest cpu patch applied in oracle#how to check patch is applied or not in oracle#how to check patch version in oracle 12c#how to check psu patch applied in a database 19c#sql query to find patches applied in oracle 11g
1 note
·
View note
Text
300+ TOP ORACLE RAC Interview Questions and Answers
ORACLE RAC Interview Questions for freshers experienced :-
1. What is cache fusion? In a RAC environment, it is the combining of data blocks, which are shipped across the interconnect from remote database caches (SGA) to the local node, in order to fulfill the reuirements for a transaction (DML, uery of Data Dictionary). 2. What is split brain? When database nodes in a cluster are unable to communicate with each other, they may continue to process and modify the data blocks independently. If the same block is modified by more than one instance, synchronization/locking of the data blocks does not take place and blocks may be overwritten by others in the cluster. This state is called split brain. 3. What is the difference between Crash recovery and Instance recovery? When an instance crashes in a single node database on startup a crash recovery takes place. In a RAC enviornment the same recovery for an instance is performed by the surviving nodes called Instance recovery. 4. What is the interconnect used for? It is a private network which is used to ship data blocks from one instance to another for cache fusion. The physical data blocks as well as data dictionary blocks are shared across this interconnect. 5. How do you determine what protocol is being used for Interconnect traffic? One of the ways is to look at the database alert log for the time period when the database was started up. Want To Get RAC Training From Experts? Enroll Now For Free Demo On Oracle RAC Training. 6. What methods are available to keep the time synchronized on all nodes in the cluster? Either the Network Time Protocol(NTP) can be configured or in 11gr2, Cluster Time Synchronization Service (CTSS) can be used. 7. What files components in RAC must reside on shared storage? Spfiles, ControlFiles, Datafiles and Redolog files should be created on shared storage. 8. Where does the Clusterware write when there is a network or Storage missed heartbeat? The network ping failure is written in $CRS_HOME/log 9. How do you find out what OCR backups are available? The ocrconfig -showbackup can be run to find out the automatic and manually run backups. 10. If your OCR is corrupted what options do have to resolve this? You can use either the logical or the physical OCR backup copy to restore the Repository.
ORACLE RAC Interview Questions 11. How do you find out what object has its blocks being shipped across the instance the most? You can use the dba_hist_seg_stats. 12. What is a VIP in RAC use for? The VIP is an alternate Virtual IP address assigned to each node in a cluster. During a node failure the VIP of the failed node moves to the surviving node and relays to the application that the node has gone down. Without VIP, the application will wait for TCP timeout and then find out that the session is no longer live due to the failure. 13. How do we know which database instances are part of a RAC cluster? You can uery the V$ACTIVE_INSTANCES view to determine the member instances of the RAC cluster. 14. What is OCLUMON used for in a cluster environment? The Cluster Health Monitor (CHM) stores operating system metrics in the CHM repository for all nodes in a RAC cluster. It stores information on CPU, memory, process, network and other OS data, This information can later be retrieved and used to troubleshoot and identify any cluster related issues. It is a default component of the 11gr2 grid install. The data is stored in the master repository and replicated to a standby repository on a different node. 15. What would be the possible performance impact in a cluster if a less powerful node (e.g. slower CPU’s) is added to the cluster? All processing will show down to the CPU speed of the slowest server. 16. What is the purpose of OLR? Oracle Local repository contains information that allows the cluster processes to be started up with the OCR being in the ASM storage ssytem. Since the ASM file system is unavailable until the Grid processes are started up a local copy of the contents of the OCR is reuired which is stored in the OLR. 17. What is the default memory allocation for ASM? In 10g the default SGA size is 1G in 11g it is set to 256M and in 12c ASM it is set back to 1G. 18. How do you backup ASM Metadata? You can use md_backup to restore the ASM diskgroup configuration in-case of ASM diskgroup storage loss. 19. What files can be stored in the ASM diskgroup? In 11g the following files can be stored in ASM diskgroups. Datafiles Redo logfiles Spfiles In 12c the files below can also new be stored in the ASM Diskgroup Password file 20. What it the ASM POWER_LIMIT? This is the parameter which controls the number of Allocation units the ASM instance will try to rebalance at any given time. In ASM versions less than 11.2.0.3 the default value is 11 however it has been changed to unlimited in later versions. 21. What is a rolling upgrade? A patch is considered a rolling if it is can be applied to the cluster binaries without having to shutting down the database in a RAC environment. All nodes in the cluster are patched in a rolling manner, one by one, with only the node which is being patched unavailable while all other instance open. 22. What are some of the RAC specific parameters? Some of the RAC parameters are: CLUSTER_DATABASE CLUSTER_DATABASE_INSTANCE INSTANCE_TYPE (RDBMS or ASM) ACTIVE_INSTANCE_COUNT UNDO_MANAGEMENT 23. What is the future of the Oracle Grid? The Grid software is becoming more and more capable of not just supporting HA for Oracle Databases but also other applications including Oracle’s applications. With 12c there are more features and functionality built-in and it is easier to deploy these pre-built solutions, available for common Oracle applications. Explore Oracle RAC Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now! 24. What components of the Grid should I back up? The backups should include OLR, OCR and ASM Metadata. 25. Is there an easy way to verify the inventory for all remote nodes You can run the OPATCH LSINVENTORY -ALL_NODES command from a single node to look at the inventory details for all nodes in the cluster. 26. If I am using Vendor Clusterware such as Veritas, IBM, Sun or HP, do I still need Oracle Clusterware to run Oracle RAC 10g? Yes. When certified, you can use Vendor Clusterware however you must still install and use Oracle Clusterware for RAC. Best Practice is to leave Oracle Clusterware to manage RAC. For details see Metalink Note 332257.1 and for Veritas SFRAC see 397460.1. 27. Is RAC on VMWare supported? Yes. 28. What is hangcheck timer used for ? The hangcheck timer checks regularly the health of the system. If the system hangs or stop the node will be restarted automatically. There are 2 key parameters for this module: hangcheck-tick: this parameter defines the period of time between checks of system health. The default value is 60 seconds; Oracle recommends setting it to 30seconds. hangcheck-margin: this defines the maximum hang delay that should be tolerated before hangcheck-timer resets the RAC node. 29. Is the hangcheck timer still needed with Oracle RAC 10g? Yes. 30. What files can I put on Linux OCFS2? For optimal performance, you should only put the following files on Linux OCFS2: Datafiles Control Files Redo Logs Archive Logs Shared Configuration File (OCR) Voting File SPFILE 31. Is it possible to use ASM for the OCR and voting disk? No, the OCR and voting disk must be on raw or CFS (cluster file system). 32. Can I change the name of my cluster after I have created it when I am using Oracle Clusterware? No, you must properly uninstall Oracle Clusterware and then re-install. 33. What the O2CB is? The O2CB is the OCFS2 cluster stack. OCFS2 includes some services. These services must be started before using OCFS2 (mount/ format the file systems). 34. What the OCR file is used for? OCR is a file that manages the cluster and RAC configuration. 35. What the Voting Disk file is used for? The voting disk is nothing but a file that contains and manages information of all the node memberships. 36. What is the recommended method to make backups of a RAC environment? RMAN to make backups of the database, dd to backup your voting disk and hard copies of the OCR file. 37. What command would you use to check the availability of the RAC system? crs_stat -t -v (-t -v are optional) 38. What is the minimum number of instances you need to have in order to create a RAC? You can create a RAC with just one server. 39. Name two specific RAC background processes RAC processes are: LMON, LMDx, LMSn, LKCx and DIAG. 40. Can you have many database versions in the same RAC? Yes, but Clusterware version must be greater than the greater database version. 41. What was RAC previous name before it was called RAC?OPS: Oracle Parallel Server 42. What RAC component is used for communication between instances?Private Interconnect. 43. What is the difference between normal views and RAC views?A RAC view has the prefix ‘G’. For example, GV$SESSION instead of V$SESSION 44. Which command will we use to manage (stop, start) RAC services in command-line mode? srvctl 45. How many alert logs exist in a RAC environment? A- One for each instance. 46. What are Oracle Clusterware Components Voting Disk — Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures. The voting disk must reside on shared disk. Oracle Cluster Registry (OCR) — Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR must reside on shared disk that is accessible by all of the nodes in your cluster 47. How do you backup voting disk #dd if=voting_disk_name of=backup_file_name 48. How do I identify the voting disk location #crsctl query css votedisk 49. How do I identify the OCR file location check /var/opt/oracle/ocr.loc or /etc/ocr.loc ( depends upon platform) or #ocrcheck 50. What is SCAN? Single Client Access Name (SCAN) is s a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides a single name for clients to access an Oracle Database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the cluster. ORACLE RAC Questions and Answers pdf download Read the full article
0 notes
Text
Best practices for upgrading Amazon RDS for Oracle DB instances from 11.2.0.4 to 19c
Amazon Relational Database Service (Amazon RDS) for Oracle provides newer versions of databases so you can keep your DB instances up-to-date. These versions can include bug fixes, security enhancements, and other improvements. When Amazon RDS for Oracle supports a new version, you can choose how and when to upgrade your DB instances. As you may be already aware, Amazon RDS for Oracle supports two types of upgrades: major version and minor version. In general, a major engine version upgrade can introduce changes that aren’t compatible with existing applications. In contrast, a minor version upgrade in Oracle generally only includes changes that are backward-compatible with existing applications. In other words, a minor version upgrade applies an Oracle Database Patch Set Update (PSU) or Release Update (RU) to a major version. For example, upgrading from 11.2.0.4 to 19c (19.0.0.0) is a major version upgrade, whereas going from 12.2.0.1.ru-2019-04.rur-2019-04.r1 to 12.2.0.1.ru-2019-07.rur-2019-07.r1 is considered a minor version upgrade. Although Amazon RDS for Oracle allows the option to automate some of the patching decisions, such as minor version patching in the form of automatic minor version upgrade, the majority of patching decisions—including the major version upgrades— are up to you. Therefore, it’s critical to be aware of common issues, steps involved, and best practices to upgrade with the least impact on your business. In this post, we a look at the 11.2.0.4 End of Support timeline in Amazon RDS for Oracle, study the version upgrade choices available to you, and dive deep into the best practices to follow during the upgrade process. This post is relevant to database administrators running their DB instances on Amazon RDS for Oracle. Most pre- and post-upgrade steps in this post are general guidelines from Oracle support and upgrade documentation that are relevant to Amazon RDS for Oracle. This post is written considering the various workloads on Amazon RDS for Oracle that could be impacted by 11.2.0.4 DB upgrade. Not all guidelines mentioned here are relevant to all customers. We strongly recommend that you use your judgment to see which one is suitable for your database based on your options and parameters used. Prior knowledge of Oracle database administration and the environment should be used for performing the upgrade. Amazon RDS for Oracle: 11.2.0.4 version End of Support timeline Oracle has announced the end date of support for Oracle Database version 11.2.0.4 as December 31, 2020, after which Oracle Support will no longer release Critical Patch Updates for this database version. Amazon RDS for Oracle will end support for Oracle Database version 11.2.0.4 Standard Edition 1 (SE1) for License Included (LI) model on October 31, 2020. For the Bring Your Own License (BYOL) model, Amazon RDS for Oracle will end the support for Oracle Database version 11.2.0.4 for all editions on December 31, 2020. All 11.2.0.4 SE1 LI instances will be automatically upgraded to 19c on November 1, 2020. Likewise, the 11.2.0.4 BYOL instances will be automatically upgraded to 19c on January 1, 2021. We highly recommend you upgrade your existing Amazon RDS for Oracle 11.2.0.4 DB instances and validate your applications before the automatic upgrades begin. The following table summarizes the timeline for Oracle 11.2.0.4 with SE1 with LI. Dates Activity Now – October 31, 2020 You can upgrade 11.2.0.4 DB instances manually to the version of your choice August 1, 2020 You can upgrade 11.2.0.4 snapshots manually to the version of your choice August 1, 2020 Amazon RDS for Oracle disables new instance creates on 11.2.0.4 for LI November 1, 2020 Amazon RDS for Oracle starts automatic upgrades of DB instances to 19c November 1, 2020 Amazon RDS for Oracle will start automatic upgrades to version 19c for any DB instances restored from snapshots The following table summarizes the timeline for Oracle 11.2.0.4 on SE, SE1, and EE with BYOL. Dates Activity Now – December 31, 2020 You can upgrade 11.2.0.4 DB instances manually to the version of your choice October 1, 2020 You can upgrade 11.2.0.4 snapshots manually to the version of your choice October 1, 2020 Amazon RDS for Oracle disables new instance creates on 11.2.0.4 for BYOL January 1, 2021 Amazon RDS for Oracle starts automatic upgrades of DB instances to 19c January 1, 2021 Amazon RDS for Oracle starts automatic upgrades of DB instances restored from snapshots to 19c For more information about the EOS of the 11.2.0.4 version by Amazon RDS for Oracle, see Announcement: Amazon RDS for Oracle – End of Support Timeline for 12.2.0.1 and 11.2.0.4 Major Versions. Version upgrade choices in Amazon RDS for Oracle Amazon RDS for Oracle supports the following major version upgrade paths (as of this writing). Current Version Supported Upgrade Path 18.0.0.0 19.0.0.0 12.2.0.1 19.0.0.0 ,18.0.0.0 12.1.0.2 19.0.0.0 ,18.0.0.0, 12.2.0.1 11.2.0.4 19.0.0.0,18.0.0.0,12.2.0.1, 12.1.0.2.v5 and higher 12.1 versions As part of deciding which version to upgrade to, you have the following options: 12.1.0.2 –Extended Support for Oracle Database 12.1.0.2 (Terminal Patch Set) ends on July 31, 2022. Amazon RDS for Oracle plans to support Oracle Database 12.1.0.2 until July 31, 2022. 12.2.0.1 –Limited Error Correction Support for Oracle Database 12.2.0.1 ends on March 31, 2022. Amazon RDS for Oracle plans to support Oracle Database 12.2.0.1 until March 31, 2022. 18c – Support for Oracle Database 18c ends on June 8, 2021. Amazon RDS for Oracle plans to support Oracle Database 18c until June 8, 2021. (There is no Extended Support for Oracle Database 18c.) 19c – Premier Support for Oracle Database 19c ends on April 30, 2024, and the Extended Support ends on April 30, 2027. Amazon RDS for Oracle plans to support Oracle Database 19c until April 30, 2027. We recommend you upgrade your 11.2.0.4 DB instances to 19c because it’s the long-term support release. When upgrading any software, checking for compatibility of the new version and its features plays a crucial role in the upgrade’s overall success. Oracle Database versions and releases can have differences in how they work and interact with applications, which may result in compatibility issues. Although the way you interact with Amazon RDS for Oracle remains the same from the major version upgrade perspective, Oracle Database specific features across major versions may change or even become obsolete. For more information about major version upgrades, see Oracle Database Engine Release Notes. Keep in mind the following: Major version downgrades aren’t supported. A major version upgrade from 11g to 12c must upgrade to an Oracle Patch Set Update (PSU) released in the same month or later. For example, a major version upgrade from Oracle version 11.2.0.4.v14 to Oracle version 12.1.0.2.v11 is supported. However, a major version upgrade from Oracle version 11.2.0.4.v14 to Oracle version 12.1.0.2.v9 isn’t supported. This is because Oracle version 11.2.0.4.v14 was released in October 2017, and Oracle version 12.1.0.2.v9 was released in July 2017. For information about the release date for each Oracle PSU, see Oracle Database Engine Release Notes. Downtime considerations for a major version upgrade Amazon RDS lets you manually initiate a major version upgrade by modifying the DB instance—either via the AWS Management Console, AWS Command Line Interface (AWS CLI), or Amazon RDS API. This is an in-place upgrade and requires downtime for your applications during the upgrade process. In the event of any issues with the upgrade phase, you can restore the latest backup. The duration of the outage varies based on your engine version and the DB instance class. You can determine the exact amount of time it takes by exercising a test upgrade using a snapshot restore of the production database in a pre-production environment. If performing the major version upgrade using the modify DB instance method isn’t desirable for your application, an alternative approach is using logical bi-directional replication with either AWS Database Migration Service (AWS DMS) or Oracle GoldenGate. This method can provide the least downtime for the upgrade. This method involves setting up logical replication between the source and target DB instances (both running on the same version). You then upgrade the target DB instance to 19c, while leaving the source to run on 11.2.0.4. When the upgrade of the target DB instance is complete, you can point your application to the upgraded target DB instance. This method, which uses bi-directional replication between the source and target DB instances, can also be used as a fallback plan, should the upgrade break due to incompatibility. This post covers the upgrade process using the modify DB instance method. The logical replication method of upgrading is out of scope of this post. How Amazon RDS for Oracle performs a major version upgrade When a major version upgrade is invoked on the console, AWS CLI, or Amazon RDS API, the automation completes the following steps: Takes a pre-upgrade snapshot (if configured for backups). You can use this snapshot to roll back to if needed. Shuts down the instance and prepares it for the upgrade. Runs Oracle upgrade scripts. Takes a post-upgrade snapshot. When Amazon RDS initiates Step 1, the instance’s status changes from Available to Upgrading. After Step 4, it returns to Available. The following diagram illustrates the high-level steps when the modify-db-instance AWS CLI call is invoked. Best practices for major version upgrades In this section, we dive deep into the recommended best practices during various phases of the upgrade process: pre-upgrade, upgrade, and post-upgrade. What we discuss in the following sections are the common steps typically completed in most Oracle Database upgrades. For a complete list, see the Oracle Database Upgrade Guide. Pre-upgrade phase Oracle uses the following terminology when upgrading to a higher release (or major version upgrade in Amazon RDS terminology): Deprecated features – Features that are no longer being enhanced, but are still supported for the full life of this release of Oracle Database. Desupported features – Those that are no longer supported by fixing bugs related to that feature. Oracle can choose to remove the code required to use the feature. Where indicated, a deprecated feature can be desupported in a future major release. When moving across multiple major releases during the upgrade process, it’s highly recommended to consult Oracle documentation for deprecated and desupported features, options, and parameters in the intermediary releases as well. For more information, see Behavior Changes, Deprecated and Desupported Features for Oracle Database. You should consider the following factors in the pre-upgrade phase. Downtime considerations A typical upgrade with all possible options in the option group might take 1–2 hours. To reduce downtime, consider the following: Take a manual snapshot using the create-db-snapshot AWS CLI prior to starting the upgrade phase. This speeds up the time taken for a pre-upgrade snapshot (which is automatically invoked during the upgrade phase). If you’re upgrading to 19c, it’s recommended to convert all DBMS_JOB to DBMS_SCHEDULER before the upgrade. During the upgrade, Oracle tries to convert the DBMS_JOB to DBMS_SCHEDULER. If you have a large number of DBMS_JOB entries, the upgrade takes longer. Make sure the audit trails aren’t very large. Pre-upgrade checks and upgrades can take longer with large audit trails. Gather optimizer statistics before the upgrade using DBMS_STATS.gather_dictionary_stats. Also gather fixed and dictionary stats. Remove options that aren’t used. It’s a good practice to remove unused options to speed up the upgrades and result in fewer issues and conflicts when moving from one major version to another. Remove or reset parameters that aren’t used for the same preceding reasons. If necessary, you might also have to upgrade the instance class based on the target instance choice. For more information, see DB Instance Class Support for Oracle. Multi-AZ considerations If your DB instance is in a Multi-AZ deployment, Amazon RDS for Oracle upgrades both the primary and Multi-AZ standby simultaneously. Option groups considerations Consider the following for option groups: By default, when one engine is upgraded to the next higher major version, Amazon RDS for Oracle chooses the default option group unless the new corresponding custom option group for the target version isn’t chosen. For example, when upgrading from 11.2.0.4 to 19c, you should create a new option group that’s compatible with the new version 19c and matches closest to the 11.2.0.4 options, and use it during upgrade process. You should also consider factors such as APEX upgrade, which is recommended to be handled as a separate preparatory process, to speed up the upgrade. This also applies to OEM agents. In some cases, options are uninstalled and reinstalled as you move from one version to another. For example, SQLT is freshly installed, which deletes any old metadata stored by the option. When choosing the version of the OEM agent, consider the compatibility with the OMS. Note the option group and the VPC. If a DB instance is in a VPC, the option group associated with the instance is linked to that VPC. This means that you can’t use the option group assigned to a DB instance if you try to restore the instance to a different VPC or a different platform (for example, when you use the OEM option). When you upgrade a major version, understand the changes Oracle makes to the options in that change. For example, Oracle has desupported the Multimedia option in 19c. However, it separated the MDSYS functionality into a separate option called Locator. Oracle also recommends you move from Locator to Spatial and Graph prior to the upgrade. All MDSYS functionality is available with Spatial and Graph. So, when you upgrade to 19c and install Spatial and Graph, all stored procedures work fine. For more information, see “My Oracle support” article 2347372.1 for more details. The PL/SQL package DBMS_XMLQUERY is deprecated in Oracle Database 18c. Oracle recommends using DBMS_XMLGEN instead. Parameter groups considerations If you associate a new parameter group with a DB instance, reboot the database after the upgrade completes. If you need to reboot the instance to apply the parameter group changes, the instance’s parameter group status shows pending-reboot. You can view an instance’s parameter group status on the console or by using a describe command, such as describe-db-instances. The continuous_mine functionality of the LogMiner package DBMS_LOGMNR.START_LOGMNR is obsolete. It was deprecated in Oracle Database 12c Release 2 (12.2). There is no replacement functionality. Oracle didn’t provide any alternative to this. If you use this, you need to address it in a different way, such as AWS DMS or Oracle GoldenGate. Security considerations The following are some important security considerations: By default, Oracle accounts that have not had their passwords reset before upgrade (that are set to EXPIRED status), and that are also set to LOCKED status, are set to NO AUTHENTICATION after the upgrade is complete. Therefore, post-upgrade, any account with a password set to default, locked, and expired loses the authentication method. Although it could be reverted back, it’s recommended to validate the password for its strength before the upgrade and lock it. Check the accounts that use the case-insensitive password version. Log in to SQL*Plus as an administrative user, and enter the following SQL query. If there are any 10g versions, you should refer to the Oracle documentation to fix 10g versions, or user accounts with LOCKED after the upgrade is complete: SELECT USERNAME, PASSWORD_VERSIONS FROM DBA_USERS; Make sure that you don’t have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE. Starting with Oracle 12c, Oracle uses Oracle Real application Security (ORAS) to store Oracle security policies. Before you upgrade an 11g database to Oracle Database 19c, you must delete any data security roles defined in 11g. After the upgrade, you may have to define the data security roles again. If it’s upgraded without taking this action, any data security policy that includes a data security role becomes invalid in the new 19c database. General considerations Finally, you should consider the following general points: When upgrading to 19c, we recommend you look at the provisioned capacity and see if it would be met. Depending on the instance class your 11g database is running on, you may need to scale the compute to a newer generation of the instance class. For more information, see DB Instance Class Support for Oracle. This could also be an opportunity to evaluate Amazon RDS reserved instances and you may want to purchase new leases for your version before performing the upgrade. Make sure that no objects are invalid before upgrading. It’s a good practice to keep a list of all objects with their respective count, type, and the status prior to the upgrade and compare it after the upgrade. Take a backup of optimizer statistics for all application schemas using the DBMS_STATS package. Collect pre-upgrade AWR or Statspack snapshots prior to the upgrade. These are used during the post-upgrade performance validation. Even though you would have done this in the pre-production environment, it’s a good practice to do this comparison in the production. If you also have a DB maintenance task, such as adding partitions, it’s better to run them before upgrading. Disable scheduled database custom jobs or cron jobs. It’s recommended to disable any custom DDL triggers before upgrade and re-enable them after upgrade. If you’re using materialized views (MV), check the status of all MVs prior to the upgrade. Check the size of the MV logs. If it’s non-zero, address them to make sure they are all in sync. It’s recommended to wait until all MVs have completed refreshing. Make sure that any objects referencing remote databases across database links are accessible to reduce time spent on network timeouts. For more information, see “My Oracle support” Doc ID 1406586.1. For License Included customers, please create a support case for this document. Keep a list of clients and its drivers used along with the version numbers. Identify the corresponding version that works with 19c. Review all hidden parameters and make sure they’re modified or removed to meet the target version. Include all features affected by these parameters as part of the functional and performance testing in the pre-production environment. Upgrade phase After completing the pre-upgrade checks successfully, you can move on to the upgrade phase. If your instance has been using custom option group or parameter group configurations, you must specify new option and parameter groups for the upgrade to go through along with any other additional attributes. When you upgrade your 11.2.0.4 SE1 DB instance to 19c, by default it takes you to SE2 on 19c. In this section, we discuss how to perform the upgrade on the console or via the AWS CLI. To upgrade the engine version of a DB instance on the console, complete the following steps: On the Amazon RDS console, choose Databases. Choose the DB instance that you want to upgrade. Choose Modify. The Modify DB Instance page appears. For License model, choose the desired license type. For DB engine version, choose the new version. Choose Continue and check the summary of modifications. To apply the changes immediately, choose Apply immediately. Choosing this option can cause an outage in some cases. For more information, see Using the Apply Immediately Setting. Otherwise, you can have the upgrade take place in your next weekly maintenance window. On the confirmation page, review your changes and choose Modify DB Instance to save your changes. To perform a major version upgrade in your next maintenance window using the AWS CLI modify-db-instance command, enter the following code: aws rds modify-db-instance --db-instance-identifier mydbinstance --engine-version new_version --allow-major-version-upgrade --no-apply-immediately For information about valid engine versions, use the AWS CLI describe-db-engine-versions command. If you have multiple instances, you could use the AWS CLI combined with a shell script to upgrade multiple instances. For more information about upgrading the database engine for Amazon RDS for Oracle, see Upgrading the Oracle DB Engine. Post-upgrade phase After the upgrade phase, you can complete the following checks and post-upgrade steps: Recompile the INVALID As mentioned in the pre-upgrade phase section, checking for objects with INVALID status and fixing them is required before testing further. This is also a good time to compare the report/list (of object count with status) gathered during the pre-upgrade phase. If Amazon RDS for Oracle is used as an RMAN catalog, upgrade the RMAN recovery catalog. Use the UPGRADE CATALOG command to upgrade the RMAN recovery catalog schema from an older version to the version required by the RMAN client. Complete the following client connectivity steps: A) Make sure to set the ORACLE_HOME, PATH, LD_LIBRARY_PATH, LIBPATH, and more to 19.x in the client home. B) Make appropriate changes to the tnsnames.ora and sqlnet.ora on the client side and incorporate changes related to the 19c or the new endpoint if testing on a new RDS instance restored from the production. C) Test the client connectivity. Based on the identified target version of the driver, perform a regression test. Keep in mind there are cases where the choice of JDBC version might impact performance and change the process optimizer plan. If you used the DBSM_STATS package to export the optimizer stats as mentioned in the pre-upgrade tasks, upgrade the stats table (using DBMS_STATS.UPGRADE_STAT_TABLE) where the optimizer statistics are backed up prior to the upgrade. Then restore statistics and run performance testing. If you have database links to other Amazon RDS for Oracle DB instances or other Oracle instances on Amazon Elastic Compute Cloud (Amazon EC2), it’s recommended to test them from both a functionality and performance standpoint after the upgrade. If you have any external scheduler jobs scheduled in Amazon CloudWatch or cron, ensure those are enabled and can still connect to the database after upgrade and run with no issues. Also make sure to enable any DDL or system triggers disabled as part of the pre-upgrade check. Make sure to have enough free space in SYSTEM and SYSAUX table space. Having an additional 1–2 GB of space in each tablespace is recommended. Validate your applications against the upgraded database. It’s vital that you look at the top SQL statements (either via AWR or Statspack) and verify that they are using the desired plans. After the upgrade, if the SQL statements perform poorly due to the change in the plans by the 19c optimizer, you can use the OPTIMIZER_FEATURES_ENABLE This parameter is alterable at the session level and system level. For example, if you upgrade your database from release 11.2 to release 19c, but you want to keep the release 11.2 optimizer behavior, you can do so by setting this parameter to 11.2.0.4. At a later time, you can try the enhancements introduced in releases up to and including release 19c by setting the parameter to 19.0.0. If you have an automated process of backup and restore, disaster recovery, or production to non-production refresh procedures, we strongly recommend you test them. You need to test any process that interacts with other databases that may or may not be at the same level. Common issues We highly recommend you upgrade the manual snapshots you intend to retain longer, including the snapshots taken using AWS Backup or any third-party partner products. For example, if you used AWS Backup and the backup was migrated to Glacier based on the lifecycle defined in AWS Backup, when the backup is restored, the first thing the RDS does is force upgrade it to one of the supported versions and then complete the restore. If you want to keep it as a copy of the old version (for example, if the database is supporting an old version of a third-party application) you should plan on making a backup using native Oracle tools such as export data pump or RMAN. Also keep the version incompatibilities in mind. After upgrading the database, if you run into connectivity issues such as "ORA-28040: No matching authentication protocol", you might have to upgrade the client version or the JDBC driver. In general, it’s best to match the client and server versions. The best client version for the database version 19c would be client 19c. However, the 11.2.0.4 clients are also supported on 19c. We highly recommend testing for this combination. Please refer to “My Oracle support” document 207303.1. Testing the upgrade of your Amazon RDS for Oracle DB instance Before upgrading the production database to a new major version, you must validate your applications against the new version in a pre-production environment. This rehearsal of the upgrade process also allows you to gauge the time it takes to perform this major version upgrade. The following diagram illustrates the steps to test your upgrade. The process includes the following steps: Take a snapshot of the existing DB instance using the create-db-snapshot AWS CLI call. Restore the DB snapshot using the restore-db-instance-from-db-snapshot AWS CLI call to create a test DB instance on the same version. Run the modify-db-instance AWS CLI call on the test DB instance to upgrade it to the new major version. When the upgrade is complete, you can validate your application against the test DB instance. After the testing is complete, you can schedule and perform the production database upgrade. Additional considerations Amazon RDS for Oracle supports the read replicas feature for the versions 12.1.0.2 and above. If you upgrade your database from 11.2.0.4 to a higher version, you may want to evaluate the read replicas capability and determine if your application requires cross-Region disaster recovery and read scalability. For more information, see Managed disaster recovery and managed reader farm with Amazon RDS for Oracle using Oracle Active Data Guard. As part of upgrading to 19c, you can also use options such as OLAP and Oracle Label security. Conclusion In this post, we reviewed the 11.2.0.4 End of Support timeline in Amazon RDS for Oracle, studied the version upgrade choices available to you, and covered the best practices during the phases of the upgrade process. If you need additional assistance in planning and upgrading your Amazon RDS for Oracle DB instances running on 11.2.0.4, you can reach out to the following resources: AWS Professional Services AWS Partners Partner NAMER LATAM EMEA APAC Contact Apps Associates X X X X [email protected] House of Brick X X X X [email protected] Navisite X X [email protected] NorthBay X [email protected] Wipro X X X X [email protected] For more information about the upgrade, see the following: Upgrading the Oracle DB Engine Oracle SE2 Upgrade Paths Deprecation of Oracle 11.2.0.4 About the Authors Sundar Raghavan is a Senior Specialist Solutions Architect at Amazon Web Services. Srinagesh Battula is a Principal Product Manager at Amazon Web Services https://aws.amazon.com/blogs/database/best-practices-for-upgrading-amazon-rds-for-oracle-db-instances-from-11-2-0-4-to-19c/
0 notes