ORAPS操作手册
Oracle 数据库常用操作手册
ORACLE 数据库常用操作手册第一章SHELL篇1. RAC维护命令1.1. RAC启动与停止启动单一节点数据库srvctl start nodeapps -n <node_name>srvctl start asm -n <node_name>srvctl start instance -d <database_name> -i <instance_name>emctl start dbconsole停止单一节点数据库emctl stop dbconsolesrvctl stop instance -d <database_name> -i <instance_name>srvctl stop asm -n <node_name>srvctl stop nodeapps -n <node_name>开启关闭监听lsnrctl stoplsnrctl start或srvctl stop listener -n <node_name>srvctl start listener -n <node_name>停止启动服务srvctl stop service -d gzedusrvctl start service -d gzedu停止启动全局数据库srvctl stop database -d basesrvctl start database -d base关闭整个群集数据库root权限crs_stop -all或crsctl stop crs启动整个群集数据库root权限crs_start –all或crsctl start crs停止群集守护进程/etc/init.d/init.crs stop在Oracle环境中,当RAC不同节点间的时间差超过30秒时,会导致oracle数据库反复重启或者状态异常.重新启动整个数据库群集Usage: crs_stop resource_name [...] [-f] [-q] ["attrib=value ..."]crs_stop -c cluster_member [...] [-q] ["attrib=value ..."]crs_stop -all [-q]crs_stop -allcrs_start -allcrs_stop ora.oradb3.gsd1.2. RAC配置修改修改VIP地址oifcfg getif -globaloifcfg setif -global eth0/192.168.1.0:publicoifcfg iflist/etc/init.d/init.crs stopsrvctl modify nodeapps -n rac1 -A 192.168.1.191/255.255.255.0/eth01.3. RAC数据库检查查看群集数据库状态srvctl status database -d <database_name>crs_stat -t检查单一节点状态srvctl status nodeapps -n <node_name>所有实例和服务的状态srvctl status database -d racdb单个实例的状态srvctl status instance -d racdb -i racdb2在数据库全局命名服务的状态srvctl status service -d racdb -s racdb_taf特定节点上节点应用程序的状态srvctl status nodeapps -n linux1ASM 实例的状态srvctl status asm -n linux1列出配置的所有数据库srvctl config database显示RAC 数据库的配置srvctl config database -d racdb显示指定集群数据库的所有服务srvctl config service -d racdb显示节点应用程序的配置—（VIP、GSD、ONS、监听器）srvctl config nodeapps -n linux1 -a -g -s -l显示ASM 实例的配置srvctl config asm -n linux1查询vote的路径磁盘信息crsctl query css votedisk查看节点状态srvctl status nodeapps -n oradb3srvctl status instance -d gzedu -i gzedu1检查crs健康情况crsctl check crscrsctl start resourcesgsdctl stopgsdctl status2. 数据库维护命令2.1. 数据库的启动与停止正常启动单机数据库su - oraclelsnrctl startsqlplus /nologconnect /as sysdbastartupsqlplus /nologconnect /as sysdbastartup nomountalter database mount启动管理控制台$emctl start dbconsole启动iSQLPLUS$isqlplusctl start停止单机数据库sqlplus /nologconnect /as sysdba3. 数据的导入与导出3.1. 数据导入imp fromuser=tutor1 touser=tutor1 file=gzdec-tutor1-060221.dmp;imp open_teacher/teacher987 file=db4_TJXJY_070723.dmp fromuser=tjxjy touser=open_teacher tables=(bbs,rbbs)imp open_netcsou/netcsou987 file=db4_netcsou_070713.dmp fromuser=netcsou touser=open_netcsou grants=n3.2. 数据泵导入impdp directory=backup_dir dumpfile=base-base_msgz-091111.dmp schemas=base_msgz include=TABLE:"in('RAC_USER_ROLE')" logfile=base_msgz_export.logimpdp tt_gd_lms/888888 directory=expdp_dir dumpfile=gzedu-gd_lms-090810.dmp schemas=gd_lms remap_schema=gd_lms:tt_gd_lms remap_tablespace=gd_lms:tt_gd_lmsimpdp directory=backup_dir dumpfile=base-base_msgz-091111.dmp schemas=base_msgz parallel=4 table_exists_action=replace tables=RAC_USER_ROLEimpdp directory=backup_dir dumpfile=base-base_msgz-091111.dmp schemas=base_msgz include=TABLE:"in('RAC_USER_ROLE')" logfile=base_msgz_export.logimpdp fvdb/fvdb directory=admin_dir dumpfile=FVDB.DMP schemas=fvdb remap_tablespace=fvdb:ts_fvdb logfile=fvdb_export.log3.3. 数据导出备份整个数据库exp system/manager inctype=complete file=gzdec-tutorl-070428.dmp增量型”增量导出exp system/manager inctype=incremental file=gzdec-tutorl-070428.dmp累计型”增量导出exp system/manager inctype=cumulative file=gzdec-tutorl-070428.dmp导出一个完整数据库exp system/manager file=bible_db log=dible_db full=y导出数据库定义而不导出数据exp system/manager file=bible_db log=dible_db full=y rows=n导出一个或一组指定用户所属的全部表、索引和其他对象exp system/manager file=seapark log=seapark owner=seaparkexp system/manager file=seapark log=seapark owner=(seapark,amy,amyc,harold)exp system/fitness1388 file=gzedu_open-bayi_new.dmp owner=open_bayi3.4. 数据泵导出在命令行中使用要注意使用转意字符因为' " ( )会被认为特殊字符expdp directory=test dumpfile=sfca09.dump logfile= sfca09.log schemas=sfcdata include=table:\"like \'SFCA%\'\"expdp directory=erp schemas=wfl content=data_only exclude=table:\"IN\(\'WFS_TB_FCSMAIL\',\'WFS_TB_WFMAS\',\'WFS_TB_WFDTL\'\)\" dumpfile=wfl16.dump logfile=wfl16.loginclude/exclude 例子：include=table:”in(’DB’,'TS’)”或者include=table:”like ‘%E%’”或者include=function,package,procedure,table:”=’EMP’”或者exclude=SEQUENCE,TABLE:”IN (’EMP’,'DEPT’)”expdp schemas=base_digischool directory=backup_dir dumpfile=base-base_digischool-20091118.dmp parallel=4 logfile=base_digischool_export.log4. Linux下常命令查杀所有oracle进程ps -ef|grep "ora_"|grep -v grep|awk '{ print $2 }'|xargs kill -9显示Oracle 的Unix 进程ps -ef|grep "ora_"|grep -v grep查看消耗CPU 时间最长的进程：ps -ef|grep oracle|sort +6|tail按照此列排序来获得当前高CPU 占用的用户。
oraps
20-40天
10-20天15天
基础数据动态数据试运行A1. 生产拓扑B1. 产品
产能
B2. 系统
参数
A2. 物料编码
B3. BOM 数据D 基础数据系统
配
置A3. 客户数据基础数据说明•产能数据：根据产能调查与配置表采集数据。
•物料编码：根据物料编码规则，对原料、在制品和成品编码。
•BOM 数据：根据企业实际情况，制定BOM 关联表并采集数据，包括产品特征属性和产品产能。
所有数据统一配置到系统中。
订单数据审核F 动
态数据系统
配置E1.销售订单的未核销情况E2. 生产订单未计划情况仓库盘点审核
E3.物料在库数量E4. 车间未完成任务物料需求E5. 采购未送达原料情况动态数据说明•动态数据的系统配置需要及时性。
•动态数据主要明确生产订单的未计划量和物料的采购需求量；•动态数据配置完表示上线成功，开始进入试运行阶段。
G1. 物料和BOM 数据修正G2. 系统参数修正
G5. 订单数据修正试运行说明•试运行是系统在企业实际生产中跟踪运行来修正数据错误，确保物料和订单的总量的
准确。
G3. 任务数据修正
G4.物料在库数量修正基础数据动态数
据C1. 产能数据分析C2. 物料数据分析
20－40天15天试运行过
程
中
规范流程的
应
用
10-20天成品出库特殊领用
客户提货供应商送货
替换物料出库
车间摆放
原料摆放
成品摆放。
Oracle PL SQL编程教程说明书
About the T utorialPL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90's to enhance the capabilities of SQL.PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java.This tutorial will give you great understanding on PL/SQL to proceed with Oracle database and other advanced RDBMS concepts.AudienceThis tutorial is designed for Software Professionals, who are willing to learn PL/SQL Programming Language in simple and easy steps. This tutorial will give you great understanding on PL/SQL Programming concepts, and after completing this tutorial, you will be at an intermediate level of expertise from where you can take yourself to a higher level of expertise.PrerequisitesBefore proceeding with this tutorial, you should have a basic understanding of software basic concepts like what is database, source code, text editor and execution of programs, etc. If you already have an understanding on SQL and other computer programming language, then it will be an added advantage to proceed.Copyright & DisclaimerCopyright 2018 by Tutorials Point (I) Pvt. Ltd.All the content and graphics published in this e-book are the property of Tutorials Point (I) Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republish any contents or a part of contents of this e-book in any manner without written consent of the publisher.We strive to update the contents of our website and tutorials as timely and as precisely as possible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt. Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of our website or its contents including this tutorial. If you discover any errors on our website or inthistutorial,******************************************iT able of ContentsAbout the Tutorial (i)Audience (i)Prerequisites (i)Copyright & Disclaimer (i)Table of Contents ................................................................................................................................... i ii PL/SQL — OVERVIEW . (1)Features of PL/SQL (1)Advantages of PL/SQL (1)PL/SQL — ENVIRONMENT SETUP (3)Text Editor (14)PL/SQL — BASIC SYNTAX (15)PL/SQL — DATA TYPES (19)PL/SQL Scalar Data Types and Subtypes (19)PL/SQL Numeric Data Types and Subtypes (20)PL/SQL Character Data Types and Subtypes (21)PL/SQL Boolean Data Types (22)PL/SQL Datetime and Interval Types (22)PL/SQL Large Object (LOB) Data Types (23)PL/SQL User-Defined Subtypes (24)NULLs in PL/SQL (25)PL/SQL — VARIABLES (26)Variable Declaration in PL/SQL (26)Initializing Variables in PL/SQL (27)Variable Scope in PL/SQL (28)iiAssigning SQL Query Results to PL/SQL Variables (29)PL/SQL — CONSTANTS AND LITERALS (31)Declaring a Constant (31)The PL/SQL Literals (32)PL/SQL — OPERATORS (34)Arithmetic Operators (34)Relational Operators (36)[Comparison Operators (39)Logical Operators (44)PL/SQL Operator Precedence (46)PL/SQL — CONDITIONS (49)IF-THEN Statement (50)IF-THEN-ELSE Statement (53)IF-THEN-ELSIF Statement (55)CASE Statement (56)Searched CASE Statement (58)Nested IF-THEN-ELSE Statements (60)PL/SQL — LOOPS (62)Basic Loop Statement (63)WHILE LOOP Statement (65)FOR LOOP Statement (66)Reverse FOR LOOP Statement (68)Nested Loops (69)Labeling a PL/SQL Loop (71)The Loop Control Statements (72)iiiEXIT Statement (73)The EXIT WHEN Statement (75)CONTINUE Statement (77)GOTO Statement (80)PL/SQL — STRINGS (83)Declaring String Variables (83)PL/SQL String Functions and Operators (84)PL/SQL — ARRAYS (89)Creating a Varray Type (89)PL/SQL — PROCEDURES (94)Parts of a PL/SQL Subprogram (94)Creating a Procedure (95)Executing a Standalone Procedure (96)Deleting a Standalone Procedure (97)Parameter Modes in PL/SQL Subprograms (98)Methods for Passing Parameters (100)PL/SQL — FUNCTIONS (103)Creating a Function (103)Calling a Function (104)PL/SQL Recursive Functions (106)PL/SQL — CURSORS (108)Implicit Cursors (108)Explicit Cursors (110)Declaring the Cursor (112)Opening the Cursor (112)ivFetching the Cursor (112)Closing the Cursor (112)PL/SQL — RECORDS (114)Table-Based Records (114)Cursor-Based Records (115)User-Defined Records (116)PL/SQL — EXCEPTIONS (120)Syntax for Exception Handling (120)Raising Exceptions (121)User-defined Exceptions (122)Pre-defined Exceptions (123)PL/SQL — TRIGGERS (126)Creating Triggers (126)Triggering a Trigger (129)PL/SQL — PACKAGES (130)Package Specification (130)Package Body (130)Using the Package Elements (131)PL/SQL — COLLECTIONS (138)Index-By Table (139)Nested Tables (141)Collection Methods (144)Collection Exceptions (145)vPL/SQL — TRANSACTIONS (147)Starting and Ending a Transaction (147)Committing a Transaction (147)Rolling Back Transactions (148)Automatic Transaction Control (149)PL/SQL — DATE & TIME (150)Field Values for Datetime and Interval Data Types (150)The Datetime Data Types and Functions (151)The Interval Data Types and Functions (155)PL/SQL — DBMS OUTPUT (157)DBMS_OUTPUT Subprograms (157)PL/SQL — OBJECT-ORIENTED (160)Instantiating an Object (161)Member Methods (161)Using Map method (162)Using Order method (164)Inheritance for PL/SQL Objects (166)Abstract Objects in PL/SQL (168)viPL/SQL7The PL/SQL programming language was developed by Oracle Corporation in the late 1980s as procedural extension language for SQL and the Oracle relational database. Following are certain notable facts about PL/SQL:∙PL/SQL is a completely portable, high-performance transaction-processing language. ∙PL/SQL provides a built-in, interpreted and OS independent programming environment. ∙PL/SQL can also directly be called from the command-line SQL*Plus interface . ∙Direct call can also be made from external programming language calls to database. ∙PL/SQL's general syntax is based on that of ADA and Pascal programming language. ∙ Apart from Oracle, PL/SQL is available in TimesTen in-memory database and IBM DB2.Features of PL/SQLPL/SQL has the following features:∙PL/SQL is tightly integrated with SQL. ∙It offers extensive error checking. ∙It offers numerous data types. ∙It offers a variety of programming structures. ∙It supports structured programming through functions and procedures. ∙It supports object-oriented programming. ∙ It supports the development of web applications and server pages. Advantages of PL/SQLPL/SQL has the following advantages:∙ SQL is the standard database language and PL/SQL is strongly integrated with SQL. PL/SQL supports both static and dynamic SQL. Static SQL supports DML operations and transaction control from PL/SQL block. In Dynamic SQL, SQL allows embedding DDL statements in PL/SQL blocks.PL/SQL — OverviewPL/SQL8∙PL/SQL allows sending an entire block of statements to the database at one time. This reduces network traffic and provides high performance for the applications.∙PL/SQL gives high productivity to programmers as it can query, transform, and update data in a database.∙PL/SQL saves time on design and debugging by strong features, such as exception handling, encapsulation, data hiding, and object-oriented data types.∙Applications written in PL/SQL are fully portable.∙PL/SQL provides high security level.∙PL/SQL provides access to predefined SQL packages.∙PL/SQL provides support for Object-Oriented Programming.∙PL/SQL provides support for developing Web Applications and Server Pages.PL/SQL 9In this chapter, we will discuss the Environment Setup of PL/SQL. PL/SQL is not a stand-alone programming language; it is a tool within the Oracle programming environment. SQL* Plus is an interactive tool that allows you to type SQL and PL/SQL statements at the command prompt. These commands are then sent to the database for processing. Once the statements are processed, the results are sent back and displayed on screen.To run PL/SQL programs, you should have the Oracle RDBMS Server installed in your machine. This will take care of the execution of the SQL commands. The most recent version of Oracle RDBMS is 11g. You can download a trial version of Oracle 11g from the following link: Download Oracle 11g Express EditionYou will have to download either the 32-bit or the 64-bit version of the installation as per your operating system. Usually there are two files. We have downloaded the 64-bit version. You will also use similar steps on your operating system, does not matter if it is Linux or Solaris. ∙win64_11gR2_database_1of2.zip ∙ win64_11gR2_database_2of2.zipAfter downloading the above two files, you will need to unzip them in a single directory database and under that you will find the following sub-directories:Step 1Let us now launch the Oracle Database Installer using the setup file. Following is the first screen. You can provide your email ID and check the checkbox as shown in the following screenshot. Click the Next button.PL/SQL — Environment Setup10Step 2You will be directed to the following screen; uncheck the checkbox and click the Continue button to proceed.11Just select the first option Create and Configure Database using the radio button and click the Next button to proceed.12We assume you are installing Oracle for the basic purpose of learning and that you are installing it on your PC or Laptop. Thus, select the Desktop Class option and click the Next button to proceed.13Provide a location, where you will install the Oracle Server. Just modify the Oracle Base and the other locations will set automatically. You will also have to provide a password; this will be used by the system DBA. Once you provide the required information, click the Next button to proceed.14Again, click the Next button to proceed.15Click the Finish button to proceed; this will start the actual server installation.16This will take a few moments, until Oracle starts performing the required configuration.17Here, Oracle installation will copy the required configuration files. This should take a moment:18Once the database files are copied, you will have the following dialogue box. Just click the OK button and come out.19Upon installation, you will have the following final window.Final StepIt is now time to verify your installation. At the command prompt, use the following command if you are using Windows:You should have the SQL prompt where you will write your PL/SQL commands and scripts:PL/SQL20T ext EditorRunning large programs from the command prompt may land you in inadvertently losing some of the work. It is always recommended to use the command files. To use the command files: ∙Type your code in a text editor, like Notepad, Notepad+, or EditPlus, etc.∙Save the file with the .sql extension in the home directory.∙Launch the SQL*Plus command prompt from the directory where you created your PL/SQL file.∙Type @file_name at the SQL*Plus command prompt to execute your program.If you are not using a file to execute the PL/SQL scripts, then simply copy your PL/SQL code and right-click on the black window that displays the SQL prompt; use the paste option to paste the complete code at the command prompt. Finally, just press Enter to execute thecode, if it is not already executed.PL/SQL21In this chapter, we will discuss the Basic Syntax of PL/SQL which is a block-structured language; this means that the PL/SQL programs are divided and written in logical blocks of code. Each block consists of three sub-parts:Every PL/SQL statement ends with a semicolon (;). PL/SQL blocks can be nested within other PL/SQL blocks using BEGIN and END . Following is the basic structure of a PL/SQL block: PL/SQL — Basic Syntax22The 'Hello World' ExampleThe end; line signals the end of the PL/SQL block. To run the code from the SQL command line, you may need to type/ at the beginning of the first blank line after the last line of the code. When the above code is executed at the SQL prompt, it produces the following result: The PL/SQL IdentifiersPL/SQL identifiers are constants, variables, exceptions, procedures, cursors, and reserved words. The identifiers consist of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs and should not exceed 30 characters.By default, identifiers are not case-sensitive . So you can use integer or INTEGER to represent a numeric value. You cannot use a reserved keyword as an identifier.The PL/SQL DelimitersA delimiter is a symbol with a special meaning. Following is the list of delimiters in PL/SQL:2324The PL/SQL CommentsProgram comments are explanatory statements that can be included in the PL/SQL code that you write and helps anyone reading its source code. All programming languages allow some form of comments.The PL/SQL supports single-line and multi-line comments. All characters available inside any comment are ignored by the PL/SQL compiler. The PL/SQL single-line comments start with the delimiter -- (double hyphen) and multi-line comments are enclosed by /* and */.When the above code is executed at the SQL prompt, it produces the following result:PL/SQL Program UnitsA PL/SQL unit is any one of the following:∙PL/SQL block∙Function∙Package∙Package bodyPL/SQL25∙Procedure∙Trigger∙Type∙Type bodyEach of these units will be discussed in the following chapters.PL/SQL26End of ebook previewIf you liked what you saw…Buy it from our store @ https://。
oracleopatch工具的使用
oracleopatch工具的使用从版开始Oracle公司设计实现了个别补丁安装管理工具opatch opatch使用一个称为inventory的系统数据结构（严格说是与oui共享inventory）集中管理所有已安装的个别补丁个别补丁的安装和卸载都使用opatch命令完成冲突检测也由opatch 在安装时自动完成提供列表命令可以很方便得到已安装个别补丁的信息g（和）版本中 opatch作为一个标准工具在软件安装时自动安装（安装在$ORACLE_HOME/OPatch下）而对于版需要从metalink下载opatch 无论数据库是哪一个版本系统中是否已经安装opatch 在使用之前应从metalink下载最新版本的opatch 很遗憾由于系统实现的问题使用的opatch与之前版本（和）使用的opatch不兼容不能混用这一点必须注意opatch是使用perl编写的脚本程序（其中也使用JAVA API）编程使用的perl版本是版虽然在之前的版本中也可运行但应尽可能安装或以上的版本的perl 对于DBA来说一个好消息是如果安装版软件时保留了HTTP服务器则在$ORACLE_HOME/Apache下会自动安装perl （ g会自动安装配置perl和opatch ）opatch命令格式为opatch < mand > [< mand_options >] [ h[elp] ]命令有apply（安装个别补丁）rollback（卸载个别补丁）lsinventory（对inventory进行列表）query（显示某一个别补丁的详细信息）version（显示opatch 版本信息）在opatch目录下有用户使用指南文件（Users_Guide txt）其中有详细的命令格式和使用示例读者可以参考 Opatch执行操作时除在屏幕输出结果外还生成日志文件日志文件的路径和文件名格式如下$ORACLE_HOME/ patch_storage/< patch_id >/< action > < patch_id >_< mm dd yyyy_hh mi ss> log其中 patch_id 是Oracle技术支持部门为个别补丁分配的编号* 个别补丁安装实例沿用安装PSR实例中的环境在安装PSR 后检索metalink 发现若干在其之上的个别补丁选择其中之一安装个别补丁Patch 修复BUG 这一BUG的主要问题是TNS LISTENER 在注册ONS（OracleNotification Services）的同时如果创建子进程那么LISTENER会挂起（HANGUP）安装时首先从metalink下载补丁的压缩文件p _ _LINUX zip 将此文件解压缩至某一目录中解压缩后这一补丁的所有文件都在子目录下目录名就是个别补丁的补丁号 opatch依据目录名获得信息所以一定不要重命名子目录然后在终端窗口中执行cd命令移动到子目录中执行以下命令$ $ORACLE_HOME/OPatch/opatch apply对inventory列表确认安装操作$ $ORACLE_HOME/OPatch/opatch lsinventory执行卸载命令时也必须使子目录成为当前目录其中 Rollback命令需要两个参数 id给出个别补丁号 ph 给出个别补丁解压缩后的路径$ $ORACLE_HOME/OPatch/opatch rollback id ph /…/随后再对inventory列表则会看到这一个别补丁已经被移去* 使用opatch显示已安装的版本信息不需要启动数据库执行加选项的对inventory的列表命令可以得到已安装的软件的各个组件的详细版本信息$ $ORACLE_HOME/OPatch/opatch lsinventory detail安全补丁CPU一个CPU内包含了对多个安全漏洞的修复并且也包括相应必需的非安全漏洞的补丁 CPU是累积型的只要安装最新发布的CPU即可其中包括之前发布的所有CPU的内容事实上在CPU之前的安全漏洞修改除去个别例外也被包括在CPU中Oracle公司只对处于标准技术支持和延长支持期间的产品提供CPU更新对处于维持支持范围的产品不提供新的CPU （对于以前的版本只对处于ECS和EMS期间的版本提供CPU更新）一般对当前补丁发行版及前一个版本提供CPU 但也有只限于当前补丁发行版的例外情形也就是说一般需要先安装最新PSR后才可能安装CPU 由于是累积型的定期发布所以对于某一平台的某一版本如果两次CPU发布期间没有发现新的安全漏洞则新发布的CPU与前一版本完全相同在以下网址中可以找到CPU发布的信息但是很遗憾只有技术支持签约用户才可以从metalink下载补丁文件Oracle公司制定的CPU的发布日期大约在一月四月七月和十月的最接近的星期二对于每一个CPU 附有相应的说明文档（Critical Patch Update Note）其中介绍安装过程和注意事项在安装之前应认真阅读此文档同样也存在文档Oracle Critical Patch Update MM YYYY Known Issuesfor Oracle Database 其中列出了说明文档中没有给出的新信息在安装时首先下载压缩文件p _ _LINUX zip 解压缩到与其它个别补丁相同的目录下检查其发行说明时发现要求opatch版本比现已安装版本要高下载安装指定版本opatch 进入子目录（这是此安全补丁的补丁号）执行apply命令$ $ORACLE_HOME/OPatch/opatch apply打开此次安装生成的日志文件其中没有错误信息出现执行inventory列表命令确认安装$ $ORACLE_HOME/opatch lsinventoryopatch作用opatch是用于维护个别补丁的有人称其为interim path或是one off patch以解决TA ENQ AND US ENQ DEADLOCK DURING TRANSACTION RECOVERY IN RAC问题的interim patch 为例安装及rollback的命令如下install:oracle>unzip p _ _HP ziporacle>cdoracle>opatch applyrollback:oracle>cdoracle>opatch rollback id查看如下命令可以看到已经安装了哪些interim patch[oracle@mysql ~]$ /u /app/oracle/product/ /db_ /OPatch/opatch lsinventory不断的在opatch后面加 h可以看到这个命令更加详细的选项看一下lsinventory后面有哪些选项[oracle@mysql ~]$ /u /app/oracle/product/ /db_/OPatch/opatch lsinventory h环境介绍XXXX综合管理信息系统主机放在XXXX 数据库平台硬件架构如下数据库主机两台IBMp （jcczxt jcczxt ）安装AIX L ML 通过IBM HACMP构成双机热备数据库平台 Oracle Server Enterprise Edition Version:故障描述年月号上午值班人员接到用户反应应用出错无法存盘前台出现的错误信息如下ORA IO error reading block from file (block # )ORA : data file : /oradata/ *** is dbfORA : skgfqio :unable to quere I/OORA : skgfdisp : I/O errorIBM AIX RISC System/ Error : : I/O error此时数据库管理员重新启动数据库前台用户反应应用可能正常运行问题解决数据库管理员在检查主机日志时没有发现任何硬件故障问题在检查数据库日志时也没有任何错误提示用Oracle的实用程序DBV对数据库文件进行检查没有发现坏块年月日下午用户又报同样的错误此时DBA重新启动数据库和应用问题解决 : 用户又报同样的 DBA重新启动数据库和应用问题解决故障的定位第一步通过errpt检查主机的日志确认主机没有硬件问题磁盘没有损坏第二步通过检查数据库的日志文件(alertSID log)也没有相关的错误提示初步判定不是数据库的问题第三步通过Oracle的实用程序DBV对数据库文件进行检查没有发现坏块此时可以确认不是硬件问题是软件的问题第四步综合管理信息系统是年月号上线一直都稳定运行没有出现过类似的情况可以排除是应用程序的问题第五步通过上metalink网站查询与ORA 相关的信息发现有大量的资讯其中的故障描述与我们的情况一致由于在AIX L的操作系统上Oracle由于无法获取正确的data block头信息从而导致DBWn进程写硬盘失败 Oracle数据库从到都有此问题是由于Oracle的BUG导致需要打一个Oracle的临时补丁此时问题可以定位为oracle在AIX L平台上的BUG 需要加载一个Oracle的临时补丁patchOracle术语解释什么叫interim patch ?Interim patch是一次性的补丁针对特定的情况下触发的Bug 没有经过Oracle系统的认证Oracle会根据Bug的严重性和需要在下一次的patch set中包含此Interim patch Interim patch对环境和版本有特定的要求即不同操作系统平台和数据库版本的会有不同的Interim patch 如Interim patch 会什么叫Opatch?Opatch是oracle支持的Oracle临时补丁的安装实用程序是针对Oracle数据库第二版后安装临时补丁 Opatch是一个基于Perl的程序如何安装Opatch?从metalink上选择补丁程序选择简单搜索输入patch号选择相应的操作系统平台查询结果会显示所有可用版本的optach 下载版本号就行将下的压缩文件上传到数据库主机将压缩文件*** 到$ORACLE_HOME目录下用unzip工具解压此压缩文件具体命令如下#cp /tmp/p _ _AIX L zip $ORACLE_HOME#chown oracle:dba $ORACLE_HOME/p _ _AIX L zip以oracle用户解压此文件$ cd $ORACLE_HOME$unzip p _ _AIX L zip解决 ***)从metalink上选择补丁程序选择简单搜索输入patch号选择平台为IBM AIX Based System( bit) 下载Oracle 对应的patch大小为K)将patch 上传到数据库主机(host ))在jcczxt 主机上加载此patch因综合管理信息系统采用双机热备方式所以备机(host )没有数据库应用所以我们可以在不停下数据库的情况下加载patch 详细步骤如上以oracle数据库用户执行下面的命令$cp /tmp/p _ _AIX L zip /oracle/patch$cd /oracle/patch$unzip p _ _AIX L zip$cd$$ORACLE_HOME/Opatch/opatch install提示要求输入metalink的帐号和密码可能随便输入提示 Please shut down Oracle instances running out of this ORACLE_HOME 输入 Y此时oracle会开始安装patch 因为要重新链接lib 所以要花几分钟时间在安装完毕后会出现 OPatch succeeded 提示)将数据库切换到jcczxtlishixinzhi/Article/program/Oracle/201311/18781。
(Oracle管理)ORACLE数据库sqlplus使用技巧
Sql*plus中蕴藏着好多技巧，如果掌握这些技巧，对于在oracle数据库下进行快速开发与有效维护数据库都是有益的。
1．使用SQL*PLUS动态生成批量脚本将spool与select命令结合起来使用，可以生成一个脚本，脚本中包含有可以批量执行某一任务的语句。
例1：生成一个脚本，删除SCOTT用户下的所有的表：a. 创建gen_drop_table.sql文件，包含如下语句：SPOOL c:\drop_table.sqlSELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;SPOOL OFFb. 以SCOTT用户登录数据库SQLPLUS > @ …..\gen_dorp_table.sqlc. 在c盘根目录下会生成文件drop_table.sql文件，包含删除所有表的语句，如下所示:SQL>SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;'DROPTABLE'||TABLE_NAME||';'------------------------------------DROP TABLE DEPT;DROP TABLE EMP;DROP TABLE PARENT;DROP TABLE STA T_VENDER_TEMP;DROP TABLE TABLE_FORUM;5 rows selected.SQL>SPOOL OFFd. 对生成的drop_table.sql文件进行编辑去掉不必要的部分，只留下drop table …..语句e. 在scott用户下运行dorp_table.sql文件，删除scott用户下所有的表。
SQLPLUS > @ c:\dorp_table.sql在上面的操作中，在生成的脚本文件中会有多余的字符，如运行的sql语句，标题，或返回的行数，需要我们编辑该脚本后再运行，给实际的操作带来诸多不便。
Oracle的SQLPLUS命令的使用大全 (详解)
sql>select * from dept;
DEPTNO DNAME location
--------- ---------------------------- -----------
10 ACCOUNTING NEW YORK
2). 将列名ENAME改为新列名EMPLOYEE NAME并将新列名放在两行上：
Sql>select * from emp
Department name Salary
---------- ---------- ----------
10 aaa 11
SQL> COLUMN ENAME HEADING ’Employee|Name’
Sql>select * from emp
Employee
Department name Salary
---------- ---------- ----------
10 aaa 11
该命令包含许多子命令：
SET system_variable value
system_variable value 可以是如下的子句之一：
APPI[NFO]{ON|OFF|text}
ARRAY[SIZE] {15|n}
AUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n}
5). 不让一个列显示在屏幕上
NOPRI[NT]|PRI[NT]
SQL> col job noprint
SQL> /
EMPNO ENAME
---------- ----------------------------------------
oracle数据库操作手册
操作手册目录一．表空间 (4)1。
创建表空间 (4)2.增加表空间 (4)3。
删除表空间 (5)4.查询表空间状态 (5)5.查询数据文件路径 (5)6。
移动表空间中数据文件的路径 (5)7.移动表和索引到其他表空间 (6)8.查看表空间的使用率 (7)二．用户和权限 (9)1.创建用户 (9)2。
修改用户的密码 (9)3。
给用户授权 (9)4. 查询数据库系统上有多少用户,文件名和创建时间 (10)三．归档和非归档模式 (10)1。
查看数据库的归档模式 (10)2。
修改数据库的归档模式 (10)四．日志文件 (11)1。
查询日志文件信息 (11)2.增加日志文件配置信息 (12)3.增加日志成员 (12)4。
删除一组日志 (12)五．密码文件 (13)1。
创建密码文件 (13)六．参数文件（SPFILE PFILE） (13)1。
查看数据库使用参数文件(SPFILE 还是PFILE） (13)2.创建SPFILE (13)3。
通过PFILE 启动数据库 (13)七．STATSPACK (14)1.安装STATSPACK (14)2。
数据采集 (14)3.设置自动快照 (14)4。
设置数据采集的时间 (14)八．ORACLE信息查询 (15)1.查询ORACLE数据库的名字，创建日期 (15)2. 查询ORACLE所在操作系统的主机名，实例名,版本 (15)3.查询ORACLE数据库系统版本详细信息 (15)九．控制文件 (16)1.查询控制文件 (16)2。
备份控制文件 (16)十．索引 (16)1。
创建普通索引 (16)2。
创建位图索引 (16)3.查询索引所在的表，表空间，索引类型 (16)4.查询索引所在的列 (17)十一。
主键 (18)1.定义主键 (18)2.查询主键索引 (18)3.查询约束信息 (18)4.禁止约束 (19)5。
开启主键 (19)十二。
手工建库脚本 (19)十三.。
OracleASCP操作手册
追求至善凭技术开拓市场，凭管理增 创效益 ，凭服 务树立 形象。2 020年1 0月15 日星期 四上午9 时39分 53秒09 :39:532 0.10.15
严格把控质量关，让生产更加有保障 。2020 年10月 上午9时 39分20 .10.150 9:39Oc tober 15, 2020
作业标准记得牢，驾轻就熟除烦恼。2 020年1 0月15 日星期 四9时39 分53秒 09:39:5 315 October 2020
4 PO Reschedule In/Out
5 Create Job
6 Job Reschedule In/Out
2020/10/15
29
MSC Report (ASCP Planning Analysis(NEW) 内容： 所有掛入系統的Forecast\Sales order\Forecast
備份完畢，檢查是否備份成功，回到workbench
2020/10/15
26
Backup 8005 PROD
2020/10/15
27
Backup 8005 PROD
已存在20-Mar的日期，證明備份成功
2020/10/15
28
1
Backup 8005 PROD
2 MSC Report
3 ASCP Create PO
人生得意须尽欢，莫使金樽空对月。0 9:39:53 09:39:5 309:39 10/15/ 2020 9:39:53 AM
安全象只弓，不拉它就松，要想保安 全，常 把弓弦 绷。20. 10.1509 :39:530 9:39Oc t-2015 -Oct-2 0
加强交通建设管理，确保工程建设质 量。09: 39:5309 :39:530 9:39Th ursday, October 15, 2020
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
ORAPS 配置与操作说明
ORAPS JUMP 文档
概述.............................................................................................................................. 10
物料配置 Material Configuration ................................................................................................. 21 物料基本属性 .................................................................................................................................... 21
ORAPS JUMP 文档
字段说明 ........................................................................................................................................ 21 速度矩阵配置说明 ........................................................................................................................ 22 物料规格 ............................................................................................................................................ 23 字段说明 ........................................................................................................................................ 23 BOM .................................................................................................................................................. 23 字段说明 ........................................................................................................................................ 23
WEB 界面基本操作说明 ............................................................................................ 24
表格(Web Table) ............................................................................................................................. 24 分页显示 ............................................................................................................................................ 24 导入、导出 ........................................................................................................................................ 25 添加、删除、修改 ............................................................................................................................ 26 添加................................................................................................................................................ 27 查询工具 ............................................................................................................................................ 28 快速查询 ........................................................................................................................................ 28 通用查询 ........................................................................................................................................ 28 输入辅助 ............................................................................................................................................ 31 日期选择 ........................................................................................................................................ 31 下拉选择 ........................................................................................................................................ 32 自动完成功能 ................................................................................................................................ 32
配置说明...................................................................................................................... 10
生产配置 Production Resource Configuration............................................................................ 10 名词解释 Glossary........................................................................................................................... 10 配置顺序 Order of Configuration ................................................................................................. 13 优化说明 ............................................................................................................................................ 13 关于优化目标的说明 .................................................................................................................... 14 工段 Work Section .......................................................................................................................... 15 字段说明 ............................................................................................................................................ 15 机器组 Machine Group .................................................................................................................. 16 字段说明 ........................................................................................................................................ 16 配置逻辑说明 ................................................................................................................................ 18 机器 Machine .................................................................................................................................. 18 字段说明 ........................................................................................................................................ 18 生产速度的逻辑 ................................................................................................................................ 19