ORACLE DBA 1
‘Oracle Architecture’… hampir satu hari aku belajar secara teori.. waduss.. ngantuk gila.. ape pun instructor memang best..!! berpengalaman dan pandai membawa kuliah dengan baik… control…!!
telnet 192.168.1.30 (ini training punya ip daaaa…)
id = oracle
passwd = xs2oracle
sqlplus “/ as sysdba”
conn adi/adi
kalau nak guna command unix dalam sql kena start dengan simbol… ‘!’ contoh ‘!vi adi.txt’, ‘!ls’
apa itu Oracle Server, Oracle Instance, Oracle Database? aaaaaaa…?? nota please..!!!
Fungsi shared pool – library cache, data dictionary cache
SGA – System Global Area (memory & background process)
PGA – Program Global Area
BACKGROUND PROCESS
PMON – SMON – DBWR – LGWR – CKPT
V$ (v dollar sign) semua file view bermula dengan v$
v$fixed_table
cth : select name from v$fixed_table;
PFILE & SPFILE (Process file & System file)
create PFILE
> cp init.ora init[SID].ora
create SPFILE
> create SPFILE=[DIR/spfile[SID].ora] from PFILE=[DIR/init[SID].ora]
modifying parameters in SPFILE
> alter system set db_cache_size=50000 scope = both;
TABLESPACE
- Create tablespace
SQL> create tablespace adi permanent
2 datafile ‘/data2/oradata/ora9i/adi01.dbf’ size 10M
3 autoextend on next 1024 maxsize 20M;
SQL> create temporary tablespace aditemp
2 tempfile ‘/data2/oradata/ora9i/aditemp01dbf’ size 10M
3 autoextend on next 1024 maxsize 20M;
- view tablespace structure
> desc dba_tablespaces;
- list
> select file_name from dba_data_files;
TABLESPACE MANAGING (segment)
Dictionary – Data Dictionary managed, data dictionary manage extent, PCT increase
Local – Self Managed, auto manage extent
- Configuring automatic segment space management
> CREATE TABLESPACE data01
> DATAFILE ‘oradata/data01.dbf’ SIZE 5M
> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
> SEGMENT SPACE MANAGEMENT AUTO;
- check status tablespace management
> select tablespace_name, extent_management, segment_space_management from dba_tablespaces;
- MOVING DATA FILES (methodology)
- must be dba
> ALTER TABLESPACE adi offline
- quit to unix
$ cp adi01.dbf $ORACLE_HOME/oradata/ora9i/adi01.dbf
- login to sql
> ALTER TABLESPACE adi RENAME DATAFILE ‘adi01.dbf’ TO ‘$ORACLE_HOME/oradata/ora9i/adi01.dbf’;
> ALTER TABLESPACE adi online
STORAGE STRUCTURE
- komponen storage
DATABASE – nama database TEST/IGIMS/ETC
TABLESPACE – system, RBS (rollback segment), user_data, temp
DATA FILES – system01.dbf, undo.dbf, user1.dbf, user2.dbf, temp.dbf
SEGMENTS – table, index, partition table, partition index, etc
- check space
> select distinct tablespace_name, bytes, blocks from dba_free_space;
ADMINISTERING USER
- create user
SQL> create user adi
2 identified by adi
3 default tablespace adi
4 temporary tablespace aditemp
5 quota unlimited on adi
6 quota 1M on users
7 password expire;
- grant connect
SQL> grant connect to adi;
- create table
SQL> create table adi(nama varchar2(20),ic varchar2(12)) tablespace adi;
SQL> create table adi2(nama varchar2(20),ic varchar2(12)) tablespace users;
- view
SQL> select table_name, tablespace_name from tabs;
- change user
> conn / as sysdba
> conn adi/adi
> conn adi
password : adi
- change quota on users
SQL> conn sham/sham
Connected.
SQL> alter user adi
2 default tablespace adi
3 temporary tablespace aditemp
4 quota 0M on users;
- view all users
SQL> select username from all_users;
- drop user
> DROP USER adi;
> DROP USER adi CASCADE; (sekiranya user masih online)
GRANT & REVOKE
system, (ROLE-sysdba, sysoper) & object privileges
- create user system privilege with admin option
> grant create table to adi with admin option
- create user object privilege with grant option
> grant select on [table_name] to adi with grant option
so, user adi boleh gunakan
> select * from [user_yg_bagi_grant.table_name]
> grant dba to [user]
> revoke dba from [user]
- obtaining privileges information
dba_sys_privs
session_privs
dba_tab_privs
dba_col_privs
HOW TO VIEW STATUS USER
- check field
> desc v$session;
> select username, status from v$session
ADMINISTERING PASSWORD
- create profile
> CREATE PROFILE adi_profile LIMIT
2 FAILED_LOGIN_ATTEMPTS 3
3 PASSWORD_LOCK_TIME UNLIMITED
4 PASSWORD_LIFE_TIME 30
5 PASSWORD_REUSE_TIME 30
6 PASSWORD_GRACE_TIME 5
7 PASSWORD_VERIFY_FUNCTION verify_function;
Profile created.
- assign profile to user
> ALTER USER adi PROFILE adi_profile;
User altered.
- alter profile
> ALTER PROFILE adi_profile LIMIT
2 FAILED_LOGIN_ATTEMPTS 3
3 PASSWORD_LIFE_TIME 60
4 PASSWORD_GRACE_TIME 10;
Profile altered.
- drop profile
> DROP PROFILE adi_profile;
Profile dropped.
- apabila account kena ‘locked’.
> ALTER USER adi
2 ACCOUNT UNLOCK;
User altered.
- HOW TO LOCK/UNLOCK USER
> ALTER USER nama_user
2 ACCOUNT LOCK/UNLOCK;
- create resource
conn / as sysdba
> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;
> CREATE PROFILE adirsc_profile LIMIT
2 SESSIONS_PER_USER 2
3 CPU_PER_SESSION 1000
4 IDLE_TIME 60
5 CONNECT_TIME 480;
Profile created.
- assign resource profile to user
> ALTER USER adi PROFILE adirsc_profile;
OBTAINING INFORMATION
dba_users & dba_profiles
LOADING DATA
- DIRECT LOAD (CTH: FROM ADI TO ADI3)
> INSERT /*+APPEND*/ INTO adi3 NOLOGGING SELECT * FROM adi;
> COMMIT;
DIRECT LOAD 2ND TIME (CTH : FROM ADI TO ADI3)
????
SQLLOADER
- guna spooling technic:
> spool adi.txt
> select * from adi
> spool off
- exit to unix prompt
> (shift + ! + enter)
> !vi adi.txt
aku,81828123818,
kau,1231233,
dia,4324232,
aku,81828123818,
kau,1231233,
dia,4324232,
mereka,11111111,
~:wq!
(jangan tinggalkan sebarang baris kosong dan space antara data)
or
$ vi adi.txt
~ delete row yg tak berkaitan (esc & D – delete row one by one)
~:wq!
$ exit
- create control file
$ vi adi.ctl
~load data
~infile adi.txt
~append
~into table adi4
~field terminated by “,” optionally enclosed by “‘”
~(nama,ic)
~:wq!
- run sql loader
$ sqlldr adi/adi0% control=adi.ctl log=adicase1.log direct=true
RMAN (RECOVERY MANAGER)
BACKUP COMMAND
LOGIN to unix as Oracle
- login to rman
$ rman target /
- list setting
RMAN> show all;
- backup to disk
RMAN> BACKUP
2> FORMAT ‘/BACKUP/df_%d_%s_%p.bus’
3> DATABASE fileperset = 2;
- if one to backup to tape
RMAN> configure CONFIGURE DEFAULT DEVICE TYPE TO tape
- backup to disk for tape drive
RMAN> RUN {
2> allocate channel [channel example c1] TYPE ‘set tape’
3> maxpiecesize = 4G;
4> BACKUP
5> FORMAT ‘DF_%T_%S_%P’ FILEPERSET 3
6> [tablespace user]; }
- BACKUP ARCHIVE LOG
RMAN> BACKUP
2> FORMAT ‘/DISK1/BACKUP/ar_%t_%s_p%’
3> ARCHIVELOG ALL DELETE ALL INPUT;
- IMAGE COPIES
RMAN> COPY
2> DATAFILE ‘/ORADATA/users_01_db01.dbf’ TO
3> ‘/BACKUP/users01.dbf’ tag=DF3,
4> ARCHIVELOG ‘arch_1060.arc’ TO
5> arch_1060.bak’;
LIST OF COMMAND
- check schema
RMAN> report schema;
- list copy
RMAN> list copy;
- list backup yg dah tak boleh dipakai (dah ada banyak latest)
RMAN> report obsolete;
- delete backup yg dah tak pakai
RMAN> delete obsolete;
or
RMAN> delete noprompt obsolete;
RMAN> list backupset;
RMAN> crosscheck backupset;
RMAN> delete expired backupset;
- list of backups
RMAN> list backupset summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
——- — – – ———– ————— ——- ——- —
5 B F A DISK 16-JUN-06 1 1 TAG2006
RMAN> report unrecoverable database;
- how to check
RMAN> select sequence#,thread#,resetlogs_change# from v$archived_log;
- how to monitoring RMAN backups
?
BACKUP UNTUK MAJOR PROBLEM (datafile)
RMAN BACKUP (in noarchivelog mode)
rman target /
RMAN> startup mount
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
RMAN BACKUP (in archivelog mode)
rman target /
RMAN> startup mount
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
BACKUP UNTUK MINOR PROBLEM (content)
RMAN BACKUP (incomplete recovery until time)
RMAN> run {
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> set until time = ’2000-12-09:11:44:00′;
5> restore database;
6> recover database;
7> alter database open resetlogs;}
RMAN BACKUP (incomplete recovery until sequence)
refer log dalam directory /bdump/alert_sid.log
RMAN> run {
2> set until sequence 120 thread 1;
3> alter database mount;
4> restore database;
5> recover database; #/* recover dari log 119 */
6> sql “alter database open resetlogs”;
7> }
tips from cikgu;
RMAN> startup mount
RMAN> run {
2> set until sequence 120 thread 1; (sequence & thread dari alert_sid.log
3> restore database;
4> recover database;
5> sql “alter database open resetlogs”;
6> }