离线运行Oracle Database In-Memory Advisor

发布时间:2023年12月17日

概念

离线运行Oracle Database In-Memory Advisor,就是不在生产系统上运行。这样可以避免影响生产系统。但需要从生产系统导出以下的数据:

  1. AWR Dump
  2. AWR补充数据

过程

导出AWR Dump

连接到CDB root运行。

SQL> connect / as sysdba
SQL> @?/rdbms/admin/awrextr.sql
~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will extract the AWR data for a range of snapshots  ~
~  into a dump file.  The script will prompt users for the         ~
~  following information:                                          ~
~     (1) database id                                              ~
~     (2) snapshot range to extract                                ~
~     (3) name of directory object                                 ~
~     (4) name of dump file                                        ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     DB Name      Host
------------ ------------ ------------
* 1677860020 ORCL         instance-202
                          31013-1024-d
                          b19c-iaas


The default database id is the local one: '1677860020'.  To use this
database id, press <return> to continue, otherwise enter an alternative.

Enter value for dbid: <这里输入回车,使用默认值>

Using 1677860020 for Database ID


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days: 7

Listing the last 7 days of Completed Snapshots

DB Name        Snap Id    Snap Started
------------ --------- ------------------
ORCL              1281 06 Dec 2023 00:00
                  1282 06 Dec 2023 01:00
                  1283 06 Dec 2023 02:00
                  1284 06 Dec 2023 03:00
                  1285 06 Dec 2023 04:00
                  1286 06 Dec 2023 05:00
                  1287 06 Dec 2023 06:00
                  1288 06 Dec 2023 07:00
                  1289 06 Dec 2023 08:00
                  1290 06 Dec 2023 09:00
                  1291 06 Dec 2023 10:00

DB Name        Snap Id    Snap Started
------------ --------- ------------------
ORCL              1292 06 Dec 2023 11:00
                  1293 06 Dec 2023 12:00
                  1294 06 Dec 2023 13:00
                  1295 06 Dec 2023 14:00
                  1296 06 Dec 2023 15:00
                  1297 06 Dec 2023 16:00
                  1298 06 Dec 2023 17:00
                  1299 06 Dec 2023 18:00
                  1300 06 Dec 2023 19:00
                  1301 06 Dec 2023 20:00
                  1302 06 Dec 2023 21:00

DB Name        Snap Id    Snap Started
------------ --------- ------------------
ORCL              1303 06 Dec 2023 22:00
                  1304 06 Dec 2023 23:00
                  1305 07 Dec 2023 00:00
                  1306 07 Dec 2023 01:00
                  1307 07 Dec 2023 02:00
                  1308 07 Dec 2023 03:00
                  1309 07 Dec 2023 04:00
                  1310 07 Dec 2023 05:00
                  1311 07 Dec 2023 06:00
                  1312 07 Dec 2023 07:00
                  1313 07 Dec 2023 08:00

DB Name        Snap Id    Snap Started
------------ --------- ------------------
ORCL              1314 07 Dec 2023 09:00
                  1315 07 Dec 2023 10:00
                  1316 07 Dec 2023 11:00
                  1317 07 Dec 2023 12:00
                  1318 07 Dec 2023 13:00
                  1319 07 Dec 2023 14:00
                  1320 07 Dec 2023 15:00
                  1321 07 Dec 2023 16:00
                  1322 07 Dec 2023 17:00
                  1323 07 Dec 2023 18:00
                  1324 07 Dec 2023 19:00

DB Name        Snap Id    Snap Started
------------ --------- ------------------
ORCL              1325 07 Dec 2023 20:00
                  1326 07 Dec 2023 21:00
                  1327 07 Dec 2023 22:00
                  1328 07 Dec 2023 23:00
                  1329 08 Dec 2023 00:00
                  1330 08 Dec 2023 01:00
                  1331 08 Dec 2023 02:00
                  1332 08 Dec 2023 03:00
                  1333 08 Dec 2023 04:00
                  1334 08 Dec 2023 05:00
                  1335 08 Dec 2023 06:00

