Java知識分享網 - 輕松學習從此開始!????

Java知識分享網

Java1234官方群24:java1234官方群24
Java1234官方群24:791563025
     

Mycat實現mysql高可用集群視頻教程免費領取

畢設代做,包查重聯系人QQ:1982956321畢設大神

領取國內優秀就業,加薪,跳槽項目課程源碼-vue2+jwt+springboot+mybaits前后端分離通訊錄系統課程

SpringBoot打造企業級進銷存

Java1234 VIP課程

領取微信掃碼登錄Java實現視頻教程

Java1234至尊VIP(特價活動)

Oracle DBA commands real world experience PDF 下載


分享到:
時間:2020-03-29 20:42來源:http://www.luygg.com 作者:小鋒  侵權舉報
Oracle DBA commands real world experience PDF 下載
失效鏈接處理
Oracle DBA commands real world experience PDF 下載

下載地址:


相關截圖:


主要內容:

Oracle DBA Commands from Real World Experience
Command Auth ID Function
SELECT owner, table_name, tablespace_name FROM dba_tables where 
owner = 'TST' AND table_name = 'EXAMPLE1';
SYSDBA Check table’s current 
tablespace
SELECT username, default_tablespace FROM dba_users WHERE 
username = 'TST';
SYSDBA Check user’s default 
tablespace
SELECT * FROM database_properties WHERE property_name = 
'DEFAULT_PERMANENT_TABLESPACE';
SYSDBA Check default tablespace if 
doesn’t specify a tablespace
SELECT * FROM DBA_SYS_PRIVS ORDER BY 1; SYSDBA Check granted SYS privileges
SELECT * FROM DBA_TAB_PRIVS; SYSDBA Check granted OBJECT 
privileges
• CREATE USER john IDENTIFIED BY john1234;
• CREATE USER john IDENTIFIED BY john1234 PASSWORD EXPIRE;
• CREATE USER john PROFILE DEFAULT IDENTIFIED BY john1234 
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp 
ACCOUNT UNLOCK;
• GRANT CONNECT TO john;
• GRANT RESOURCE TO john;
SYSDBA • Create user 
• Need to change passwd
• Set up default 
tablespace and temp 
tablespace
• Best practise to grant 
user connect and 
resource
• Alter user john identified by john5678;
• Alter user john account lock;
• Alter user john account unlock;
SYSDBA • Rest password
• Lock user
• Unlock user
• create user myao identified by Topway6667;
• grant create session to myao;
• select default_tablespace, temporary_tablespace from dba_users 
where username = 'MYAO';
• select tablespace_name, status from dba_tablespaces;
• alter user myao default tablespace TBS1;
• select default_tablespace, temporary_tablespace from dba_users 
where username = 'MYAO';
• alter user myao quota 10m on tbs1;
• select username, tablespace_name, bytes, max_bytes from 
dba_ts_quotas;
SYSDBA • Example
• Set user myao can only 
create objects in 
tablespace tbs1 up to 
10m
• drop user dev_test;
• drop user dev_test cascade;
SYSDBA • Delete user only
• Delete user and all 
objects created by the 
user
alter user myao identified by test1234 password expire; SYSDBA Rest user password
SET LINESIZE 150
column username format A50
select username, account_status from dba_users;
SYSDBA Check current database 
users
select * from session_privs; Everyone Check current login user’s 
privileges
CREATE ROLE <ROLE_NAME>;
or 
CREATE ROLE <role_name> IDENTIFIED BY <password>; 
GRANT SELECT ON TO <TABLE_NAME> TO <ROLE_NAME>;
GRANT <ROLE_NAME> TO <USER>;
SYSDBA • Create role, grant the 
role permissions and 
assign users to the role 
to grant role’s 
permissions
• Also can grant a role to 
another role
set linesize 150
column profile format A20
column resource_name format A30
column resource format A20
SYSDBA • Check default profile
• Check profile of a user
column limit format A20
• select * from dba_profiles where profile='DEFAULT';
• select username, profile from dba_users where username = 
'PETER';
• create profile DW_PROFILE LIMIT 
SESSIONS_PER_USER 2
IDLE_TIME 5
CONNECT_TIME 10;
• alter user peter profile DW_PROFILE;
• create user mark identified by mark profile DW_PROFILE;
SYSDBA • Create a profile
• Change a user to a 
different profile
• Create a user with a 
new default profile
• select * from dba_profiles where profile = 'DW_PROFILE';
• alter profile DW_PROFILE LIMIT SESSIONS_PER_USER 4;
SYSDBA • Check parameters of a 
profile
• Change a parameter of 
a profile
alter profile DW_PROFILE LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1;
SYSDBA Password management via 
profile
SELECT * FROM DBA_ROLES; SYSDBA Check existing roles 
SELECT * FROM DBA_ROLE_PRIVS; SYSDBA Check roles’ permissions
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE = 'DBA'; SYSDBA Check which user added to 
DBA Role
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'DBA'; SYSDBA Check DBA role privileges
SELECT * FROM DBA_TAB_COMMENTS WHERE TABLE_NAME = 
'COURSES' AND OWNER = 'TST';
SYSDBA Check table’s comments
GRANT SELECT ANY TABLE TO TST; SYSDBA Grant select permission on 
any tables to a user
SELECT * FROM DBA_USERS WHERE USERNAME = '<user_name>';
ALTER USER <user_name> ACCOUNT UNLOCK;
ALTER USER <user_name> ACCOUNT UNLOCK IDENTIFIED BY <passwd>;
SYSDBA • Check a user status
• Unlock the user
• Set a password for the 
user
SET LINESIZE 150
COLUMN TABLESPACE_NAME FORMAT A30
COLUMN FILE_NAME FORMAT A50
select tablespace_name, file_name, bytes from dba_data_files;
SYSDBA Check tablespace info
select tablespace_name, bytes from dba_free_space where 
tablespace_name = 'TBS1';
SYSDBA Check free space of a 
specific tablespace
alter database datafile 
'/u01/app/oracle/oradata/prod/prod/data01.dbf' resize 10m;
SYSDBA Increase size of an existing 
datafile
alter tablespace tbs1 add datafile 
'/u01/app/oracle/oradata/prod/prod/data02.dbf' size 20m;
SYSDBA Add a new datafile to an 
existing tablespace
drop tablespace tbs1 including contents and datafiles; SYSDBA Drop all datafiles, data and 
tablespace
create tablespace tbs1 datafile 
'/u01/app/oracle/oradata/prod/prod/data01.dbf ' size 1m autoextend 
on;
SYSDBA Tablespace auto extend on
create tablespace tbs1 datafile 
'/u01/app/oracle/oradata/prod/prod/data01.dbf' size 10m autoextend 
on next 512k maxsize 250m;
SYSDBA Proper way of creating 
tablespace with start size, 
auto extend, increase size 
and maximum size
alter tablespace tbs1 rename to tbs2; SYSDBA Rename tablespace
alter tablespace tbs2 drop datafile 
'/u01/app/oracle/oradata/prod/prod/data02.dbf';
SYSDBA Drop one datafile from 
multiple datafiles
• alter tablespace tbs2 offline; SYSDBA • Take tablespace offline
• mv /u01/app/oracle/oradata/prod/prod/data01.dbf 
/u01/app/oracle/oradata/prod/prod/data99.dbf
• alter database rename file 
'/u01/app/oracle/oradata/prod/prod/data01.dbf' to 
'/u01/app/oracle/oradata/prod/prod/data99.dbf';
• alter tablespace tbs2 online;
• mv existing datafile 
name
• rename tablespace
• Take tablespace online
• alter system set db_16k_cache_size=60m scope=both;
• create tablespace tbs1 datafile 
'/u01/app/oracle/oradata/prod/prod/data01.dbf' size 10m 
blocksize 16k;
SYSDBA • Enable block size 16k
• Create a tablespace 
with block size 16k
• select tablespace_name, file_name, bytes/1024/1024, status from 
dba_temp_files;
• alter database tempfile 
'/u01/app/oracle/oradata/prod/data/temp01.dbf' resize 70m;
• create temporary tablespace temp1 tempfile 
'/u01/app/oracle/oradata/prod/data/temp02.dbf' size 10m;
• select * from database_properties where property_name like 
'%TABLESPACE%';
• alter database default temporary tablespace temp1;
SYSDBA • Check temp tablespace 
info
• Resize an existing 
tempfile of a temp 
tablespace
• Add a new tempfile to a 
temp tablespace
• Check default 
tablespaces
• Change default 
tablespace to a different 
tablespace
• create temporary tablespace temp2 tempfile 
'/u01/app/oracle/oradata/prod/data/temp03.dbf' size 10m 
tablespace group tmp_group;
• alter tablespace temp1 tablespace group tmp_group;
• alter database default temporary tablespace TMP_GROUP;
SYSDBA • Create a temporary 
temp tablespace group
• Add a temp tablespace 
to a temp tablespace 
group
• Set new default 
temporary tablespace 
as the temp tablespace 
group
select tablesapce_name, contents, status from dba_tablespaces; SYSDBA Check tablespaces
alter tablespace undotbs1 add datafile 
'/u01/app/oracle/oradata/prod/prod/undotbs01b.dbf' size 10m 
autoextend on next 1m maxsize unlimited;
SYSDBA Add datafile to a UNDO 
tablespace
• create undo tablespace undotbs2 datafile 
'/u01/app/oracle/oradata/prod/prod/undotbs2_01.dbf' size 5m 
reuse autoextend on;
• show parameter undo;
• alter system set undo_tablespace=undotbs2;
SYSDBA • Create a new UNDO 
tablespace
• Check undo parameters, 
only 1 activated UNDO 
tablespace for an 
instance
• Change default 
activated UNDO 
tablespace
select segment_name, owner, tablespace_name, status from 
dba_rollback_segs;
SYSDBA Check UNDO tablespace 
status
alter system set undo_retention=1800; SYSDBA Change UNDO retention
• archive log list
• shutdown immediate
• startup mount
• alter database archivelog;
• alter system set log_archive_dest_1 = 
'LOCATION=/u01/app/oracle/oradata/prod/arch/' scope=both;
• alter database open;
SYSDBA • Check archive mode
• Shutdown database
• Startup on mount mode
• Enable archive mode
• Setup archive 
destination
• Startup normally
show parameter diagnostic_dest SYSDBA Check diagnostic files 
location
• cd /u01/app/oracle
• adrci
• set home diag/rdbms/prod/prod
• show alert -p "message_text like '%ORA-%'"
• show alert -p "message_text like '%incident%'"
• show trace 
/u01/app/oracle/diag/rdbms/prod/prod/trace/prod_aqpc_3629.trc
SYSDBA • Check errors via adrci 
tool
• Check incidents via adrci 
tool
• Check trace files via 
adrci tool
• show problem
• show incident
• show incident -mode detail -p "incident_id=18153"
• ips create package problem 1 correlate all
• ips generate package 1 in "/u01" • check problems
• check incidents
• check one incident 
details
• create IPS package
• output IPS
• COLD BACKUP – archive log mode enabled
• select file_name from dba_data_files;
• select name from v$controlfile;
• archive log list
• shutdown transactional
• cp -r <all_necessary files> <backup_location>
SYSDBA • COLD BACKUP
• Check data files
• Check control files
• Check archive log files
• shutdown transactional
• copy necessary files
• HOT BACKUP – archive log mode enabled
• select file_name from dba_data_files;
• select name from v$controlfile;
• select group#, sequence#, status from v$log; (e.g. 11)
• alter database begin backup;
• select file#, status from v$backup;
• cp -r <necessary_files > <hot_backup_location>
• alter database end backup;
• select file#, status from v$backup;
• alter system archive log current;
• select group#, sequence#, status from v$log; (e.g.12)
• alter database backup controlfile to 
'/u01/hot_backup/20180608/control.bk';
• cp 1_11_976120556.dbf /u01/hot_backup/20180608/ (e.g. this is 
the only between sequence numbers 11 and 12)
SYSDBA • HOT BACKUP
• Check dat files
• Check control files
• Check sequence 
number
• Alter to backup mode
• Check if all files are 
under backup mmode
• Copy files
• End backup mode
• Check if quit backup 
mode
• Archive current log files
• Check sequence 
number
• Backup control file
• Copy required archive 
redo file
• RECOVER A CONTROL FILE
• shutdown abort
• cp control01.ctl /u01/app/oracle/oradata/prod/prod/
• cp /u01/hot_backup/20180608/prod/control01.ctl 
/u01/app/oracle/oradata/prod/fra/prod
• mv control01.ctl control02.ctl (control files should be consistent)
• recover database using backup controlfile until cancel;
• /u01/app/oracle/oradata/prod/log/redo01.log
• /u01/app/oracle/oradata/prod/log/redo02.log 
• /u01/app/oracle/oradata/prod/log/redo03.log (Try all redo log 
files one by one until "Log applied, Media recovery complete" • alter database open resetlogs;
• select instance_name, status from v$instance;
SYSDBA • RECOVER A CONTROL 
FILE
• Shutdown database
• Copy two control files 
back to where they 
used to be
• Rename them properly
• Recover control files
• Add redo files one by 
one until recovery 
completed
• Reset all logs
• Verify after recovery
• RECOVER A SYSTEM FILE SYSDBA • RECOVER A SYSTEM FILE
• select name from v$datafile;
• shutdown abort
• cp /u01/hot_backup/20180608/prod/system01.dbf 
/u01/app/oracle/oradata/prod/prod/
• startup mount
• recover tablespace system;
• AUTO
• alter database open;
• Check system file 
location
• Shutdown database
• Copy system file from 
backup
• Startup mount
• Recovery system 
tablespace and set 
AUTO recovery
• Open database
• RECOVERY NON- SYSTEM FILES
• select file_name from dba_data_files where tablespace_name = 
'USERS';
• cp /u01/hot_backup/20180608/prod/users01.dbf 
/u01/app/oracle/oradata/prod/prod/
• alter tablespace users offline;
• recover tablespace users;
• AUTO
• alter tablespace users online;
SYSDBA • RECOVERY NON?SYSTEM FILES
• Check file location
• Copy file back
• Set tablespace offline
• Recovery non-system 
tablespace and set 
AUTO recovery
• Put tablespace online
• RMAN Commands 1
• SHOW ALL;
• CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 
DAYS;
• CONFIGURE CONTROLFILE AUTOBACKUP ON;
• BACKUP DATABASE;
• BACKUP AS COPY DATABASE;
• BACKUP CURRENT CONTROLFILE;
• BACKUP AS BACKUPSET DATAFILE
 'ORACLE_HOME/oradata/users01.dbf',
 'ORACLE_HOME/oradata/tbsl01.dbf'; • BACKUP ARCHIVELOG COMPLETION TIME BETWEEN 'SYSDATE-30'
AND 'SYSDATE'; • BACKUP TABLESPACE system, users, tbs1;
SYSDBA • Show all configuration 
parameters
• Retention policy
• Backup control files 
every time runs a bkp
• Backup entire databases
• Only backup data files
• Backup control files
• Backup individual data 
files
• Backup all archive logs 
back to 30 days
• Backup individual 
tablespaces
• RMAN Commands 2
• BACKUP SPFILE;
• LIST BACKUP OF DATABASE;
• BACKUP INCREMENTAL LEVEL 0 DATABASE;
• BACKUP INCREMENTAL LEVEL 1 DATABASE;
• BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
• BACKUP INCREMENTAL LEVEL 1 TABLESPACE SYSTEM DATAFILE
 'ora_home/oradata/trgt/tbs01.dbf';
BACKUP INCREMENTAL LEVEL = 1 CUMULATIVE TABLESPACE users;
SYSDBA • Backup SP file
• List all backups for the 
database
• Level 0 backup
• Level 1 backup 
• Level 1 cumulative 
backup
• Level 1 for tablespace 
and datafile
• Level 1 cumulative for 
individual tablespace
SQL> list
1 select sid, serial#, username, to_char(logon_time, 'yyyy-mm-dd 
hh24:mi:ss'), osuser, machine, sql_id, status from v$session
2 where username is not null
3* order by 4 desc
• SQL> /
SYSDBA • Check current sessions
Issue Solution Auth 
ID
Error starting at line : 3 in command -
INSERT INTO TST.EXAMPLE1 (ID) VALUES ('001')
Error report -
ORA-01950: no privileges on tablespace 'USERS'
ALTER USER TST QUOTA UNLIMITED ON 
USERS;
SYSDBA
ORA-00214: control file 
'/u01/app/oracle/oradata/prod/fra/prod/control02.ctl'
version 2315 inconsistent with file
'/u01/app/oracle/oradata/prod/prod/control01.ctl' version 
2307
Copy control file with the same version, 
or copy the same control file and 
rename it to control01.ctl and 
control02.ctl 
SYSDBA
 

------分隔線----------------------------
鋒哥公眾號


鋒哥微信號


日本色在线