一、oracle建庫與刪庫 （1）oracle11g建庫(一般習慣配置gdbname與sid名一樣，sys密碼與system密碼一樣，以方便記憶)

[oracledb@ ~]$ dbca -silent -createDatabase -templateName /u01/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbc -gdbname GDBNAME -sid SIDNAME -characterSet AL32UTF8 -NATIONALCHARACTERSET UTF8 -sysPassword SYSPASSWORD -systemPassword SYSTEMPASSWORD -TOTALMEMORY 2048

刪庫方法一：

[oracledb@ ~]$ dbca -silent -deleteDatabase -sourceDB  SIDNAME -sysDBAUserName sys -sysDBAPassword SYSPASSWORD

刪除庫方法二：

#步驟1：配置回應檔案：

[oracledb@ ~]$ cat /u01/oracle/response/dbca.rsp OPERATION_TYPE = "deleteDatabase" SOURCEDB = "SIDNAME" SYSDBAUSERNAME = "sys"SYSDBA PASSWORD = "SYSPASSWORD"

#步驟2：執行回應檔案刪庫：

[oracledb@ ~]$ dbca -silent -responseFile /u01/oracle/response/dbca.rsp

（2）oracle12c 建庫

[oracledb@ ~]$ dbca -silent -createDatabase -templateName /u01/oracle/product/orahome/assistants/dbca/templates/General_Purpose.dbc  -gdbname GDBNAME -sid SIDNAME -characterSet AL32UTF8 -NATIONALCHARACTERSET UTF8 -sysPassword SYSPASSWORD -systemPassword SYSTEMPASSWORD -TOTALMEMORY 2048

#刪庫同上，注意執行刪庫命令之後，需要手動刪除遺留的目錄

（3）刪除一般會自動刪除以下路徑或檔案中的庫資訊

A：/u01/oracle/admin/SIDNAME B：cat /etc/oratab C：/u01/oracle/oradata/SIDNAME

以下路徑需要手動清理

D：/u01/oracle/cfgtoollogs/dbca/SIDNAME E：/u01/oracle/diag/rdbms/SIDNAME F：/u01/oracle/product/11.2.0/dbhome_1/dbs/hc_SIDNAME.dat

二、建立庫對應的賬號密碼 1、切換SID

[oracledb@ ~]$ export ORACLE_SID=SIDNAME

2、切換字符集

#檢視oracle資料庫的字符集

SQL> select userenv('language') from dual;

#檢視oracle資料庫的編碼

SQL> select * from nls_database_parameters where parameter ='NLS_CHARACTERSET'; [oracledb@ ~]$ set NLS_LANG=AMERICAN_AMERICA.AL32UTF8     #windows_os [oracledb@ ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8  #linux_os

3、建立庫對應的使用者資訊

SQL> create temporary tablespace SIDNAME_temp tempfile '/u01/oracle/oradata/SIDNAME/SIDNAME_temp.dbf 'size 64m autoextend on next 64m maxsize unlimited extent management local;

SQL> create tablespace SIDNAME_data logging datafile '/u01/oracle/oradata/SIDNAME/SIDNAME_data.dbf' size 64m autoextend on next 64m maxsize 2048m extent management local;

SQL> create user USERNAME identified by USERPASSWORD default tablespace SIDNAME_data temporary tablespace SIDNAME_temp;

SQL> grant connect,resource to USERNAME;

SQL> grant create view to USERNAME;

SQL> grant unlimited tablespace to USERNAME;

SQL> grant create public synonym to USERNAME;

SQL> grant drop public synonym to USERNAME;

SQL> create or replace directory dir_dump  as '/u01/oracle/backup';

SQL> grant read,write on directory dir_dump to USERNAME;

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

—根據例項環境修改processes與sessions引數值(需要重啟oracle資料庫)

SQL> alter system set processes=1000 scope=spfile;  SQL> alter system set sessions=1105 scope=spfile;

知識點：

