Shulou(Shulou.com)06/02 Report--

This article shows you how to organize Oracle operation and maintenance commands, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

I. commands for oracle to build and delete databases

(1) build oracle11g database (it is customary to configure gdbname is the same as sid name, and sys password is the same as system password to facilitate memory)

[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

Method 1 for deleting the library:

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

Delete library method 2:

# step 1: configure response file: [oracledb@ ~] $cat / u01/oracle/response/dbca.rspOPERATION_TYPE = "deleteDatabase" SOURCEDB = "SIDNAME" SYSDBAUSERNAME = "sys" SYSDBAPASSWORD = "SYSPASSWORD" # step 2: perform response file deletion: [oracledb@ ~] $dbca-silent-responseFile / u01/oracle/response/dbca.rsp

(2) Building oracle12c database

[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 204 delete command is the same as above. Note that after running the delete command, you need to delete the remaining directories manually.

(3) deletion usually automatically deletes the library information in the following path or file

A:/u01/oracle/admin/SIDNAMEB:cat / etc/oratabC:/u01/oracle/oradata/SIDNAME

The following paths need to be manually cleaned

D:/u01/oracle/cfgtoollogs/dbca/SIDNAMEE:/u01/oracle/diag/rdbms/SIDNAMEF:/u01/oracle/product/11.2.0/dbhome_1/dbs/hc_SIDNAME.dat

Second, create the account password corresponding to the database

1. Switch SID

[oracledb@ ~] $export ORACLE_SID=SIDNAME

2. Switch character set

# View oracle database character set SQL > select userenv ('language') from dual;# view oracle database encoding 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. Create the user information corresponding to the library

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 maxsize oradata size SIDNAME maxsize 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'/ u01According to oracle, grant read,write on directory dir_dump to USERNAME;SQL > ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED

-modify processes and sessions parameter values according to the instance environment (oracle database needs to be restarted)

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

Knowledge points:

The definition of the oracle11g:sessions value should be greater than or set to 1.1processes+5, and if it is less than 1.1processes+5, oracle automatically sets this parameter to 1.1processes+5 at startup. This is mainly due to the session initiated by the background process and about 10% of the recursive session. Oracle12c:1.1processes+22## query the number of concurrent connections in the current oracle: SQL > select count (*) from v$session where status='ACTIVE';# to view the number of connections of different users: SQL > select username,count (username) from v$session where username is not null group by username;# to view all users: select * from all_users;# current number of connections select count (*) from vprocessors # maximum number of connections allowed in the database select value from v$parameter where name = 'processes'

III. Database restore and backup commands

View the expdp export backup storage path:

Sql > select * from dba_directories

1. Backup and restore operations for the entire library

(1), database backup (note that sometimes SIDNAME and SCHEMASNAME are not consistent, pay attention to the distinction when using, general configuration of the same name, easy to remember, but also convenient for operation and maintenance. The parallel parameter adds a reasonable value according to the configuration of server memory and so on.)

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

(2), restore

Case 1. The instance name of the original database is different from that of the target database (note that the data tablespace name of some environments is not SIDNAME_data, please verify when using it)

# format: [oracledb@ ~] $impdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME DUMPFILE=XXXX.dmp DIRECTORY=dir_dump remap_schema= source SCHEMASNAME: destination SCHEMASNAME remap_tablespace= source _ data: destination _ data

Case 2: the source database has the same instance name as the destination database

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

Knowledge expansion: excluding statistics when using EXCLUDE=STATISTICS restore, you can use the following command to complete statistics

# the command is as follows: SQL > exec dbms_stats.gather_schema_stats (ownname= > 'SIDNAME',estimate_percent= > 10 ALL' degree = > 8 scene cascade = > true,granularity= >' ALL')

2. Backup and restore operations for single table

(1) backup list table

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

(2) restore single table

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

Extensions: table_exists_action parameter description

When using imp for data import, if the table already exists, drop the table first, and then import it. When using impdp to complete database import, if the table already exists, there are four processing methods: parameter (1) skip: default operation parameter (2) replace: first drop the table, then create the table, and finally insert the data parameter (3) append: add the data parameter to the original data (4) truncate: first truncate, then insert the data

(3) back up multiple tables

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

(4) restore multiple tables

# format: [oracledb@ ~] $impdp USERNAME/USERPASSWORD@SIDNAME dumpfile=tablenameXXXX.dmp DIRECTORY=dir_dump remap_table= source TABLENAME1: destination TABLENAME11 TABLE_EXISTS_ACTION= replacement [oracledb @ ~] $impdp USERNAME/USERPASSWORD@SIDNAME dumpfile=tablenameXXXX.dmp DIRECTORY=dir_dump remap_table= source TABLENAME2: destination TABLENAME22 TABLE_EXISTS_ACTION=REPLACE

3. Expand knowledge

1) expand 1:

Case 1. When restoring an export from a higher version to a lower version, such as from 12 to 11, add the version number of the lower version version=11.1.0.2.0 when the export is performed at 12c

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

Case 2. When restoring a lower version to a higher version, the higher version is generally compatible with the lower version. At present, there is no problem with restoring 11 to 12 in personal operation and maintenance work.

2) extension 2:

(1) backup according to the specified size, such as 5G per copy (parallel is multithreaded, the number of threads is less than the number of files generated, and the number of threads is less than the number of cpu threads)

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

(2) restore multiple backup files:

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

