Feed aggregator
When Upgrading DB Don’t Trust Doc Alone
What’s up Doc!
So there I was, reading documentation and planning upgrade but still not perfect.
DBUA Command-Line Syntax for Active and Silent Mode
changeUserTablespacesReadOnly does not show from help but exists in documenation.
$ which dbua
/app/product/19.3.0.0/bin/dbua
$ dbua -help
Usage: dbua [<flag>] [<option>]
Following are the possible flags:
-createPartialBackup – Flag to create a new offline partial RMAN backup by setting the user tablespaces in R/O mode.
-backupLocation
-disableParallelUpgrade – Flag to disable the parallel execution of database upgrade.
-executePreReqs – Flag to execute the pre-upgrade checks alone for the specified database.
-sid | -dbName
-sid
-dbName
-help – Shows this usage help.
-ignorePreReqs – Ignore error conditions in pre-upgrade checks.
-keepEvents – Flag to keep the configured database events during upgrade.
-silent – This flag allows you to carry on configuration in silent mode.
-sid | -dbName
-sid
-dbName
-skipListenersMigration – Flag to skip the listener migration process as part of the database upgrade.
Following are the possible options:
[-asmsnmpPassword – <Specify ASMSNMP user password>]
[-backupLocation – <Specify directory to backup your database before starting the upgrade>]
[-createGRP – <true | false> To create a guaranteed restore point when database is in archive log and flashback mode.]
[-createListener – <true | false> To create a listener in newer release Oracle home specify listenrName:lsnrPort.]
[-dbName – <Specify Database Name>]
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
[-dbsnmpPassword – <Specify DBSNMP user password>]
[-disableUpgradeScriptLogging – <true | false> This command disables the detailed log generation for running SQL scripts during the upgrade process. By default this is enabled. To enable the log generation, don’t specify this command.]
[-emConfiguration – <DBEXPRESS | CENTRAL | BOTH | NONE>]
[-dbsnmpPassword – <Specify DBSNMP user password>]
[-emPassword – <Specify EM admin user password>]
[-emUser – <Specify EM admin username to add or modify targets>]
[-emExpressPort – <Specify the port where EM Express will be configured>]
[-omsHost – <Specify EM management server host name>]
[-omsPort – <Specify EM management server port number>]
[-asmsnmpPassword – <Specify ASMSNMP user password>]
[-ignoreScriptErrors – <true | false> Specify this flag for ignoring ORA errors during custom scripts.]
[-initParam – <Specify a comma separated list of initialization parameter values of the format name=value,name=value>]
[-initParamsEscapeChar – <Specify escape character for comma when a specific initParam has multiple values. If the escape character is not specified backslash is the default escape character>]
[-excludeInitParams – <Specify a comma separated list of initialization parameters to be excluded.>]
[-keepDeprecatedParams – <true | false> To retain deprecated parameters during database upgrade.]
[-localListenerWithoutAlias – To set LOCAL_LISTENER without TNS Alias.]
[-listeners – <To register the database with existing listeners, specify listeners by comma separated listenerName:Oracle Home. Listeners from lower release home are migrated to newer release home. Specifying -listeners lsnrName1,lsnrName2 or -listeners lsnrName1:<Oracle home path>,-listeners lsnrName2:<Oracle home path>, DBUA searches specified listeners from GI home (if configured), target home and source home>]
[-localRacSid – <Specify the local System Identifier of the cluster database if the cluster database is not registered in OCR>]
[-logDir – <Specify the path to a custom log directory>]
[-newGlobalDbName – <Specify New Global Database Name. This option can only be used for Oracle Express Edition upgrade>]
[-newSid – <Specify New System Identifier. This option can only be used for Oracle Express Edition upgrades>]
[-newInitParam – <Specify a comma separated list of initialization parameter values of the format name=value,name=value. Use this option to specify parameters that are allowed only on the target Oracle home>]
[-initParamsEscapeChar – <Specify escape character for comma when a specific initParam has multiple values. If the escape character is not specified backslash is the default escape character>]
[-oracleHomeUserPassword – <Specify Oracle Home user password>]
[-pdbs – <Specify a comma separated list with the names of the pluggable databases (PDB) that will be upgraded. Specify ALL to select all or NONE to select none of the pluggable databases for upgrade>]
-sid | -dbName
-sid – <Specify System Identifier>
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
-dbName – <Specify Database Name>
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
[-pdbsWithPriority – <Specify a comma separated list of pluggable databases (PDB) to be upgraded along with its corresponding priorities (being 1 the top priority) of the format <pdb name>:<upgrade priority>,<pdb name>:<upgrade priority> >]
-sid | -dbName
-sid – <Specify System Identifier>
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
-dbName – <Specify Database Name>
[-oracleHome – <Specify the Oracle home path of the database>]
[-sysDBAUserName – <User name with SYSDBA privileges>]
[-sysDBAPassword – <Password for sysDBAUserName user name>]
[-performFixUp – <true | false> Enable or disable fix ups for the silent upgrade mode.]
[-postUpgradeScripts – <Specify a comma separated list of SQL scripts with their complete pathnames. These scripts will be executed at the end of the upgrade>]
[-preUpgradeScripts – <Specify a comma separated list of SQL scripts with their complete pathnames. These scripts will be executed before the upgrade>]
[-recompile_invalid_objects – <true | false> Recompile invalid objects as part of the upgrade.]
[-upgrade_parallelism – <Specify number of CPU’s to be used for parallel upgrade>]
[-upgradeTimezone – <true | false> Upgrade the timezone files of the database.]
[-upgradeXML – <Specify the path to the existing pre-upgrade XML file> This option only applies to in-place database upgrades.]
[-useExistingBackup – <true | false> To restore database using existing RMAN backup.]
[-useGRP – <Specify the name of the existing guaranteed restore point> To restore the database using a specified guaranteed restore point.]
Even when -createListener show as valid syntax, using -createListener is not recognized.
$ cat run_dbua.sh
date /app/product/19.3.0.0/bin/dbua -silent \ -sid db01 \ -oracleHome /app/product/11.2.0.4 \ -useGRP upgrade19c \ -recompile_invalid_objects TRUE \ -upgradeTimezone TRUE \ -emConfiguration NONE \ -skipListenersMigration \ -createListener false \ --- failed -upgrade_parallelism 8 date exit ./run_dbua.sh: line 10: -createListener: command not found This works. /app/product/19.3.0.0/bin/dbua -silent -sid db01 -skipListenersMigration -oracleHome /app/product/11.2.0.4 -recompile_invalid_objects true -upgradeTimezone true -emConfiguration NONE -upgrade_parallelism 4 -createListener false
What am i missing?
Hopefully, you will have better luck than I did.
Update: there as a space from line above thanks to https://twitter.com/VincePoore
$ grep -r '[[:blank:]]$' run_dbua.sh -skipListenersMigration \ $ grep -r '[[:blank:]]$' run_dbua.sh | wc -l 1
Column Groups
Here’s an odd little detail about the statistics of column groups. At first glance it’s counter-intuitive but it’s actually an “obvious” (once you’ve thought about it for a bit) consequence of the approximate_ndv() algorithm for gathering stats.
I’ll present it as a question:
I have a table with two columns: flag and v1. Although the column are not declared as non-null neither holds any nulls. If there are 26 distinct values for flag, and 1,000,000 distinct values for v1, what’s the smallest number of distinct values I should see if I create the column group (flag, v1) ?
The question is, of course, a little ambiguous – there’s the number of distinct values that the column (group) holds and the number that a fresh gather of statistics reports it as holding. Here are the stats from a test run of a simple script that creates, populates and gathers stats on my table:
select column_name, num_distinct from user_tab_cols where table_name = 'T1' / COLUMN_NAME NUM_DISTINCT -------------------------------- ------------ FLAG 26 ID 1000000 V1 999040 SYS_STUQ#TO6BT1REX3P1BKO0ULVR9 989120
There are actually 1,000,000 distinct values for v1 (it’s a varchar2() representation of the id column), but the approximate_ndv() mechanism can have an error of (I believe) up to roughly 1.3%, so Oracle’s estimate here is a little bit off.
The column group (represented by the internal column defonition SYS_STUQ#TO6BT1REX3P1BKO0ULVR9) must hold (at least) 1,000,000 distinct values – but the error in this case is a little larger than the error in v1, with the effect that the number of combinations appears to be less than the number of distinct values for v1!
There’s not much difference in this case between actual and estimate, but there test demonstrates the potential for a significant difference between the estimate and the arithmetic that Oracle would do if the column group didn’t exist. Nominally the optimizer would assume there were 26 million distinct values (though in this case I had only created 1M rows in the table and the optimizer would sanity check that 26M).
So, although the difference between actual and estimate is small, we have to ask the question – are there any cases where the optimizer will ignore the column group stats because of a sanity check that “proves” the estimate is “wrong” – after all it must be wrong if the num_distinct is less than the num_distinct of one of the components. Then again maybe there’s a sanity check that only ignores the column group if the estimate is “wrong enough”, but allows for small variations.
I mention this only because an odd optimizer estimate has shown up recently on the Oracle-L mailing list, and the only significant difference I can see (at present) is that a bad plan appears for a partition where this column group anomaly shows up in the stats, but a good plan appears when the column group anomaly isn’t present.
Footnote:If you want to recreate the results above, here’s the model I’ve used (tested on 19.3.0.0 and 11.2.0.4):
rem
rem Script: column_group_stats_5.sql
rem Author: Jonathan Lewis
rem Dated: Oct 2020
rem
rem Last tested
rem 19.3.0.0
rem 11.2.0.4
rem
execute dbms_random.seed(0)
create table t1
as
with generator as (
select
rownum id
from dual
connect by
level <= 1e4 -- > comment to avoid WordPress format issue
)
select
chr(65 + mod(rownum,26)) flag,
rownum id,
lpad(rownum,10,'0') v1
from
generator v1,
generator v2
where
rownum <= 1e6 -- > comment to avoid WordPress format issue
order by
dbms_random.value
/
select column_name, num_distinct
from user_tab_cols
where table_name = 'T1'
/
begin
dbms_stats.gather_table_stats(
ownname => null,
tabname => 'T1',
method_opt => 'for all columns size 1 for columns(v1, flag) size 1'
);
end;
/
select column_name, num_distinct
from user_tab_cols
where table_name = 'T1'
/
Footnote 2:
As an interesting little statistical quirk, if I defined the column group as (flag, v1) rather than (v1, flag) the estimate for the column group num_distinct was 1,000,000.
Partner Webcast – Delivering your Application Solutions on Autonomous Database
We share our skills to maximize your revenue!
How can I do a variable "in list"
Select for update statement too slow
Complete transient session for testing
Query never finish without clear reason
SQL Profile example when best plan not clear
I resolved another production performance issue with a SQL Profile yesterday. I have several posts about SQL Profiles, so I do not want to be redundant, but this case was a little different because it was not clear that I had a better plan. I want to document the challenge that I had deciding if I had the best plan and show the resolution.
On September 21 when I think I was on vacation or otherwise not in the office there was a big performance issue on an older production database. The on-call DBA identified the sql_id of the top SQL statement as 30q69rbpn7g75. But he and an application support developer together could not connect that SQL statement back to the long running reports that were impacting the business. Eventually the issue went away later that night. Here is some of the execution history from that original issue:
SQL_ID PLAN_HASH_VALUE END_INTERVAL_TIME EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average disk write megabytes Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ---------------------------- ----------------------
30q69rbpn7g75 1463081877 21-SEP-20 10.00.04.123 AM 5 420798.922 144724 209959.863 0 0 0 4824516.8 710748.4 0 870
30q69rbpn7g75 1732425686 21-SEP-20 10.00.04.123 AM 13 66.9426923 56.1538462 7.25523077 0 0 0 6410.23077 249.769231 0 414.538462
30q69rbpn7g75 592872749 21-SEP-20 10.00.04.123 AM 1 4144.437 3240 955.246 0 0 0 63878 29725 0 89
30q69rbpn7g75 4175108531 21-SEP-20 10.00.04.123 AM 11 172328.731 92788.1818 62448.1775 0 0 0 3463219 466919.273 0 610.090909
30q69rbpn7g75 2823019537 21-SEP-20 11.00.17.704 AM 19 332354.362 212357.895 22580.778 0 0 0 11145610.8 163882.105 0 303.526316
30q69rbpn7g75 2908690533 21-SEP-20 11.00.17.704 AM 1 23377.169 13070 11680.972 0 0 0 1582917 89479 0 258
30q69rbpn7g75 291832905 21-SEP-20 11.00.17.704 AM 2 40314.0255 15940 24490.36 0 0 0 1831813 128525 0 248
30q69rbpn7g75 1467059382 21-SEP-20 11.00.17.704 AM 1 20179.636 5760 16155.407 0 0 0 124599 84761 0 780
30q69rbpn7g75 1033740578 21-SEP-20 11.00.17.704 AM 1 1728.49 1570 2.906 0 0 0 1525 35 0 12
30q69rbpn7g75 4175108531 21-SEP-20 11.00.17.704 AM 8 147782.833 59617.5 65356.3268 0 0 0 2280007 245985.25 0 402
30q69rbpn7g75 3938646712 21-SEP-20 11.00.17.704 AM 2 139722.393 55905 86105.482 0 0 0 6080269 616766 0 1143.5
30q69rbpn7g75 2823019537 21-SEP-20 12.00.31.659 PM 48 238332.678 138706.875 19077.4738 0 0 0 6928661.85 99573.2708 0 145.395833
30q69rbpn7g75 2823019537 21-SEP-20 01.00.45.195 PM 64 147520.373 80835.1563 19092.0985 0 0 0 4148771.28 106131.016 0 79.890625
30q69rbpn7g75 2823019537 21-SEP-20 02.00.58.673 PM 58 180185.939 113102.931 14365.2987 0 0 0 5926129.21 123920.569 0 22.0344828
30q69rbpn7g75 2823019537 21-SEP-20 03.00.12.404 PM 37 307432.645 201436.216 22904.6901 0 0 0 10204978.9 158950.973 0 201.243243
30q69rbpn7g75 2823019537 21-SEP-20 04.00.26.543 PM 28 465140.082 326940.357 30687.9033 0 0 0 16715547.3 263153 0 460.571429
30q69rbpn7g75 2823019537 21-SEP-20 05.00.40.707 PM 14 934982.157 690958.571 41595.1995 0 0 0 34940770.8 365038.357 0 243.285714
30q69rbpn7g75 2823019537 21-SEP-20 06.00.54.453 PM 14 818768.534 640054.286 42596.9506 0 0 0 33547406.9 451864.786 0 471.5
30q69rbpn7g75 2823019537 21-SEP-20 07.00.08.229 PM 4 2329248.39 2013515 75722.5718 0 0 0 104343531 1027683.25 0 859
30q69rbpn7g75 2823019537 21-SEP-20 08.00.21.705 PM 1 4006478.22 3707840 88265.422 0 0 0 186157328 1082000 0 2744
30q69rbpn7g75 2823019537 21-SEP-20 09.00.34.774 PM 1 1818375.63 1771470 20586.628 0 0 0 88206433 374924 0 2692
30q69rbpn7g75 2823019537 21-SEP-20 10.00.49.484 PM 2 1742051 1399440 41061.122 0 0 0 68750135.5 335797 0 1479.5
30q69rbpn7g75 2823019537 21-SEP-20 11.00.02.543 PM 0 3552963.71 3183770 142948.208 0 0 0 154159601 633488 0 0
This output is from my sqlstat.sql script. Notice how earlier in the day there are several plans with widely varying elapsed times. Plan hash value 1732425686 averages 66 millisconds during the hour ending at 10 am. I do not know why the query has so many different plans. 2823019537 was the bad plan and it got locked in throughout the afternoon and into the night.
Later in that same week I reviewed the plans and found that the longest running plans used certain indexes on the top table and the shorter ones used another index. I looked at the “Segments by Logical Reads” section of an AWR report during the problem time and found that the top segment was index X6_WFCTOTAL. I used by getplans.sql to get all the plans for SQL id 30q69rbpn7g75 and found that the fastest ones used range scans against index X1_WFCTOTAL and the slow ones did range or skip scans against indexes X5_WFCTOTAL or X6_WFCTOTAL. So I picked one plan, 382022017, and used coe_xfr_sql_profile.sql to force 30q69rbpn7g75 to always run with plan 382022017 which used index X1_WFCTOTAL. Here is some execution history of the plan I picked:
SQL_ID PLAN_HASH_VALUE END_INTERVAL_TIME EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average disk write megabytes Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ---------------------------- ----------------------
30q69rbpn7g75 382022017 24-SEP-20 09.00.13.658 AM 2 1713.2625 865 5.805 0 0 0 2135 76.5 0 141
30q69rbpn7g75 382022017 24-SEP-20 10.00.26.654 AM 2 355.329 120 265.0765 0 0 0 8183 1324.5 0 673
30q69rbpn7g75 382022017 24-SEP-20 11.00.39.519 AM 1 190.386 180 28.981 0 0 0 17505 1759 0 1878
30q69rbpn7g75 382022017 24-SEP-20 01.00.05.144 PM 4 217.37625 200 20.723 0 0 0 21009.25 392.25 0 1865
30q69rbpn7g75 382022017 24-SEP-20 04.00.45.160 PM 5 507.1578 114 432.2858 0 0 0 7076.6 936.2 0 407.8
30q69rbpn7g75 382022017 24-SEP-20 05.00.58.322 PM 3 47.5793333 10 35.1866667 0 0 0 504.333333 81.3333333 0 54
30q69rbpn7g75 382022017 24-SEP-20 06.00.11.202 PM 1 313.107 110 229.071 0 0 0 8178 399 0 396
30q69rbpn7g75 382022017 25-SEP-20 04.00.21.833 AM 3 30.7433333 16.6666667 15.0446667 0 0 0 927 89 0 110.666667
This plan was averaging less than 1800 milliseconds. But after putting in the supposedly better plan it seemed to have worse execution times that other plans. Also, the plan hash value was different than 382022017. Here is the execution history from when I put this SQL Profile in place:
SQL_ID PLAN_HASH_VALUE END_INTERVAL_TIME EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average disk write megabytes Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ---------------------------- ----------------------
30q69rbpn7g75 653445232 28-SEP-20 12.00.42.470 PM 1 92139.015 26930 61332.901 0 20.998 0 2202073 2197909 0 171
30q69rbpn7g75 653445232 28-SEP-20 01.00.55.521 PM 9 83373.8866 25152.2222 56386.2852 0 14.6918889 0 2102264.11 2065530 0 2613.88889
30q69rbpn7g75 653445232 28-SEP-20 02.00.08.369 PM 5 101830.312 29264 70157.0998 0 9.1714 0 2439219.6 2432260.2 0 348
30q69rbpn7g75 653445232 28-SEP-20 03.00.21.759 PM 9 89705.846 26606.6667 61716.6917 0 14.2046667 0 2212464.67 2196829.33 0 825.111111
30q69rbpn7g75 653445232 28-SEP-20 04.00.34.724 PM 2 91173.826 26425 63832.838 0 22.1385 0 2181959 2195820.5 0 310.5
30q69rbpn7g75 653445232 28-SEP-20 05.00.47.682 PM 4 90240.0368 26430 62587.1345 0 16.558 0 2200555 2197764.5 0 168.75
30q69rbpn7g75 653445232 28-SEP-20 07.00.13.544 PM 3 107541.972 26816.6667 75418.071 0 16.164 0 2193977 2193579.33 0 39.3333333
30q69rbpn7g75 653445232 29-SEP-20 02.00.45.539 AM 1 92416.895 26410 66108.169 0 21.449 0 2194591 2193764 0 158
30q69rbpn7g75 653445232 29-SEP-20 03.00.30.438 AM 1 103773.265 26510 75455.905 0 13.887 0 2200242 2198725 0 122
30q69rbpn7g75 653445232 29-SEP-20 05.00.56.488 AM 13 74637.6784 20391.5385 51139.8206 0 16.9292308 0 1686133.69 1684935.15 0 24.2307692
30q69rbpn7g75 653445232 29-SEP-20 06.00.09.283 AM 10 105894.074 28882 72971.0734 0 20.1169 0 2418827.8 2417314.8 0 56
30q69rbpn7g75 653445232 29-SEP-20 07.00.22.558 AM 4 89408.2108 26080 61537.7755 0 10.0275 0 2174791 2169846 0 421.75
30q69rbpn7g75 653445232 29-SEP-20 08.00.35.596 AM 7 71644.7906 20574.2857 48465.1234 0 8.331 0 1697739 1694385.43 0 232.857143
30q69rbpn7g75 653445232 29-SEP-20 09.00.48.530 AM 2 113993.942 32580 72589.2515 0 6.2465 0 2672441 2667206 0 145
30q69rbpn7g75 653445232 29-SEP-20 10.00.01.174 AM 6 99793.2442 28600 69181.7687 0 4.65783333 0 2394135.83 2387505.17 0 246
30q69rbpn7g75 653445232 29-SEP-20 11.00.14.240 AM 10 94787.9044 28367 65535.8735 0 10.6895 0 2353904.6 2336951.3 0 476.8
30q69rbpn7g75 653445232 29-SEP-20 12.00.27.167 PM 5 89167.86 26462 61499.462 0 14.4808 0 2200557.8 2195895 0 201.8
Notice first that the plan hash value is 653445232 instead of 382022017. I usually see things like this when the plan has system generated table temporary table names, but I have not seen that in the plan. There must be something like that going on. Either that on the SQL Profile just locks in a slightly different plan. Anyway, 653445232 is the plan caused by the SQL Profile. Notice how the average elapsed time hangs around 90,000 to 100,000 milliseconds. But 382022017 has elapsed times under 1800 milliseconds. Seeing these results, I dropped the SQL Profile. Kind of like a doctor who swears to “do no harm” I did not want to slow down queries that were running very efficiently in my attempt to prevent another system slowdown or outage like we had on September 21. I dropped the SQL Profile on September 29.
Then yesterday, October 19, the problem recurred. This time the bad plan was 3908549093 and used a skip scan on X6_WFCTOTAL. I seem to see a lot of bad plans with skip scans. Here was the execution history before I put the SQL Profile back in:
SQL_ID PLAN_HASH_VALUE END_INTERVAL_TIME EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average disk write megabytes Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ---------------------------- ----------------------
30q69rbpn7g75 3908549093 19-OCT-20 11.00.07.389 AM 16 832180.951 450528.75 68650.1103 0 0 0 21204921.8 495900.5 0 562
30q69rbpn7g75 3908549093 19-OCT-20 12.00.21.033 PM 33 392068.144 194336.364 47412.7197 0 0 0 9271475.06 221593.545 0 309.454545
30q69rbpn7g75 3908549093 19-OCT-20 01.00.34.570 PM 4 3543778.15 1793980 261653.391 0 0 0 82176276.8 1088971 0 1036.5
Between 12 and 1 pm the system was really bogged down with executions of 30q69rbpn7g75 taking almost an hour, 3543778 milliseconds. So, I put the SQL Profile back in. I had the script sitting there from the first time I tried it. These are all kept in the sqlt/utl directory. After putting it in things cleared up and the backlog of reports slowly emptied. I think we killed one long running session and had the user rerun the report with the SQL Profile in place.
SQL_ID PLAN_HASH_VALUE END_INTERVAL_TIME EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average disk write megabytes Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ---------------------------- ----------------------
30q69rbpn7g75 653445232 19-OCT-20 02.00.48.739 PM 27 106287.519 26936.2963 61603.699 0 7.41166667 0 2226367.89 2204705.19 0 1358.25926
30q69rbpn7g75 3908549093 19-OCT-20 02.00.48.739 PM 0 9380042.72 6465310 308289.185 0 0 0 294469425 1581492 0 7067
30q69rbpn7g75 653445232 19-OCT-20 03.00.01.772 PM 80 103522.356 26259.25 67322.2418 0 21.1012125 0 2159835.1 2142347.26 0 914.2625
30q69rbpn7g75 653445232 19-OCT-20 04.00.14.862 PM 31 99252.0065 26990 66069.6995 0 24.5769355 0 2227363.45 2213021.26 0 885.709677
30q69rbpn7g75 653445232 19-OCT-20 05.00.27.768 PM 5 94587.0244 26988 64440.3338 0 24.1514 0 2223326.4 2204672.6 0 1553.6
30q69rbpn7g75 653445232 19-OCT-20 06.00.40.629 PM 5 93301.2074 26690 65105.9732 0 14.0782 0 2213653.6 2203033 0 736.4
30q69rbpn7g75 653445232 19-OCT-20 07.00.53.923 PM 3 101117.233 27193.3333 72020.9813 0 15.4373333 0 2225218.33 2207644 0 1623
30q69rbpn7g75 653445232 20-OCT-20 12.00.59.660 AM 1 92061.35 26550 64953.945 0 22.245 0 2210157 2206170 0 235
30q69rbpn7g75 653445232 20-OCT-20 02.00.25.964 AM 1 92872.242 26470 66092.822 0 11.999 0 2208305 2206231 0 158
30q69rbpn7g75 653445232 20-OCT-20 05.00.36.773 AM 2 88107.2095 26075 61670.129 0 17.2175 0 2205332.5 2203981 0 116
30q69rbpn7g75 653445232 20-OCT-20 06.00.49.657 AM 1 91007.493 26210 64276.474 0 9.972 0 2208516 2206310 0 177
30q69rbpn7g75 653445232 20-OCT-20 07.00.02.613 AM 4 101878.314 26940 65491.475 0 30.476 0 2210945.25 2204828.75 0 461.5
30q69rbpn7g75 653445232 20-OCT-20 08.00.15.526 AM 10 97441.3635 26496 67549.4579 0 3.178 0 2197412.4 2192467.5 0 478.1
30q69rbpn7g75 653445232 20-OCT-20 09.00.28.457 AM 5 59362.9672 17038 41573.7714 0 7.767 0 1416804.4 1416061 0 10.2
30q69rbpn7g75 653445232 20-OCT-20 10.00.41.653 AM 6 108681.505 30798.3333 75082.4997 0 10.5146667 0 2558854.67 2549531.83 0 405
30q69rbpn7g75 653445232 20-OCT-20 11.00.54.611 AM 1 186021.778 53440 131017.985 0 18.976 0 4285650 4202806 0 814
But with the SQL Profile in place it still in consistently around 90,000 milliseconds even today. There are no executions under 1800 milliseconds like before. It is a puzzle, but it got us through the issue. I am planning to leave this in place to prevent another production outage, but I suspect that these reports may be running longer than normal in many cases. But at least they are consistent and the business needs are being met.
So, this is another example of a SQL Profile to the rescue in a production database performance issue. But this was different because it was not clear that the one plan was always the best one. I went so far as to put it in and take it out and then put it in again. I ended up leaving it in because it resolved a performance issue that we had twice and do not want to have in the future. Hopefully any less than optimal performance outside of these peak times will be worth it since we are preventing an outage with significant impact to the business.
Bobby
Generate string based on pattern
Update rows when cursor returns no data
How to return header and data using sys_refcursor in oracle pl sql
From DBA to DBI
Recently Pradeep Parmer at AWS had a blog post about transitioning from DBA to DBI or in other words from database administrator to database innovator. I wonder what exactly is the difference here as any DBA worth his or her salt is an innovator in itself.
Administering a database is not about sleepily issuing backup commands or in terms of Cloud managed databases clicking here and there. Database administration has evolved over time just like other IT roles and is totally different what it was few years back.
Regardless of the database engine you use, you have to have a breadth of knowledge about operating systems, networking, automation, scripting, on top of database concepts. With managed database services in cloud like AWS RDS or GCP Cloud SQL or Big Query many of the skills have become outdated but new ones have sprung up. That has always been the case with DBA field.
Taking the example of Oracle; what we were doing in Oracle 8i became obsolete in Oracle 11g and Oracle 19c is a totally different beast. Oracle Exadata, RAC, various types of DR services, fusion middleware are in itself a new ballgame with every version.
Even with managed database services, the role of DBA has become more involved in terms of migrations and then optimizing what's running within the databases from stopping the database costs going through the roof.
So the point here is that DBAs have always been innovators. They have always been trying to find out new ways to automate the management and healing of their databases. They always are under the pressure to eke out last possible optimization out of their system and that's still the case even if those databases are supposedly managed by cloud providers.
With purpose built databases which are addressed different use case for different database technology the role of DBA has only become more relevant as they have to evolve to address all this graph, in-memory, and other cool nifty types of databases.
We have always been innovators my friend.
DBMS_ASSERT returning the ORA-44002: invalid object name exception for existing database links and directories
Oracle TNS poison attack vulnerability
Oracle Processes consuming 100% CPU
SQL Server: Generating SQL script using PowerShell and Template file
In this blog post, I will share with you a small PowerShell script I did recently.
I have noticed that my customer performs a very repetitive and time-consuming task almost every day.
New columns are added to tables on their business-critical database and they need to maintain SQL scripts file with all the ALTER TABLE statements for each new column.
For every new column, my customer copy-pastes the following SQL Script and then change parts of it.
/***********************************
*
* New column
* Schema: Order
* Table: TestTable2
* Column: ColumnName1
*
* History
* Date: 18/10/2020
* User: Steven Naudet
*
************************************/
IF NOT EXISTS (
SELECT *
FROM sys.tables AS t
JOIN sys.[columns] AS c ON t.[object_id] = c.[object_id]
JOIN sys.schemas AS s ON s.[schema_id] = t.[schema_id]
WHERE 1=1
AND s.name = 'Order'
AND t.name = 'TestTable2'
AND c.name = 'ColumnName1'
)
BEGIN
PRINT 'Altering table Order.TestTable2 adding column [ColumnName1]' ;
ALTER TABLE [Order].TestTable2
ADD
ColumnName1 NOT NULL;
END
/***********************************
*
* End New column ColumnName1
*
************************************/
The highlighted lines are manually edited by my customer every time there’s a new column to be added to the database, which can occur 20 times per week.
I decided to write a PowerShell function to do this task faster so my customer can work on more interesting things instead.
The idea is to use a Template file for the SQL Script. The file is similar to the SSMS templates.
The PowerShell script modifies the template and as output sends the SQL to Clipboard using Set-Clipboard.
Consecutive calls to the function will add the SQL commands after one another in the Clipboard. This way my customer can just Paste the generated SQL script to his SQL source control tool.
You can see the script in action with the GIF below.
Here is the script.
function New-AddColumnSQL {
[CmdletBinding()]
param (
[Parameter(Mandatory=$true)][string] $Schema,
[Parameter(Mandatory=$true)][string] $Table,
[Parameter(Mandatory=$true)][string] $Column,
[Parameter(Mandatory=$true)][string] $Type,
[Parameter(Mandatory=$false)][string] $defaultValue,
[Parameter(Mandatory=$false)][switch] $isNotNull = $false,
[Parameter(Mandatory=$false)][string] $User = 'Steven NAUDET'
)
$TemplateFile = 'Z:\scripts\TemplateAddColumn.sql'
$Clipboard = Get-Clipboard
# Clear Clipboard if first call to the function
if ($Clipboard -like '*Altering table*') {
$returnMessage = 'SQL Script appended to Clipboard'
} else {
$returnMessage = 'SQL Script pasted to Clipboard'
Set-Clipboard -Value $null
}
$ColumnDef = $Type
# NOT NULL
if($isNotNull) {
$ColumnDef = $ColumnDef + ' NOT'
}
$ColumnDef = $ColumnDef + ' NULL'
# DEFAULT value
if($defaultValue) {
$ColumnDef = $ColumnDef + ' DEFAULT ' + $defaultValue
}
$SQLscript = Get-Item -Path $TemplateFile | Get-Content
$SQLscript = $SQLscript.Replace('<Date>', (Get-Date -UFormat "%d/%m/%Y"))
$SQLscript = $SQLscript.Replace('<SchemaName>', $Schema)
$SQLscript = $SQLscript.Replace('<TableName>', $Table)
$SQLscript = $SQLscript.Replace('<ColumnName>', $Column)
$SQLscript = $SQLscript.Replace('<UserName>', $User)
$SQLscript = $SQLscript.Replace('<ColumnDefinition>', $ColumnDef)
Set-Clipboard $SQLscript -Append
return $returnMessage
}
There’s probably a lot of room for improvement for this code but the goal of this blog post is to show you how handy PowerShell can be. It can help you save a lot of time.
I took about 1 hour to write this code and I’m sure my customer will save more than that every month.
Cet article SQL Server: Generating SQL script using PowerShell and Template file est apparu en premier sur Blog dbi services.
Oracle Database Appliance vs Oracle Cloud Infrastructure
Oracle Database Appliances are very popular these days. And not only among new customers for this kind of engineered systems. Almost all customers already using old generation ODAs are renewing their infrastructure by choosing again ODAs, meaning that the solution is good enough and probably better than anything else. But now, public clouds are a real alternative to on-premise servers, and Oracle Cloud Infrastructure is a solid competitor vs Amazon and Azure public clouds. So what’s the best solution for your databases, ODA or OCI? Let’s do the match.
Round 1 – CostYes, it is important. You will need to buy ODAs and you will need a budget for that. Nothing new regarding this platform, it requires an investment. ODA is cheaper since light models are available, but if you need significant amount of storage, it comes at a cost. But hopefully, the cost is quite similar to another x86 platform, and the ODA doesn’t have these hidden costs due to additional work for troubleshooting compatibility issues.
Cost works differently on OCI. Basically, you will pay for servers, storage, services on a monthly basis. No initial investment is needed, and that is one of the advantages of OCI. However, don’t expect the “TCO” to be lower than acquiring your own hardware. I do not mean that cloud solutions are expensive, but the cost will be quite similar to an on-premise solution after some years. Going to the cloud is mainly changing your mind about what’s an infrastructure. Is it servers you manage on your own or is it a platform for running your information system?
There is no winner in this round, you will only know after several years which solution would have been the less expensive.
Winner: none
Round 2 – Deployment speedODA allows fast deployment of a new database infrastructure. Actually, it’s the best on-premise solution regarding that point. And it’s a serious advantage over DIY platforms. Being able to create your first database the same day you open the box is quite nice. But OCI is even better, because at this very moment we are talking now, your future servers are already available, Terabytes of storage are waiting for you, and databases are almost there, few clicks away from now. If you’re looking for fast deployment, OCI is an easy winner.
Winner: OCI
Round 3 – SecurityEverybody is talking about security. Is my database safer in the cloud than in my own datacenter? Actually, it’s quite hard to tell. For sure, OCI is a public cloud, meaning that your database can be reached from virtually everywhere. But you will probably build strong security rules to protect your cloud infrastructure. You will use IPSec VPN between OCI and your on-premise site, or a FastConnect channel to dedicate a link between your on-premise equipment and OCI avoiding data to transit through the internet. Putting your database in the cloud is not less secure than giving remote connection on your infrastructure to your employees or providers. Furthermore, databases in OCI are stored using encryption, even with Standard Edition and without the need for Advanced Security option.
On ODA, you database is in your network, meaning not on something public and meaning less visible. This is good, but again, only if you have good security rules inside your company.
Winner: none
Round 4 – PerformanceODA is a strong performer, especially the X8-2M model. With up to 75TB of NVMe SSD, it’s quite tough to achieve better performance with anything else. Yes you could grab few MB/s more or ms less with few other solutions, but do you really think that your users will see the difference? No. And what about OCI? OCI rely on SSD storage only, that’s a very good start. And do they offer NVMe? Yes, for sure. Bare metals servers (BM Dense I/O) provide up to 51TB of RAW capacity based on 8 NVMe drives. And something tells me that these servers are actually nearly the same as ODA X7-2Ms. So expect similar performance on both solutions.
Winner: none
Round 5 – License managementNo doubt that on-demand capacity of Enterprise licenses is one of the key feature of the ODA. You can start with only 1 Enterprise license on each ODA, and increase the number of licenses when you need more resources. A kind of fine tuning for the licenses.
On OCI, you can choose to bring your own license you bought long time ago, and keep your investment for later if for some reason you would like to go back to on-premise infrastructure. Or you can choose to include the license fees into the monthly fees. With the first option, you manage your licenses as you always did, and should be careful when you increase the cloud resources dedicated to your databases (mainly the oCPUs). With the second option, you don’t have to manage your licenses anymore: you don’t need to buy them, pay the yearly support, or review them regularly because all is included with your OCI database services. It’s simply a great feature.
Winner: OCI
Round 6 – SimplicityODA and OCI share the same goal: simplify your database infrastructure. ODA is simplifying by providing the best automation available for deploying complex Oracle stack. And when you come from an existing on-premise infrastructure, migration to ODA will be quite easy. OCI looks even more simplifying, but if you will not have to work on the servers, you’ll have to think about how to implement your infrastructure. Which subnet for my databases? Should I also move my application servers? What kind of network connectivity with my on-premise environment? Which kind of database service fits my needs?
If you’re starting from scratch with Oracle databases, it’s probably more simple to go directly to OCI. If you’re migrating from an existing on-premise environment, it’s probably more simple to replace your existing servers with ODAs. No winner here.
Winner: none
Round 7 – ControlFor some customers, being able to control their infrastructure is vital. On public clouds, you will not have control on everything, because someone will do a part of the maintenance job, mostly automated tasks. And this is for some other customers something they don’t want to manage. On ODA, you control everything on your server: first, it’s not mandatory to connect it to the internet. Updates on ODA cannot be automated and will be applied manually through good old zipfiles, and in case of serious problems, ODA is fast to redeploy. So if you need to have total control over your infrastructure, the ODA is the best solution for you.
OCI is only a good solution if you already planned to lose some control, for obvious workload reasons.
Winner: ODA
Round 8 – ResilienceDisaster recovery solutions were not so common 10 years ago. People were relying on tape backups, were confident about this solution and were believing they would be able to restore the tape “somewhere”, without asking them where actually was “somewhere”. At best, the old servers were kept for disaster recovery usage, in the same rack.
This has definitely changed, and now disaster recovery is part of each new infrastructure design. And regarding the software side of the database, this is something mature and highly reliable (with Data Guard or Dbvisit standby). The most complex part being to design the split into multiple datacenters (2, most of the time). Implementing that cleverly, avoiding Single Point Of Failure that could wipe out the efforts to achieve high resiliency, being a tough challenge. ODA is a server like others, and you will have to do the same amount of work to design a high resilient infrastructure.
Cloud providers have been thinking about disaster recovery since the very beginning. The datacenters are spread all around the world, and each one has separate availability domains (isolated building blocks), allowing multiple levels of disaster recovery scenarios. Furthermore, storage and backups naturally embed this high resilience. And as everyone will use the same mechanisms, you can trust OCI regarding resilience.
As a conclusion, it’s nearly impossible to reach the level of resilience of OCI on your on-premise ODA infrastructure, that must be said…
Winner: OCI
What about the other solutions?For sure, it still possible to build your own database infrastructure with classic servers. But do you really have time for that?
EXADATA is also a nice solution if you need such a beast for multi-TB databases with high number of transactions or fastest BI platform. And now it can bring you both the advantages of OCI and appliance with the Cloud@customer mode. Oracle brings the server in your datacenter, and you only pay for it monthly as if you were using it in the cloud.
Hybrid solution with a mix of ODA of OCI could also fit your needs but you’ll have to manage both technologies, and that’s not so smart. Unless you need this kind of solution for the transition to the cloud…
Is ODA better than OCI? Is OCI better than ODA? Both solutions are smart choices and none will disappoint you if you achieve to leverage the advantages and avoid the constraints of each one. On OCI, you will benefit from immediate availability of the resources, fast provisioning, flexibility, no-brainer license management. With ODA, you will keep your database infrastructure at home, and you will have strong performance and full control over your servers, including for the cost. Choosing between these two solutions is only a matter of strategy, and this does not only concern the DBA.
Cet article Oracle Database Appliance vs Oracle Cloud Infrastructure est apparu en premier sur Blog dbi services.
Followup with Database runInstaller applyRU Failed Me
This is a followup Database runInstaller applyRU Failed Me
I finally figured out my error which I should have seen from the beginning and better error reporting would have helped.
Can you guess what’s wrong?
unzip -qod $ORACLE_HOME /vagrant_software/p6880880_190000_LINUX.zip; echo $?
versus
unzip -qod $ORACLE_HOME /vagrant_software/p6880880_190000_Linux-x86-64.zip; echo $?
The wrong platform for opatch was used.
Here is what should have been deployed.
[oracle@ol7-112-dg1 ~]$ unzip -qod $ORACLE_HOME /vagrant_software/p6880880_190000_Linux-x86-64.zip; echo $?
0
[oracle@ol7-112-dg1 ~]$
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517242;Database Release Update : 19.3.0.0.190416 (29517242)
OPatch succeeded.
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.21
OPatch succeeded.
[oracle@ol7-112-dg1 ~]$
[oracle@ol7-112-dg1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.3.0.0/db_1
[oracle@ol7-112-dg1 ~]$
### This failed:
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
/u01/app/oracle/product/19.3.0.0/db_1/OPatch/opatch: line 839: [: too many arguments
/u01/app/oracle/product/19.3.0.0/db_1/OPatch/opatch: line 839: [: too many arguments
Java (1.7) could not be located. OPatch cannot proceed!
OPatch returns with error code = 1
[oracle@ol7-112-dg1 ~]$
### This works but why?
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch version -jdk $ORACLE_HOME/jdk
OPatch Version: 12.2.0.1.21
OPatch succeeded.
[oracle@ol7-112-dg1 ~]$
### Here is java version and noticed it's 64-Bit
[oracle@ol7-112-dg1 bin]$ $ORACLE_HOME/jdk/bin/java -version
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)
[oracle@ol7-112-dg1 bin]$$ORACLE_HOME/runInstaller -applyRU /home/oracle/patch/31305339 should now work.
Database runInstaller applyRU Failed Me
I still remembered the cliche a manager used to tell me, “Slow and steady win the race.”
Looks like it is true with Oracle software as you never know what’s going to get.
I am trying to install 19.3 database software and apply Patch 31305339 – GI Release Update 19.8.0.0.200714 which failed misserably.
There is little to no information for the failure (at least from what I was able to ascertain).
Resolving the issue requires starting over from the beginning.
Here are the steps taken:
[oracle@ol7-112-dg1 ~]$ cat /etc/system-release Oracle Linux Server release 7.7 [oracle@ol7-112-dg1 ~]$ ll /etc/ora* -rw-r--r--. 1 root root 32 Aug 8 2019 /etc/oracle-release -rw-rw-r--. 1 oracle oinstall 790 Oct 14 19:13 /etc/oratab [oracle@ol7-112-dg1 ~]$ echo $new_db_home /u01/app/oracle/product/19.3.0.0/db_1 [oracle@ol7-112-dg1 ~]$ rm -rf $new_db_home [oracle@ol7-112-dg1 ~]$ ls $new_db_home ls: cannot access /u01/app/oracle/product/19.3.0.0/db_1: No such file or directory [oracle@ol7-112-dg1 ~]$ echo $zip_loc /vagrant_software [oracle@ol7-112-dg1 ~]$ ls -l $zip_loc/LINUX.X64_193000_db_home.zip -rwxrwxrwx. 1 vagrant vagrant 3059705302 Sep 5 2019 /vagrant_software/LINUX.X64_193000_db_home.zip [oracle@ol7-112-dg1 ~]$ unzip -qo $zip_loc/LINUX.X64_193000_db_home.zip -d $new_db_home; echo $?; ls $new_db_home 0 addnode crs dbjava dmu hs jdbc md olap ords plsql rdbms runInstaller sqlj ucp apex css dbs drdaas install jdk mgw OPatch oss precomp relnotes schagent.conf sqlpatch usm assistants ctx deinstall dv instantclient jlib network opmn oui QOpatch root.sh sdk sqlplus utl bin cv demo env.ora inventory ldap nls oracore owm R root.sh.old slax srvm wwg clone data diagnostics has javavm lib odbc ord perl racg root.sh.old.1 sqldeveloper suptools xdk [oracle@ol7-112-dg1 ~]$ $new_db_home/OPatch/opatch version OPatch Version: 12.2.0.1.17 OPatch succeeded. [oracle@ol7-112-dg1 ~]$ unzip -qo $zip_loc/p6880880_190000_LINUX.zip -d $new_db_home; echo $? 0 [oracle@ol7-112-dg1 ~]$ $new_db_home/OPatch/opatch version /u01/app/oracle/product/19.3.0.0/db_1/OPatch/opatch: line 839: [: too many arguments /u01/app/oracle/product/19.3.0.0/db_1/OPatch/opatch: line 839: [: too many arguments Java (1.7) could not be located. OPatch cannot proceed! OPatch returns with error code = 1 [oracle@ol7-112-dg1 ~]$ $new_db_home/OPatch/opatch version -jdk $new_db_home/jdk OPatch Version: 12.2.0.1.21 OPatch succeeded. [oracle@ol7-112-dg1 ~]$ cd $new_db_home [oracle@ol7-112-dg1 db_1]$ ls -l /home/oracle/patch/31305339 total 132 drwxr-x---. 5 oracle oinstall 81 Jul 10 05:20 31281355 drwxr-x---. 5 oracle oinstall 62 Jul 10 05:17 31304218 drwxr-x---. 5 oracle oinstall 62 Jul 10 05:18 31305087 drwxr-x---. 4 oracle oinstall 48 Jul 10 05:20 31335188 drwxr-x---. 2 oracle oinstall 4096 Jul 10 05:18 automation -rw-rw-r--. 1 oracle oinstall 5054 Jul 10 05:46 bundle.xml -rw-rw-r--. 1 oracle oinstall 120878 Jul 20 19:04 README.html -rw-r--r--. 1 oracle oinstall 0 Jul 10 05:17 README.txt [oracle@ol7-112-dg1 db_1]$ pwd /u01/app/oracle/product/19.3.0.0/db_1 [oracle@ol7-112-dg1 db_1]$ ./runInstaller -debug -applyRU /home/oracle/patch/31305339 Preparing the home to patch… Applying the patch /home/oracle/patch/31305339… OPatch command failed while applying the patch. For details look at the logs from /u01/app/oracle/product/19.3.0.0/db_1/cfgtoollogs/opatchauto/. [oracle@ol7-112-dg1 db_1]$ cd /u01/app/oracle/product/19.3.0.0/db_1/cfgtoollogs/opatchauto -bash: cd: /u01/app/oracle/product/19.3.0.0/db_1/cfgtoollogs/opatchauto: No such file or directory [oracle@ol7-112-dg1 db_1]$ cd /u01/app/oracle/product/19.3.0.0/db_1/cfgtoollogs/ [oracle@ol7-112-dg1 cfgtoollogs]$ ll total 4 drwxrwx---. 2 oracle oinstall 4096 Oct 17 16:44 oui [oracle@ol7-112-dg1 cfgtoollogs]$ [oracle@ol7-112-dg1 db_1]$ ./runInstaller -debug -applyRU /home/oracle/patch/31305339 ERROR: The home is not clean. This home cannot be used since there was a failed OPatch execution in this home. Use a different home to proceed. [oracle@ol7-112-dg1 db_1]$ Executing "gridSetup.sh" Fails with "ERROR: The home is not clean"(Doc ID 2279633.1) Fails with "ERROR: The home is not clean" . [root]# rm-Rf [oracle]$ unzip linuxx64_12201_grid_home.zip -d
Pages
