03 05
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
02 12
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
02 04
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 [email protected][link_name];
12 23
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];
12 17
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
12 15
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;
10 13
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
10 01
podane za Oracle® Database Reference 10g Release 2 (10.2) Part Number B14237-04
Table B-1 Creating the Data Dictionary Scripts
Table B-2 Creating Additional Data Dictionary Structures
Table B-4 Upgrade and Downgrade Scripts
Table B-5 Java Scripts
09 28
how to clone database by RMAN:
it depends on recovering files from previous backup
- 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
- create password file for new database:
orapwd file=orapw[db_new] password=[pass]
- add new database to listener.ora and tnsnames.ora,
- create dirs for new database file,
- create directories for trace logs as admin with subdirectories: adump, udump, cdump, bdump,
- startup new auxiliary database in nomout mode(! then exit from your session because it will be hang on)
- start rman with ORACLE_SID = auxiliary database:
- 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)
- 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
09 28
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
Ostatnie komentarze