Oracle管理

一、客戶端連接服務(wù)器
1.查看服務(wù)器監(jiān)聽程序配置文件（先不用改動）
[root@oracle/]#su - oracle
[oracle@oracle~]$ cd $ORACLE_HOME/network/admin
[oracle@oracleadmin]$ vim listener.ora

創(chuàng)新互聯(lián)公司堅信：善待客戶，將會成為終身客戶。我們能堅持多年，是因為我們一直可值得信賴。我們從不忽悠初訪客戶，我們用心做好本職工作，不忘初心，方得始終。10年網(wǎng)站建設(shè)經(jīng)驗創(chuàng)新互聯(lián)公司是成都老牌網(wǎng)站營銷服務(wù)商,為您提供成都做網(wǎng)站、網(wǎng)站制作、網(wǎng)站設(shè)計、H5響應(yīng)式網(wǎng)站、網(wǎng)站制作、品牌網(wǎng)站建設(shè)、微信小程序服務(wù),給眾多知名企業(yè)提供過好品質(zhì)的建站服務(wù)。

2.查看服務(wù)器的實例名
SQL> select instance_name from v$instance;

INSTANCE_NAME

Orcl

3.設(shè)置客戶端配置文件tnsnames.ora
ORCL = //ORCL即為連接標(biāo)識符
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rhel1.benet.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = orcl)
)
4.連接實例
請輸入用戶名: sys/123456@orcl as sysdba
5.停止監(jiān)聽器
[oracle@rhel1 admin]$lsnrctl stop listener
6.再連接

請輸入用戶名: sys/123456@orcl as sysdba
ERROR:
ORA-12541: TNS: 無監(jiān)聽程序

7、再創(chuàng)建一個監(jiān)聽器，同時做靜態(tài)注冊，并進(jìn)行連接
①.創(chuàng)建新的監(jiān)聽器
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = rhel1.benet.com)(PORT = 1522))
)
)
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /opt/oracle/product/11.2/db_1)
(GLOBAL_DBNAME = orclabc)
)//所有()前面至少加一個空格
)
②.重新加載配置文件（可以省略）
[oracle@rhel1 admin]$lsnrctl reload
③.啟動listener1監(jiān)聽器
The command completed successfully
[oracle@rhel1 admin]$lsnrctl start listener1
④.在客戶機上修改tnsnames.ora配置文件
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rhel1.benet.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = orcl)
)
)
ORCL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rhel1.benet.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = orcl)
)
)
⑤.在客戶機上連接連接listener1監(jiān)聽器
請輸入用戶名: sys/123456@orcl1 as sysdba

連接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

二?管理控制文件
1.獲得控制文件信息
SQL> select name from v$controlfile;

NAME

/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/oradata/orcl/control02.ctl