oracle11g：sessions值的定義應該大於或者定於1.1processes+5，如果小於1.1processes+5，則oracle在啟動時自動將該引數設定為1.1processes+5。這主要是考慮到後臺程式發起的session和大約10%的遞迴session。

oracle12c：1.1processes+22

##查詢當前oracle的併發連線數：

SQL> select count(*) from v$session where status='ACTIVE';

#檢視不同使用者的連線數：

SQL> select username,count(username) from v$session where username is not null group by username;

#檢視所有使用者：

select * from all_users;

#當前的連線數

select count(*) from v$process;

#資料庫允許的最大連線數

select value from v$parameter where name = 'processes';

三、資料庫的還原與備份命令

檢視expdp匯出備份儲存路徑：

sql> select * from dba_directories;

1、針對整個庫備份與還原操作

（1）、資料庫的備份(注意有時SIDNAME與SCHEMASNAME不一致，要注意使用時區分，一般配置一樣的名稱，方便記憶的同時，也方便運維。parallel引數根據伺服器記憶體等配置情況新增合理的數值。)

#備份：

[oracledb@ ~]$  expdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME dumpfile=SIDNAME`date +%Y%m%d`.dmp directory=dir_dump parallel=2

12

（2）、還原

情況1、原始庫與目標庫例項名不同（注意有些環境的資料表空間名不是SIDNAME_data，使用時注意核實）

#格式：

[oracledb@ ~]$ impdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME DUMPFILE=XXXX.dmp  DIRECTORY=dir_dump remap_schema=源SCHEMASNAME:目標SCHEMASNAME remap_tablespace=源_data:目標_data

情況2：源庫與目標庫例項名相同

#格式：

[oracledb@ ~]$ impdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME DUMPFILE=XXXX.dmp  DIRECTORY=dir_dump EXCLUDE=STATISTICS

知識擴充套件：使用EXCLUDE=STATISTICS還原時排除統計，可以使用如下命令完成統計

#命令如下：

