100 Essential Dameng DM8 DBA Commands for Daily Operations & Troubleshooting

Overview

This article provides a curated reference of 100 commands that Dameng DM8 DBAs should master for daily operations, organized into 11 functional categories. The commands assume a Linux environment with DM_HOME=/opt/dmdbms, database directory /dmdata/DAMENG, instance name DMSERVER, listener port 5236, and backup directory /dmbackup. Key tools include DIsql for online management/backup, DMRMAN for offline backup/restore/recovery, dminit for initialization, dmserver for instance control, and dexp / dimp for logical import/export.

1. Database Connection & Instance Management (Commands 1-10)

Environment setup: export DM_HOME, PATH, LD_LIBRARY_PATH into dmdba user's .bash_profile. Local connection: disql SYSDBA/'StrongPassword'@127.0.0.1:5236. Remote connection: disql APP/'AppPassword'@192.168.1.100:5236 — if fails, check service, listener, firewall, credentials, network. dminit HELP lists initialization parameters. Initialize new database with

dminit PATH=/dmdata DB_NAME=DAMENG INSTANCE_NAME=DMSERVER PORT_NUM=5236 SYSDBA_PWD='StrongPassword' SYSAUDITOR_PWD='AuditPassword'

generating data files, control files, logs, and dm.ini. Start foreground: dmserver /dmdata/DAMENG/dm.ini (for verification/debug only). Mount mode: dmserver /dmdata/DAMENG/dm.ini mount for mode changes, archive config, partial recovery. Service script: /etc/rc.d/init.d/DmServiceDMSERVER start|stop. Register service:

cd $DM_HOME/script/root && ./dm_service_installer.sh -t dmserver -dm_ini /dmdata/DAMENG/dm.ini -p DMSERVER

.

2. Database, Instance & Parameter Checks (Commands 11-20)

Version: SELECT * FROM V$VERSION — critical before upgrades/migrations. Database info: SELECT * FROM V$DATABASE (name, creation time, archive mode, status). Instance info: SELECT * FROM V$INSTANCE (name, mode, startup status/time). Current time: SELECT SYSDATE FROM DUAL for time sync checks. Current user: SELECT USER FROM DUAL before high-risk ops. Query parameter:

SELECT PARA_NAME, PARA_VALUE, SESS_VALUE, FILE_VALUE, PARA_TYPE FROM V$DM_INI WHERE PARA_NAME='MAX_SESSIONS'

— PARA_VALUE (system), SESS_VALUE (session), FILE_VALUE (config file). Function alternative: SELECT SF_GET_PARA_VALUE(2,'MAX_SESSIONS') with scope=2 for memory value. All parameters:

SELECT PARA_NAME, PARA_VALUE, SESS_VALUE, FILE_VALUE, PARA_TYPE, DESCRIPTION FROM V$DM_INI ORDER BY PARA_NAME

— record current/file/default/type before changes. Dynamic modify: SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1) with scope=1 updates memory and dm.ini for dynamic params. Static modify: SP_SET_PARA_VALUE(2,'MAX_SESSIONS',2000) with scope=2 updates config file only, requires restart.

3. User, Role & Permission Management (Commands 21-30)

Create business user:

CREATE USER APP IDENTIFIED BY "App@123456" DEFAULT TABLESPACE TS_APP

— avoid granting DBA to app users. Change password: ALTER USER APP IDENTIFIED BY "NewApp@123456" respecting complexity policy. Unlock user: SP_UNLOCK_USER('APP') after failed login lockouts. Drop user cascade: DROP USER APP CASCADE — removes schema objects, confirm first. Create role: CREATE ROLE R_APP_READ for centralized permission management. Grant role: GRANT RESOURCE TO APP — use predefined/custom roles, avoid over-grant. Grant table select: GRANT SELECT ON APP.T_ORDER TO REPORT_USER for read-only access. Revoke: REVOKE SELECT ON APP.T_ORDER FROM REPORT_USER on offboarding. Schema-level grant: GRANT SELECT ON SCHEMA APP TO REPORT_USER for many objects. Audit all privileges: complex UNION query across DBA_ROLE_PRIVS, DBA_SYS_PRIVS, DBA_TAB_PRIVS filtered by grantee — used for permission audits and baseline checks.

4. Tablespace & Datafile Management (Commands 31-40)

Create tablespace:

CREATE TABLESPACE TS_APP DATAFILE '/dmdata/DAMENG/TS_APP01.DBF' SIZE 1024 AUTOEXTEND ON NEXT 128 MAXSIZE 20480

(initial 1024MB, extend 128MB, max 20480MB). Add datafile:

ALTER TABLESPACE TS_APP ADD DATAFILE '/dmdata/DAMENG/TS_APP02.DBF' SIZE 1024 AUTOEXTEND ON NEXT 128 MAXSIZE 20480

— multiple files ease capacity management. Resize:

ALTER TABLESPACE TS_APP RESIZE DATAFILE '/dmdata/DAMENG/TS_APP01.DBF' TO 4096

(MB default, ensure no used pages at tail). Offline: ALTER TABLESPACE TS_APP OFFLINE before moving files. Online: ALTER TABLESPACE TS_APP ONLINE then verify object access. Rename datafile:

ALTER TABLESPACE TS_APP RENAME DATAFILE '/dmdata/DAMENG/TS_APP01.DBF' TO '/dmdata2/DAMENG/TS_APP01.DBF'

— offline user tablespace first, OS-level move, SYSTEM/ROLL cannot be moved this way. Tablespace-file mapping: join V$TABLESPACE and V$DATAFILE on TS.ID=DF.GROUP_ID for migration/capacity/fault location. Usage rate: query DBA_FREE_SPACE and DBA_DATA_FILES computing total/free/used MB and percentage — core daily patrol query. Top 20 segments:

SELECT TOP 20 OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, ROUND(BYTES/1024/1024,2) AS SIZE_MB FROM DBA_SEGMENTS ORDER BY BYTES DESC

to locate growth drivers. Drop empty tablespace: DROP TABLESPACE TS_APP — migrate/delete objects first, never drop blindly in production.

5. Table, Index & Statistics Management (Commands 41-50)

Create table:

CREATE TABLE APP.T_ORDER (ID BIGINT IDENTITY(1,1) PRIMARY KEY, ORDER_NO VARCHAR(64) NOT NULL, USER_ID BIGINT, AMOUNT DECIMAL(18,2), CREATE_TIME TIMESTAMP DEFAULT SYSDATE) TABLESPACE TS_APP

— IDENTITY(1,1) starts at 1 increments by 1. Add column: ALTER TABLE APP.T_ORDER ADD STATUS VARCHAR(20) DEFAULT 'NEW' — test lock time/log volume on large tables first. Truncate: TRUNCATE TABLE APP.T_ORDER — high-risk, not rollback-able like DELETE. Create index:

CREATE INDEX APP.IDX_ORDER_USER_TIME ON APP.T_ORDER(USER_ID, CREATE_TIME)

— column order by filter selectivity and sort needs. Rebuild index: ALTER INDEX APP.IDX_ORDER_USER_TIME REBUILD — consumes CPU/I/O/temp/log, not a mechanical scheduled task. Gather table stats: SP_TAB_STAT_INIT('APP','T_ORDER') after bulk load/cleanup/distribution shift. Gather index stats: SP_INDEX_STAT_INIT('APP','IDX_ORDER_USER_TIME') — bad stats mislead optimizer cost estimates. Explain plan:

EXPLAIN SELECT * FROM APP.T_ORDER WHERE USER_ID=1001 ORDER BY CREATE_TIME DESC

— shows estimated plan without execution. Real execution stats:

SET AUTOTRACE TRACEONLY; SELECT * FROM APP.T_ORDER WHERE USER_ID=1001

— focus on logical/physical reads, disk sorts, rows returned, elapsed time, I/O wait. Invalid objects:

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS<>'VALID' ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME

after migrations/upgrades/dependency changes.

6. Session, Slow SQL, Lock & Blocking Troubleshooting (Commands 51-60)

Session stats by client/user: group V$SESSIONS by STATE, CLNT_IP, CLNT_TYPE, CURR_SCH, USER_NAME to spot connection spikes. Active SQL: join V$SESSIONS with SF_GET_SESSION_SQL(SESS_ID) filtering STATE IN ('ACTIVE','WAIT') ordered by execution seconds — SF_GET_SESSION_SQL returns full SQL text. Long-running SQL: SELECT * FROM V$LONG_EXEC_SQLS ORDER BY EXEC_TIME DESC — requires ENABLE_MONITOR enabled. Historical top SQL:

SELECT TOP 20 START_TIME, TIME_USED/1000 AS TIME_USED_SECONDS, TOP_SQL_TEXT FROM V$SQL_HISTORY ORDER BY TIME_USED DESC

