| 失效链接处理 |
|
Oracle DBA commands real world experience PDF 下载 下载地址：
提取码：a8of
相关截图：  主要内容：
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 NONSYSTEM 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
|
| Java1234官方群25： | 
|
| Java1234官方群25： | 838462530 |
苏公网安备 32061202001004号
