AWR元数据导出

SQL> @?/rdbms/admin/awrextr

~~~~~~~~~~~~~

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

------------ ------------ ------------

* 247678299  PROD         cloud1

* 247678299  PROD         cloud2

  823024575  TPIYWDB      DRTPICBS-D

  3014054514 ZDATA        zdata-01

  3014054514 ZDATA        zdata-02

  1347846813 ORCL         EDISTJ

 

The default database id is the local one: ' 247678299'.  To use this

database id, press to continue, otherwise enter an alternative.

 

Enter value for dbid: 247678299

 

Using 247678299 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 without

specifying a number lists all completed snapshots.

 

 

Enter value for num_days: 8

 

Listing the last 8 days of Completed Snapshots

 

DB Name        Snap Id    Snap Started

------------ --------- ------------------

PROD                92 21 Jul 2014 12:35

                    93 21 Jul 2014 14:00

                    94 21 Jul 2014 15:00

                    95 21 Jul 2014 16:00

                    96 21 Jul 2014 17:14

                    97 21 Jul 2014 18:00

                    98 22 Jul 2014 13:36

                    99 22 Jul 2014 15:00

                   100 22 Jul 2014 16:00

                   101 22 Jul 2014 17:00

                   102 22 Jul 2014 18:00

 

DB Name        Snap Id    Snap Started

------------ --------- ------------------

PROD               103 23 Jul 2014 10:09

                   104 23 Jul 2014 11:00

                   105 23 Jul 2014 12:00

                   106 23 Jul 2014 13:00

                   107 23 Jul 2014 14:00

                   108 23 Jul 2014 15:00

                   109 23 Jul 2014 16:00

                   110 23 Jul 2014 17:00

                   111 23 Jul 2014 18:00

                   112 23 Jul 2014 19:00

                   113 25 Jul 2014 11:14

 

DB Name        Snap Id    Snap Started

------------ --------- ------------------

PROD               114 25 Jul 2014 12:00

                   115 25 Jul 2014 13:00

                   116 25 Jul 2014 14:00

                   117 25 Jul 2014 15:00

                   118 25 Jul 2014 16:00

                   119 25 Jul 2014 17:00

                   120 25 Jul 2014 18:00

                   121 26 Jul 2014 13:46

                   122 26 Jul 2014 15:00

 

 

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 92

Begin Snapshot Id specified: 92

 

Enter value for end_snap: 122

End   Snapshot Id specified: 122

 

 

Specify the Directory Name

~~~~~~~~~~~~~~~~~~~~~~~~~~

 

Directory Name                 Directory Path

------------------------------ -------------------------------------------------

DATA_FILE_DIR                  /u01/app/oracle/product/11.2.0/db_1/demo/schema/s

                               ales_history/

 

DATA_PUMP_DIR                  /u01/app/oracle/product/11.2.0/db_1/rdbms/log/

DPDATA                         /home/oracle

LOG_FILE_DIR                   /u01/app/oracle/product/11.2.0/db_1/demo/schema/l

                               og/

 

MEDIA_DIR                      /u01/app/oracle/product/11.2.0/db_1/demo/schema/p

                               roduct_media/

 

 

Directory Name                 Directory Path

------------------------------ -------------------------------------------------

ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/11.2.0/db_1/ccr/state

SS_OE_XMLDIR                   /u01/app/oracle/product/11.2.0/db_1/demo/schema/o

                               rder_entry/

 

SUBDIR                         /u01/app/oracle/product/11.2.0/db_1/demo/schema/o

                               rder_entry//2002/Sep

 

XMLDIR                         /u01/app/oracle/product/11.2.0/db_1/rdbms/xml

 

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

 

Enter value for directory_name: DPDATA

 

Using the dump directory: DPDATA

 

Specify the Name of the Extract Dump File

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The prefix for the default dump file name is awrdat_92_122.

To use this name, press to continue, otherwise enter

an alternative.

 

Enter value for file_name:

 

Using the dump file prefix: awrdat_92_122

|

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

|  The AWR extract dump file will be located

|  in the following directory/file:

|   /home/oracle

|   awrdat_92_122.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:

|   /home/oracle

|   awrdat_92_122.log

|

 

End of AWR Extract

 

 

 

导出表结构：

 

expdp \'/ as sysdba\' CONTENT=METADATA_ONLY schemas=cloud dumpfile=cloud.dmp directory=dpdata

 

schemas对应业务用户

dumpfile随便起名

directory需要从数据库里面去查询，相关语句是

select directory_name,directory_path from dba_directories;

 

   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29843794/viewspace-1268870/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29843794/viewspace-1268870/