ORACLE AS

Oracle Komentarze (1) »

OPMNCTL (Oracle Process Management and Notification Server)
$ORACLE_AS_HOME/opmn/bin/opmnctl help
$ORACLE_AS_HOME/opmn/bin/opmnctl status help
$ORACLE_AS_HOME/opmn/bin/opmnctl status -l
$ORACLE_AS_HOME/opmn/bin/opmnctl stopall
$ORACLE_AS_HOME/opmn/bin/opmnctl startall

check AS version:
cat $ORACLE_AS_HOME/config/ias.properties | grep Version
$ORACLE_AS_HOME/OPatch/opatch lsinventory -detail

ORACLE AWR

Oracle Komentarze (1) »
Oracle Enterprise Manager => Advisor Central


AWR (Automatic Workload Repository)
statystyki przechowywane w tabelach WRH$ w przestrzeni SYSAUX
warunki:
STATISTICL_LEVEL= TYPICAL | ALL
widoki:
memory:
DBA_HIST_SGA
DBA_HIST_PGASTAT
DBA_HIST_PGA_TARGET_DEVICE
DBA_HIST_SGASTAT
DBA_HIST_BUFFER_POOL_STAT
DBA_HIST_DB_CACHE_ADVICE
DBA_HIST_SHARED_POOL_ADVICE
sql:
DBA_HIST_SQL_SUMMARY
DBA_HIST_SQLSTAT
DBA_HIST_SQL_PLAN

other:
DBA_HIST_SYSSTAT
DBA_HIST_FILESTATXS