2.獲取控制文件中包含的內(nèi)容
SQL> select type,record_size,records_total,records_used from v$controlfile_record_section;
3.創(chuàng)建多路復(fù)用控制文件
①先創(chuàng)建存放控制文件的目錄，并更改屬主位oracle
[oracle@rhel1 ~]$su - root
[root@rhel1 ~]#mkdir -p /backup1/control
[root@rhel1 ~]#mkdir -p /backup2/control
[root@rhel1 ~]#chown -R oracle /backup1
[root@rhel1 ~]#chown -R oracle /backup2
②在數(shù)據(jù)庫仍然打開時，修改spfile中的contro_files參數(shù)
[root@rhel1 ~]#su - oracle
[oracle@rhel1 ~]$sqlplus sys/123456 as sysdba
SQL> alter system set
2 control_files=
3 '/opt/oracle/oradata/orcl/control01.ctl',
4 '/backup1/control/control02.ctl',
5 '/backup2/control/control03.ctl' scope=spfile;
③關(guān)閉數(shù)據(jù)庫
SQL> shutdown immediate;
④使用操作系統(tǒng)命令將文件復(fù)制到新的位置
SQL> quit;
[oracle@rhel1 ~]$ cp /opt/oracle/oradata/orcl/control01.ctl /backup1/control/control02.ctl
[oracle@rhel1 ~]$ cp /opt/oracle/oradata/orcl/control01.ctl /backup2/control/control03.ctl
⑤重新啟動數(shù)據(jù)庫
[oracle@rhel1 ~]$sqlplus sys/123456 as sysdba
SQL>startup
4.備份與恢復(fù)控制文件
方法一：直接用現(xiàn)有的完好的控制文件覆蓋損壞或丟失的控制文件
①模擬故障，停止數(shù)據(jù)庫，刪除控制文件
sql>shutdown immediat
sql>quit
$rm -f /badkup1/control/control02.ctl
②啟動數(shù)據(jù)庫
sql>startup 觀察現(xiàn)象
③恢復(fù)控制文件
sql>shutdown abort;
sql>quit
$ cp /opt/oracle/oradata/orcl/control01.ctl /backup1/control/control02.ctl
④再次啟動數(shù)據(jù)庫
sql>startup
方法二：利用專用的備份數(shù)據(jù)庫語句
①創(chuàng)建備份
SQL> quit
[oracle@rhel1 ~]$su - root
[root@rhel1 ~]#mkdir /opt/oracle/oradata/orcl/backup
[root@rhel1 ~]#chown -R oracle /opt/oracle/oradata/orcl/backup/
SQL> alter database backup controlfile to '/opt/oracle/oradata/orcl/backup/control.bkp';
②模擬故障
SQL> shutdown immediate
SQL> quit
[oracle@rhel1 ~]$ rm backup1/control/control02.ctl
[oracle@rhel1 ~]$sqlplus sys/123456 as sysdba
SQL> startup
ORACLE instance started.

Total System Global Area 780824576 bytes
Fixed Size 2217424 bytes
Variable Size 490736176 bytes
Database Buffers 281018368 bytes
Redo Buffers 6852608 bytes
ORA-00205: error in identifying control file, check alert log for more info
③恢復(fù)控制文件
? 使用os命令復(fù)制備份文件到原來的路徑，為了保持一致，將沒有丟失的控制文件也恢復(fù)一份
[oracle@rhel1 ~]$cp/opt/oracle/oradata/orcl/backup/control.bkp /opt/oracle/oradata/orcl/control01.ctl
[oracle@rhel1 orcl]$ cp /opt/oracle/oradata/orcl/backup/control.bkp /backup1/control/control02.ctl
[oracle@rhel1 orcl]$ cp /opt/oracle/oradata/orcl/backup/control.bkp /backup2/control/control03.ctl
[oracle@rhel1 orcl]$sqlplus sys/123456 as sysdba
SQL> alter database mount;
? 查看當(dāng)前活動的日志文件
SQL> startup mount;
SQL> select group#,sequence#,archived,status from v$log;
GROUP# SEQUENCE# ARCHIV STATUS

 1      4 NO     INACTIVE
 3      6 NO     CURRENT
 2      5 NO     INACTIVE

? 利用控制文件來恢復(fù)數(shù)據(jù)庫
SQL> select group#,status,type,member from v$logfile;
SQL> recover database using backup controlfile;
ORA-00279: change 1039911 generated at 05/30/2017 12:55:30 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/ORCL/archivelog/2017_05_30/o1_mf_16%u_.arc
ORA-00280: change 1039911 for thread 1 is in sequence #6 //根據(jù)提示在下面輸入當(dāng)前的日志文件
/opt/oracle/oradata/orcl/redo03.log
? 打開數(shù)據(jù)庫
SQL> alter database open resetlogs;
resetlogs選項的意思是要打開數(shù)據(jù)時，重置重做日志，即將重做日志的sequence置零

三.管理重做日志文件

使用v$log查看重做日志信息
[oracle@oracle~]$sqlplus / as sysdba
SQL> select group#,sequence#,bytes,members,archived,status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED
STATUS
1 4 209715200 1 NO
CURRENT
2 2 209715200 1 NO
INACTIVE
3 3 209715200 1 NO
INACTIVE

2.使用v$logfile查看重做日志組信息
SQL> select group#,status,type,member from v$logfile;

GROUP# STATUS        TYPE

MEMBER

 3           ONLINE

/u01/app/oracle/oradata/orcl/redo03.log

 2           ONLINE

/u01/app/oracle/oradata/orcl/redo02.log

 1           ONLINE

