Orasash by pioro

Daily operations

Starting / stopping collection jobs

To start job connect as repository owner and run script

SQL> @start.sql

or pl/sql function

SQL> exec sash_repo.start_collecting_jobs;

To stop run script

SQL> @stop

or pl/sql funcktion

exec sash_repo.stop_collecting_jobs;

Checking jobs status

SQL> @job_stat.sql

Output should be similar to this

SQL> @job_stat.sql

JOB_NAME LAST_START_DATE NEXT_RUN_DATE STATE FAILURE_COUNT
------------------------------ ----------------------------------- ----------------------------------- --------------- -------------
SASH_PKG_GET_ALL_POLTP1 24-NOV-11 03.00.30.431000 +00:00 24-NOV-11 03.15.30.300000 +00:00 SCHEDULED 0
SASH_REPO_PURGE 25-NOV-11 00.00.00.000000 +00:00 SCHEDULED 0
SASH_REPO_WATCHDOG 24-NOV-11 11.00.30.408000 +00:00 24-NOV-11 11.05.30.000000 +00:00 SCHEDULED 0
SASH_PKG_COLLECT_POLTP1 24-NOV-11 11.00.30.295000 +00:00 24-NOV-11 11.00.30.000000 +00:00 RUNNING 0

Checking log table

Check repository error log using the following script

SQL> @checklog.sql

Example output

SQL> @checklog.sql

R START_TIME ACTION MESSAGE
- ------------------------- ------------------------------------------------ ------------------------------------------------------------------------
W 2011-11-24 11:07:20 add_db no db link - moving forward POLTP1
I 2011-11-24 11:07:20 configure_db get_event_names
I 2011-11-24 11:07:20 configure_db get_users
I 2011-11-24 11:07:20 configure_db get_params
I 2011-11-24 11:07:20 configure_db get_data_files
I 2011-11-24 11:07:20 configure_db get_metrics
I 2011-11-24 11:07:20 add_instance_job adding scheduler job sash_pkg_collect_POLTP1
I 2011-11-24 11:07:20 add_instance_job adding scheduler job sash_pkg_get_all_POLTP1
I 2011-11-24 11:07:21 create_repository_jobs adding new repository job

9 rows selected.

Data retention change

To change default month retention you can use following pl/sql function

exec sash_repo.set_retention('');

where <retention> is one of: - d - last week - w - last month - h - last 24 h - m - last 30 minutes

Adding new database

New databases can be added by using adddb.sql script when connected to repository using respository owner. The following questions have to be answered:

database name,
number of instances (1 for single instance database or more than one for RAC database),
target database host name
instance(s) name
listener port
password for sash user on target database.

SQL> @adddb
Enter database name newdb
Enter number of instances [default 1]
Enter host name for instance number 1 newdb.localdomain
Enter instance name for instance number 1 [ default newdb ]
Enter listener port number [default 1521]
Enter SASH password on target database sash
"------------------------------------------------------------------------------------"
Database added.
"------------------------------------------------------------------------------------"

Managing multi-database repository

If you added more than one database to the repository there is concept of current database. All SASH views limit data to the database defined as current and allow DBA to query views like in single database repository. To display a current database the following script can be used:

SQL> @currentdb.sql

List of configured databases

DBNAME                               DBID HOST                             INST_NUM CURRENT_DB
------------------------------ ---------- ------------------------------ ---------- ----------
test122                        3501342205 192.168.1.46                            1 *
testse                         4169104998 oraclese                                1

To switch a current database to other one the following script can be used:

SQL> @switchdb.sql

DBNAME                               DBID HOST                             INST_NUM CURRENT_DB
------------------------------ ---------- ------------------------------ ---------- ----------
test122                        3501342205 192.168.1.46                            1 *
testse                         4169104998 oraclese                                1

Switch to database with DBID 4169104998

Switch to instance with INST_NUM 1

Database switched.

DBNAME                               DBID HOST                             INST_NUM CURRENT_DB
------------------------------ ---------- ------------------------------ ---------- ----------
test122                        3501342205 192.168.1.46                            1
testse                         4169104998 oraclese                                1 *
SQL> @currentdb.sql

List of configured databases

DBNAME                               DBID HOST                             INST_NUM CURRENT_DB
------------------------------ ---------- ------------------------------ ---------- ----------
test122                        3501342205 192.168.1.46                            1
testse                         4169104998 oraclese                                1 *

Manuall configuration

If for any reason adddb.sql can't be used steps specified below allow expirienced user to manually configure new database To add database(s) instance(s) to repository using following package can be used:

exec sash_repo.add_db('svr1-vip', , , '', '', , '', );
select db_link from sash.sash_targets;
exec sash_pkg.configure_db('');
exec sash_pkg.set_dbid('');

ex. one instance

exec sash_repo.add_db('svr1', 1521, 'sash', 'test', 'test1', 1, '11.2.0.2', 8);
exec sash_pkg.configure_db('test1');
exec sash_pkg.set_dbid('test1');

ex. RAC

exec sash_repo.add_db('svr1-vip', 1521, 'sash', 'test', 'test1', 1, '11.2.0.2', 8);
exec sash_repo.add_db('svr2-vip', 1521, 'sash', 'test', 'test2', 2, '11.2.0.2', 8);
exec sash_pkg.configure_db('test1');
exec sash_pkg.set_dbid('test1');

To setup a new jobs following package can be used:

SQL> exec sash_repo.setup_jobs