DB Name        Snap Id    Snap Started
------------ --------- ------------------
ORCL              1336 08 Dec 2023 07:00
                  1337 08 Dec 2023 08:00
                  1338 08 Dec 2023 09:00
                  1339 08 Dec 2023 10:00
                  1340 08 Dec 2023 11:00
                  1341 08 Dec 2023 12:00
                  1342 08 Dec 2023 13:00
                  1343 08 Dec 2023 14:00
                  1344 08 Dec 2023 15:00
                  1345 08 Dec 2023 16:00
                  1346 08 Dec 2023 17:00

DB Name        Snap Id    Snap Started
------------ --------- ------------------
ORCL              1347 08 Dec 2023 18:00
                  1348 08 Dec 2023 19:00
                  1349 08 Dec 2023 20:00
                  1350 08 Dec 2023 21:00
                  1351 08 Dec 2023 22:00
                  1352 08 Dec 2023 23:00
                  1353 09 Dec 2023 00:00
                  1354 09 Dec 2023 01:00
                  1355 09 Dec 2023 02:00
                  1356 09 Dec 2023 03:00
                  1357 09 Dec 2023 04:00

DB Name        Snap Id    Snap Started
------------ --------- ------------------
ORCL              1358 09 Dec 2023 05:00
                  1359 09 Dec 2023 06:00
                  1360 09 Dec 2023 07:00
                  1361 09 Dec 2023 12:44
                  1362 09 Dec 2023 14:00
                  1363 09 Dec 2023 15:00
                  1364 09 Dec 2023 16:00
                  1365 09 Dec 2023 17:00
                  1366 09 Dec 2023 18:00
                  1367 09 Dec 2023 19:00
                  1368 09 Dec 2023 20:00

DB Name        Snap Id    Snap Started
------------ --------- ------------------
ORCL              1369 09 Dec 2023 21:00
                  1370 09 Dec 2023 22:00
                  1371 09 Dec 2023 23:00
                  1372 10 Dec 2023 00:00
                  1373 10 Dec 2023 01:00
                  1374 10 Dec 2023 02:00
                  1375 10 Dec 2023 03:00
                  1376 10 Dec 2023 04:00
                  1377 10 Dec 2023 05:00
                  1378 10 Dec 2023 06:00
                  1379 10 Dec 2023 07:00

DB Name        Snap Id    Snap Started
------------ --------- ------------------
ORCL              1380 10 Dec 2023 08:00
                  1381 10 Dec 2023 09:00
                  1382 10 Dec 2023 10:00
                  1383 10 Dec 2023 11:00
                  1384 10 Dec 2023 12:00
                  1385 10 Dec 2023 13:00
                  1386 10 Dec 2023 14:00
                  1387 10 Dec 2023 15:00
                  1388 10 Dec 2023 16:00
                  1389 10 Dec 2023 17:00
                  1390 10 Dec 2023 18:00

DB Name        Snap Id    Snap Started
------------ --------- ------------------
ORCL              1391 10 Dec 2023 19:00
                  1392 10 Dec 2023 20:00
                  1393 10 Dec 2023 21:00
                  1394 10 Dec 2023 22:00
                  1395 10 Dec 2023 23:00
                  1396 11 Dec 2023 00:00
                  1397 11 Dec 2023 01:00
                  1398 11 Dec 2023 02:00
                  1399 11 Dec 2023 03:00
                  1400 11 Dec 2023 04:00
                  1401 11 Dec 2023 05:00

DB Name        Snap Id    Snap Started
------------ --------- ------------------
ORCL              1402 11 Dec 2023 06:00
                  1403 11 Dec 2023 07:00
                  1404 11 Dec 2023 08:00
                  1405 11 Dec 2023 09:00
                  1406 11 Dec 2023 10:00
                  1407 11 Dec 2023 11:00
                  1408 11 Dec 2023 12:00
                  1409 11 Dec 2023 13:00
                  1410 11 Dec 2023 14:00
                  1411 11 Dec 2023 15:00
                  1412 11 Dec 2023 16:00

DB Name        Snap Id    Snap Started
------------ --------- ------------------
ORCL              1413 11 Dec 2023 17:00
                  1414 11 Dec 2023 18:00
                  1415 11 Dec 2023 19:00
                  1416 11 Dec 2023 20:00
                  1417 11 Dec 2023 21:00
                  1418 11 Dec 2023 22:00
                  1419 11 Dec 2023 23:00
                  1420 12 Dec 2023 00:00
                  1421 12 Dec 2023 01:00
                  1422 12 Dec 2023 02:00
                  1423 12 Dec 2023 03:00


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1391
Begin Snapshot Id specified: 1391