/u01/app/oracle/oradata/orcl/redo01.log

3.創(chuàng)建重做日志組
SQL> quit[oracle@oracle~]$su
br/>[oracle@oracle~]$su
[root@oracleoracle]#mkdir -p /backup/orcl/log
[root@oracleoracle]#chown -R oracle /backup
[oracle@oracle~]$sqlplus / as sysdba
SQL> alter database add logfile group 4
('/u01/app/oracle/oradata/orcl/redo04a.log',
'/backup/orcl/log/redo04b.log') size 10m;
數(shù)據(jù)庫已更改。

4.刪除重做日志組4（只是刪除了日志組，日志文件并沒有刪除）
①刪除之前先查看下，然后再刪除
SQL> select group#,sequence#,bytes,members,archived,status from v$log;

GROUP#  SEQUENCE#      BYTES    MEMBERS ARCHIVED

STATUS

 1  4  209715200      1 NO

CURRENT

 2  2  209715200      1 NO

INACTIVE

 3  3  209715200      1 NO

INACTIVE

GROUP#  SEQUENCE#      BYTES    MEMBERS ARCHIVED

STATUS

 4      0   10485760      2 YES

UNUSED

SQL> alter database drop logfile group 4;

數(shù)據(jù)庫已更改。
說明：
① 當(dāng)前的日志組不能刪除，要刪除當(dāng)前日志組需要先對當(dāng)前日志組進(jìn)行切換，使用命令為alter system switch logfile
② 活動的日志組不可以刪除
③ 沒有歸檔的日志組不可以刪除（前提是已經(jīng)運行在歸檔模式）

5.添加/刪除重做日志文件，分別向日志組1和2添加一個日志文件
①添加重做日志文件
SQL> alter database add logfile member
2 '/backup/orcl/log/redo01a.log' to group 1,
3 '/backup/orcl/log/redo02b.log' to group 2;

數(shù)據(jù)庫已更改。

②刪除日志文件
SQL> alter database drop logfile member
'/backup/orcl/log/redo02b.log';

數(shù)據(jù)庫已更改。
③查看日志文件
SQL> select group#,status,type,member from v$logfile;

GROUP# STATUS        TYPE

MEMBER

 3           ONLINE

/u01/app/oracle/oradata/orcl/redo03.log

 2           ONLINE

/u01/app/oracle/oradata/orcl/redo02.log

 1           ONLINE

/u01/app/oracle/oradata/orcl/redo01.log

GROUP# STATUS        TYPE

MEMBER

 1 INVALID       ONLINE

/backup/orcl/log/redo01a.log

說明：
? 不能刪除當(dāng)前組的成員，若要刪除則先執(zhí)行強制性切換重做日志的命令
? 活動的日志成員不可以刪除
? 沒有歸檔的日志文件不能刪除（前提是已運行在歸檔模式下）
? 當(dāng)日志組只有一個成員

6.日志切換和檢查點時間
①強制切換日志文件
SQL> alter system swith logfile;
②強制產(chǎn)生檢查點事件
SQL> alter system checkpoint;

四?管理歸檔日志文件

配置數(shù)據(jù)庫歸檔日志
① 查看數(shù)據(jù)庫歸檔模式，確定當(dāng)前不處于歸檔模式
SQL> archive log list;
數(shù)據(jù)庫日志模式非存檔模式
自動存檔禁用
存檔終點 /u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch
最早的聯(lián)機日志序列 2
當(dāng)前日志序列 4

② 關(guān)閉數(shù)據(jù)庫并啟動數(shù)據(jù)庫到mount狀態(tài)
SQL> shutdown immediate
數(shù)據(jù)庫已經(jīng)關(guān)閉。
已經(jīng)卸載數(shù)據(jù)庫。
ORACLE 例程已經(jīng)關(guān)閉。
SQL> startup mount;
ORACLE 例程已經(jīng)啟動。

Total System Global Area 1593835520 bytes
Fixed Size 8793256 bytes
Variable Size 1023411032 bytes
Database Buffers 553648128 bytes
Redo Buffers 7983104 bytes
數(shù)據(jù)庫裝載完畢。