— combine with execution count, rows, call frequency. High-memory SQL:

SELECT * FROM V$SYSTEM_LARGE_MEM_SQLS ORDER BY MEM_USED_BY_K DESC

— check large sorts, hash joins, large result sets, missing filters. Blocked transactions: SELECT * FROM V$LOCK WHERE BLOCKED=1 — BLOCKED=1 means lock request blocked. Blocker/waiter details: complex CTE joining V$LOCK, SYSOBJECTS, V$SESSIONS twice to show wait/block session IDs, users, SQL, client IP, wait seconds, object name. Deadlock history: SELECT * FROM V$DEADLOCK_HISTORY — auto-resolved deadlocks still need SQL/object/access-order analysis. Kill session: SP_CLOSE_SESSION(123456) — replace with actual SESS_ID, assess rollback volume/impact first. System wait events:

SELECT TOP 20 * FROM V$SYSTEM_EVENT ORDER BY TOTAL_WAITS DESC

— combine wait count, total/avg wait time, business time window.

7. Physical Backup, Restore & Recovery (Commands 61-71)

Online full backup: BACKUP DATABASE BACKUPSET '/dmbackup/full/db_full_20260723' — requires OPEN + archive mode, store on separate disk, copy off-site. Tablespace backup:

BACKUP TABLESPACE TS_APP BACKUPSET '/dmbackup/tablespace/ts_app_20260723'

— supplements but doesn't replace full strategy. Single-table backup:

BACKUP TABLE APP.T_ORDER BACKUPSET '/dmbackup/table/t_order_20260723'

— pre-change protection. Verify backupset:

SELECT SF_BAKSET_CHECK('DISK','/dmbackup/full/db_full_20260723')

— returns 1 if valid, verification must be routine. Start DMRMAN: dmrman — core offline tool. Offline full backup:

RMAN> BACKUP DATABASE '/dmdata/DAMENG/dm.ini' FULL BACKUPSET '/dmbackup/offline/db_full_20260723'

— stop instance first. Show backupset: RMAN> SHOW BACKUPSET '/dmbackup/offline/db_full_20260723' — type, time, DB info, backup chain. Verify offline:

RMAN> CHECK BACKUPSET '/dmbackup/offline/db_full_20260723'

— include in daily patrol. Restore:

RMAN> RESTORE DATABASE '/dmdata_restore/DAMENG/dm.ini' FROM BACKUPSET '/dmbackup/full/db_full_20260723'

— target instance must be down. Recover:

RMAN> RECOVER DATABASE '/dmdata_restore/DAMENG/dm.ini' FROM BACKUPSET '/dmbackup/full/db_full_20260723'

— RESTORE recovers datafiles, RECOVER replays logs to consistency. Update DB_MAGIC:

RMAN> RECOVER DATABASE '/dmdata_restore/DAMENG/dm.ini' UPDATE DB_MAGIC

— required after restore/recover to make target startable.

8. Logical Import/Export & Archive Management (Commands 72-80)

Full logical export:

dexp SYSDBA/'StrongPassword'@127.0.0.1:5236 FILE=dm_full.dmp LOG=dm_full.log DIRECTORY=/dmbackup/exp FULL=Y

— directory must exist with RW permissions. Schema export: dexp ... SCHEMAS=APP for single-schema migration. Table export: dexp ... TABLES=APP.T_ORDER,APP.T_ORDER_DETAIL — includes data, indexes, constraints. Full import:

dimp SYSDBA/'StrongPassword'@127.0.0.1:5236 FILE=/dmbackup/exp/dm_full.dmp LOG=dm_full_import.log FULL=Y

— assess user/tablespace/schema/object conflicts first. Schema import: dimp ... SCHEMAS=APP — confirm target user/default tablespace/privileges. Table import: dimp ... TABLES=APP.T_ORDER,APP.T_ORDER_DETAIL — set TABLE_EXISTS_ACTION=SKIP|APPEND|TRUNCATE|REPLACE for existing tables. Backup all archive logs:

RMAN> BACKUP ARCHIVE LOG ALL DATABASE '/dmdata/DAMENG/dm.ini' BACKUPSET '/dmbackup/archive/arch_all_20260723'

— archive must be configured. Restore archive logs:

RMAN> RESTORE ARCHIVE LOG FROM BACKUPSET '/dmbackup/archive/arch_all_20260723' TO ARCHIVEDIR '/dmdata_restore/archive' OVERWRITE 2

— archive dir must be continuous/complete for recovery. Repair local archive: RMAN> REPAIR ARCHIVELOG DATABASE '/dmdata/DAMENG/dm.ini' — run with DB down, fixes archive after abnormal shutdown.

9. Checkpoint, Archive, Log & Memory Checks (Commands 81-90)

Manual checkpoint: CHECKPOINT(100) — use for specific scenarios like backup archive-gap complaints, not a substitute for root-cause analysis. Archive status: SELECT * FROM V$ARCH_STATUS — check ARCH_STATUS=VALID; real-time/instant/remote status on primary. Archive config: SELECT * FROM V$DM_ARCH_INI — type, destination, file size, space limit. Archive files: SELECT * FROM V$ARCH_FILE — file list with valid LSN ranges. Enable SQL log: SP_SET_PARA_VALUE(1,'SVR_LOG',1) — then configure sqllog.ini in DB directory; avoid long-term unfiltered logging in production (perf/disk overhead). Reload SQL log config: SP_REFRESH_SVR_LOG_CONFIG() after sqllog.ini changes. Buffer pool: SELECT * FROM V$BUFFERPOOL — pages, page size, free/evicted pages, data page access. Memory pool: SELECT * FROM V$MEM_POOL — shared pool allocation vs actual usage. Dictionary cache: SELECT * FROM V$DB_CACHE — watch during frequent DDL/dynamic SQL. System stats:

SELECT * FROM V$SYSSTAT WHERE CLASSID IN (11,5) ORDER BY CLASSID DESC

— always diff two time points; cumulative since startup misleads.

10. Jobs, Security, Backup History & Standby Patrol (Commands 91-100)

DB jobs: join SYSJOB.SYSJOBS and SYSJOB.USER_JOBS for ID, name, enabled, owner, create/modify time, last/next run, command — verify backup/stats/cleanup jobs enabled with correct next run. Job schedules: SELECT * FROM SYSJOB.SYSJOBSCHEDULES — check frequency/date/time windows. Recent job history: correlated subquery on SYSJOB.SYSSTEPHISTORIES2 limiting to latest 10 executions per job ordered by start time — backup file existence ≠ job success, check history + logs. Password policy:

SELECT PARA_NAME, PARA_VALUE, SESS_VALUE, FILE_VALUE FROM V$DM_INI WHERE PARA_NAME IN ('PWD_POLICY','PWD_MIN_LEN')

— enforce strong passwords per security requirements. All non-system user privileges: same UNION query as command 30 but filtered to exclude SYS, SYSDBA, SYSSSO, SYSAUDITOR — audit DBA/high-priv/cross-schema/stale accounts regularly. Default backup path: SELECT PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='BAK_PATH' — also OS df -h and du -sh for disk space. Backup history:

SELECT PATH, START_TIME, END_TIME, READ_SIZE, WRITE_SIZE, AVG_READ, AVG_WRITE, BACKUP_NAME, TYPE, STATUS, ERROR FROM V$BACKUP_HISTORY ORDER BY START_TIME DESC

— monitors speed, volume, status, errors. Data Guard status:

SELECT GROUP_NAME, INST_NAME, DW_TYPE, DW_MODE, AUTO_RESTART, DW_STATUS, DW_SUB_STATUS, LAST_MSG_TIME, SWITCH_COUNT FROM V$DMWATCHER

— focus on process status, last communication, switchover count. MAL links:

SELECT SRC_SITE, DEST_SITE, CTL_LINK_STATUS, DATA_LINK_STATUS FROM V$MAL_LINK_STATUS

— both control and data links should be CONNECTED. Primary-to-standby archive send: SELECT * FROM V$ARCH_SEND_INFO (run on primary) — log shipping stats per standby.

Closing Remarks

The author emphasizes that memorizing commands is less important than knowing when to use them and how to narrow fault scope from results. Examples: high tablespace usage ≠ immediate add datafile — distinguish normal growth, abnormal batch writes, index bloat, misplaced backup files. High CPU ≠ blame SQL — correlate active sessions, plans, wait events, locks, system resources. Standby NORMAL ≠ reliable DR — regularly verify archive continuity, backup validity, recovery procedures. The 100 commands form a baseline checklist; DBAs should adapt paths, names, directories to their environment and evolve into standardized patrol scripts and runbooks.