tag:blogger.com,1999:blog-91921796811623461392024-09-19T21:31:01.526+08:00Tips for Oracle DBAOracle Database Administration, How To, Daily DBA jobs, Oracle Error Cause and ActionUnknownnoreply@blogger.comBlogger9724125tag:blogger.com,1999:blog-9192179681162346139.post-85137288650382422582022-02-25T12:15:00.000+08:002012-11-06T11:54:42.646+08:00MenuOracle Database AdministrationUndo Data Management
Changing Database To Archive Log Mode
Making Oracle Database Autostart
Find Cause And Solution For Oracle Error With oerr
Increase Concurrency By Indexing Foreign Key
Global Login Script - glogin.sql
Configure Oracle LISTENER
Always Test Your Patch File
Optimal Flexible Architecture (OFA)
Oracle Initialization File SPFILE and PFILE
Oracle Unknownnoreply@blogger.comtag:blogger.com,1999:blog-9192179681162346139.post-11224134646677207162015-12-17T18:15:00.000+08:002015-12-17T18:15:00.277+08:00Tablespaces in multitenancyNote:In a non-CDB, all the tablespaces belong to one database. In the CDB, one set of tablespaces belong to the root container, and each PDB has its own set of tablespaces. Common objects are created and their data stored in a tablespace in the root container. The common object is visible in the PDBs through links. There are new clauses in the CREATE DATABASE command. The USER_DATAUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-35996338686330564032015-12-17T15:38:00.000+08:002015-12-17T15:38:00.627+08:00Using glogin.sql to have friendly and nice SQLPLUS multitenancy environmentWhen we are working at multitenancy it is good to have glogin configured to show container info. I am having following glogin.sql.You can find the glogin.sql file in $ORACLE_HOME/sqlplus/admincolumn id_plus_exp format 990 heading icolumn parent_id_plus_exp format 990 heading pcolumn plan_plus_exp format a60column object_node_plus_exp format a8column other_tag_plus_exp format a29column Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-76141788221849749912015-12-16T16:28:00.000+08:002015-12-16T16:28:01.038+08:00Cost Based Optimizer (CBO) Parameters SettingThe parameter that control the optimizer behaviors are:CURSOR_SHARINGdetermines what kind of SQL statements can shared the same cursors. The possible values you can use are:FORCEFORCE, forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement. SIMILARSIMILAR causes statements that may differ in some Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-89510950222323508912015-12-15T00:42:00.000+08:002015-12-15T00:42:00.665+08:00SQL Tuning ToolsSeveral tuning tools to detect and rectify SQL errors and performance issueAutomatic Database Diagnostic MonitorThe Automatic Database Diagnostic Monitor (ADDM) continually analyzes the performance data that is collected from the database instance. SQL Tuning AdvisorThe SQL tuning advisor analyzes SQL statements that have been identified as problematic, in an effort to retune them. By default, Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-25458472438272061042015-12-13T16:33:00.000+08:002015-12-13T16:33:00.500+08:00General Database Tuning TaskMany SQL Tuning tasks should be performed on a regular basis. You may see a way to rewrite a WHERE clause, but it may depend on a new index being built. The following is a list of tasks that gives you a background of some important tasks that must be performed, and gives you an idea of what dependencies you may have as you tune SQL:1. Identifying high-load SQL statementsIdentifying high-load SQL Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-48563674561970567952015-12-13T14:54:00.000+08:002015-12-13T14:54:00.617+08:00Poor performance SQL Statements SQL statements can perform poorly for a variety of reason:1. Stale optimizer statisticsSQL execution plans are generated by the cost-based optimizer (CBO). For CBO to effectively choose the most efficient plan, it need accurate information on the data volume and distribution of tables and indexes referenced in the queries. Without accurate optimizer statistics, the CBO can be easily mislead and Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-3449197512729422372015-12-13T13:55:00.000+08:002015-12-13T13:55:00.145+08:00What is PGA (Program Global Area)PGAs are memory regions that contain data and control information about a server or background process. A PGA is suballocated from the aggregated PGA area. Only a server process can access the PGA.The content of the PGA memory varies, depending on whether or not the instance runs the shareed server option, Generally, the PGA memory divided into session memory, cursors, and SQL workarea.Session Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-42387747315796614802015-12-10T17:57:00.000+08:002015-12-10T17:57:00.543+08:00Automatically Open PDBs When CDB startedTo automatically open PDB when CDB is started, we can use trigger at CDB level.1. Login to CDB rootSQL> show con_nameCON_NAME------------------------------CDB$ROOTSQL> CREATE TRIGGER open_pdb3 2 AFTER STARTUP ON DATABASE 3 BEGIN 4 EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE pdb3 OPEN'; 5 END open_pdb3; 6 /Trigger created.2. Test itUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-89376334375186888352015-12-08T04:20:00.000+08:002015-12-08T04:20:00.860+08:00Install Oracle 12c Client/Database on Windows [INS-30131]I need to upgrade all the clients station to 12c, however I encountered following error[INS-30131] Initial setup required for the execution of installer validations failed When I click on detailCause - Failed to access the temporary location.Action: Ensure that the current user has required permissions to access the temporary location. This error occur when I try to install Oracle 12c for Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-77623739464058026152015-12-04T15:36:00.000+08:002015-12-04T15:36:00.646+08:00List All Users CreatedThere are two types of users as following:Local UserA user in a non-CDB maps to a local user in a PDB.A local user is defined in the PDB’s own data dictionary—and so is not known outside ofthat PDB.A local user can connect only to the PDB where it is defined.A local user is specific to a particular PDB and owns a schema in this PDB.According to the privileges granted, a user can work on the Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-79964719092094552502015-12-04T10:46:00.000+08:002015-12-04T10:46:00.085+08:00Oracle 12c Data Dictionary Views CDB_xxx, DBA_xxx, ALL_xxx, USER_xxxIn a CDB, for every DBA_* view, a CDB_* view is defined.In the root, CDB views can be used to obtain information about tables, tablespaces, users, privileges, parameters, PDBs and other types of objects contained in the root and all PDBs.In a PDB, the CDB_* views show objects visible through a corresponding DBA_* view only.In addition, to all the columns found in a given DBA_* view, the Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-36015988694405137602015-12-01T20:43:00.000+08:002015-12-01T20:43:00.845+08:00Create PDB from existing PDB (Clone PDB)1. Connect to CDB as root[oracle@ora12cn1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.1.0.1.0 Production on Sun Dec 1 19:12:07 2013Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Advanced Analytics and Real Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-24004946121910002352015-11-26T17:37:00.000+08:002015-11-26T17:37:00.315+08:00Create PDB from PDB$SEED (appropriate PDB directory)This is an extension example from previous post of Create PDB from PDB$SEEDThe objective is to convert the data files and temp files to pdb folder appropriately.Previous example result is as followCheck the data and temp files SQL> alter session set container=pdb2; Session altered.SQL> show con_nameCON_NAME------------------------------PDB2SQL> select file_name from dba_data_files;Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-56749053232263049032015-11-25T23:27:00.000+08:002015-11-25T23:27:00.247+08:00Drop Pluggable DatabaseTo remove pluggable database permanently from CDB can use following command:1. Connect to root and close the pluggable database[oracle@ora12cn1 Desktop]$ sqlplus / as sysdbaSQL*Plus: Release 12.1.0.1.0 Production on Mon Nov 25 23:21:29 2013Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-62589210753714399282015-11-25T17:52:00.000+08:002015-11-25T17:52:00.209+08:00Create PDB from PDB$SEEDThe creation of a new PDB from the seed is nearly instantaneous. The operation copies thedata files from the READ ONLY seed PDB to the target directory defined in the CREATEPLUGGABLE DATABASE statement.It creates tablespaces such as SYSTEM to store a full catalog including metadata pointing toOracle-supplied objects, SYSAUX for local auxiliary data.It creates default schemas and common users thatUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-9616408980049901252015-11-23T01:38:00.000+08:002015-11-23T01:38:00.797+08:00Understand Server PoolWhen Oracle Clusterware is installed, two server pools are created automatically: Generic and Free. All servers in a new installation are assigned to the Free server pool, initially. Servers move from Free to newly defined server pools automatically.The Free Server PoolThe Free server pool contains servers that are not assigned to any other server pools. The attributes of the Free server pool areUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-11586136426881064702015-11-22T01:24:00.000+08:002015-11-22T01:24:00.063+08:00To View 12c TablespacesTo view tablespace and it's corresponding containerSQL> col name form a12SQL> SELECT file#, tbs.name, tbs.ts#, tbs.con_id 2 FROM v$datafile dbf, v$tablespace tbs 3 WHERE dbf.ts#=tbs.ts# 4 AND dbf.con_id=tbs.con_id; FILE# NAME TS# CON_ID---------- ------------ ---------- ------ 1 Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-23698123842067798432015-11-22T00:43:00.000+08:002015-11-22T00:43:00.128+08:00All Files of the CDBTo view logfile that we can query v$logfile, we can see the con_id. In this example I am using ASM +DATA volume.SQL> col member format a50SQL> select GROUP#, CON_ID, MEMBER from v$logfile; GROUP# CON_ID MEMBER---------- ---------- -------------------------------------------------- 1 0 +DATA/ORA12C/ONLINELOG/Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-9517830990911242402015-11-22T00:32:00.000+08:002015-11-22T00:32:00.397+08:00To Know Your Current Connected ContainerYou can use SHOW CON_NAME and CON_ID to know which container you are connected to.SQL> show con_nameCON_NAME------------------------------CDB$ROOTSQL> show con_idCON_ID------------------------------1Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-85087515581213366402015-11-22T00:06:00.000+08:002015-11-22T00:06:00.135+08:00To Find The Pluggable DatabasesTo find PDB within CDB, we can query v$pdbs, in this example I have 1 PDB namely QUOTESQL> select con_id, name, open_mode from v$pdbs;CON_ID NAME OPEN_MODE---------- ------------------------------ ---------- 2 PDB$SEED &Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-40193359862897671002015-11-21T19:26:00.000+08:002015-11-21T19:26:00.173+08:00Explore Oracle 12c Services In RACTo check the services via Oracle Listener[oracle@ora12cn1 admin]$ lsnrctl statusLSNRCTL for Linux: Version 12.1.0.1.0 - Production on 21-NOV-2013 22:23:08Copyright (c) 1991, 2013, Oracle. All rights reserved.Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))STATUS of the LISTENER------------------------Alias Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-41646527317238895872015-11-21T15:16:00.000+08:002015-11-21T15:16:00.250+08:00Connect To Oracle 12c CDB In RAC EnvironmentDB Name = ora12cInstance Name = ora12c_1 (node 1) and ora12c_2 (node 2)To connect to the multitenant container database ora12c instrance ora12c_1.Execute . oraenv to switch to the appropriate system variable (ORACLE_SID and ORACLE_HOME) or you can set it manually.[oracle@ora12cn1 ~]$ . oraenvORACLE_SID = [ora12c_1] ? ora12c_1The Oracle base remains unchanged with value /u01/app/oracle[oracleUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-42903466799779701222015-11-21T00:36:00.000+08:002015-11-21T00:36:00.706+08:00Explore 12c CDB Background ProcessesTo explore the ora12c instance within 12c 2-nodes RAC node 1, the background processes and the multitenant container database.[oracle@ora12cn1 db_1]$ ps -ef |grep ora12coracle 3248 1 0 Nov20 ? 00:00:01 ora_pmon_ora12c_1oracle 3250 1 0 Nov20 ? 00:00:01 Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-9192179681162346139.post-85193556863665742032015-09-27T14:35:00.000+08:002015-09-27T14:34:59.994+08:00ORA-19174: FORX0001: invalid regular expression flagsORA-19174: FORX0001: invalid regular expression flags
Cause:
An error occurred during the processing of the XQuery expression.
Action:
Check the detailed error message for the possible causes.
Unknownnoreply@blogger.com0