3) extended 3 Export filtering does not export a table:

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

4) extension 4 when restoring different libraries, do not change the data structure and use truncate parameters:

# format [oracledb@ ~] $impdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME DIRECTORY=dir_dump dumpfile=XXX.dmp remap_schema= source SCHEMASNAME: destination SCHEMASNAME remap_tablespace= source _ data: destination _ data TABLE_EXISTS_ACTION=truncate

5) expand 5. Keep the table when exporting the backup and clear the table data (query parameter):

# format [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) extension 6 preserve tables when exporting backups, clear table data, and filter two tables at the same time

# format: [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) extension 7 only counts the data of each table in the database, but does not export, parameter estimate_only=y

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

8) expand 8 to import a single table into a temporary table:

# format (adding parameter EXCLUDE=STATISTICS EXCLUDE=INDEX when involving statistics and indexing)-sql implementation to copy from this table to another temporary table SQL > CREATE TABLE destination TABLENAME AS (SELECT * FROM source TABLENAME);-clear data SQL > delete from destination TABLENAME; [oracledb@ ~] $impdp USERNAME/USERPASSWORD@SIDNAME DIRECTORY=dir_dump DUMPFILE=tablenameXXX.dmp remap_table= source TABLENAME: destination TABLENAME TABLE_EXISTS_ACTION=REPLACE EXCLUDE=STATISTICS EXCLUDE=INDEX [oracledb@ ~] $expdp USERNAME/USERPASSWORD@SIDNAME schemas=SCHEMASNAME ESTIMATE_ONLY=y NOLOGFILE=y FULL=y

9) extension 9 the content of individual sql scripts is very long, and it will always be stuck when executing with PLSQL Developer tools. When there is no response, in addition to using PLSQL Developer tools to execute in the command window, you can also use shell terminals to execute.

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

10) extension 10 how to correctly terminate expdp and impdp tasks, the steps are as follows:

Step 1: view dba_datapump_jobsselect job_name,state from dba_datapump_jobs; step 2, stop the expdp export task correctly using stop_jobexpdp USERNAME/USERPASSWORD@SIDNAME attach=SYS_EXPORT_SCHEMA_02 step 3, stop the task Export > stop_job=immediate Are you sure you wish to stop this job ([yes] / no): yes step 4, view the backup job status in the system select owner_name,job_name, state from dba_datapump_jobs

Extension: the following commands are valid in interactive mode:

HELP: summarize interactive commands. KILL_JOB: detach and delete jobs. PARALLEL: changes the number of active worker for the current job. PARALLEL=.2START_JOB: starts / resumes the current job. START_JOB=SKIP_CURRENT skips any actions performed when the job stops before starting the job. STATUS: the frequency (in seconds) of the job status to be monitored when the default value (0) displays the new status when available. STATUS [= interval] STOP_JOB: sequentially closes the executed job and exits the client. STOP_JOB=IMMEDIATE will immediately close the data pump job.

11) extension 11

# check the value of NLS_CHARACTERSET in DB: SQL > select * from v$nls_parameters where parameter='NLS_CHARACTERSET'; SQL > select * from nls_database_parameters t where t.parameterized values NLSrequests CHARACTERSET`

12) extension 12 about exp, imp, expdp, impdp

(1) exp and imp are client-side tool programs that can be used either on the client side or on the server side. (2), expdp and impdp are server-side tools, they can only be used on the oracle server, not on the client. (3) imp only applies to exp exported files, not expdp exported files; impdp only applies to expdp exported files, not exp exported files. (4) for servers with more than 10g, it is usually not possible to export empty tables with 0 rows of data using exp, but expdp must be used to export at this time.

13) when the 13oracle user password has the @ symbol, expdp connects with sqlplus and uses rar to compress the backed up files.

@ echo offrem-dmp backup directory, same as dump dirset backup_dir=e:\ app\ oracle\ backuprem-today Day for dmp file remainingset day=%date:~0,4%%date:~5,2%%date:~8,2%set remain_day=7rem-delete files before 7 daysforfiles / p "% backup_dir%" / d -% remain_day% / c "cmd / c del / f @ path" rem-export oracle data to dmp fileexpdp username /\ "xxx@yyy\" @ orcl directory=dir_dump dumpfile= username _% day%.dmp logfile= username _% day%.log schemas= username parallel=4 compression=ALLrem-sqlplus Connsqlplus user name / "xxx@yyy" @ orclrem-if compress the dumpfile and delete source dumpfile Unmark remset rar= "C:\ Program Files (x86)\ WinRAR\ WinRAR.exe"% rar% a-df% backup_dir%\ day%.rar% backup_dir%\ user name _% day%.dmp% backup_dir%\ user name _% day%.log

4. Clean up (error during restore, clear user tablespace)

1. Delete the data tablespace:

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

2. Delete temporary tablespace:

# execute statement: SQL > drop tablespace mepro_temp including contents and datafiles cascade constraints

3. Delete the user:

# execute statement: SQL > drop user srmhdld cascade

4. Report that the user is connecting and cannot delete the solution.

-method 1: restart and execute the drop user statement quickly (recommended) SQL > shutdown immediate;SQL > startup--- method 2: delete the connected session (the connected session is connected for a long time, cleaning is time-consuming, no method 1 is fast) # query user session 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 -delete the relevant user session SQL > alter system kill session 'serial#, sid';. The above is how to organize Oracle OPS commands. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.