OraSASH

Oracle Simulation ASH

This project is maintained by Marcin Przepiorowski

Hosted on GitHub Pages based on the Dinky theme

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

Login as user sash to repository database and run following script -
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:
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