SQL> exec dbms_stats.gather_schema_stats(ownname=>'SIDNAME',estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');

2、針對單表備份與還原操作

（1）、備份單表

格式：

[oracledb@ ~]$ expdp USERNAME/USERPASSWORD@SIDNAME dumpfile=tablenameXXXX.dmp DIRECTORY=dir_dump tables=TABLENAME

（2）、還原單表

#格式：

[oracledb@ ~]$ impdp USERNAME/USERPASSWORD@SIDNAME dumpfile=tablenameXXXX.dmp DIRECTORY=dir_dump TABLES=TABLENAME TABLE_EXISTS_ACTION=REPLACE

擴充套件：table_exists_action引數說明

使用imp進行資料匯入時，若表已經存在，要先drop掉表，再進行匯入。
而使用impdp完成資料庫匯入時，若表已經存在，有四種的處理方式：
引數(1) skip：預設操作
引數(2) replace：先drop表，然後建立表，最後插入資料
引數(3) append：在原來資料的基礎上增加資料
引數(4) truncate：先truncate，然後再插入資料
（3）備份多張表

#格式：

[oracledb@ ~]$ expdp USERNAME/USERPASSWORD@SIDNAME dumpfile=tablenameXXXX.dmp DIRECTORY=dir_dump TABLES=源TABLENAME1,源TABLENAME2,.....

（4）還原多張表

#格式：

[oracledb@ ~]$ impdp USERNAME/USERPASSWORD@SIDNAME dumpfile=tablenameXXXX.dmp DIRECTORY=dir_dump remap_table=源TABLENAME1:目標TABLENAME11 TABLE_EXISTS_ACTION=REPLACE [oracledb@ ~]$ impdp USERNAME/USERPASSWORD@SIDNAME dumpfile=tablenameXXXX.dmp DIRECTORY=dir_dump remap_table=源TABLENAME2:目標TABLENAME22 TABLE_EXISTS_ACTION=REPLACE

3、擴充套件知識

1）擴充套件1：

情況1、高版本往低版本匯出還原時，如12往11還原時，在12c執行匯出時，新增低版本的版本號version=11.1.0.2.0

#格式：

[oracledb@ ~]$ expdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME dumpfile=XXX.dmp DIRECTORY=dir_dump version=11.1.0.2.0

情況2、低版本往高版本還原時，高版本一般相容低版本，目前個人運維工作中11往12還原沒遇到什麼問題。

2）擴充套件2：

（1）、按指定大小備份，如每份5G大小（parallel是多執行緒處理，執行緒數要小於生成檔案個數，執行緒要小於 cpu 執行緒數）

#備份格式

[oracledb@ ~]$ expdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME DIRECTORY=dir_dump dumpfile=XXX_%U.dmp logfile=expdpXXX.log filesize=5G parallel=16

（2）、多個備份檔案還原：

#還原格式

[oracledb@ ~]$ impdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME DIRECTORY=dir_dump dumpfile=XXX_%U.dmp logfile=impdpXXX.log parallel=16

3）擴充套件3匯出過濾不匯出某張表：

#格式

[oracledb@ ~]$ expdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME DIRECTORY=dir_dump dumpfile=XXX.dmp  exclude=TABLE:\"IN \'TABLENAME\'\"

4）擴充套件4不同庫還原時不改變資料結構使用truncate引數：

#格式

[oracledb@ ~]$ impdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME DIRECTORY=dir_dump dumpfile=XXX.dmp remap_schema=源SCHEMASNAME:目標SCHEMASNAME remap_tablespace=源_data:目標_data TABLE_EXISTS_ACTION=truncate

5）擴充套件5匯出備份時保留表，清除表資料(query引數)：

#格式

[oracledb@ ~]$ expdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME DIRECTORY=dir_dump dumpfile=XXX.dmp logfile=expdpXXX.log  query=TABLENAME1:'" where 1=2"',TABLENAME2:'" where 1=2"',........

6）擴充套件6匯出備份時保留表，清除表資料，同時過濾某兩張表

#格式

[oracledb@ ~]$ expdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME DIRECTORY=dir_dump dumpfile=XXX.dmp logfile=expdpXXX.log  query=TABLENAME1:'" where 1=2"',TABLENAME2:'" where 1=2"' exclude=TABLE:\"IN \'TABLENAME1\'\'TABLENAME2\'\"

7）擴充套件7僅統計資料庫各表資料，但不匯出，引數estimate_only=y

#格式

[oracledb@ ~]$ expdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME DIRECTORY=dir_dump estimate_only=y

8）擴充套件8匯入單表到某臨時表：

#格式（涉及統計與索引時新增引數EXCLUDE=STATISTICS EXCLUDE=INDEX）
—sql實現從這張表複製到另一張臨時表

SQL> CREATE TABLE 目標TABLENAME  AS (SELECT * FROM 源TABLENAME);

—清空表中的資料

SQL> delete from 目標TABLENAME;

[oracledb@ ~]$ impdp USERNAME/USERPASSWORD@SIDNAME DIRECTORY=dir_dump DUMPFILE=tablenameXXX.dmp remap_table=源TABLENAME:目標TABLENAME TABLE_EXISTS_ACTION=REPLACE EXCLUDE=STATISTICS EXCLUDE=INDEX  [oracledb@ ~]$ expdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME ESTIMATE_ONLY=y NOLOGFILE=y FULL=y

9）擴充套件9個別sql 內容很長，使用PLSQL Developer工具執行時會一直卡住無響應時除使用PLSQL Developer工具的在命令視窗中執行外，也可以使用終端執行

#格式

[oracle@localhost ~]$ export ORACLE_SID=SIDNAME [oracle@localhost ~]$ sqlplus  / as sysdba sql> conn USERNAME/USERPASSWORD@SIDNAME sql> @/u01/oracle/backup/XXX.sql

10）擴充套件10如何正確終止expdp與impdp任務，操作步驟如下：

步驟1、檢視檢視dba_datapump_jobs

select job_name,state from dba_datapump_jobs;

步驟2、正確停止expdp匯出任務使用stop_job

expdp USERNAME/USERPASSWORD@SIDNAME attach=SYS_EXPORT_SCHEMA_02

步驟3、停止任務

Export> stop_job=immediate  Are you sure you wish to stop this job ([yes]/no): yes

步驟4、檢視系統中的備份job狀態

select owner_name,job_name ,state from dba_datapump_jobs;

擴充套件：下列命令在互動模式下有效：

HELP： 總結互動命令。 KILL_JOB： 分離和刪除作業。 PARALLEL： 更改當前作業的活動 worker 的數目。 PARALLEL=.2 START_JOB： 啟動/恢復當前作業。 START_JOB=SKIP_CURRENT 在開始作業之前將跳過作業停止時執行的任意操作。 STATUS ：在預設值（0）將顯示可用時的新狀態的情況下，要監視的頻率（以秒計）作業狀態。 STATUS[=interval] STOP_JOB： 順序關閉執行的作業並退出客戶機。 STOP_JOB=IMMEDIATE 將立即關閉資料泵作業。

11）擴充套件11

#檢視DB中的NLS_CHARACTERSET的值

SQL> select * from v$nls_parameters  where parameter='NLS_CHARACTERSET';  SQL> select * from nls_database_parameters t where t.parameter='NLS_CHARACTERSET';

12）擴充套件12關於exp、imp、expdp、impdp

(1)、exp和imp是客戶端工具程式，它們既可以在客戶端使用，也可以在服務端使用。
(2)、expdp和impdp是服務端的工具程式，他們只能在oracle服務端使用，不能在客戶端使用。
(3)、imp只適用於exp匯出的檔案，不適用於expdp匯出檔案；impdp只適用於expdp匯出的檔案，而不適用於exp匯出檔案。
(4)、對於10g以上的伺服器，使用exp通常不能匯出0行資料的空表，而此時必須使用expdp匯出。
13）擴充套件13oracle使用者密碼有@符號時，expdp與sqlplus連線方式及使用rar壓縮備份的檔案

@echo off rem ---- dmp backup directory, same as dump dir set backup_dir=e:\app\oracle\backup rem ---- today, day for dmp file remaining set day=%date:~0,4%%date:~5,2%%date:~8,2% set remain_day=7 rem --- delete files before 7 days forfiles /p "%backup_dir%" /d -%remain_day%  /c "cmd /c del /f @path" rem --- export oracle data to dmp file expdp 使用者名稱/\"xxx@yyy\"@orcl directory=dir_dump dumpfile=使用者名稱_%day%.dmp logfile=使用者名稱_%day%.log schemas=使用者名稱 parallel=4 compression=ALL rem --- sqlplus conn sqlplus 使用者名稱/"""xxx@yyy"""@orcl rem ---- if compress the dumpfile and delete source dumpfile, unmark rem set rar="C:\Program Files (x86)\WinRAR\WinRAR.exe" %rar% a -df %backup_dir%\使用者名稱_%day%.rar %backup_dir%\使用者名稱_%day%.dmp %backup_dir%\使用者名稱_%day%.log

四、清理（還原時出錯，清使用者表空間） 1、刪除資料表空間：

#執行語句：

[oracledb@ ~]$ sqlplus / as sysdbaSQL> drop tablespace mepro_data including contents and datafiles cascade constraint;

2、刪除臨時表空間：

#執行語句：

SQL> drop tablespace mepro_temp including contents and datafiles cascade constraints;

3、刪除使用者：

#執行語句：

SQL> drop user srmhdld cascade;

4、報使用者正在連線，無法刪除的解決方法

---方法1：重啟並迅速執行drop user語句（個人推薦）

SQL> shutdown immediate; SQL> startup

---方法2：刪除正在連線的session（連線的session連線著很多時，清理耗時，沒有方法1快速）
#查詢使用者會話

SQL> select username,serial#,sid,program,machine,status from v$session where username='USERNAME' AND STATUS='ACTIVE';; SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;

---刪除相關使用者會話

SQL> alter system kill session 'serial#, sid';

本文原創地址：

Oracle 常用運維命令整理

相關文章