Use ipcs to view runtime ipc usage. For AMM (only in 11g) also need to ls /dev/shm dir. See my downloaded file oracle11ginternals.html from http://blog.tanelpoder.com/2007/08/21/oracle-11g-internals-part-1-automatic-memory-management/ On Windows, Directory $ORACLE_HOME/database corresponds to UNIX $ORACLE_HOME/dbs. GLOBAL NAMEs = SERVICE NAMES: lowercase ., like "tstsid.admc". Called "service name" by "lsnrctl status" and in tnsnames.ora files (on RHS). Hence variablized below as . NET_SERVICE_NAMEs are service *aliases* defined on client side by LHS inside tnsnames.ora. Can determine global database name, of you can connect, with: select * from global_name; Instance data saved under $ORACLE_BASE/oradata/ control*.ctl redo*.log *.dbf # tablespace files SYNONYMS Public synonmys include the schema, private synonyms do not. Current schema objects have higher precedence than public synonyms. Synonyms do not have privs. Privs granted to them are actually applied to the underlying objects. DISK SPACE table/index segment = multiple extents = multiple contiguous data blocks RUN STATES Idle Unmounted. SGA allocated, BG processes running, [Ctrl file NOT LOADED] Mount state. Ctrl file loaded. [redo + DB files not open] Open. Redo + DB files opened. ACCOUNTS Double-quotes allowed with passwords, but somehow passwords now case-sensitive even if quotes not used in "IDENTIFIED BY" clause. User names are still case-insensitive. Grant CONNECT to all users. (and RESOURCE to allow them to create objects in their own schema; Seems that granting RESOURCE gives unlimited quota on all tablespaces). EXCEPT don't need to grant anything but "dba" to DBAs. Any OS user with the OSDBA group may start and stop. I think any DB user with SYSOPER may connect-as and start/stop. It looks like when connecting x/y as sysdba, x makes no difference at all. The invoker just needs the OS group. This is definitely the case for startup/shutdown. * as sysdba: The user name makes no difference, and you may as well use "/". because it just checks your OS group. Easy Connect Naming A great improvement: host[:port]/ TNS_ADMIN defaults to $ORACLE_HOME/network/admin. tnsnames.ora is read from here. I don't think that sqlnet.ora is. EM Control Console (aka Database Console) emctl {start|stop|status} dbconsole http://localhost:1158/em (a Java class, answers to all addrs) log in as SYSTEM normally (can only "connect as normal") SYS / "Connect As" SYSDBA: For backup/recover/upgrade (I notice that SYS can only "connect as SYSDBA"). Other (non-admin), account with SELECT_CATALOG_ROLE (incl. all DBAs). * These accounts are real DB accounts. Use Setup/Administrators to grant privs needed by other Control Console users. To start up DB instance from here Log in with host account with the OSDBA group. Just give any DB account + password, since just depends on OS group of prev. (??Unfortunately, doesn't support "/"? Seems to work now?). Guide say to use SYS / syspwd "as SYSOPER"? But "connect / as sysoper" works. Start in open mode. Useful for Non-admin purposes. ?I think regular users need Select Any Dictionary priv.? CRITICAL ORACLE SERVER PROGRAMS. These must all be backgrounded& explicitly. runInstaller (= OUI) tnsping dbca (script that runs a Java class) netca (script that runs a Java class) [Use to create/manage tnslsnr] emctl {start|stop|status} dbconsole starts emwd.pl (perl script that runs other stuff and... INCLUDING oc4j! tnslsnr {start|stop|status} (binary) (by default answers to all addrs) No network config files are necessary for network access by sqlplus or JDBC. Funny thing is, after installation, a couple restarts of lsnrctl are usually required before it starts serving the instance. sqlplus dbua New Upgrade Assistant. sysresv CRITICAL FILES $TNS_ADMIN/tnsnames.ora $ORACLE_HOME/network/admin/ (use $TNS_ADMIN?) sqlnet.ora Just set names.directory_path = TNSNAMES Default names.default_domain is very different from "world". This is applied to connection strings BEFORE LOOKING THEM UP in tnsnames.ora!!!! $ORACLE_HOME/network/admin/ (use $TNS_ADMIN?) listener.ora Optional, since all defaults usually works fine. Don't need to list DB instances, since they auto-register. --------------------------------------- ASM: Seems that you turn over entire disks to Oracle to manage, something like a PV. Only difference from RAID mirror is that mirroring is done at file level and spread to component disks according to Failure Groups. OSASM is presumably the OS group for SYSATM, the OS group for managing it. There is a corresponding connect as option. To create an ORACLE-HOME-independent ASM area, run "$ORACLE_HOME/bin/localconfig add" as root, then use DBCA to make a ASM with its own, new $ORACLE_HOME. "localconfig" configures ands starts CSS Cluster Synchronization Service. The stadalone ASM instances(at least) run an OMS (Oracle Enterrise Manager) instance. I guess that the instance must have its own SYS password. Docs say to get ADM admin privs by connecting as SYS "as SYSASM". One ASM instance per node (host?). I think multiple ASM instances share a network-shared disks. Disk Group Types external redudnancy (no Oracle-managed redundancy) normal redundancy (mirror usually?, default) high redundancy (3-way mirror) Data is spread (striped) evenly across component disks). Each "ASM Disk" can really be a partition or a network file. normal and HR ASM disks each belong to a Failure Group to indicate shared point-of-failure. By default each disk has its own Failure Group. ECM "Usable Free (GB) is actual, net usable space. When you add disk(s) to a group you specify relative resources to use to redistribute resources from 0 to 11, with 0 meaning don't do anything now. To Change Disk Discovery Path, use wildcard*! Supposedly a "oracleasm" command, but not present. I see "asmcmd", but that isn't working. If get ORA-00845, need to make a big shmfs tmpfs. --------------------------------------- Dedicated vs. Shared Connection Mode Looks like biggest determinant is that Dedicated Mode can't be throttled. --------------------------------------- Fri Feb 27 15:18:18 EST 2009. Downloaded the general (all-inclusive) 11gR1 distro from Oracle. Will attempt to install it onto OpenSUSE 11.1 according to the Linux 64-bit quick installation guide. Ships with Sun Java 1.5! This is used for dbca, netca, EM). IMPORTANT!!!!! Need to add to /etc/fstab (and reboot) tmpfs /dev/shm tmpfs rw,exec,size=1300M 0 0 Also takes size units like 4g. Value must be >= memory_max_target value. Value defaults to half of RAM. Not documented GB vs. GiB, MB vs. MiB. libstdc++33 has replaced compat-libstdc++ (apparently just on SuSE) UNDOCUMENTED PACKAGE REQUIREMENTS!!!!! pdksh. ANY ksh fine. ? gcc-32bit ? looks to be unnecessary? glibc-devel, libstdc++-devel for i686 seem to be unnecessary [Feb 2014 install I am installing all prereqs, unnecessary or no] With 11gR2, newer versions of required RPMs are fine even tough they are not detected. Just tell installer to ignore-all once you know that the requested or newer rpms are present. No issue here with Centos. groupadd -g 1414 oinstall (Can name the Oracle Install group anything) # This MUST be primary group for all Oracle s/w owners groupadd -g 1403 dba useradd -c Oracle -u 1414 -g oinstall -G dba CAN USE SEPARATE USERS FOR INSTALLATION (oinstall grp) and ADMIN (SYSDBA/OSDBA). So say the docs. I wonder...? SYSDBA and SYSOPER are privileges, used by connecting "... as sysoper/sysdba". Either can startup and shutdown DB. Distinguished by other privileges. OSDBA and OSOPER are OS groups authorizing SYSDBA and SYSOPER. UNIXes default to: dba, oper. Windows: ORA_DBA, ORA_OPER. The groups are set at ORACLE_HOME installation time and only visible in "rdbms/lib/config.c". If set to "", there is no OSOPER and therefore no SYSOPER privilege. Oracle's orapwd facility can also grant SYSDBA/SYSOPER privileges to Oracle accounts. DBA user SYS may ONLY connect "as SYSDBA". SYSOPER is logical name for the OS Operator group OSOPER. UNIX Oper = "oper".* SYSOPER has database creation and has the power to recover/recreate databases. Says elsewhere that SYSOPER is just for starting/stopping DB!!!! SYSASM is just for Automatic Storage Management and clustering stuff. Oracle also has the ability to use hard files for SYSOPER/SYSDBA passwords. 11.2 system requirements, per Linux Install Guide http://docs.oracle.com/cd/E11882_01/install.112/e47689/pre_install.htm#LADBI1085 RAM. Min 1 GB. Recommend 2 GB. 1 GB free in /tmp or $TMP and $TMPDIR for installer 4.6 GB for Oracle system (little more for Ent. Edition) CMDB Server using only 4.3 GB. But the 12c installer WILL NOT LET YOU PROCEED WITH < 6246 MB for $ORACLE_HOME!!! Actually uses 4.5 GB. 6.1 GB for 12.1. Doesn't say but must be system + DB files. So the same. 1.5 GB for DB files (little more for Ent. Edition) 1.2 GB for BDA-created database with my BDA template. CMDB Server uses 7GB for data files + 7 GB for diag data = 14 GB. With 12.1, a small default TPO/generic datagbase takes up 1.8 GB! AFTER installation check available shm: df -h /dev/shm/ (Linux Install Guide explains how to make more available). Had to raise following system params. 11gr1 val; CMDB+DB server val. *=same Just see table in docs. kernel.sem -> 250 256000 100 1024; 250 32000 100 128 net.core.rmem_default -> 262144; * net.core.rmem_max -> 4194304; * net.core.wmem_default -> 262144; * net.core.wmem_max -> 262144; 1048576 # SUSE ONLY!: vm.hugetlb_shm_group = 1414; 0 # number is UID for oinstall group ID! fs.aio-max-nr -> 1048576; * # The following values were astronomically high, which probably means the # values will grow as long as system resources are available, but it # kills OUI. Therefore, lowering the following: kernel.shmall -> 2097152 ; 4294967296 kernel.shmmax -> 2147483648 ; 68719476736 fs.file-max -> 6553600; 6815744 net.ipv4.ip_local_port_range [purposefully unset]; 9000 65500 Ignoring ip_local_port_range recommendation. Going to set this to satisfy BDA Raise ulimits for software owner in /etc/security/limits.conf and verify with "ulimit -{HS}{nus}: soft nofile 1024 (prolly ok) *hard nofile 65536 *soft nproc 2047 *hard nproc 16384 soft stack 10240 (prolly ok) *hard stack 32768 Do not need the SUSE chkconfig step for a normal SUSE system. The installer defaults (silently) the 150kB-per-product "inventory" directory to a peer directory of $ORACLE_BASE. Just make sure that oracle has no write privs to peer $ORACLE_BASE/../oraInventory and you will be prompted about the inventory directory to use. Setting export ORACLE_BASE=/local/oracle-base export ORACLE_HOME=$ORACLE_BASE/11gr1 # They really want this UNDER BASE. (I'm using oraInventory of /local/ora-inv. Takes 150kB/product). # They like the convention ORACLE_HOME=$ORACLE_BASE/product//db_* export ORACLE_SID=SID01 (2041-02-25 install, not setting before runInstaller since don't want to install an instance) Directions say to create $ORACLE_BASE and data dirs with same exact ownership and privs. IF $ORACLE_HOME or oracleInventory will be outside of $ORACLE_BASE, you'll need to create them in the same way. DBCA defaults data directory to $ORACLE_BASE/oradata. Run .../runInstaller from distro AS "oracle". [I'm installing Standard Edition One instead of Enterprise. CHANGED MIND.] Picked Custom install. De-selected Enterprise Edition Options (entire group). (Would normally de-select developer products, but this is my workstation). + ODBC Driver [ Would like to install Oracle Configuration Manager, which seems to be an information collector which is hereafter REQUIRED for customer support! Can apparently change the operating mode to "disconnected", at least. Can't run it without a CSI number (and a Metalink account), so not installing I don't know how dependent this system is on the Support Manager of the Command Console. ] Telling it to install no database, since want to invoke dbca myself for that. For some reason, they make a unique "Name" for each $ORACLE_HOME. I accept default generated name of "OraDb11g_home1". At end, has you run scripts as root to write config to /etc and write shared scripts to /usr/local/bin/. Records ports for the web apps at $ORACLE_HOME/install/portlist.ini. Does not list the tnslsnr port (1521) though??? Gets updated by dbca too. After no-DB-instance system installation, nothing at all is running. Neither the Enterprise Manager DB Control that the install guide says, nor the Ultra (Enterprise Manager gets installed with DB instances, and may be local or Grid-based). Search listed in the *.ini file though nothing running there (maybe it listens when instances run?) INSTANCE IDS SIDs can be host-specific. No reason not to make the Global DB name = SID + ".org". dbca ---- Do verify, but I believe you always get the SCOTT tables, though the SCOTT account is locked. dbca "Sample Schemas" Human Resources Order Entry Online Catalog Product Media Information Exchange Sales History EXAMPLE tablespace ~ 130MB. Has 2 character set settings DATABASE char set Sets default character set to an OS default char set. Must explicitly set Unicode if that's what you want, and they name it AL32UTF8. National Char set Set to UTF8 or UTF16. Defaults to 16. For UTF definitely specify "AL16UTF16" Looks like, if you choose a directory to hold the instance's data files, it will actually make directory .ora spfile.ora init.ora init.ora init.ora is a PFILE. dbs/init.ora SPFILEs are the binary equivalents. dbs/spfile.ora Control Console (and ALTER command???) can only Persist spfiles. Can only immediately activate parameters with a spfile. Converting PFILEs and SPFILEs. IMPORTANT! Don't write spfile in use by DB. Default paths are with : dbs/spfile.ora, dbs/initora connect... as sysdba; CREATE SPFILE FROM PFILE; // Loads PFILE, does not get from FS. CREATE SPFILE='/path/to/spfileX.ora' FROM PFILE='/path/to/initX.ora'; CREATE SPFILE FROM PFILE=/path/to/initX.ora; CREATE PFILE FROM SPFILE; // Creates dbs/init.ora CREATE PFILE=/path/to/initX.ora FROM spfile; I see that by default now you get a good SPFILE and a useless "init.ora" file. To see the live PFILE/SPFILE parameters, use SQL (with relevant authority): SELECT name, value FROM v$parameter WHERE name = 'control_files'; But names don't have the *. prefixes, and seems to report wrong on .x parameters. ALTER SYSTEM.. SCOPE=MEMORY/BOTH/SPFILE Templates reside at $ORACLE_HOME/assistants/dbca/templates/ *.dbc Seed *.dbt Nonseed Seed templates reference*.dfb files (compressed RMAN backups), *.ctl (just for ASM or Oracle-managed). *.dbt's seem more simple and more powerful. For some reason, *.dfb's for my new forks are size 0! GOTCHA! Database Character Sets DO NOT GET SAVED INTO TEMPLATES!!! Don't believe the HTML reports. (National character set does save). Though you set the Memory amount, only the percentage ever gets applied, and is all that matters. There is no opportunity to make several settings. Especially for option install. Edit and change undesireds to "false". For v.12 dbca had to add cursor_sharing value manually (FYI 12 does not support "SIMILAR"). Need to remove the XDB dispatcher element. sqlplus /nolog CONNECT AS SYSOPER Enter user-name: sys Enter password: ... For network connection, a couple options are sqlplus user@ sqlplus 'user@"[:port]/"' ("Easy Connect Naming") (Even with NO tnsnames.ora! and regardless of names.default_domain) sqlplus 'user@(description=...)' Where (description=...) exactly as would be in tnsnames.ora file. E.g.: sqlplus system/pass@'(description=(address_list=(address=(protocol=tcp)(host=rac11)(port=1521)))(connect_data=(sid=racdb1_2)))' JDBC client works with NO tnsnames.ora involvement!!! jdbc:oracle:thin:@beyla:1521:SID01 # Even with NO tnsnames.ora! #and regardless of names.default_domain # Must supply port in this case OR jdbc:oracle:thin:@//localhost/SID01.admc ("Easy Connect Naming"). OR jdbc:oracle:thin:@ ODBC Script odbc_update_ini.sh TOTALLY SUCKS AND IS OBSOLETE! Must have LD_LIBRARY_PATH set (TODO: Find out if only libclint.so.* needed). Must set LD_LIBRARY_PATH and ORACLE_HOME just like for native access. There are no .odbc.ini settings to specify target database (host/port/SID). Therefore, for a local DB, you may set env var ORACLE_SID; and you can use connection string variable DBQ set to a connect string (like for tnsnames.ora) Cannot get jdbc:odbc to work with Oracle ODBC :(. DO STUDY http://download.oracle.com/docs/cd/B28359_01/server.111/b28301/toc.htm DATA DICTIONARY dba_* views: All all_* views: Counter-intuitively, not ALL, but what user has view privs on. v$ views: Dynamic performance views. Available only to admins. RMAN Rman repository is in the control file by default. Can only restore backups which are recorded in the control file. Writes either Image copies (straight files) or Backup sets (efficient). Does incrementals in usual fashion. IIRC, Image copies can only be written to disk. v$rman_output contains RMAN reports since the last instance start. (this is what the Console's View Backup Report shows). You probably do not need reports to use and manipulate the backups, since they are managed in the control file. Cross-check verifies that backups listed in the metadata exist on disk or in online? tapes. FlashBack This is a logical rollback feature unreleated to backups, and non-database recoveries can be performed while the DB remains online. I don't know how to enable/disable the feature. Must "Enable Row Movement" on tables to permit rowids to be rolled back. Though DB must be taken offline to perform database rollbacks, these restorations are much faster than traditional point-in-time recoveries. Diagnostics ADDM Automatic Database Diagnotic Monitor stores 8 days of hourly snapshots to awr.* in SYSAUX tablespace. As of v11 all logs, traces, dumps are stored in Audotmatic Diagnostics Repository, ADR, in files outside of the instance. Patch "sets" apply to the DB "system" and increment the version. Download them from Metalink. TRACING Set init params to enable the feature. Set init param SLQ_TRACE = true OR In the session, ALTER SESSION SET SQL_TRACE = false; tkprof ora53269.trc ora63269.prf [nam=val...] EXPLAIN PLAN FOR SELECT...; by default writes to a temp table. Can run a script to create the plan_table in your schema instead. Very complicated to read the plan_table output, so scripts (with function definitions?) are provided. Cluster associates one or more high usage table for joins. INDEX-ORGANIZED TABLES (a.o.t. default HEAP tables) Entire table is stored in the index, instead of storing table + index. Higher performance, but have constraints. Must be specified by index col. Can't be clustered. Can't contain virtual columns. Can't contain LONG type. VM images centos-base r00tt00t centos-preOracle same password for both root and oracle My dbca-created smallish database (documented here) takes 1.9 GiB. runInstaller from Tue Feb 25 13:20:23 EST 2014 12.1 installer (expanded) takes up 2.6 GiB 12.1 ORACLE_HOME takes up 5 GiB Needed to remove noexec setting in /etc/fstab. Validation fails saying that reference data not available. Stack trace looks like an installer bug. Installing "Standard Editon One", which is Standard minus RAC, HA, Clusterware, storage management. export ORACLE_BASE=/local/oraclesys export ORACLE_HOME=/local/oraclesys/12cr1home1 and before installing instance: export ORACLE_SID=SID01 Admin pwd for both SYS and SYSTEM: 0raAdmin Yes to Configure EM Database Express # Will not accept ANY port value for Network Configuration! therefore Disabling Listener Selection #Listener port 1599. Verify this is what is normally 1521. #Listener name listener12. Verify this is what is normally listener (case-insensitive, I think). # After all that, the default listener is configured anyway. Disable Fast Recovery 200 processes Dedicated Server Mode (the default) I entered my email addr but didn't opt in or enter any support account info. ARDCI trace logs are written under $ORACLE_BASE/diag/rdbms/. These logs only required if will use "adrci" commands. "adrci -help" To purge logs under diag/rdbms///incident, run: adrci exec='set homepath diag/rdbms; purge -age 60 -type incident' # unit is minutes Purge policy: adrci exec='set homepath diag/rdbms; set control(SHORTP_POLICY = 24)' # unit is hours Fri Mar 7 17:10:55 EST 2014 Installing Oracle 12.1 for a Remedy database. (No updates available for 12.1 at this time). Adjust system RAM so that 40% of RAM is desired Oracle memory of 3 GB. Installer gives INS-13001 error message due to CentOS. Just Yes to continue. TO verify, check that /tmpOraInstall*/*.err contains no error messages other than about "Reference data". Choosing Standard Edition One dbca run: Looks like it does no good to set ORACLE_HOME var before installing ORACLE_HOME. Advanced Mode Global DB name ardb02.admc. Sid ARDB02 De-select Configure Enterprise Manager... Select "Use the Same Adm...: my oracle password Listener Selection page. Do nothing. Don't select any. (Often takes a few listener restarts before it starts working???) Change to Unicode on the Initialize Parameters page. For UTF: AL32UTF8. For some reason the global DB name is transformed to all-caps! I think that for a dedicated database server, 40% for memory is too low. 2.7 G of my 8 G remain unused. Redid with 70% usage. N.b. memory_target specifies total RAM used. With the auto-allocation setting, 75% will be used for SGA, 25% for PGA. (sga_target and pga_aggregate_target, in MB) After install, from a /nlog sqlplus session, create init*.ora PFILE: CREATE PFILE FROM SPFILE; // Creates dbs/init.ora CLIENT Quick Client installers do not have most tools and have no Administrator installation type. Client Administrator install takes up 1.8 GB. Need 400 MB in /tmp. Going to try the Linux x86-64 installer on VMware because the AMD64 version only goes up to 10.x To greatly reduce size, trying the Basic Lite RPM + SQL*Plus Additional libs NON-QUICK client installers. There is only 64 bit AMD and general 32-bit. Therefore trying the AMD64. Regular Client Installer. Don't need to set LD_LIBRARY_PATH. "oraenv" don't work. Quick Client Installer. Contains only files, no installer. Need to set LD_LIBRARY_PATH. MEM RESIZING COMMANDS: Want to use 2 GB of RAM. alter system set memory_max_target = 2058M scope = spfile; RESTART alter system set memory_target = 2058M; ALTER SYSTEM SET SGA_TARGET = 0; ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0; !! Must set /dev/shm size >= memory_max_target value. See above for /dev/shm. DBCA settings (Set automatic memory allocation/management. May only be there for 11.2.) Manual edits to *.dbc XML file. Most general to set percentage with memory_target 1 MB. Remove dispatchers if not using XDB. Set options all to "false" since UI doesn't let you. Oracle Inventory Pointer File location is platform-specific: For Solaris: /var/opt/oracle/oraInst.loc For Linux: /etc/oraInst.loc For Windows this pointer is located in the registry key: \\HKEY_LOCAL_MACHINE\\Software\Oracle\inst_loc. The file specifies ONLY Inventory dirs and install group.