③ 將數(shù)據(jù)庫設(shè)置為歸檔模式并查看歸檔模式是否改變
SQL> alter database archivelog;

數(shù)據(jù)庫已更改。

SQL> archive log list;
數(shù)據(jù)庫日志模式存檔模式
自動存檔啟用
存檔終點 /u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch
最早的聯(lián)機日志序列 2
下一個存檔日志序列 4
當(dāng)前日志序列 4
SQL> alter database open ;

數(shù)據(jù)庫已更改。

④ 查看歸檔日志文件的路徑
查看當(dāng)前有效的歸檔日志文件存儲目錄
SQL> selectdest_id,dest_name,status,destination from v$archive_dest ;
DEST_ID

DEST_NAME

STATUS

DESTINATION

LOG_ARCHIVE_DEST_1
VALID
/u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch

DEST_ID

DEST_NAME

STATUS

DESTINATION

LOG_ARCHIVE_DEST_2
INACTIVE//省略部分信息
SQL> select dest_id,name,archived from v$archived_log;
DEST_ID NAME ARCHIV
1/opt/oracle/flash_recovery_area/ORCL/archivelog/2016_11_19/o1_mf_1_8d2yh7kfx.arc YES
⑤ 改歸檔日志文件存放路徑
[root@oracleserver~]#mkdir /aa
[root@oracleserver~]#chown -R oracle /aa
[root@oracleserver~]#su – oracle
[oracle@oracleserver~]$sqlplus / as sysdba
SQL> alter system set log_archive_dest='/aa' scope=spfile;

實驗五：數(shù)據(jù)字典管理
1.創(chuàng)建一個班級表空間，空間大小為100m，數(shù)據(jù)文件放在/data目錄下
[root@rhel1 ~]#mkdir /data
[root@rhel1 ~]#chown -R oracle /data
[root@rhel1 ~]#su - oracle
[oracle@rhel1 ~]$sqlplus / as sysdba
SQL> create tablespace t374
datafile '/data/t374.dbf' size 100m；
2.創(chuàng)建本人用戶，默認(rèn)表空間為班級表空間
SQL> create user name
identified by 123456
default tablespace t374;

授予其連接數(shù)據(jù)庫和創(chuàng)建以及創(chuàng)建視圖表的權(quán)限
SQL> grant connect,resource,create view to name;
切換稱本人賬戶并創(chuàng)建表student,表里包含name和passwd字段
SQL>SQL> conn name;
SQL> create table student
(name varchar(10),
passwd varchar(20));
5.創(chuàng)建視圖student_view
SQL> create view student_view
2 as
3 select * from student;

6.查詢當(dāng)前用戶有哪些表
SQL> desc user_tables;
SQL>select * from user_tables;
SQL> select table_name fromuser_tables;
TABLE_NAME

STUDENT
7.查看當(dāng)前用戶有哪些視圖
SQL> desc user_views;
SQL> select view_name fromuser_views;
VIEW_NAME

STUDENT_VIEW
8.查詢當(dāng)前用戶有哪些數(shù)據(jù)庫對象
SQL> select object_name from user_objects;
OBJECT_NAME

STUDENT_VIEW
STUDENT
9.查詢當(dāng)前用戶的信息，包括用戶id，用戶狀態(tài)，默認(rèn)表空間
①查看表結(jié)構(gòu)
SQL> desc user_users;
Name Null? Type

USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME
②格式化輸出
SQL> col user_id for 999
SQL> col account_status for a10
SQL> col default_tablespace for a30
③查詢
SQL> select user_id,account_status,default_tablespace from user_users;

USER_ID ACCOUNT_ST DEFAULT_TABLESPACE

 91 OPEN       T374

10.查詢當(dāng)前用戶能訪問的所有對象
SQL> select owner,object_name,object_type from all_objects;
11.查詢所有的數(shù)據(jù)字典，并查詢所有的以user開頭的所有表
SQL> desc dictionary;
Name Null? Type

TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)
SQL> select table_name from dictionary where table_name like 'USER%';
12.查看scott用戶的表和表空間
SQL> col owner for a10
SQL> col table_namefor a20
SQL> col tablespace_name for a30
SQL> select owner,table_name,tablespace_name from dba_tables where owner='SCOTT';
OWNER TABLE_NAME TABLESPACE_NAME