DBA_HIST_WR_CONTROL
DBA_HIST_SNAPSHOT
DBA_HIST_DATABASE_INSTANCE
DBA_HIST_ACTIVE_SESS_HISTORY
sprawdzenie częstotliwości:
SELECT * FROM DBA_HIST_WR_CONTROL
ustawienie częstotliwości:
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS ([retention – min],[interwal – min]);
manualny snapshot:
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
drop snapshot:
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE([low_id],[end_id]);
raporty AWR:
$oracle_home/rdbms/admin/awrrpt.sql
$oracle_home/rdbms/admin/awrrpti.sql – z wyborem instancji
raporty ADDM (Automatic Database Diagnostic Monitor:
analiza danych z AWR pod względem wąskich gardeł
$oracle_home/rdbms/admin/addmrpt.sql

ORACLE DBLINKS

Oracle Komentarze (0) »

SELECT * FROM dba_db_links;
SELECT * FROM user_db_links;
DROP DATABASE LINK [link_name];
CREATE DATABASE LINK [link_name] CONNECT TO [user] IDENTIFIED BY [password] USING ‚[tns_alias]’;
SELECT count(*) FROM user_tables@[link_name];

ORACLE UNDO

Oracle Komentarze (0) »

shrink undo tablespace:
* CREATE UNDO TABLESPACE [name] DATAFILE ‚[path]’ size [nr]G ;
* ALTER SYSTEM SET UNDO_TABLESPACE = [name];
* DROP TABLESPACE [old_name] including contents and datafiles;

check rollback status:
SELECT used_urec FROM v$transaction; –records
SELECT used_ublk FROM v$transaction; –blocks
SELECT time_remaning FROM v$session_longops WHERE sid = [sid];

ORACLE CREATE

Oracle Komentarze (0) »

create database manualy:


* create PFILE
main changes:
– db_name
– control_files
– audit_file_dest
– background_dump_dest
– user_dump_dest
– core_dump_dest

example init[dbname].ora

*.__db_cache_size=939524096
*.__java_pool_size=16777216
*.__large_pool_size=16777216
*.__shared_pool_size=587202560
*.__streams_pool_size=33554432
*.control_files='[path]/control1.ora’,'[path]/control2.ora’,'[path]/control3.ora’
*.audit_file_dest='[path]/admin/adump’
*.core_dump_dest='[path]/admin/cdump’
*.user_dump_dest='[path]/admin/udump’
*.background_dump_dest='[path]/admin/bdump’
*.compatible=’10.2.0.1′
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_files=1500
*.db_name='[dbname]’
*.global_names=FALSE
*.job_queue_processes=10
*.log_archive_format='[dbname]%t%s%r.arc’
*.log_checkpoint_interval=10000
*.max_dump_file_size=’10240′
*.open_cursors=1000
*.optimizer_mode=’CHOOSE’
*.pga_aggregate_target=629145600
*.processes=400
*.query_rewrite_enabled=’true’
*.remote_login_passwordfile=’EXCLUSIVE’
*.service_names='[dbname]’
*.session_max_open_files=40
*.sga_target=1610612736
*.shared_pool_reserved_size=0
*.undo_management=’AUTO’
*.undo_retention=100000
*.undo_tablespace=’UNDO_TS’

* create password file for new database:
orapwd file=orapw[dbname] password=[pass]

* add new database to listener.ora and tnsnames.ora,

* create dirs for new database file,

* create dirs for trace logs as admin with subdirectories: adump, udump, cdump, bdump,

* run script:

spool crt_db.spool
startup nomount
create database [dbname]
user sys identified by [pass]
user system identified by [pass]
maxinstances 5
maxloghistory 5
maxlogfiles 10
maxlogmembers 5
maxdatafiles 1000
character set EE8ISO8859P2
national character set AL16UTF16
datafile ‚[path]/system01.dbf’ size 512M autoextend on next 10M maxsize unlimited
sysaux datafile ‚[path]/sysaux01.dbf’ SIZE 1024M
logfile group 1 (‚[path]/redo01.log’) size 100m,
group 2 (‚[path]/redo02.log’) size 100m,
group 3 (‚[path]/redo03.log’) size 100m
default temporary tablespace TEMP tempfile ‚[path]/temp01.dbf’ size 2048M
undo tablespace UNDO_TS datafile ‚[path]/undotbs01.dbf’ size 3000M autoextend off,
‚[path]/undotbs02.dbf’ size 512M autoextend on next 10M maxsize unlimited;
spool off

* run script:

spool catalog.spool
@$ORACLE_HOME/rdbms/admin/catalog.sql
spool off
spool catproc.spool
@$ORACLE_HOME/rdbms/admin/catproc.sql
spool off
spool catrep.spool
@$ORACLE_HOME/rdbms/admin/catrep.sql
spool off
spool initjvm.spool
@$ORACLE_HOME/javavm/install/initjvm.sql
spool off
spool dbmsrand.spool
@$ORACLE_HOME/rdbms/admin/dbmsrand.sql
spool off
spool utlrp.spool
@$ORACLE_HOME/rdbms/admin/utlrp.sql
spool off

ORACLE LOCKS

Oracle Komentarze (0) »

find locks:

select 
oracle_username
os_user_name,
locked_mode,
object_name,
object_type
from
v$locked_object a,dba_objects b
where
a.object_id = b.object_id 
 
select l1.sid, ' BLOCK ', l2.sid from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

ORACLE BINARY CLONE

Oracle Komentarze (0) »

How to copy binaries to other server:

1. turn off database and all services

2. make backup ORACLE_HOME

in new server:
3. make dir ORACLE_HOME

4. set other system variables

5. put ORACLE_HOME from previous backup

6. set user and group ORACLE_HOME dir

7. remove .ora files from ORACLE_HOME/network/admin

8. run command:

ORACLE_HOME/oui/bin/runInstaller -clone -silent ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=new_Oracle_home_name

9. run script root.sh

10. try run dbca


	  

ORACLE SCRIPTS

Oracle Komentarze (0) »

podane za Oracle® Database Reference 10g Release 2 (10.2) Part Number B14237-04

Table B-1 Creating the Data Dictionary Scripts

Script Name Needed For Description

catalog.sql

All databases

Creates the data dictionary and public synonyms for many of its views

Grants PUBLIC access to the synonyms

catproc.sql

All databases

Runs all scripts required for, or used with PL/SQL

catclust.sql

Real Application Clusters

Creates Real Application Clusters data dictionary views

Table B-2 Creating Additional Data Dictionary Structures

Script Name Needed For Run By Description

catblock.sql

Performance management

SYS

Creates views that can dynamically display lock dependency graphs

catexp7.sql

Exporting data to Oracle7

SYS

Creates the dictionary views needed for the Oracle7 Export utility to export data from the Oracle Database in Oracle7 Export file format

caths.sql

Heterogeneous Services

SYS

Installs packages for administering heterogeneous services

catio.sql

Performance management

SYS

Allows I/O to be traced on a table-by-table basis

catoctk.sql

Security

SYS

Creates the Oracle Cryptographic Toolkit package

catqueue.sql

Advanced Queuing

Creates the dictionary objects required for Advanced Queuing

catrep.sql

Oracle Replication

SYS

Runs all SQL scripts for enabling database replication

catrman.sql

Recovery Manager

RMAN or any user with GRANT_RECOVERY_CATALOG_OWNER role

Creates recovery manager tables and views (schema) to establish an external recovery catalog for the backup, restore, and recovery functionality provided by the Recovery Manager (RMAN) utility

dbmsiotc.sql

Storage management

Any user

Analyzes chained rows in index-organized tables

dbmspool.sql

Performance management

SYS or SYSDBA

Enables DBA to lock PL/SQL packages, SQL statements, and triggers into the shared pool

userlock.sql

Concurrency control

SYS or SYSDBA

Provides a facility for user-named locks that can be used in a local or clustered environment to aid in sequencing application actions

utlbstat.sql and utlestat.sql

Performance monitoring

SYS

Respectively start and stop collecting performance tuning statistics

utlchn1.sql

Storage management

Any user

For use with the Oracle Database. Creates tables for storing the output of the ANALYZE command with the CHAINED ROWS option. Can handle both physical and logical rowids.

utlconst.sql

Year 2000 compliance

Any user

Provides functions to validate that CHECK constraints on date columns are year 2000 compliant

utldtree.sql

Metadata management

Any user

Creates tables and views that show dependencies between objects

utlexpt1.sql

Constraints

Any user

For use with the Oracle Database. Creates the default table (EXCEPTIONS) for storing exceptions from enabling constraints. Can handle both physical and logical rowids.

utlip.sql

PL/SQL

SYS

Used primarily for upgrade and downgrade operations. It invalidates all existing PL/SQL modules by altering certain dictionary tables so that subsequent recompilations will occur in the format required by the database. It also reloads the packages STANDARD and DBMS_STANDARD, which are necessary for any PL/SQL compilations.

utlirp.sql

PL/SQL

SYS

Used to change from 32-bit to 64-bit word size or vice versa. This script recompiles existing PL/SQL modules in the format required by the new database. It first alters some data dictionary tables. Then it reloads the packages STANDARD and DBMS_STANDARD, which are necessary for using PL/SQL. Finally, it triggers a recompilation of all PL/SQL modules, such as packages, procedures, and types.

utllockt.sql

Performance monitoring

SYS or SYSDBA

Displays a lock wait-for graph, in tree structure format

utlpwdmg.sql

Security

SYS or SYSDBA

Creates PL/SQL functions for default password complexity verification. Sets the default password profile parameters and enables password management features.

utlrp.sql

PL/SQL

SYS

Recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, and types.

utlsampl.sql

Examples

SYS or any user with DBA role

Creates sample tables, such as emp and dept, and users, such as scott

utlscln.sql

Oracle Replication

Any user

Copies a snapshot schema from another snapshot site

utltkprf.sql

Performance management

SYS

Creates the TKPROFER role to allow the TKPROF profiling utility to be run by non-DBA users

utlvalid.sql

Partitioned tables

Any user

Creates tables required for storing output of ANALYZE TABLE ...VALIDATE STRUCTURE of a partitioned table

utlxplan.sql

Performance management

Any user

Creates the table PLAN_TABLE, which holds output from the EXPLAIN PLAN statement

Table B-4 Upgrade and Downgrade Scripts

Script Name Needed For Description

catdwgrd.sql

Downgrading

Provides a direct downgrade path from the new Oracle Database 10g release

catupgrd.sql

Upgrading

Provides a direct upgrade path to the new Oracle Database 10g release

utlu102i.SQL

Pre-Upgrade Information

Analyzes the database to be upgraded, detailing requirements and issues for the upgrade to release 10.2

utlu102s.SQL

Post-Upgrade Status

Displays the component upgrade status after an upgrade to release 10.2

Table B-5 Java Scripts

Script Name Description

initjvm.sql

Initializes JServer by installing core Java class libraries and Oracle-specific Java classes

rmjvm.sql

Removes all elements of the JServer

catjava.sql

Installs Java-related packages and classes

ORACLE RMAN CLONE

Oracle Komentarze (0) »

how to clone database by RMAN:
it depends on recovering files from previous backup

  1. create PFILE for new database based on PFILE from source database
    init[db_new].ora
    main changes:
    • db_name
    • control_files
    • audit_file_dest
    • background_dump_dest
    • user_dump_dest
    • core_dump_dest
  2. create password file for new database:
    orapwd file=orapw[db_new] password=[pass]
  3. add new database to listener.ora and tnsnames.ora,
  4. create dirs for new database file,
  5. create directories for trace logs as admin with subdirectories: adump, udump, cdump, bdump,
  6. startup new auxiliary database in nomout mode(! then exit from your session because it will be hang on)
  7. start rman with ORACLE_SID = auxiliary database:
  8. at the auxiliary server run command:
    rman auxiliary / target sys/[sys_pass]@[targetdb_tnsalias] catalog rman/[rman_pass]@[catalogdb_tnsalias]
    you should get an output:
    connected to target database: [db_name] (DBID=[db_id])
    connected to recovery catalog database
    connected to auxiliary database: [db_name] (not mounted)
  9. run script:
    run {
    set newname for datafile 4 to ‚/oracle/oradata/[db_new]/users01.dbf’;
    set newname for datafile 3 to ‚/oracle/oradata/[db_new]/sysaux01.dbf’;
    set newname for datafile 2 to ‚/oracle/oradata/[db_new]/undotbs01.dbf’;
    set newname for datafile 1 to ‚/oracle/oradata/[db_new]/system01.dbf’;
    set newname for datafile 5 to ‚/oracle/oradata/[db_new]/example01.dbf’;
    set newname for tempfile 1 to ‚/oracle/oradata/[db_new]/temp.dbf’;
    duplicate target database to [db_new]
    pfile=/oracle/product/10.2.0/db_1/dbs/init[db_new]aux.ora
    logfile
    ‚/oracle/oradata/[db_new]/redo01.log’ size 50m,
    ‚/oracle/oradata/[db_new]/redo02.log’ size 50m,
    ‚/oracle/oradata/[db_new]/redo03.log’ size 50m;}

    to generate script above use sql at source db:
    spool clonescript.rcv
    select ‚set newname for datafile ‚||file_id||’ to ”’||file_name||”’;’ from dba_data_files order by file_id;
    select ‚set newname for tempfile ‚||file_id||’ to ”’||file_name||”’;’ from dba_temp_files order by file_id;
    spool off;
    and next modify output in vim:
    :% s/ora6\/oradata\/DBSRC/u01\/oradata\/DBAUX\/datafile
    or another script:

    run {
    set until time „to_date(‚2010-07-02:01:00′,’yyyy-mm-dd:hh24:mi’)”;
    configure default device type to sbt;
    configure device type sbt parallelism 2;
    configure auxiliary channel 1 device type sbt parms ‚ENV=(NB_ORA_SERV=server_name,NB_ORA_CLIENT=server_target_name)’;
    configure auxiliary channel 2 device type sbt parms ‚ENV=(NB_ORA_SERV=server_name,NB_ORA_CLIENT=server_target_name)’;
    duplicate target database to [db_new]
    DB_FILE_NAME_CONVERT=(‚[target_path1]/’,'[aux_path]’,
    ‚[target_path2]/’,'[aux_path]’)
    pfile=[pfile_aux_path]/[db_new].ora
    logfile
    ‚[logfile_aux_path]/redo01.log’ size 100m,
    ‚[logfile_aux_path]/redo02.log’ size 100m,
    ‚[logfile_aux_path]/redo03.log’ size 100m;
    }

for more read documentation

ORACLE DBCA

Oracle Komentarze (0) »

how to add database to DBCA:
if you want to add database, which was made by hand „create database etc.” not automatically by dbca, you should update /etc/oratab

WordPress - Hosting: Twój hosting - Skórka: N.Design Studio - Spolszczenie: Adam Klimowski.
RSS wpisów RSS komentarzy Zaloguj się