Install SQLDIR on a shared management server
| Question ID: 3329 | |
| Created By: | 2008-AUG-25 14:26:27 [Gap12345] |
| Updated By: | 2008-AUG-29 11:02:21 [Vitaliy] |
| Status: | Open |
| Severity: | Normal |
| Read Only: | No |
|
9441
2008-AUG-25 14:26:27
|
||||
|
I have many oracle databases and i need to configure the dba tool , ilooked at the pdf file and it was telling about creating a management server , Iam new to the dba field so can u tell me the detail steps as u did for the installation doc for sun solaris 5.8 . my unix box is sun solaris , so it would be helpful if u send me the details steps for the sun os for multiple databses . Thanks venkat
|
9442
2008-AUG-25 15:06:39
|
||||
|
Venkat, Welcome to DBAToolZ.com! Generally management server is a separate host with a separate ORACLE database installed that you strictly use for ORACLE Database management tools. Typically these tools install their repository into this separate database and in some cases they also install their management scripts onto the host operating system. Were you specifically referring to the SQLDIR tool? Regards, - Vitaliy
[edited by: Vitaliy at 15:06 (CST) on Aug. 25, 2008]
|
9444
2008-AUG-25 15:23:26
|
||||
|
yeah the sqldir tool Thanks venkat
|
9445
2008-AUG-25 18:11:03
|
||||
|
SUMMARY -------- In this example the management host is called "sdrep" and the management DATABASE is called "DBAREP". I will demonstrate how to install SQLDIR repository into this management server and how to setup another "managed" database called PROD which resides on a different server called "prodsrv" to use the shared SQLDIR repository. ## First follow quick install notes on the SQLDIR download page: SQLDIR download page ## NOTE: ## Only do STEP #1 and #2 then follow the steps below: ## ## ## validate the SQLDIR executable is working properly ## sdrep.DBAREP-> pwd /u01/app/oracle/admin/scripts/dbatoolz/sqldir sdrep.DBAREP-> cd bin sdrep.DBAREP-> ls -lta total 1330 -rw-r--r-- 1 oracle dba 277488 Jan 10 2007 sqldir_ddl.log -rw-r--r-- 1 oracle dba 7813 Jan 10 2007 sqldir.list -rw-r--r-- 1 oracle dba 723 Jan 10 2007 sqldir_ddl.sql -rw-r--r-- 1 oracle dba 150834 Jan 10 2007 sqldir_grp.sql -rw-r--r-- 1 oracle dba 68073 Jan 10 2007 sqldir_txt.sql drwxr-xr-x 2 oracle dba 512 Jan 10 2007 . -rwxr-xr-x 1 oracle dba 23361 Jun 18 2003 FULL_LINUX_sqldir -rwxr-xr-x 1 oracle dba 521 Apr 1 2002 bnsql.sh drwxr-xr-x 6 oracle dba 512 Feb 25 2002 .. -rw-r--r-- 1 oracle dba 1643 Feb 25 2002 TEMPLATE.sql -rwxr-xr-x 1 oracle dba 699 Jan 8 2002 dirlist.sh -rwxr-xr-x 1 oracle dba 614 Dec 19 2001 nsql.sh -rwxr-xr-x 1 oracle dba 17572 Nov 10 2001 DEMO_sqldir -rw-r--r-- 1 oracle dba 36864 Nov 10 2001 DEMO_sqldir.exe -rw-r--r-- 1 oracle dba 569 Nov 9 2001 groups.cfg -rw-r--r-- 1 oracle dba 569 Nov 5 2001 groups.cfg.ALL.SEED -rw-r--r-- 1 oracle dba 3909 Nov 5 2001 sqldir.list.UNIX.SEED -rw-r--r-- 1 oracle dba 849 Nov 5 2001 dirlist.bat -rw-r--r-- 1 oracle dba 4064 Nov 5 2001 sqldir.list.WIN.SEED -rwxr-xr-x 1 oracle dba 17412 Nov 5 2001 sqldir -rw-r--r-- 1 oracle dba 36864 Nov 4 2001 FULL_sqldir.exe sdrep.DBAREP-> ./sqldir USAGE: sqldir [-d] <sql_directory_file> <cfg_groups_file> sdrep.DBAREP-> file sqldir sqldir: ELF 32-bit MSB executable SPARC Version 1, dynamically linked, not stripped sdrep.DBAREP-> ## Copy tns alias for DBAREP over to prodsrv ## sdrep.DBAREP-> cat /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora DBAREP = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sdrep.domain.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = DBAREP.domain.com) ) ) sdrep.DBAREP-> ssh -l oracle prodsrv The authenticity of host 'prodsrv (198.206.187.119)' can't be established. RSA key fingerprint is e0:ab:17:61:d7:52:ec:a9:5d:d2:f3:d1:94:b6:31:7f. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'prodsrv,198.206.187.119' (RSA) to the list of known hosts. oracle@prodsrv's password: Last login: Fri May 30 15:24:58 2008 from *********** Sun Microsystems Inc. SunOS 5.9 Generic May 2002 prodsrv.PROD-> prodsrv.PROD-> vi /u01/app/oracle/xupgdb/9.2.0/network/admin/tnsnames.ora ------ paste the following --------- DBAREP = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sdrep.domain.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = DBAREP.domain.com) ) ) prodsrv.PROD-> tnsping DBAREP TNS Ping Utility for Solaris: Version 9.2.0.7.0 - Production on 25-AUG-2008 15:03:30 Copyright (c) 1997 Oracle Corporation. All rights reserved. Used parameter files: /u01/app/oracle/xupgdb/9.2.0/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sdrep.domain.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DBAREP.domain.com))) OK (10 msec) prodsrv.PROD-> prodsrv.PROD-> exit Connection to prodsrv closed. sdrep.DBAREP-> ## Create SQLDIR repository OWNER ## sdrep.DBAREP-> sqlplus /nolog SQL*Plus: Release 10.2.0.3.0 - Production on Mon Aug 25 15:03:59 2008 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. SQL> connect / as sysdba Connected. SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM UNDOTBS1 SYSAUX TEMP USERS FDB_DATA FDB_LOB 7 rows selected. SQL> create user sqldir identified by sqldir 2 default tablespace USERS 3 temporary tablespace TEMP; User created. SQL> grant connect , resource to sqldir; Grant succeeded. SQL> revoke unlimited tablespace from sqldir; Revoke succeeded. SQL> alter user sqldir quota unlimited on users; User altered. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options sdrep.DBAREP-> ## Prepare the list of SQL scripts which will be loaded into SQLDIR repository ## we do this by creating a list of sql scripts and saving it into a sqldir.list file ## sdrep.DBAREP-> pwd /u01/app/oracle/admin/scripts/dbatoolz/sqldir/bin sdrep.DBAREP-> cd /u01/app/oracle/admin/scripts/dbatoolz/sqldir/bin sdrep.DBAREP-> ls -l /u01/app/oracle/admin/scripts/dbatoolz/sqldir/sql/*.sql | awk '{ print $9 }' > sqldir.list sdrep.DBAREP-> sdrep.DBAREP-> head sqldir.list /u01/app/oracle/admin/scripts/dbatoolz/sqldir/sql/1.sql /u01/app/oracle/admin/scripts/dbatoolz/sqldir/sql/2.sql /u01/app/oracle/admin/scripts/dbatoolz/sqldir/sql/3.sql /u01/app/oracle/admin/scripts/dbatoolz/sqldir/sql/FND_SIGNON.sql /u01/app/oracle/admin/scripts/dbatoolz/sqldir/sql/MSC_CL_COLLECTION.sql /u01/app/oracle/admin/scripts/dbatoolz/sqldir/sql/MSC_CL_PULL.sql /u01/app/oracle/admin/scripts/dbatoolz/sqldir/sql/MSC_MANAGE_PLAN_PARTITIONS.sql /u01/app/oracle/admin/scripts/dbatoolz/sqldir/sql/MSC_POST_PRO.sql /u01/app/oracle/admin/scripts/dbatoolz/sqldir/sql/OWA_CUSTOM.sql /u01/app/oracle/admin/scripts/dbatoolz/sqldir/sql/SFA_BOOKING_VIEW_EIM.sql sdrep.DBAREP-> ## Generate SQLDIR repository DDL scripts ## ## This is done by the SQLDIR utility which scans all the ## SQL scripts listed in "sqldir.list" ## ## NOTE: groups.cfg is the Group definition file ## you can edit it as you please and supply the GROUP ## between <SQLDIR_GRP></SQLDIR_GRP> ## (inside each of the *.sql file) ## sdrep.DBAREP-> ./sqldir sqldir.list groups.cfg +=============================================+ | SQLDIR v3.1 | +---------------------------------------------+ | Copyright (c) 1998 DBAToolZ.com | | All rights reserved. | +=============================================+ ... SCANNING 1.sql (275) lines ... SCANNING 2.sql (86) lines ... SCANNING 3.sql (59) lines ... SCANNING FND_SIGNON.sql (875) lines ... SCANNING MSC_CL_COLLECTION.sql (23827) lines ... SCANNING MSC_CL_PULL.sql (144) lines ... SCANNING MSC_MANAGE_PLAN_PARTITIONS.sql (1526) lines ... SCANNING MSC_POST_PRO.sql (3984) lines ... SCANNING OWA_CUSTOM.sql (51) lines ... SCANNING SFA_BOOKING_VIEW_EIM.sql (157) lines ... SCANNING S_lobs.sql (16) lines ... SCANNING S_move.sql (160) lines ... SCANNING S_move_lobs.sql (133) lines ... SCANNING TEMPLATE_16sp.sql (136) lines ... ... ... ... SCANNING x_banner.sql (41) lines ... SCANNING x_db_name.sql (53) lines ... SCANNING x_dir.sql (159) lines ... SCANNING x_dir_share.sql (92) lines ... SCANNING x_dir_start.sql (53) lines TOTAL Files: 333 To load DBAToolZ script library run sqldir_ddl.sql from SQL*Plus To access DBAToolZ script library run x_dir.sql from SQL*Plus For more information and software updates visit DBAToolZ.com ## once the scripts are parsed three DDL files are created ## in the current working directory: ## sdrep.DBAREP-> ls -lta | head total 1358 drwxr-xr-x 2 oracle dba 512 Aug 25 15:16 . -rw-r--r-- 1 oracle dba 150834 Aug 25 15:12 sqldir_grp.sql -rw-r--r-- 1 oracle dba 68073 Aug 25 15:12 sqldir_txt.sql -rw-r--r-- 1 oracle dba 723 Aug 25 15:12 sqldir_ddl.sql -rw-r--r-- 1 oracle dba 22132 Aug 25 15:11 sqldir.list -rw-r--r-- 1 oracle dba 277488 Jan 10 2007 sqldir_ddl.log -rwxr-xr-x 1 oracle dba 23361 Jun 18 2003 FULL_LINUX_sqldir -rwxr-xr-x 1 oracle dba 521 Apr 1 2002 bnsql.sh drwxr-xr-x 6 oracle dba 512 Feb 25 2002 .. sdrep.DBAREP-> ## Here's what the DDL scripts are and what they do: ## ## sqldir_grp.sql - this script inserts all groups into sqldir_groups ## and maps all scripts to these groups via sqldir_mapping table ## ## sqldir_txt.sql - this script inserts all sql scripts names and ## description into sqldir_scripts table ## ## sqldir_ddl.sql - this script creates SQLDIR repository tables and calls ## sqldir_txt.sql and sqldir_grp.sql to load them ## ## Next step is to create SQLDIR repository ## we'll use sqldir/sqldir username we created previously ## sdrep.DBAREP-> pwd /u01/app/oracle/admin/scripts/dbatoolz/sqldir/bin sdrep.DBAREP-> sqlplus /nolog SQL*Plus: Release 10.2.0.3.0 - Production on Mon Aug 25 15:21:12 2008 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. SQL> connect sqldir/sqldir Connected. SQL> @sqldir_ddl.sql SQL> set term off Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options sdrep.DBAREP-> ## Next we connect to SQLDIR repository and test if it's working ## sdrep.DBAREP-> cd ../sql sdrep.DBAREP-> pwd /u01/app/oracle/admin/scripts/dbatoolz/sqldir/sql sdrep.DBAREP-> sdrep.DBAREP-> sqlplus /nolog SQL*Plus: Release 10.2.0.3.0 - Production on Mon Aug 25 15:22:24 2008 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. SQL> connect sqldir/sqldir Connected. SQL> @x_dir Session altered. +=====================================================+ | SQLDIR SQL*Plus Menu v1.2 | +=====================================================+ ID Group Name Cnt --- --------------------------------------------- ---- 14 SGA Maintenance ............................. 19 27 Favorite Scripts ............................ 46 22 Segment Management .......................... 30 26 Partitioning ................................ 29 12 Maintenance ................................. 24 8 Utility ..................................... 25 23 Backup Management ........................... 5 16 User Management ............................. 38 5 APPS Administration ......................... 21 2 APPS Installation ........................... 9 4 APPS Concurrent Managers .................... 13 19 Database Init Parameters .................... 5 20 Latches ..................................... 6 17 Statistics .................................. 18 7 Tablespace .................................. 34 1 APPS General ................................ 8 6 Data Files .................................. 17 25 SQL Tracing Tuning .......................... 26 15 Table ....................................... 29 18 Storage Management .......................... 17 11 Rollback .................................... 8 13 Reverse Engineering ......................... 11 9 Index ....................................... 12 10 Redo Log .................................... 11 24 Parallel Query .............................. 4 0 TOTAL Number Of Scripts: .................... 333 21 Locks ....................................... 8 3 APPS Concurrent Programs .................... 11 Enter Group Id or to search Enter [s SEARCH_STRING] : ^C 15:23:02 SQLDIR> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options sdrep.DBAREP-> ## At this point the management server is ready -- the next step is to ## allow the managed database PROD "to see" the SQLDIR repository on the ## management server. This is accomplished using x_dir_share.sql script ## which does the following: ## 1) creates public database link to the management server ## 2) creates the synonyms for the SQLDIR repository tables ## over the dblink ## sdrep.DBAREP-> pwd /u01/app/oracle/admin/scripts/dbatoolz/sqldir/sql sdrep.DBAREP-> ls -l x_dir* -rw-r--r-- 1 oracle dba 3639 Feb 25 2002 x_dir.sql -rw-r--r-- 1 oracle dba 2301 Feb 25 2002 x_dir_share.sql -rw-r--r-- 1 oracle dba 1083 Feb 25 2002 x_dir_start.sql sdrep.DBAREP-> sqlplus system@PROD SQL*Plus: Release 10.2.0.3.0 - Production on Mon Aug 25 15:31:00 2008 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Enter password: Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production SQL> @x_dir_share.sql "Existing SQLDIR synonyms" no rows selected "USAGE: SQL> x_dir_share.sql SQLDIR_USERNAME SQLDIR_PASSWORD SQLDIR_TNS" "----------------------" " 1 = SQLDIR_USERNAME " " 2 = SQLDIR_PASSWORD " " 3 = SQLDIR_TNS" "----------------------" Enter value for 3: DBAREP old 1: create public database link sqldir_&&3 new 1: create public database link sqldir_DBAREP Enter value for 1: sqldir Enter value for 2: sqldir old 2: connect to &&1 identified by &&2 new 2: connect to sqldir identified by sqldir old 3: using '&&3' new 3: using 'DBAREP' Database link created. old 1: create public synonym sqldir_groups for sqldir_groups@sqldir_&&3 new 1: create public synonym sqldir_groups for sqldir_groups@sqldir_DBAREP Synonym created. old 1: create public synonym sqldir_mapping for sqldir_mapping@sqldir_&&3 new 1: create public synonym sqldir_mapping for sqldir_mapping@sqldir_DBAREP Synonym created. old 1: create public synonym sqldir_scripts for sqldir_scripts@sqldir_&&3 new 1: create public synonym sqldir_scripts for sqldir_scripts@sqldir_DBAREP Synonym created. Session altered. +=====================================================+ | SQLDIR SQL*Plus Menu v1.2 | +=====================================================+ ID Group Name Cnt --- --------------------------------------------- ---- 14 SGA Maintenance ............................. 19 27 Favorite Scripts ............................ 46 22 Segment Management .......................... 30 26 Partitioning ................................ 29 12 Maintenance ................................. 24 8 Utility ..................................... 25 23 Backup Management ........................... 5 16 User Management ............................. 38 5 APPS Administration ......................... 21 2 APPS Installation ........................... 9 4 APPS Concurrent Managers .................... 13 19 Database Init Parameters .................... 5 20 Latches ..................................... 6 17 Statistics .................................. 18 7 Tablespace .................................. 34 1 APPS General ................................ 8 6 Data Files .................................. 17 25 SQL Tracing Tuning .......................... 26 15 Table ....................................... 29 18 Storage Management .......................... 17 11 Rollback .................................... 8 13 Reverse Engineering ......................... 11 9 Index ....................................... 12 10 Redo Log .................................... 11 24 Parallel Query .............................. 4 0 TOTAL Number Of Scripts: .................... 333 21 Locks ....................................... 8 3 APPS Concurrent Programs .................... 11 Enter Group Id or to search Enter [s SEARCH_STRING] : ^C 15:35:49 SYSTEM@PROD:prodsrv> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production sdrep.DBAREP-> ## Finally we can now connect to the managed database called PROD right from ## the management server "sdrep" ## ## The benefit of this is that we only maintain ONE SQLDIR repository ## and ONE set of SQL scripts which we can access from any managed ## database on our network: ## sdrep.DBAREP-> pwd /u01/app/oracle/admin/scripts/dbatoolz/sqldir/sql sdrep.DBAREP-> sdrep.DBAREP-> sqlplus system@PROD @x_dir.sql SQL*Plus: Release 10.2.0.3.0 - Production on Mon Aug 25 15:59:01 2008 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Enter password: Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production Session altered. +=====================================================+ | SQLDIR SQL*Plus Menu v1.2 | +=====================================================+ ID Group Name Cnt --- --------------------------------------------- ---- 14 SGA Maintenance ............................. 19 27 Favorite Scripts ............................ 46 22 Segment Management .......................... 30 26 Partitioning ................................ 29 12 Maintenance ................................. 24 8 Utility ..................................... 25 23 Backup Management ........................... 5 16 User Management ............................. 38 5 APPS Administration ......................... 21 2 APPS Installation ........................... 9 4 APPS Concurrent Managers .................... 13 19 Database Init Parameters .................... 5 20 Latches ..................................... 6 17 Statistics .................................. 18 7 Tablespace .................................. 34 1 APPS General ................................ 8 6 Data Files .................................. 17 25 SQL Tracing Tuning .......................... 26 15 Table ....................................... 29 18 Storage Management .......................... 17 11 Rollback .................................... 8 13 Reverse Engineering ......................... 11 9 Index ....................................... 12 10 Redo Log .................................... 11 24 Parallel Query .............................. 4 0 TOTAL Number Of Scripts: .................... 333 21 Locks ....................................... 8 3 APPS Concurrent Programs .................... 11 Enter Group Id or to search Enter [s SEARCH_STRING] : ## ## END ## Regards, - Vitaliy
[edited by: Vitaliy at 11:02 (CST) on Aug. 29, 2008]
|
9449
2008-AUG-26 11:15:18
|
||||
|
Hi thanks for u post this is a really good site and Iam getting responses fast , I have a small question do u have these scripts all the apps and the dba scripts in one tool such as where i can install like toad or sqlplus Thanks venkat
|
9450
2008-AUG-26 11:32:10
|
||||
|
Venkat, You can download SQLPLUS from oracle.com -- if free. Here's a link for SOLARIS/SPARC (get the 10gr2_client_sol.cpio.gz): Oracle SQLPlus download Instead of paying for TOAD get Oracle SQL Developer - it works great, it's free and it doesn't require ORACLE Client on your PC: Oracle SQL Developer Download HTH, - Vitaliy
|
9452
2008-AUG-26 13:45:42
|
||||
|
no i mean with the scripts which u have the 391 scripts , i mean if i need to run on production, because yesterday i installed on my server and again the files systems are refreshed and so i dont have it now so its hard for me to install the sql directory again and again so just wondering whether can i run this scripts in a tool the scripts u have in the full version sql dir . Thanks venkat
|
9453
2008-AUG-26 13:46:19
|
||||
|
no i mean with the scripts which u have the 391 scripts , i mean if i need to run on production, because yesterday i installed on my server and again the files systems are refreshed and so i dont have it now so its hard for me to install the sql directory again and again so just wondering whether can i run this scripts in a tool the scripts u have in the full version sql dir . Thanks venkat
|
9454
2008-AUG-26 14:51:58
|
||||
|
Venkat, I have bunch of temp files and work in progress stuff in my working directory of SQLDIR. If I gave it so someone it will be confusing to sort though all that. I do want to take inventory of all the scripts, pick the most useful once and then cut a new tar ball of SQLDIR all in one package. When do I get to it? I don't know -- can't promise anything right now. The other point is that SQLDIR is meant to be a standalone tool which allows you to create your own library of sql scripts. The scripts that I provide with it in the sql.tar archive are meant to be an example really, not a "do it all" toolkit. Regards, - Vitaliy
|
9457
2008-AUG-27 15:55:41
|
||||
|
hi , my client needs a shell script to be run on windows i have the unix version, iam not able to figure out the exact synatax , can u help out with that script, it needs to be run on windows, can u send me that iam forwarding u the link, Thanks venkat
|
9458
2008-AUG-27 17:13:35
|
||||
|
Sorry I am not a windows guy.