Enter value for end_snap: 1401
End   Snapshot Id specified: 1401


Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR                  /u01/app/oracle/admin/ORCL/dpdump/
DBMS_OPTIM_ADMINDIR            /u01/app/oracle/product/19c/dbhome_1/rdbms/admin
DBMS_OPTIM_LOGDIR              /u01/app/oracle/product/19c/dbhome_1/cfgtoollogs
JAVA$JOX$CUJS$DIRECTORY$       /u01/app/oracle/product/19c/dbhome_1/javavm/admin
                               /

OPATCH_INST_DIR                /u01/app/oracle/product/19c/dbhome_1/OPatch
OPATCH_LOG_DIR                 /u01/app/oracle/product/19c/dbhome_1/rdbms/log
OPATCH_SCRIPT_DIR              /u01/app/oracle/product/19c/dbhome_1/QOpatch
ORACLE_BASE                    /u01/app/oracle
ORACLE_HOME                    /u01/app/oracle/product/19c/dbhome_1

Directory Name                 Directory Path
------------------------------ -------------------------------------------------
ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/19c/dbhome_1/ccr/state
ORACLE_OCM_CONFIG_DIR2         /u01/app/oracle/product/19c/dbhome_1/ccr/state
SDO_DIR_ADMIN                  /u01/app/oracle/product/19c/dbhome_1/md/admin
SDO_DIR_WORK
XMLDIR                         /u01/app/oracle/product/19c/dbhome_1/rdbms/xml
XSDDIR                         /u01/app/oracle/product/19c/dbhome_1/rdbms/xml/sc
                               hema


Choose a Directory Name from the above list (case-sensitive).
<下面输入AWR Dump文件存放的目录,一般选DATA_PUMP_DIR>
Enter value for directory_name: DATA_PUMP_DIR 

Using the dump directory: DATA_PUMP_DIR

Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_1391_1401.
To use this name, press <return> to continue, otherwise enter
an alternative.

Enter value for file_name: <这里输入回车,使用默认值>

Using the dump file prefix: awrdat_1391_1401
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   /u01/app/oracle/admin/ORCL/dpdump/
|   awrdat_1391_1401.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Extract Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR extract operation can be
|  monitored in the following directory/file:
|   /u01/app/oracle/admin/ORCL/dpdump/
|   awrdat_1391_1401.log
|

End of AWR Extract

导出AWR补充数据

导出AWR Dump后,应马上运行导出AWR补充数据。

也是连接到CDB root运行:

SQL> connect / as sysdba
SQL> @/home/oracle/imadvisor/imadvisor_awr_augment_export.sql

********************************************************************************
* This script will create for you an Automatic Workload Repository (AWR)
* augment that will supply additional data required by the Oracle Database
* In-Memory Advisor.  Without this data augment, you cannot use an AWR export
* with the In-Memory Advisor.  (But with it, you can!)
*

* Note: The Automatic Workload Repository (AWR) augment you are about to create
* can only be used with an AWR export from this local database
* (DBID=1677860020).
*

* Also note: It is best to capture an AWR augment in the same timeframe
* (preferably after) the corresponding AWR export.
********************************************************************************





You may optionally include one the following SQL Tuning Set(s) as part of the
AWR augment.

Enter the sqlset_owner and sqlset_name in the prompts below to include a SQL
Tuning Set.

Otherwise, press ENTER for both sqlset_owner and sqlset_name if you do not wish
to include any SQL Sets.

