SQL> set pagesize 500
SQL> set linesize 500
查詢數(shù)據(jù)庫版本
SELECT * FROM V$VERSION;
select BUILD_VERSION from v$instance ; #查看小版本
查看當(dāng)前有多少用戶
SQL> SELECT USERNAME FROM DBA_USERS;
SELECT * FROM DBA_TABLESPACES;
set pagesize 500
set linesize 500
SELECT
? ? TABLESPACE_NAME AS 表空間名,
? ? ROUND(SUM(TOTAL_SIZE) / 1024.0 / 1024.0, 2) AS "總大小(MB)",
? ? ROUND(SUM(USED_SIZE)/ 1024.0 / 1024.0, 2) AS "已使用大小(MB)",
? ? CAST(ROUND(SUM(USED_SIZE) * 1.0 / SUM(TOTAL_SIZE) * 100,2) AS VARCHAR) || '%' AS 使用率
FROM
(
? ? SELECT
? ? ? ? UPPER(T1.TABLESPACE_NAME) AS TABLESPACE_NAME,
? ? ? ? T1.BYTES AS TOTAL_SIZE,
? ? ? ? T1.BYTES - T2.BYTES AS USED_SIZE
? ? FROM
? ? ? ? DBA_DATA_FILES T1,
? ? ? ? DBA_FREE_SPACE T2
? ? WHERE
? ? ? ? T1.TABLESPACE_NAME = T2.TABLESPACE_NAME
? ? ? ? AND T1.FILE_ID = T2.FILE_ID
)
GROUP BY
? ? TABLESPACE_NAME;
--------------------查看表空間同數(shù)據(jù)文件對(duì)應(yīng)關(guān)系
SELECT
? ? TS.NAME AS 表空間名,
? ? DF.PATH AS 數(shù)據(jù)文件
FROM
? ? V$TABLESPACE AS TS,
? ? V$DATAFILE AS DF
WHERE
? ? TS.ID = DF.GROUP_ID
ORDER BY
? ? 1;
查看所有用戶、創(chuàng)建時(shí)間、默認(rèn)表空間、等基本信息
SELECT
? ? USERNAME AS 用戶名,
? ? DEFAULT_TABLESPACE AS 默認(rèn)數(shù)據(jù)表空間,
? ? DEFAULT_INDEX_TABLESPACE AS 默認(rèn)索引表空間,
? ? TEMPORARY_TABLESPACE AS 臨時(shí)表空間,
? ? DECODE(ACCOUNT_STATUS,'OPEN','正常','LOCKED','鎖定','未知') AS 用戶狀態(tài),
? ? CREATED AS 創(chuàng)建時(shí)間
FROM
? ? DBA_USERS;
--------------------查看用戶數(shù)據(jù)庫限制
SELECT
? ? T1.USERNAME AS 用戶名,
? ? DECODE(T2.AUTHENT_TYPE,1,'數(shù)據(jù)庫密碼認(rèn)證',2,'操作系統(tǒng)認(rèn)證',3,'遠(yuǎn)程認(rèn)證','未知認(rèn)證方式') AS 用戶認(rèn)證方式,
? ? T2.SESS_PER_USER AS 用戶最大會(huì)話數(shù),
? ? T2.CONN_IDLE_TIME AS "用戶空閑期（分鐘1-1440）",
? ? T2.FAILED_NUM AS 用戶登錄失敗次數(shù)限制,
? ? T2.LIFE_TIME AS "口令有效期（天0-365）",
? ? T2.REUSE_TIME AS "口令等待期（天0-365）",
? ? T2.REUSE_MAX AS 口令變更次數(shù),
? ? T2.LOCK_TIME AS "用戶鎖定時(shí)間（分1-1440）",
? ? T2.GRACE_TIME AS "口令寬限期1-30",
? ? T2.PASSWORD AS 密碼策略,
? ? T2.RN_FLAG AS 只讀,
? ? T2.ALLOW_ADDR AS 允許訪問的IP,
? ? T2.NOT_ALLOW_ADDR AS 不允許訪問的IP,
? ? T2.ALLOW_DT AS 允許訪問的時(shí)間,
? ? T2.NOT_ALLOW_DT AS 不允許訪問的時(shí)間,
? ? T2.LAST_LOGIN_DTID AS 上次登錄時(shí)間,
? ? T2.LAST_LOGIN_IP AS 上次登錄IP,
? ? T2.FAILED_ATTEMPS AS 自上一次登錄成功以來失敗次數(shù)
FROM
? ? DBA_USERS T1,
? ? SYSUSERS T2
WHERE
? ? T1.USER_ID = T2.ID;
--------------------查看用戶對(duì)象統(tǒng)計(jì)信息
SELECT?
? ? S2.NAME AS 模式名,
? ? S1.TYPE$ AS 主類型,
? ? S1.SUBTYPE$ AS 子類型,
? ? COUNT(*) AS 對(duì)象數(shù)量
FROM
? ? SYSOBJECTS S1,
? ? SYSOBJECTS S2
WHERE
? ? S1.SCHID = S2.ID
? ? AND S2.TYPE$ = 'SCH'
GROUP BY
? ? S2.NAME,
? ? S1.TYPE$,
? ? S1.SUBTYPE$
ORDER BY
? ? S2.NAME,
? ? S1.TYPE$,
? ? S1.SUBTYPE$;
--------------------查看用戶占用空間大小
SELECT
? ? USERNAME AS 用戶名,
? ? USER_USED_SPACE(USERNAME) * PAGE / 1024.0 / 1024.0 AS "用戶占用空間(MB)"
FROM
? ? DBA_USERS
ORDER BY
? ? 2 DESC;
SELECT
? ? T2.NAME AS 模式名,
? ? T1.NAME AS 表名,
? ? TABLE_ROWCOUNT(T2.NAME,T1.NAME) AS 行數(shù)
FROM
? ? SYSOBJECTS T1,
? ? SYSOBJECTS T2
WHERE
? ? T1.SCHID = T2.ID
? ? AND T1.SUBTYPE$ = 'UTAB'
? ? AND T2."TYPE$" = 'SCH'
ORDER BY 3 DESC;
--------------------統(tǒng)計(jì)所有用戶表行數(shù)以及篩查某行數(shù)級(jí)別以上表行數(shù) 輸入?yún)?shù)1：百萬，千萬，億...等等，也可以共存，復(fù)制一行
SELECT
? ? 模式名,
? ? COUNT(表名) AS 表數(shù)量,
? ? COUNT(CASE WHEN 行數(shù) > ? THEN 行數(shù) ELSE NULL END) AS 百萬表數(shù)量
FROM
(
? ? SELECT
? ? ? ? T2.NAME AS 模式名,
? ? ? ? T1.NAME AS 表名,
? ? ? ? TABLE_ROWCOUNT(T2.NAME,T1.NAME) AS 行數(shù)
? ? FROM
? ? ? ? SYSOBJECTS T1,
? ? ? ? SYSOBJECTS T2
? ? WHERE
? ? ? ? T1.SCHID = T2.ID
? ? ? ? AND T1.SUBTYPE$ = 'UTAB'
? ? ? ? AND T2."TYPE$" = 'SCH'
? ? --ORDER BY 3 DESC
)
GROUP BY 模式名
--------------------獲取所有用戶表定義
SELECT
? ? T2.NAME AS 模式名,
? ? T1.NAME AS 表名,
? ? T1.CRTDATE AS 創(chuàng)建時(shí)間,
? ? DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('TABLE',T1.NAME,T2.NAME)) AS 表定義
FROM
? ? SYSOBJECTS T1,
? ? SYSOBJECTS T2
WHERE
? ? T1."SUBTYPE$" = 'UTAB'
? ? ? AND T1.SCHID = T2.ID
? ? ? AND T2."TYPE$" = 'SCH';
--------------------統(tǒng)計(jì)用戶表列信息
SELECT
? ? S1.NAME AS 表名,
? ? S2.NAME AS 列名,
? ? S2."TYPE$" AS 字段類型,
? ? S2."LENGTH$" AS 字段長(zhǎng)度
FROM
? ? SYSOBJECTS S1,
? ? SYSCOLUMNS S2
WHERE
? ? S1."SUBTYPE$" = 'UTAB'
? ? AND S1.ID = S2.ID;
--------------------查看表占用空間大小
SELECT
? ? S2.NAME AS 模式名,
? ? S1.NAME AS 表名,
? ? TABLE_USED_SPACE(S2.NAME,S1.NAME) * PAGE /1024.0/1024.0 AS "表占用空間(MB)"
FROM
? ? SYSOBJECTS S1,
? ? SYSOBJECTS S2
WHERE
? ? S1.SCHID = S2.ID
? ? AND S1."SUBTYPE$" = 'UTAB'
? ? AND S2."TYPE$" = 'SCH'
ORDER BY
? ? 3 DESC;
查詢會(huì)話數(shù)
SELECT * FROM V$SESSIONS;
SELECT? COUNT(*)? FROM V$SESSIONS;
查看數(shù)據(jù)庫的創(chuàng)建日期和歸檔方式
select create_time,arch_mode from v$database;
在某個(gè)用戶下找所有的索引
select user_indexes.table_name,
user_indexes.index_name,
uniqueness,
column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type,
user_indexes.table_name,
user_indexes.index_name,
column_position;
查看字符集
返回建庫時(shí)，指定的字符集。返回值：0 表示 GB18030，1 表示 UTF-8。
SELECT SF_GET_UNICODE_FLAG ();
查看數(shù)據(jù)庫對(duì)象
select owner, object_type, status, count(*) count#
from all_objects
?group by owner, object_type, status;
查看日志文件
select *?from v$rlogfile;
查詢事務(wù)數(shù)
SELECT COUNT(*) FROM V$TRX;
查詢等待事件
SELECT * FROM V$TRXWAIT;
查詢內(nèi)存池
SELECT? *FROM V$MEM_POOL;
查看實(shí)例的運(yùn)行狀態(tài)
SELECT * FROM V$INSTANCE;
守護(hù)集群?jiǎn)?dòng)/停止命令
[dmdba@~]$ /home/dmdba/dmdbms/bin/DmWatcherServiceWatcher start#A/B機(jī)器
[dmdba@~]$ /home/dmdba/dmdbms/bin/DmWatcherServiceWatcher stop#A/B機(jī)器
[dmdba@~]$/home/dmdba/dmdbms/bin/DmMonitorServiceMonitor start #監(jiān)控機(jī)器
##實(shí)例啟動(dòng)/停止命令
[dmdba@~]$ /home/dmdba/dmdbms/bin/DmServiceGRP1_RT_01 start#A/B機(jī)器
[dmdba@~]$ /home/dmdba/dmdbms/bin/DmServiceGRP1_RT_01 stop#A/B機(jī)器
查看進(jìn)程是否存在
ps -ef | grep dmwatcher
ps -ef | grep dmserver
添加disql環(huán)境變量
vi ~/.bash_profile
export PATH=$PATH:$DM_HOME/bin:$DM_HOME/tool
source?~/.bash_profile
DM重做主從
A機(jī)器:
SQL> ALTER DATABASE MOUNT;
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SQL> SP_SET_OGUID(45331);
SQL> ALTER DATABASE PRIMARY;
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
B機(jī)器:
SQL> ALTER DATABASE MOUNT;
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SQL> SP_SET_OGUID(45331);
SQL> ALTER DATABASE STANDBY;
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
A/B機(jī)器：
SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SQL> alter database normal;#單機(jī)模式，主從不用執(zhí)行
SQL> alter database open force;
A/B機(jī)器監(jiān)控啟動(dòng)
/home/dmdba/dmdbms/bin/dmwatcher /dmdata/data/DMTEST/dmwatcher.ini &
查看服務(wù)自啟動(dòng)
systemctl list-unit-files --type=service | grep enable |grep Dm
systemctl disable DmXXX.service #關(guān)閉服務(wù)自啟動(dòng)
查看DB 當(dāng)前正在適用的Redo log：
SQL>select cur_file from V$RLOG;
查看當(dāng)前Redo log 信息：
SQL>select file_id,path,rlog_size/1024/1024 as "Size" from V$RLOGFILE;
刪除歸檔日志：
可以通過系統(tǒng)函數(shù)SF_ARCHIVELOG_DELETE_BEFORE_TIME刪除指定時(shí)間之前的歸檔文件，該函數(shù)返回刪除的歸檔日志文件個(gè)數(shù)。如下命令為刪除系統(tǒng)當(dāng)前時(shí)間10天前的歸檔日志文件
select sf_archivelog_delete_before_time(sysdate -10);
手動(dòng)切換歸檔命令
alter system switch logfile;
歸檔日志的連續(xù)性檢查
SELECT SF_REMOTE_ARCHIVE_CHECK('GRP1_RT_02');
查看主備庫的狀態(tài)：
SELECT CASE WHEN "STATUS$" = '1' THEN 'Startup' WHEN "STATUS$" = '2' THEN 'After Redo' WHEN "STATUS$" = '3' THEN 'MOUNT' WHEN "STATUS$" = '4' THEN 'OPEN' WHEN "STATUS$" = '5' THEN 'SUSPEND' END "庫狀態(tài)" , CASE? ? ? ? WHEN "ROLE$" = '0' THEN 'Normal'? ? ? ? WHEN "ROLE$" = '1' THEN 'Parmary'? ? ? ? WHEN "ROLE$" = '2' THEN 'Standby'? ? END "模式" FROM? ? v$database;
查看主備庫的SCN是否一致：
select file_LSN, cur_LSN from v$rlog;
?查看主備庫的permanent值是否一致：
select permanent_magic;
開啟主備集群的邏輯日志參數(shù)
修改開啟在主、備RLOG_APPEND_LOGIC邏輯追加日志參數(shù)，0為關(guān)閉，1為開啟，注意參數(shù)PARA_TYPE為SYS類型可動(dòng)態(tài)修改；其它參數(shù)類型詳見管理員手冊(cè)。如下為參數(shù)查詢及修改:
SQL> SELECT PARA_NAME,PARA_VALUE,DEFAULT_VALUE,SESS_VALUE,FILE_VALUE,PARA_TYPE FROM V$DM_INI WHERE PARA_NAME LIKE 'RLOG_APPEND_LOGIC';
SQL> call sp_set_para_value(1,'RLOG_APPEND_LOGIC',1);
SQL> SELECT PARA_NAME,PARA_VALUE,DEFAULT_VALUE,SESS_VALUE,FILE_VALUE,PARA_TYPE FROM V$DM_INI WHERE PARA_NAME LIKE 'RLOG_APPEND_LOGIC';
查看所有的系統(tǒng)表
SELECT * FROM DBA_TABLES;
查看DDL
select owner, table_name from dba_tables where owner = 'SYSDBA' and table_name like 'DMHS%' and status ='VALID';
查看歸檔的模式
select name,arch_mode from v$database;
#查看歸檔文件
select * from sys.v$arch_file;
#查看歸檔配置
select * from v$dm_arch_ini;
主備切換
[dmdba@localhost bin]$ ./dmmonitor ./dmmonitor.ini
login
SYSDBA/SYSDBA
switchover GRP1_RT_02
查看可切換的列表
choose switchover?GRP1_RT_01
分裂后，需要?jiǎng)h掉分裂庫生成的守護(hù)進(jìn)程控制文件，守護(hù)進(jìn)程在檢測(cè)到本地庫分裂時(shí)，自動(dòng)創(chuàng)建 dmwatcher.ctl 文件.
rm -rf?dmwatcher.ctl?
然后主備備份后數(shù)據(jù)傳給備份，依次啟動(dòng)數(shù)據(jù)庫和守護(hù)進(jìn)程。
備份與恢復(fù)
數(shù)據(jù)量小的可以使用dexp和dimp
數(shù)據(jù)庫級(jí)
./dexp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=dbbak.dmp LOG=dbbak.log FULL=Y DIRECTORY=/backup/databak
用戶級(jí)
./dexp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=db_users.dmp LOG=db_users.log
OWNER=TEST1,TEST2,...,TESTN DIRECTORY=/backup/FULL_BAK
模式級(jí)
./dexp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=db_users.dmp LOG=db_users.log
SCHEMAS=TEST1,TEST2,...,TESTN DIRECTORY=/backup/FULL_BAK
表級(jí)
./dexp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=db_users.dmp LOG=db_users.log
TABLES=模式.表名,...,模式.表名 DIRECTORY=/backup/FULL_BAK
數(shù)據(jù)庫級(jí)
./dimp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=/backup/FULL_BAK/db_bak.dmp LOG=db_recover.log FULL=Y DIRECTORY=/backup/IMP_BAKDIR
模式級(jí)
./dimp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=/backup/FULL_BAK/db_bak.dmp LOG=db_recover.log SCHEMAS=TEST1,...,TESTN DIRECTORY=/backup/IMP_BAKDIR
不同schema需要加個(gè)參數(shù)
./dimp USERID=SYSDBA/SYSDBA@localhost:5236 FILE=/backup/FULL_BAK/db_bak.dmp LOG=db_recover.log REMAP_SCHEMA=MODE01:MODE02 DIRECTORY=/backup/IMP_BAKDIR
用戶級(jí)
./dimp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=/backup/FULL_BAK/db_bak.dmp LOG=db_recover.log OWNER=TEST1,...,TESTN DIRECTORY=/backup/IMP_BAKDIR
不同用戶需要加參數(shù)，與shcema一致。
./dimp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=/backup/FULL_BAK/db_bak.dmp LOG=db_recover.log FROMUSER=USER01 TOUSER=USER02? DIRECTORY=/backup/IMP_BAKDIR
表級(jí)
./dimp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=/backup/FULL_BAK/db_bak.dmp LOG=db_recover.log TABLES=模式.表名,...,模式.表名 DIRECTORY=/backup/IMP_BAKDIR
創(chuàng)建某個(gè)schema模式下只讀用戶
select 'grant select on 模式名.'||table_name||' to 只讀賬號(hào);' from user_tables;
查看schema對(duì)應(yīng)的用戶
select a.id scheid, a.name schename, b.id userid, b.name username
from SYS.SYSOBJECTS a, SYS.SYSOBJECTS b
where a."TYPE$" = 'SCH' and a.pid = b.id;
