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_typefrom v$locked_object a,dba_objects bwhere 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;
.
Ostatnie komentarze