oracle11g常用命令.
第一章:日志管理1. forcing log switchessql> alter system switch logfile;2. forcing checkpointssql> alter system checkpoint;3. adding online redo log groupssql> alter database add logfile [group 4]sql> ('/disk3/log4a.rdo','/disk4/log4b.rdo' size 1m;4. adding online redo log memberssql> alter database add logfile membersql> '/disk3/log1b.rdo' to group 1,sql> '/disk4/log2b.rdo' to group 2;5. changes the name of the online redo logfilesql> alter database rename file 'c:/oracle/oradata/oradb/redo01.log' sql> to 'c:/oracle/oradata/redo01.log';6. drop online redo log groupssql> alter database drop logfile group 3;7. drop online redo log memberssql> alter database drop logfile member 'c:/oracle/oradata/redo01.log'; 8.clearing online redo log filessql> alter database clear [unarchived] logfile 'c:/oracle/log2a.rdo'; ing logminer analyzing redo logfilesa. in the init.ora specify utl_file_dir = ' 'b. sql> execute dbms_logmnr_d.build('oradb.ora','c:\oracle\oradb\log';c. sql> execute dbms_logmnr_add_logfile('c:\oracle\oradata\oradb\redo01.log', sql>dbms_logmnr.new;d. sql> execute dbms_logmnr.add_logfile('c:\oracle\oradata\oradb\redo02.log', sql>dbms_logmnr.addfile;e. sql> executedbms_logmnr.start_logmnr(dictfilename=>'c:\oracle\oradb\log\oradb.ora ';f. sql> select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameterssql> v$logmnr_logs;g. sql> execute dbms_logmnr.end_logmnr;第二章:表空间管理1. create tablespacessql> create tablespace tablespace_name datafile'c:\oracle\oradata\file1.dbf' size 100m,sql> 'c:\oracle\oradata\file2.dbf' size 100m minimum extent 550k [logging/nologging] sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0sql> [online/offline] [permanent/temporary] [extent_management_clause] 2.locally managed tablespacesql> create tablespace user_data datafile'c:\oracle\oradata\user_data01.dbf'sql> size 500m extent management local uniform size 10m;3. temporary tablespacesql> create temporary tablespace temp tempfile'c:\oracle\oradata\temp01.dbf'sql> size 500m extent management local uniform size 10m;4. change the storage settingsql> alter tablespace app_data minimum extent 2m;sql> alter tablespace app_data default storage(initial 2m next 2m maxextents 999;5. taking tablespace offline or onlinesql> alter tablespace app_data offline;sql> alter tablespace app_data online;6. read_only tablespacesql> alter tablespace app_data read only|write;7. droping tablespacesql> drop tablespace app_data including contents;8. enableing automatic extension of data filessql> alter tablespace app_data add datafile 'c:\oracle\oradata\app_data01.dbf'size 200msql> autoextend on next 10m maxsize 500m;9. change the size fo data files manuallysql> alter database datafile 'c:\oracle\oradata\app_data.dbf'resize 200m;10. Moving data files: alter tablespacesql> alter tablespace app_data rename datafile 'c:\oracle\oradata\app_data.dbf'sql> to 'c:\oracle\app_data.dbf';11. moving data files:alter databasesql> alter database rename file 'c:\oracle\oradata\app_data.dbf' sql> to'c:\oracle\app_data.dbf';第三章:表1. create a tablesql> create table table_name (column datatype,column datatype] ... sql> tablespace tablespace_name [pctfree integer] [pctused integer] sql> [initrans integer] [maxtrans integer]sql> storage(initial 200k next 200k pctincrease 0 maxextents 50 sql>[logging|nologging] [cache|nocache]2. copy an existing tablesql> create table table_name [logging|nologging] as subquery3. create temporary tablesql> create global temporary table xay_temp as select * from xay; on commit preserve rows/on commit delete rows4. pctfree = (average row size - initial row size *100 /average row size pctused = 100-pctfree- (average row size*100/available data space5.change storage and block utilization parametersql> alter table table_name pctfree=30 pctused=50 storage(next 500k sql> minextents 2 maxextents 100;6.manually allocating extentssql> alter table table_name allocate extent(size 500k datafile'c:/oracle/data.dbf';7.move tablespacesql> alter table employee move tablespace users;8. deallocate of unused spacesql> alter table table_name deallocate unused [keep integer]9. truncate a tablesql> truncate table table_name;10. drop a tablesql> drop table table_name [cascade constraints];11. drop a columnsql> alter table table_name drop column comments cascade constraints checkpoint 1000;alter table table_name drop columns continue;12. mark a column as unusedsql> alter table table_name set unused column comments cascade constraints;alter table table_name drop unused columns checkpoint 1000; alter table orders drop columns continue checkpoint 1000data_dictionary : dba_unused_col_tabs第四章:索引1. creating function-based indexessql> create index summit.item_quantity on summit.item(quantity-quantity_shipped; 2. create a B-tree indexsql> create [unique] index index_name on table_name(column,.. asc/desc tablespace sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer]sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0 sql> maxextents 50;3. pctfree(index=(maximum number of rows-initial number of rows*100/maximum number of rows4. creating reverse key indexessql> create unique index xay_id on xay(a reverse pctfree 30 storage(initial 200k sql> next 200k pctincrease 0 maxextents 50 tablespace indx;5. create bitmap indexsql> create bitmap index xay_id on xay(a pctfree 30 storage( initial 200k next 200k sql> pctincrease 0 maxextents 50 tablespace indx;6. change storage parameter of indexsql> alter index xay_id storage (next 400k maxextents 100;7. allocating index spacesql> alter index xay_id allocate extent(size 200k datafile'c:/oracle/index.dbf';8. alter index xay_id deallocate unused;第五章:约束1. define constraints as immediate or deferredsql> alter session set constraint[s] = immediate/deferred/default; set constraint[s] constraint_name/all immediate/deferred;2. sql> drop table table_name cascade constraintssql> drop tablespace tablespace_name including contents cascade constraints3. define constraints while create a tablesql> create table xay(id number(7 constraint xay_id primary key deferrablesql> using index storage(initial 100k next 100k tablespace indx; primarykey/unique/references table(column/check4. enable constraintssql> alter table xay enable novalidate constraint xay_id;5. enable constraintssql> alter table xay enable validate constraint xay_id;第六章:LOAD 数据1.loading data using direct_load insertsql> insert /*+append */ into emp nologgingsql> select * from emp_old;2. parallel direct-load insertsql> alter session enable parallel dml;sql> insert /*+parallel(emp,2 */ into emp nologgingsql> select * from emp_old;3. using sql*loadersql> sqlldr scott/tiger \sql> control = ulcase6.ctl \sql> log = ulcase6.log direct=true第七章:reorganizing data1. using expoty$exp scott/tiger tables(dept,emp file=c:\emp.dmp log=exp.log compress=n direct=y2. using import$imp scott/tiger tables(dept,emp file=emp.dmp log=imp.log ignore=y 3.transporting a tablespacesql>alter tablespace sales_ts read only;$exp sys/.. file=xay.dmp transport_tablespace=y tablespace=sales_ts triggers=n constraints=n$copy datafile$imp sys/.. file=xay.dmp transport_tablespace=ydatafiles=(/disk1/sles01.dbf,/disk2/sles02.dbfsql> alter tablespace sales_ts read write;4. checking transport setsql> DBMS_tts.transport_set_check(ts_list=>'sales_ts' ..,incl_constraints=>true;在表tran sport_set_violati ons 中查看sql> dbms_tts.isselfco ntai ned为true 是,表示自包含第八章: managing password security and resources1. controlling account lock and passwordsql> alter user juncky identified by oracle account unlock;2. user_provided password functionsql> function_name(userid in varchar2(30,password in varchar2(30, old_password in varchar2(30 return boolean3. create a profile : password settingsql> create profile grace_5 limit failed_login_attempts 3sql> password_lock_time unlimited password_life_time 30sql>password_reuse_time 30 password_verify_function verify_function sql> password_grace_time 5;4. altering a profilesql> alter profile default failed_login_attempts 3sql> password_life_time 60 password_grace_time 10;5. drop a profilesql> drop profile grace_5 [cascade];6. create a profile : resource limitsql> create profile developer_prof limit sessions_per_user 2sql> cpu_per_session 10000 idle_time 60 connect_time 480;7. view => resource_cost : alter resource costdba_Users,dba_profiles8. enable resource limitssql> alter system set resource_limit=true;第九章:Managing users1. create a user: database authenticationsql> create user juncky identified by oracle default tablespace users sql> temporary tablespace temp quota 10m on data password expire sql> [account lock|unlock] [profile profilename|default];2. change user quota on tablespacesql> alter user juncky quota 0 on users;3. drop a usersql> drop user juncky [cascade];4. monitor userview: dba_users , dba_ts_quotas第十章:managing privileges1. system privileges: view =>system_privilege_map ,dba_sys_privs,session_privs2. grant system privilegesql> grant create session,create table to managers;sql> grant create session to scott with admin option;with admin option can grant or revoke privilege from any user or role; 3.sysdba and sysoper privileges:sysoper: startup,shutdown,alter database open|mount,alter database backup controlfile,alter tablespace begin/end backup,recover databasealter database archivelog,restricted sessionsysdba: sysoper privileges with admin option,create database,recover database until4.password file members: view:=> v$pwfile_users5.O7_dictionary_accessibility =true restriction access to view or tables in other schema6. revoke system privilegesql> revoke create table from karen;sql> revoke create session from scott;7. grant object privilegesql> grant execute on dbms_pipe to public;sql> grant update(first_name,salary on employee to karen with grant option;8. display object privilege : view => dba_tab_privs, dba_col_privs9. revoke object privilegesql> revoke execute on dbms_pipe from scott [cascade constraints]; 10.audit record view :=> sys.aud$11. protecting the audit trailsql> audit delete on sys.aud$ by access;12. statement auditingsql> audit user;13. privilege auditingsql> audit select any table by summit by access;14. schema object auditingsql> audit lock on summit.employee by access whenever successful;15. view audit option : view=>all_def_audit_opts,dba_stmt_audit_opts,dba_priv_audit_opts, dba_obj_audit_opts16. view audit result: view=> dba_audit_trail,dba_audit_exists,dba_audit_object,dba_audit_session,dba_audit_statement 第十一章: manager role1.create rolessql> create role sales_clerk;sql> create role hr_clerk identified by bonus;sql> create role hr_manager identified externally; 2.modify rolesql> alter role sales_clerk identified by commission; sql> alter role hr_clerk identified externally;sql> alter role hr_manager not identified;3. assigning rolessql> grant sales_clerk to scott;sql> grant hr_clerk to hr_manager;sql> grant hr_manager to scott with admin option; 4.establish default rolesql> alter user scott default role hr_clerk,sales_clerk; sql> alter user scott default role all;sql> alter user scott default role all except hr_clerk; sql> alter user scott default role none;5. enable and disable rolessql> set role hr_clerk;sql> set role sales_clerk identified by commission; sql> set role all except sales_clerk;sql> set role none;6. remove role from usersql> revoke sales_clerk from scott;sql> revoke hr_manager from public;7. remove rolesql> drop role hr_manager;8. display role informationview: =>dba_roles,dba_role_privs,role_role_privs,dba_sys_privs,role_sys_privs,role_tab_privs,session_roles第十二章: BACKUP and RECOVERY1.v$sga,v$instance,v$process,v$bgprocess,v$database,v$datafile,v$sgasta t2. Rman need set dbwr_IO_slaves or backup_tape_IO_slaves andlarge_pool_size3. Monitoring Parallel Rollback> v$fast_start_servers , v$fast_start_transactions4. perform a closed database backup (noarchivelog> shutdown immediate> cp files /backup/> startup5. restore to a different location> connect system/manager as sysdba> startup mount> alter database rename file '/disk1/../user.dbf'to '/disk2/../user.dbf';> alter database open;6. recover syntax--recover a mounted database>recover database;>recover datafile '/disk1/data/df2.dbf';>alter database recover database;--recover an opened database>recover tablespace user_data;>recover datafile 2;>alter database recover datafile 2;7. how to apply redo log files automatically>set autorecovery on>recover automatic datafile 4;8. complete recovery:--method 1(mounted databae>copy c:\backup\user.dbf c:\oradata\user.dbf>startup mount>recover datafile 'c:\oradata\user.dbf;>alter database open;--method 2(opened database,initially opened,not system or rollback datafile>copy c:\backup\user.dbf c:\oradata\user.dbf (alter tablespace offline >recover datafile 'c:\oradata\user.dbf' or>recover tablespace user_data;>alter database datafile 'c:\oradata\user.dbf' online or>alter tablespace user_data online;--method 3(opened database,initially closed not system or rollback datafile>startup mount>alter database datafile 'c:\oradata\user.dbf' offline;>alter database open>copy c:\backup\user.dbf d:\oradata\user.dbf>alter database rename file 'c:\oradata\user.dbf'to'd:\oradata\user.dbf'>recover datafile 'e:\oradata\user.dbf' or recover tablespace user_data; >alter tablespace user_data online;--method 4(loss of data file with no backup and have all archive log >alter tablespace user_data offline immediate;>alter database create datafile 'd:\oradata\user.dbf'as'c:\oradata\user.dbf''>recover tablespace user_data;>alter tablespace user_data online9. perform an open database backup> alter tablespace user_data begin backup;> copy files /backup/> alter database datafile '/c:/../data.dbf' end backup;> alter system switch logfile;10. backup a control file> alter database backup controlfile to 'control1.bkp';> alter database backup controlfile to trace;11. recovery (noarchivelog mode> shutdown abort> cp files> startup 12.recovery of file in backup mode >alter database datafile 2 end backup; 13.clearing redo log file >alter database clear unarchived logfile group 1; >alter database clear unarchived logfile group 1 unrecoverable datafile; 14.redo log recovery >alter database add logfile group 3 'c:\oradata\redo03.log'size 1000k; >alter database drop logfile group 1; >alter database open; or >cp c:\oradata\redo02.log' c:\oradata\redo01.log >alter database clear logfile 'c:\oradata\log01.log'; 16。
oracle11g 中adrci的使用
代码:adrci>>show homes;show base; echo '20070712'ADR Homes:diag/rdbms/orcl/orclADR base is "/home/oracle"20070712adrci>>adrci>>adrci>>exit[oracle@ractest ~]$ adrci exec="show homes;echo '20070712';echo '';sh ow base; "ADR Homes:diag/rdbms/orcl/orcl20070712ADR base is "/home/oracle"2) 使用script选项。
adrci SCRIPT=adrci_script.txt但shahand使用的beta版似乎还有些问题。
好像没有任何结果一样代码:[oracle@ractest ~]$ cat /tmp/ashow homes;[oracle@ractest ~]$ adrci script=/tmp/a[oracle@ractest ~]$ cat /tmp/afadsfdsa[oracle@ractest ~]$ adrci script=/tmp/a[oracle@ractest ~]$ cat /tmp/ashow trace;[oracle@ractest ~]$ adrci script=/tmp/a[oracle@ractest ~]$ cat /tmp/aSET HOMEPATH /home/oracle/diag/rdbms/orcl/orcl;show trace;[oracle@ractest ~]$ adrci script=/tmp/a[oracle@ractest ~]$三．使用ADRCI查看Oracle数据库后台报警日志（alert_sid.log）和跟踪文件注意：以下大部分命令都需要用Ctrl+C 来结束，并返回到adrci命令行1.查看完整alert信息：adrci>>SHOW ALERT2. 查看最新alert信息：adrci>> SHOW ALERT –TAIL查看最新20条alert信息：adrci>> SHOW ALERT -TAIL 20只查看600的错误adrci>>SHOW ALERT -P "MESSAGE_TEXT LIKE '%ORA-600%'"查看ORA-错误信息，注意这里的参数很好，比较人性化，可以帮助提供错误时间Options:[-p <predicate_string>]: The predicate string must be double quot ed.The fields in the predicate are the fields in the alert message'sXML schema. To get the field definitions, use command:"describe&3．查看跟踪文件常用的有：(1)列出所有跟踪文件：SHOW TRACEFILE(2)模糊查询跟踪文件，比如某个进程的，注意这里区分大小写SHOW TRACEFILE %mmon%(3)可以指定某个路径SHOW TRACEFILE %mmon% -PATH/home/steve/temp(4)象ls那样按时间排序SHOW TRACEFILE -RT四．其他体验和说明1.关于在adrci中执行os命令，可以直接在adrci中执行os命令。
常用oracle数据库命令
常用oracle数据库命令
1. 启动Oracle数据库
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup
3. 创建表空间
创建表空间的命令是：
SQL> create tablespace 表空间名 datafile '路径名' size 大小;
6. 创建用户
7. 删除用户
删除用户的命令是：
8. 授权用户
SQL> grant 权限 to 用户名;
9. 撤销用户的权限
11. 查看表结构
查看表结构的命令是：
SQL> desc 表名;
SQL> create table 表名 (列名数据类型, 列名数据类型, …);
13. 删除表
14. 插入数据
插入数据的命令是：
SQL> update 表名 set 列名=新值 where 某条件;
18. 创建索引
20. 查看索引
21. 查询相关信息
查询相关信息的命令是：
SQL> select * from v$session; -- 查看会话 22. 查看数据库版本
24. 备份数据库
$ exp 用户名/密码 file=备份文件路径备注：以上命令均需要在Oracle登录后进行。
oracle11g教程从入门到精通
韩顺平—玩转oracle视频教程笔记一：Oracle认证，与其它数据库比较，安装Oracle安装会自动的生成sys用户和system用户:（1）sys用户是超级用户，具有最高权限，具有sysdba角色，有create database的权限，该用户默认的密码是change_on_install（2）system用户是管理操作员，权限也很大.具有sysoper角色，没有create database的权限,默认的密码是manager（3）一般讲,对数据库维护，使用system用户登录就可以拉也就是说sys和system这两个用户最大的区别是在于有没有create database的权限。
二: Oracle的基本使用——基本命令sql＊plus的常用命令连接命令1。
conn[ect]用法：conn 用户名/密码＠网络服务名[as sysdba/sysoper]当用特权用户身份连接时，必须带上as sysdba或是as sysoper2。
disc［onnect］说明：该命令用来断开与当前数据库的连接3.psssw［ord］说明: 该命令用于修改用户的密码，如果要想修改其它用户的密码，需要用sys/system登录。
4.show user说明: 显示当前用户名5.exit说明: 该命令会断开与数据库的连接，同时会退出sql*plus文件操作命令1。
start和＠说明：运行sql脚本案例： sql>@ d：\a。
sql或是sql〉start d:\a.sql2.edit说明: 该命令可以编辑指定的sql脚本案例: sql>edit d:\a.sql,这样会把d：\a。
sql这个文件打开3。
spool说明: 该命令可以将sql*plus屏幕上的内容输出到指定文件中去。
案例: sql>spool d：\b。
sql 并输入 sql>spool off交互式命令1。
＆说明：可以替代变量,而该变量在执行时，需要用户输入。
Oracle11g的管理工具
3.3 SQL*Plus
• SQL*Plus的主要功能包括：
– 插入、修改、删除、查询数据，以及执行SQL、 PL/SQL块；
– 查询结果的格式化、运算处理、保存、打印输出； – 显示表的定义，并与终端用户交互； – 连接数据库，定义变量； – 完成数据库管理； – 运行存储在数据库中的子程序或包； – 启动/停止数据库实例。
• SQL*Plus是Oracle公司在SQL基础上经过扩充的 语言，它也是一种开发环境，可以运行在任何 Oracle平台上。SQL*Plus可以执行输入的SQL语 句和包含SQL语句的文件，通过SQL*Plus可以与 数据库进行交互，开发数据库应用程序，使用户 可以根据需要完成对数据库中数据的检索、格式 化和控制等操作。
• 在SQL*Plus中，从“SQL>”提示符输入命令或者 SQL语句时，命令以分号结束，然后按回车键执 行。如果SQL语句的结尾没有“；”，SQL*Plus 会认为语句还没有结束，自动转换到下已行等待 输入。
• SQL*Plus可以执行SQL语句，语句执行完成后， 保存在一个被称为SQL buffer的内存区域中，并 且只能保存一条最近执行的SQL语句，可以对保 存在SQL buffer中的SQL语句进行修改，然后再 次执行。
3.3.2 SQL*PLUS的编辑功能
• 除了SQL *Plus语句，在SQL *Plus中执行 的其它语句我们称之为SQL *Plus命令。它 们执行完后，不保存在SQL buffer的内存区 域中，它们一般用来执行编辑功能或者对 输出的结果进行格式化显示，以便于制作 报表。
3.3.2 SQL*PLUS的编辑功能
第3章 Oracle 11g的常用工具
学习目的
• 掌握数据库配置助手（Database Configuration Assistant，DBCA）的功能 和使用方法；
Oracle常用命令大全（很有用，做笔记）
Oracle常⽤命令⼤全（很有⽤，做笔记）⼀、ORACLE的启动和关闭1、在单机环境下要想启动或关闭ORACLE系统必须⾸先切换到ORACLE⽤户，如下su - oraclea、启动ORACLE系统oracle>svrmgrlSVRMGR>connect internalSVRMGR>startupSVRMGR>quitb、关闭ORACLE系统oracle>svrmgrlSVRMGR>connect internalSVRMGR>shutdownSVRMGR>quit启动oracle9i数据库命令：$ sqlplus /nologSQL*Plus: Release 9.2.0.1.0 - Production on Fri Oct 31 13:53:53 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> connect / as sysdbaConnected to an idle instance.SQL> startup^CSQL> startupORACLE instance started.2、在双机环境下要想启动或关闭ORACLE系统必须⾸先切换到root⽤户，如下su － roota、启动ORACLE系统hareg －y oracleb、关闭ORACLE系统hareg －n oracleOracle数据库有哪⼏种启动⽅式说明：有以下⼏种启动⽅式：1、startup nomount⾮安装启动，这种⽅式启动下可执⾏：重建控制⽂件、重建数据库读取init.ora⽂件，启动instance，即启动SGA和后台进程，这种启动只需要init.ora⽂件。
2、startup mount dbname安装启动，这种⽅式启动下可执⾏：数据库⽇志归档、数据库介质恢复、使数据⽂件联机或脱机，重新定位数据⽂件、重做⽇志⽂件。
oracle11gRAC启动和停止操作说明
oracle11gRAC启动和停止操作说明（ORACLE）查看集群中的数据库运行状态，最后接着是RAC名，不是实例的名称。
srvctl status database -d taxoracle@svrtrpc001:~> srvctl status database -d taxInstance tax1 is running on node svrtrpc001Instance tax2 is running on node svrtrpc002Instance tax3 is running on node svrtrpc003-----------------------------------------------------(ORACLE)查看数据库的运行状态，最后接着是RAC名，不是实例的名称。
srvctl status database -d tax -voracle@svrtrpc001:~> srvctl status database -d tax -vInstance tax1 is running on node svrtrpc001. Instance status: Open.Instance tax2 is running on node svrtrpc002. Instance status: Open.Instance tax3 is running on node svrtrpc003. Instance status: Open.-----------------------------------------(ORACLE)查看实例运行状态，最后接着节点名srvctl status instance -d tax -i tax1oracle@svrtrpc001:~> srvctl status instance -d tax -i tax1Instance tax1 is running on node svrtrpc001---------------------------------------------------（ORACLE）查看集群的配置情况srvctl config -p tax -n taxoracle@svrtrpc001:~> srvctl config -p tax -n taxDatabase unique name: taxDatabase name: taxOracle home: /u01/app/oracle/product/11.2.0Oracle user: oracleSpfile: +DATADG/tax/spfiletax.oraDomain:Start options: openStop options: immediateDatabase role: PRIMARYManagement policy: AUTOMATICServer pools: taxDatabase instances: tax1,tax2,tax3Disk Groups: DATADGMount point paths:Services:Type: RACDatabase is administrator managed-----------------------------------------------启动2.3 srvctl startI think maybe it's one of the most frequently used commands as well as 'srvctl stop'.Available options: database|instance|service|nodeapps|asm# Start databasesrvctl start database -d orcl -o nomountsrvctl start database -d orcl -o mountsrvctl start database -d orcl -o open# Grammar for start instancesrvctl start instance -d [db_name] -i [instance_name]-o [start_option] -c [connect_str] -q# Start all instances on the all nodessrvctl start instance -d orcl -i orcl1,orcl2,...# Start ASM instancesrvctl start ASM -n [node_name] -i asm1 -o open# Start all apps in one nodesrvctl start nodeapps -n [node_name]# New: srvctl can manage listener's startup in DB 10.2srvctl start listener -n-------------------------------------------------关闭2.4 srvctl stopI think maybe it's also one of the most frequently used commands as well as 'srvctl start'.Available options: database|instance|service|nodeapps|asm# Stop databasesrvctl stop database -d orcl -o normalsrvctl stop database -d orcl -o immediatesrvctl stop database -d orcl -o abort# Grammar for start instancesrvctl stop instance -d [db_name] -i [instance_name]-o [start_option] -c--------------------------1、检查集群状态：[grid@rac02 ~]$ crsctl check clusterCRS-4537: Cluster Ready Services is onlineCRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online2、所有 Oracle 实例—（数据库状态）:[grid@rac02 ~]$ srvctl status database -d racdb Instance racdb1 is running on node rac01Instance racdb2 is running on node rac023、检查单个实例状态：[grid@rac02 ~]$ srvctl status instance -d racdb -i racdb1 Instance racdb1 is running on node rac014、节点应用程序状态：[grid@rac02 ~]$ srvctl status nodeappsVIP rac01-vip is enabledVIP rac01-vip is running on node: rac01VIP rac02-vip is enabledVIP rac02-vip is running on node: rac02Network is enabledNetwork is running on node: rac01Network is running on node: rac02GSD is disabledGSD is not running on node: rac01GSD is not running on node: rac02ONS is enabledONS daemon is running on node: rac01ONS daemon is running on node: rac02eONS is enabledeONS daemon is running on node: rac01eONS daemon is running on node: rac025、列出所有的配置数据库：[grid@rac02 ~]$ srvctl config databaseracdb6、数据库配置：[grid@rac02 ~]$ srvctl config database -d racdb -a Database unique name: racdbDatabase name: racdbOracle home: /u01/app/oracle/product/11.2.0/dbhome_1 Oracle user: oracleSpfile: +RACDB_DATA/racdb/spfileracdb.ora Domain: /doc/b02033077.html, Start options: openStop options: immediateDatabase role: PRIMARYManagement policy: AUTOMATICServer pools: racdbDatabase instances: racdb1,racdb2Disk Groups: RACDB_DATA,FRAServices:Database is enabledDatabase is administrator managed7、ASM状态以及ASM配置：[grid@rac02 ~]$ srvctl status asmASM is running on rac01,rac02[grid@rac02 ~]$ srvctl config asm -aASM home: /u01/app/11.2.0/gridASM listener: LISTENERASM is enabled.8、TNS监听器状态以及配置：[grid@rac02 ~]$ srvctl status listenerListener LISTENER is enabledListener LISTENER is running on node(s): rac01,rac02[grid@rac02 ~]$ srvctl config listener -aName: LISTENERNetwork: 1, Owner: gridHome:/u01/app/11.2.0/grid on node(s) rac02,rac01End points: TCP:15219、SCAN状态以及配置：[grid@rac02 ~]$ srvctl status scanSCAN VIP scan1 is enabledSCAN VIP scan1 is running on node rac02[grid@rac02 ~]$ srvctl config scanSCAN name: /doc/b02033077.html,, Network: 1/192.168.1.0/255.255.255.0/eth0SCAN VIP name: scan1, IP: //doc/b02033077.html,/192.168.1.5510、VIP各个节点的状态以及配置：[grid@rac02 ~]$ srvctl status vip -n rac01VIP rac01-vip is enabledVIP rac01-vip is running on node: rac01[grid@rac02 ~]$ srvctl status vip -n rac02VIP rac02-vip is enabledVIP rac02-vip is running on node: rac02[grid@rac02 ~]$ srvctl config vip -n rac01VIP exists.:rac01VIP exists.: /rac01-vip/192.168.1.53/255.255.255.0/eth0 [grid@rac02 ~]$ srvctl config vip -n rac02VIP exists.:rac02VIP exists.: /rac02-vip/192.168.1.54/255.255.255.0/eth011、节点应用程序配置—（VIP、GSD、ONS、监听器）[grid@rac02 ~]$ srvctl config nodeapps -a -g -s -l-l option has been deprecated and will be ignored. VIP exists.:rac01VIP exists.: /rac01-vip/192.168.1.53/255.255.255.0/eth0 VIP exists.:rac02VIP exists.: /rac02-vip/192.168.1.54/255.255.255.0/eth0 GSD exists.ONS daemon exists. Local port 6100, remote port 6200 Name: LISTENERNetwork: 1, Owner: gridHome:/u01/app/11.2.0/grid on node(s) rac02,rac01End points: TCP:152112、验证所有集群节点间的时钟同步:[grid@rac02 ~]$ cluvfy comp clocksync -verboseVerifying Clock Synchronization across the cluster nodesChecking if Clusterware is installed on all nodes...Check of Clusterware install passedChecking if CTSS Resource is running on all nodes...Check: CTSS Resource running on all nodesNode Name Status------------------------------------ ------------------------rac02 passedResult: CTSS resource check passedQuerying CTSS for time offset on all nodes...Result: Query of CTSS for time offset passedCheck CTSS state started...Check: CTSS stateNode Name State------------------------------------ ------------------------rac02 ActiveCTSS is in Active state. Proceeding with check of clock time offsets on all nodes...Reference Time Offset Limit: 1000.0 msecsCheck: Reference Time OffsetNode Name Time Offset Status------------ ------------------------ ------------------------rac02 0.0 passedTime offset is within the specified limits on the following set of nodes:"[rac02]"Result: Check of clock time offsets passedOracle Cluster Time Synchronization Services check passed Verification of Clock Synchronization across the clusternodes was successful.13、集群中所有正在运行的实例— (SQL):SELECT inst_id , instance_number inst_no , instance_name inst_name , parallel , status , database_status db_status , active_state state , host_name host FROM gv$instance ORDER BY inst_id;14、所有数据库文件及它们所在的 ASM 磁盘组— (SQL):15、ASM 磁盘卷:16、启动和停止集群：以下操作需用root用户执行。
oracle11g删除实例
oracle11g删除实例在Oracle 11g 中，删除数据库实例通常涉及以下步骤：1. 停止数据库服务：在删除实例之前，确保数据库实例处于关闭状态。
你可以使用SQL*Plus 或其他管理工具连接到数据库并执行以下命令：```sqlSHUTDOWN IMMEDIATE;```这将立即关闭数据库实例。
如果你希望等待当前会话完成并关闭数据库，请使用`SHUTDOWN NORMAL;`。
2. 卸载数据库：在数据库实例关闭后，可以卸载数据库。
这将从Oracle 目录中删除数据库实例的注册信息。
可以使用`dbca`（Database Configuration Assistant）实用工具来完成此操作。
```bashdbca -silent -deleteDatabase -sourceDB <your_database_name>```注意：请将`<your_database_name>` 替换为要删除的数据库实例的名称。
3. 删除数据库文件：删除数据库文件和目录。
这包括数据文件、控制文件、日志文件等。
确保在删除这些文件之前备份重要数据。
4. 删除监听器：如果数据库实例有专用的监听器，你可能需要删除它。
可以使用`lsnrctl` 实用程序执行此操作。
```bashlsnrctl stop <listener_name>lsnrctl status # 确保监听器已停止lsnrctl delete <listener_name>```注意：请将`<listener_name>` 替换为你的监听器的名称。
这些步骤需要谨慎执行，特别是在生产环境中。
在删除数据库实例之前，请确保已经做好了数据备份，并理解删除操作的后果。
建议在执行删除操作之前与数据库管理员或有经验的Oracle 用户讨论。
oracle 11g ssl 配置查询语句 -回复
oracle 11g ssl 配置查询语句-回复在Oracle 11g中配置SSL是一项重要的任务，以确保安全的数据库通信。
SSL（Secure Sockets Layer）是一种安全协议，用于加密客户端和服务器之间的通信，防止数据在传输过程中被窃取或篡改。
本文将为您详细介绍如何配置Oracle 11g的SSL功能，让您的数据库通信更加安全可靠。
首先，我们需要确保您已经获得了有效的SSL证书。
证书是一种数字证明，用于验证服务器身份并加密通信。
您可以从公认的认证机构（CA）或第三方供应商获得SSL证书。
现在，我们可以开始配置Oracle 11g的SSL功能。
请按照以下步骤进行操作：第一步：检查Oracle 11g是否已安装SSL支持在开始配置SSL之前，我们需要确保Oracle 11g已经安装了SSL支持。
要检查是否已安装SSL支持，您可以执行以下查询语句：SELECT * FROM voption WHERE parameter = 'Advanced Security';如果结果集中显示“TRUE”，这意味着已安装了Advanced Security选项，也就是SSL支持。
第二步：创建Oracle钱包钱包是存储SSL证书和密钥的文件容器。
要创建Oracle钱包，您需要执行以下步骤：1. 使用以下命令登录到数据库服务器：sqlplus / as sysdba2. 在SQL*Plus提示下，输入以下命令创建钱包：SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE'<wallet_directory>' IDENTIFIED BY '<password>' FORCE keystore_type [auto_login] [ENABLE] HSM;在上述命令中，您需要指定钱包的目录和密码。
Oracle 11g完全手册
一．Window下关闭Oracle 11g正常在Windows下安全关闭Oracle数据库系统的步骤如下：1.从命令行下找到oracle的BIN目录，然后输入isqlplusctl stop命令停止isqlplus进程。
2.同样在BIN目录下，emctl stop dbconsole停止企业管理器的控制台进程。
3.同样在BIN目录下，lsnrctl stop停止监听进程。
4.在命令行下输入sqlplus /nolog , 然后conn / as sysdba连接到数据库shutdown immediate;====================================================================== select username,password from dba_users; --查看用户信息select username,account_status from dba_users; --查看用户是否解锁alter user scott account unlock; --解锁scott用户alter user scott account lock;--锁scott用户alter user scott identified by tiger; --为scott用户修改密码为tiger创建用户以及给表赋予权限：创建用户create user user_name identified by password;grant dba to user_name；查询表的权限grant select on gecs_law to user_name;select file_name from dba_data_files; --查看数据文件路径select name from v$controlfile; --查看控制文件路径select member from v$logfile; --查看日志文件路径====================================================================== Oracle关于shutdown、startup几个参数：shutdown有四个参数：normal、transactional、immediate、abort。
oracle11gimp用法
oracle11gimp用法Oracle 11g IMP（Import）用法指南Oracle 11g是一种强大的关系数据库管理系统，它提供了多种工具和功能，其中之一是IMP（Import）工具，它用于将先前导出的数据和对象导入到Oracle数据库中。
本文将介绍Oracle 11g IMP工具的用法，帮助您使用它进行数据导入。
首先，确保您已经安装了Oracle 11g数据库，并具备正确的权限以执行导入操作。
使用IMP工具进行数据导入的基本语法如下：```imp username/password@database file=<导入文件路径> full=y```其中，`username/password@database`是您的数据库登录凭据，`file=<导入文件路径>`是导入文件的路径和名称，`full=y`表示导入所有数据和对象。
下面是一些常用的IMP工具参数：1. FROMUSER和TOUSER：用于指定要导入数据的源用户和目标用户。
例如：```imp username/password@database fromuser=<源用户> touser=<目标用户>file=<导入文件路径> full=y```2. TABLES：可以通过指定TABLES参数来限制只导入特定的表。
例如：```imp username/password@database tables=(table1, table2) file=<导入文件路径> full=y```3. IGNORE：用于指定在导入过程中遇到重复记录时的操作。
可以选择忽略（ignore）或替换（replace）重复记录。
例如：```imp username/password@database ignore=y file=<导入文件路径> full=y```4. INDEXES：可以使用INDEXES参数来控制是否导入表的索引。