SQLSET_OWNER                                     SQLSET_NAME
------------------------------------------------ ------------------------------------------------
SYS                                              SYS_AUTO_STS
Enter value for sqlset_owner: <输入回车>
Enter value for sqlset_name: <输入回车>

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ---------------------------------------------------------------------
DATA_PUMP_DIR                  /u01/app/oracle/admin/ORCL/dpdump/
DBMS_OPTIM_ADMINDIR            /u01/app/oracle/product/19c/dbhome_1/rdbms/admin
DBMS_OPTIM_LOGDIR              /u01/app/oracle/product/19c/dbhome_1/cfgtoollogs
JAVA$JOX$CUJS$DIRECTORY$       /u01/app/oracle/product/19c/dbhome_1/javavm/admin/
OPATCH_INST_DIR                /u01/app/oracle/product/19c/dbhome_1/OPatch
OPATCH_LOG_DIR                 /u01/app/oracle/product/19c/dbhome_1/rdbms/log
OPATCH_SCRIPT_DIR              /u01/app/oracle/product/19c/dbhome_1/QOpatch
ORACLE_BASE                    /u01/app/oracle
ORACLE_HOME                    /u01/app/oracle/product/19c/dbhome_1
ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/19c/dbhome_1/ccr/state
ORACLE_OCM_CONFIG_DIR2         /u01/app/oracle/product/19c/dbhome_1/ccr/state
SDO_DIR_ADMIN                  /u01/app/oracle/product/19c/dbhome_1/md/admin
SDO_DIR_WORK
XMLDIR                         /u01/app/oracle/product/19c/dbhome_1/rdbms/xml
XSDDIR                         /u01/app/oracle/product/19c/dbhome_1/rdbms/xml/schema

Please enter the Oracle directory object to use for export (default=DATA_PUMP_DIR)?
<输入回车>
Using directory DATA_PUMP_DIR...


The default IM Advisor AWR augment dump file name prefix is imadvisor_awr_augment.
Please press <return> to use this name prefix; otherwise, enter an alternative name prefix?
<输入回车>
Using "imadvisor_awr_augment" as the AWR augment dump file name prefix...

Setting up the AWR agument staging schema...
No errors.
old 942:   dmp_name := NVL('&&dump_file_name_prefix', :dmp_name_default);
new 942:   dmp_name := NVL('imadvisor_awr_augment', :dmp_name_default);

PL/SQL procedure successfully completed.


Exporting AWR agument data...
IMADVISOR_AWR_AUGMENT_EXPORT Data Pump status: SUCCESS

PL/SQL procedure successfully completed.


Dropping the AWR agument staging schema...
Directory path for Data Pump dump and log files:
/u01/app/oracle/admin/ORCL/dpdump/
Data Pump dump file: imadvisor_awr_augment.dmp
Data Pump log file: imadvisor_awr_augment_export.log

PL/SQL procedure successfully completed.


All done.

以上一般为用户执行,会生成2个dump文件;然后将这两个文件给到DBA,就可以执行下面的导入和分析了。
数据可以导入到non-CDB,CDB R O O T 或 P D B 。本例导入到 C D B ROOT或PDB。本例导入到CDB ROOTPDB。本例导入到CDBROOT。

导入AWR Dump

SQL> @?/rdbms/admin/awrload
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will load the AWR data from a dump file. The   ~
~  script will prompt users for the following information:    ~
~     (1) name of directory object                            ~
~     (2) name of dump file                                   ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR                  /u01/app/oracle/admin/ORCL2/dpdump/
DBMS_OPTIM_ADMINDIR            /u01/app/oracle/product/19c/dbhome_1/rdbms/admin
DBMS_OPTIM_LOGDIR              /u01/app/oracle/product/19c/dbhome_1/cfgtoollogs
JAVA$JOX$CUJS$DIRECTORY$       /u01/app/oracle/product/19c/dbhome_1/javavm/admin
                               /

OPATCH_INST_DIR                /u01/app/oracle/product/19c/dbhome_1/OPatch
OPATCH_LOG_DIR                 /u01/app/oracle/product/19c/dbhome_1/rdbms/log
OPATCH_SCRIPT_DIR              /u01/app/oracle/product/19c/dbhome_1/QOpatch
ORACLE_BASE                    /u01/app/oracle
ORACLE_HOME                    /u01/app/oracle/product/19c/dbhome_1

Directory Name                 Directory Path
------------------------------ -------------------------------------------------
ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/19c/dbhome_1/ccr/state
ORACLE_OCM_CONFIG_DIR2         /u01/app/oracle/product/19c/dbhome_1/ccr/state
SDO_DIR_ADMIN                  /u01/app/oracle/product/19c/dbhome_1/md/admin
SDO_DIR_WORK
XMLDIR                         /u01/app/oracle/product/19c/dbhome_1/rdbms/xml
XSDDIR                         /u01/app/oracle/product/19c/dbhome_1/rdbms/xml/sc
                               hema