SCOTT DEPT USERS
SCOTT EMP USERS
SCOTT BONUS USERS
SCOTT SALGRADE USERS

實驗六：動態(tài)數(shù)據(jù)字典
1.查看和日志文件相關(guān)的信息（注意大寫）
SQL> select * from v$fixed_table where name like 'v$LOG%';
2.查看日志組狀態(tài)信息
SQL> select group#,members,archived,status from v$log;
GROUP# MEMBERS ARCHIV STATUS

 1      1 NO     INACTIVE
 2      1 NO     INACTIVE
 3      1 NO     CURRENT

3.查看日志文件信息
SQL> col type for a10
SQL> col group# for 99
SQL> select * fromv$logfile;
GROUP# STATUS TYPE MEMBER IS_REC

 3            ONLINE     /opt/oracle/oradata/orcl/redo03.log      NO
 2            ONLINE     /opt/oracle/oradata/orcl/redo02.log      NO
 1            ONLINE     /opt/oracle/oradata/orcl/redo01.log      NO

4.查看當(dāng)前正在使用的重做日志文件的信息
SQL> select l.group#,l.archived,l.status,lf.type,lf.member from v$log l, v$logfilelf where l.group#=lf.group#;

GROUP# ARCHIV STATUS TYPE

MEMBER

 3 NO     INACTIVE                 ONLINE

/opt/oracle/oradata/orcl/redo03.log

 2 NO     INACTIVE                 ONLINE

/opt/oracle/oradata/orcl/redo02.log

 1 NO     CURRENT                  ONLINE

/opt/oracle/oradata/orcl/redo01.log
5.查看實例信息
SQL> col instance_name for a20;
SQL> col host_name for a10
SQL> select instance_name,host_name,version,startup_time,logins from v$instance;

INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME

LOGINS

orcl rhel1.bene 11.2.0.1.0 30-MAY-17
t.com
ALLOWED
6.查看數(shù)據(jù)庫信息
SQL> col name for a10;
SQL> select name,created,log_mode from v$database;

NAME CREATED LOG_MODE

ORCL 30-MAY-17 NOARCHIVELOG

文章題目：Oracle管理
網(wǎng)站鏈接：http://bm7419.com/article38/igcgsp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供響應(yīng)式網(wǎng)站、Google、網(wǎng)站建設(shè)、企業(yè)建站、小程序開發(fā)、微信公眾號

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

Oracle管理

INSTANCE_NAME

NAME

STATUS

MEMBER

STATUS

STATUS

MEMBER

MEMBER

④ 查看歸檔日志文件的路徑查看當(dāng)前有效的歸檔日志文件存儲目錄SQL> selectdest_id,dest_name,status,destination from v$archive_dest ;DEST_ID

DEST_NAME

STATUS

DESTINATION

DEST_ID

DEST_NAME

STATUS

DESTINATION

6.查詢當(dāng)前用戶有哪些表SQL> desc user_tables;SQL>select * from user_tables;SQL> select table_name fromuser_tables;TABLE_NAME

STUDENT7.查看當(dāng)前用戶有哪些視圖SQL> desc user_views;SQL> select view_name fromuser_views;VIEW_NAME

STUDENT_VIEW8.查詢當(dāng)前用戶有哪些數(shù)據(jù)庫對象SQL> select object_name from user_objects;OBJECT_NAME

MEMBER

LOGINS

④ 查看歸檔日志文件的路徑
查看當(dāng)前有效的歸檔日志文件存儲目錄
SQL> selectdest_id,dest_name,status,destination from v$archive_dest ;
DEST_ID

6.查詢當(dāng)前用戶有哪些表
SQL> desc user_tables;
SQL>select * from user_tables;
SQL> select table_name fromuser_tables;
TABLE_NAME

STUDENT
7.查看當(dāng)前用戶有哪些視圖
SQL> desc user_views;
SQL> select view_name fromuser_views;
VIEW_NAME

STUDENT_VIEW
8.查詢當(dāng)前用戶有哪些數(shù)據(jù)庫對象
SQL> select object_name from user_objects;
OBJECT_NAME