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

Original: https://www.enmotech.com/web/detail/1/796/1.html

Introduction

Recently, in DBASK Mini Program, we have newly linked official accounts in data fields such as Han Feng Channel, Internet Reconnaissance, Database SQL, SQL Database Development, Cross-border Architects, and Sequoia Architecture Notes. You are welcome to read and share.

Collection of questions and answers

Next, we share the problems and diagnosis summary sorted out in this issue for your reference and study. The detailed diagnosis and analysis process can be redirected to Mini Program through the title link.

Question 1. Whether the Windows system needs to set up filesystemio_options

For example, the data version is 10g

Diagnostic conclusion: no setting is required, refer to "Best Practices For Oracle Database Performance On Windows"

Problem 2. Windows installation oracle dbca database building error ora-27102 out of memory

Windows 2016 (64bit) install oracle 11g R2 (64bit) dbca build an error ora-27102 out of memory,windows system memory 64g allocated to oracle memory 24g sufficient free memory, this is because there are any restrictions on the 2016 system how to solve it?

Diagnostic conclusion: the problem is the parameters of the window operating system. Change the number of processor cores from the default 1 to 8 or the maximum in the control panel, restart, and then build the library with dbca. Success.

Question 3. Cluster resource ora.LISTENER_LEAF.lsnr, resource offline, what kind of resource is this?

Cluster resource ora.LISTENER_LEAF.lsnr, resource offline. Db version 12.2.0.1.

Diagnostic conclusion: this is a feature of 12c Oracle Flex Cluster, which introduces the concept of leaf node and does not need to connect to shared storage directly. LISTENER_LEAF is used to register instances running on leaf node.

Question 4. The Execute to Parse% index is 24.95. the proportion of hard parsing is very high.

In the database, Execute to Parse% index 24.95 SQL hard parsing proportion is very low, excluding cursor_sharing= force, system load is very low, AWR sampling time is 60 minutes, db time1mins.

Hope to get the SQL to find the SQL text that causes a lot of hard parsing, or to connect to mode.

Gets a way to reduce hard parsing.

Diagnostic conclusion: generally speaking, the main reason for high hard resolution of SQL is not using binding variables, followed by insufficient memory or BUG and other reasons.

You can use the SQL in the details to find out the SQL that does not use bound variables.

Question 5. Is there any difference in Asm disk group redundancy mode IO performance?

Asm disk group redundancy mode, is there any difference in IO performance? What's the difference?

Diagnostic conclusion: in the read scenario, only one of the AU is read regardless of redundancy, so there is no loss of read performance.

In the write scenario, the IO performance of externally redundant ASM disk groups can be approximately understood as the IO synthesis of all LUN, including IOPS and throughput. Normal redundancy is double-write, because you have to write two identical AU each time, so it can be understood as the loss of half of the IO-related indicators. The loss of High redundancy is 2/3.

Question 6. Can ogg 12c apply trail files with a source of 10g?

For example, can 10g trail files be applied to 12c? what do you need to pay attention to?

Diagnostic conclusion: it should be no problem, it is recommended to test and verify it. The parameter FORMAT RELEASE is added to the source extraction process and the transmission process. In addition, the target side needs non-PDB mode.

Question 7. What is the impact of the uniqueness constraint and index of deleting a table with hundreds of millions of records

Such as the question, will it affect if you delete the uniqueness constraints and indexes of a table with 100 million records? How long will it take to rebuild?

Diagnostic conclusion: of course, the deletion itself has no effect. It's just that data integrity cannot be guaranteed and indexes cannot be utilized. As for the creation time, depending on the table size, the current traffic, and the system iUnix, you need to scan the table to read the data, and then sort in memory to create a unique index. You can take a look at the session_longops, or guess the time required based on the segment size of the index.

Question 8. Building goldengate for TB-level database

Using table or schema to build ogg at this level? Which way is convenient for later maintenance when the table structure changes in the future?

Diagnostic conclusion: if you have to use OGG, it is recommended to split multiple processes according to the table, otherwise problems with one process will affect the synchronization of the entire library.

Question 9. The impact of oracle rac time adjustment

Rac is configured with clock synchronization. Due to a problem with the clock synchronization server, the time of the two nodes of the rac is adjusted to 3 days at the same time, and then the cluster is shut down to manually adjust the system time. After starting the cluster, it is found that there are problems with both begintime and snapshot time of undo. Now undo is rebuilt. Does this accident have any other impact on the database? The problem of business data has been communicated with R & D and has no impact.

Expert answer: if the business data confirms that there is no problem, and the database can start and run normally, it will not be a big problem. Log and monitoring data classes are mainly dependent on timestamps. It is recommended to check and deal with them:

1. Clean up the log of the problem under the alertlog backup of grid/db

2. Delete part of snapshot from AWR backup to avoid confusion

3. Related records of sys.WRH$_ACTIVE_SESSION_HISTORY

Question 10. Is Oracle Stream no longer supported? What version did you start with?

Previous old systems, some of which are still replicating with Stream streams, are no longer supported by Oracle. What will we do in the future?

Diagnostic conclusion: Oracle Streams has been deprecated in Oracle Database 12c version 1 (12.1). Support features introduced in Oracle Database 12c and later are not supported, including multi-tenant architecture, LONG VARCHAR data types, long identifiers, and other features.

Oracle Database 18c is the final version supported by Oracle Streams. Starting with Oracle Database 19c, Oracle Streams will no longer be supported.

For replication, Oracle GoldenGate is the ultimate solution for Oracle database replication.

Question 11. How to migrate data files when DG is added to ASM

The DG on the asm where the original tablespace created by the oracle12c database is used up, I added a new DG how to modify the parameter settings of the tablespace on the original DG, such as automatic tablespace expansion

Diagnostic conclusion: turn off the automatic extension of all data files on the previous DG, and then create a data file for the corresponding tablespace on the new DG. There are also temp, undo, which are easy to migrate, and can be moved to the new DG.

Question 12. About the two modes of Extended RAC, the mode of stress test storage replication is better than ASM redundancy.

We are implementing a disaster recovery project to compare the performance of Extended RAC in storage replication and ASM redundancy for customer selection. The current test results show that the storage replication method is better than the ASM redundancy method. Do the test results meet the expectations? how to understand the results?

Diagnostic conclusion: I think it should be in line with expectations. There will be more additional hardware support at the storage replication level, such as cache, such as IO replication optimization at the hardware level. And these are simple ASM multiple copies of the write does not have. After all, storage-level replication products as a commercial product to sell the price, must have a more worthwhile function.

Source: Mo Tianlun (ID:enmocs)

(note: for specific answers, please go to DBASK Mini Program or the official account "data and Cloud" article history)

Want to know more about databases and cloud technologies?

Come and follow the official account of "data and Cloud" and the official website of "Yunhe Enmo". We look forward to learning and making progress with you!

(scan the QR code above and follow the official account of "data and Cloud" for more science and technology articles.)

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.