Choose a Directory Name from the list above (case-sensitive).

Enter value for directory_name: DATA_PUMP_DIR

Using the dump directory: DATA_PUMP_DIR

Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:

Enter value for file_name: awrdat_1391_1401

Loading from the file name: awrdat_1391_1401.dmp


|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   /u01/app/oracle/admin/ORCL2/dpdump/
|   awrdat_1391_1401.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Load Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR load operation can be
|  monitored in the following directory/file:
|   /u01/app/oracle/admin/ORCL2/dpdump/
|   awrdat_1391_1401.log
|

End of AWR Load

导入AWR 补充数据

SQL> @imadvisor_awr_augment_import.sql

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ---------------------------------------------------------------------
DATA_PUMP_DIR                  /u01/app/oracle/admin/ORCL2/dpdump/
DBMS_OPTIM_ADMINDIR            /u01/app/oracle/product/19c/dbhome_1/rdbms/admin
DBMS_OPTIM_LOGDIR              /u01/app/oracle/product/19c/dbhome_1/cfgtoollogs
JAVA$JOX$CUJS$DIRECTORY$       /u01/app/oracle/product/19c/dbhome_1/javavm/admin/
OPATCH_INST_DIR                /u01/app/oracle/product/19c/dbhome_1/OPatch
OPATCH_LOG_DIR                 /u01/app/oracle/product/19c/dbhome_1/rdbms/log
OPATCH_SCRIPT_DIR              /u01/app/oracle/product/19c/dbhome_1/QOpatch
ORACLE_BASE                    /u01/app/oracle
ORACLE_HOME                    /u01/app/oracle/product/19c/dbhome_1
ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/19c/dbhome_1/ccr/state
ORACLE_OCM_CONFIG_DIR2         /u01/app/oracle/product/19c/dbhome_1/ccr/state
SDO_DIR_ADMIN                  /u01/app/oracle/product/19c/dbhome_1/md/admin
SDO_DIR_WORK
XMLDIR                         /u01/app/oracle/product/19c/dbhome_1/rdbms/xml
XSDDIR                         /u01/app/oracle/product/19c/dbhome_1/rdbms/xml/schema

Please enter the Oracle directory object to use for import (default=DATA_PUMP_DIR)?
DATA_PUMP_DIR
Using directory DATA_PUMP_DIR...


The default IM Advisor AWR augment dump file name prefix is imadvisor_awr_augment.
Please press <return> to use this name prefix; otherwise, please enter an alternative name prefix?

Using "imadvisor_awr_augment" as the AWR augment dump file name prefix...

Gathering information about the AWR agument...
IMADVISOR_AWR_AUGMENT_MASTER Data Pump status: SUCCESS

Setting up the AWR agument schema...

Importing AWR agument data...
IMADVISOR_AWR_AUGMENT_IMPORT Data Pump status: SUCCESS


All done.

发现一个错别字,提示中的agument应为augument。

运行In-Memory Advisor


SQL> imadvisor_recommendations
SP2-0734: unknown command beginning "imadvisor_..." - rest of line ignored.
SQL> @imadvisor_recommendations


This script creates and runs an In-Memory Advisor task that analyzes
your workload to determine an optimal In-Memory configuration.

This script then generates an HTML recommendation report file in the
current working  directory: imadvisor_<task_name>.html

This script also generates a sqlplus DDL script to implement the
recommendations: imadvisor_<task_name>.sql

NOTE: You may specify one of your existing tasks if you wish to optimize for a
different In-Memory size.

Using an existing, executed task is faster than a new task since a new task
requires statistics gathering and analysis.

But if you wish to analyze a different workload or use a different statistics
capture window or add a SQLSET, you must specify a new task.

The following is a list of your existing tasks:


TASK_NAME                      DATE_CREATED
------------------------------ -----------------------------
im_advisor_task_20231212034203 2023-DEC-12 03:42:32



Default task_name (new task): im_advisor_task_20231212035008
Enter value for task_name:

Advisor task name specified: im_advisor_task_20231212035008 (default)

New Advisor task will be named: im_advisor_task_20231212035008...

By default, the Advisor runs against a live workload on this database.
This database also has imported, augmented AWR workloads.

Press ENTER or respond NO to run against a live workload.
Respond YES to run against an augmented AWR workload.

Enter value for run_against_augmented_awr: YES

The Advisor can use the following augmented AWR imports:

Augmented AWR Import DBID
-------------------------
1677860020


Enter value for dbid: 1677860020


Analyzing and reporting on an augmented AWR workload with DBID=1677860020...
Enter value for pdb_name: orclpdb1

orclpdb1

The In-Memory Advisor optimizes the In-Memory configuration for a specific
In-Memory size that you choose.

After analysis, the In-Memory Advisor can provide you a list of performance
benefit estimates for a range of In-Memory sizes.  You may then choose the
In-Memory size for which you wish to optimize.

If you already know the specific In-Memory size you wish, please enter
the value now.  Format: nnnnnnn[KB|MB|GB|TB]

Or press <ENTER> to get performance estimates first.
Enter value for inmemory_size: 10G

The In-Memory Advisor will optimize for this In-Memory size: 10G


Enter begin time for report:

--    Valid input formats:
--      To specify absolute begin time:
--        [MM/DD[/YY]] HH24:MI[:SS]
--        Examples: 02/23/03 14:30:15
--                  02/23 14:30:15
--                  14:30:15
--                  14:30
--      To specify relative begin time: (start with '-' sign)
--        -[HH24:]MI
--        Examples: -1:15  (SYSDATE - 1 Hr 15 Mins)
--                  -25    (SYSDATE - 25 Mins)

Default begin time: 12/10/23 18:00:44
Enter value for begin_time:

Report begin time specified:


Enter duration in minutes starting from begin time:
(defaults to <latest-snapshot-end-time> - begin_time)

Enter value for duration:

Report duration specified:

Using 2023-DEC-10 18:00:44.000000000 as report begin time
Using 2023-DEC-11 05:00:07.000000000 as report end time


You may optionally specify a comma separated list of object owner
and name patterns to be considered for In Memory Placement.
Example:

GEEK_SUMMARY.%,%.GEEK_%

Press ENTER to consider all objects.


Enter value for consider_objects_like:

Considering all objects for In Memory placement.

In-Memory Advisor: Adding statistics...
DECLARE
*
ERROR at line 1:
ORA-20001: No analytics database activity was detected
ORA-06512: at "C##IMADVISOR.DBMS_INMEMORY_ADVISOR_PRVT", line 6961
ORA-06512: at "C##IMADVISOR.DBMS_INMEMORY_ADVISOR_PRVT", line 756
ORA-06512: at "C##IMADVISOR.DBMS_INMEMORY_ADVISOR_PRVT", line 4110
ORA-06512: at "C##IMADVISOR.DBMS_INMEMORY_ADVISOR_PRVT", line 6739
ORA-06512: at "C##IMADVISOR.DBMS_INMEMORY_ADVISOR_PRVT", line 6947
ORA-06512: at "SYS.DBMS_INMEMORY_ADVISOR", line 46
ORA-06512: at line 11


Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0

错误处理

在导入AWR dump时,可能出现错误,原因是目标端的数据库不兼容,并非是源数据库导出数据的问题:

```sql

|
begin
*
ERROR at line 1:
ORA-13509: error encountered during updates to a AWR table
ORA-39002: invalid operation
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 5958
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 5996
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 2539
ORA-06512: at line 4



End of AWR Load
## 参考
- [Running Oracle Database In-Memory Advisor Offline](https://blogs.oracle.com/coretec/post/running-oracle-database-in-memory-advisor-of-one-database-on-another)
- [Multitenant : Default Tablespace Clause During PDB Creation in Oracle Database 12c Release 2 (12.2)](https://oracle-base.com/articles/12c/multitenant-default-tablespace-clause-12cr2)
- [Oracle Database In-Memory Advisor](https://xiaoyu.blog.csdn.net/article/details/100048836)



文章来源:https://blog.csdn.net/stevensxiao/article/details/134941557
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。