1. 概述 最近论坛很多人提的问题都与行列转换有关系，所以我对行列转换的相关知识做了一个总结，希望对大家有所帮助，同时有何错疏，恳请大家指出，我也是在写作过程中学习，算是一起和大家学习吧！ 行列转换包括以下六种情况： 1) 列转行 2) 行转列 3) 多列转换成字符串 4) 多行转换成字符串 5) 字符串转换成多列 6) 字符串转换成多行 下面分别进行举例介绍。 首先声明一点，有些例子需要如下10g及以后才有的知识： A. 掌握model子句 B. 正则表达式 C. 加强的层次查询 讨论的适用范围只包括8i,9i,10g及以后版本。 2. 列转行 CREATE TABLE t_col_row( ID INT, c1 VARCHAR2(10), c2 VARCHAR2(10), c3 VARCHAR2(10)); INSERT INTO t_col_row VALUES (1, 'v11', 'v21', 'v31'); INSERT INTO t_col_row VALUES (2, 'v12', 'v22', NULL); INSERT INTO t_col_row VALUES (3, 'v13', NULL, 'v33'); INSERT INTO t_col_row VALUES (4, NULL, 'v24', 'v34'); INSERT INTO t_col_row VALUES (5, 'v15', NULL, NULL); INSERT INTO t_col_row VALUES (6, NULL, NULL, 'v35'); INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL); COMMIT; SELECT * FROM t_col_row; 2.1 UNION ALL 适用范围：8i,9i,10g及以后版本 SELECT id, 'c1' cn, c1 cv FROM t_col_row UNION ALL SELECT id, 'c2' cn, c2 cv FROM t_col_row UNION ALL SELECT id, 'c3' cn, c3 cv FROM t_col_row; 若空行不需要转换，只需加一个where条件， WHERE COLUMN IS NOT NULL 即可。 2.2 MODEL 适用范围：10g及以后 SELECT id, cn, cv FROM t_col_row MODEL RETURN UPDATED ROWS PARTITION BY (ID) DIMENSION BY (0 AS n) MEASURES ('xx' AS cn,'yyy' AS cv,c1,c2,c3) RULES UPSERT ALL ( cn[1] = 'c1', cn[2] = 'c2', cn[3] = 'c3', cv[1] = c1[0], cv[2] = c2[0], cv[3] = c3[0] ) ORDER BY ID,cn; 2.3 COLLECTION 适用范围：8i,9i,10g及以后版本 要创建一个对象和一个集合： CREATE TYPE cv_pair AS OBJECT(cn VARCHAR2(10),cv VARCHAR2(10)); CREATE TYPE cv_varr AS VARRAY(8) OF cv_pair; SELECT id, t.cn AS cn, t.cv AS cv FROM t_col_row, TABLE(cv_varr(cv_pair('c1', t_col_row.c1), cv_pair('c2', t_col_row.c2), cv_pair('c3', t_col_row.c3))) t ORDER BY 1, 2;
My all-time favorite presentation is called "All
About Binds." It takes me about three hours to do the entire talk--which
covers performance, memory utilization, scalability, security, bind
mismatches, bind variable peeking, and cursor sharing--from start to
finish.
I have a query that runs slowly (it takes
about two minutes). So what do I do? I turn on tracing (SQL_TRACE=TRUE)
before running the query, and consistently, 100 percent of the time,
without my changing anything else, when I enable tracing the query
comes back instantly!
This message on Ask Tom shows exactly what I
demonstrate in the "All About Binds" presentation, during which I say,
"I'm going to show you how to tune by setting SQL_TRACE=TRUE. You are
probably all thinking that I'm going to run a query, observe it running
slowly, trace it, and then tune it. Well, I'm not going to do that--all
I'm going to do is set SQL_TRACE=TRUE, and you'll observe that the
query performance and resource utilization are dramatically
affected--all for the better!"
I know exactly what is happening in the Ask Tom scenario--it is a combination of two things:
When you set SQL_TRACE=TRUE, you set up a
new "parse environment" (a made-up term). You have changed your session
in such a way that it will not
share any existing SQL that was not parsed with SQL_TRACE enabled. So
it is highly likely that you will either hard-parse a new version of
the query or use some existing child cursor that is different from the
one you would use with SQL_TRACE disabled.
Bind variable peeking happens at hard-parse time and may affect the plan chosen by the optimizer.
Checked for relevance on 12-May-2009 PURPOSE -------
This document has been created to explain the new Dataguard Configuration parameters that were added in Oracle 10G. The Dataguard configuration and db_unique_name parameters have been added to remove some complexities, and limitations inherent in previous versions of DataGuard.
SCOPE & APPLICATION -------------------
For use by Customers and Support analysts in understanding and using the new features.
Dataguard Configuration: LOG_ARCHIVE_CONFIG and VALID_FOR in Oracle 10G ------------------------------------------------------------------------
The Data Guard Configuration feature allows the user to optionally identify the current database as well as all the other databases in the configuration using the new LOG_ARCHIVE_CONFIG parameter. When used, the LOG_ARCHIVE_CONFIG parameter defines the list of databases in this configuration. Log transport will not be allowed to any database not on the list .
Each database in the configuration will have a db_unique_name and the configuration can include 1 or more db_unique_names.
To set this up you will assign each database a unique name, example:
db_unique_name = 'Chicago_Sales' # Note this parameter is not dynamic
You will then add all the databases you want to be in this configuration, example:
In this Simple Configuration we will only allow log transport between these 2 databases, uniquely identified as: Chicago_Sales and Denver_Sales
Following this you can now specify the db_unique_name for each log archive destination. This will be used along with the VALID_FOR parameter to determine what logs to transport dependent on the database's current ROLE in the configuration. The advantage of this feature is that we can switchover without having to change parameters to defer/enable destinations.
Is should be noted that although the DG_CONFIG attribute is an optional attribute of the optional LOG_ARCHIVE_CONFIG initialization parameter, it must be set to enable the dynamic addition of a standby database to a Data Guard configuration that has a Real Application Clusters primary database running in either maximum protection or maximum availability mode.
How does the VALID_FOR Parameter Work ? ---------------------------------------
The VALID_FOR Parameter states that a specific log transport destination is VALID_FOR only when this database is in a certain ROLE (STANDBY/PRIMARY).
In this way you can set up a transport destination that will only be enabled when this db_unique_name is in a specific ROLE.
The default setting is ..
VALID_FOR=(ALL_LOGFILES, ALL_ROLES)
That setting will state that the log_archive_dest_n is valid for online redo log and standby redo log archival. It is also Valid if the database is currently in PRIMARY or STANDBY ROLE.
More simply put: always archive to this destination.
The full syntax for a local archive destination would be like this..
The real use of this parameter is to dynamically enable/defer destinations dependent on whether the database is mounted in the PRIMARY or STANDBY ROLE.
So we would only ship redo to Denver_Sales if Chicago_sales is running in the PRIMARY ROLE. Clearly if you were to Switchover and Chicago_Sales would take on the STANDBY_ROLE then this destination would become deferred dynamically.
What views show the Setup ? ---------------------------
The view V$ARCHIVE_DEST has new columns to handle the new values.
SQL> column DEST_NAME format A19 SQL> column DB_UNIQUE_NAME format A15 SQL> column VALID_NOW format A9 SQL> column VALID_TYPE format A15 SQL> column VALID_ROLE format A15 SQL> select DEST_NAME,DB_UNIQUE_NAME,VALID_NOW as CURRENT,VALID_TYPE,VALID_ROLE from V$ARCHIVE_DEST WHERE DB_UNIQUE_NAME <> 'NONE';
This article describes similarities and differences between different
features available for high availability and distributed systems.
It contains comparison between multiple features in matrix format
It would help users to select feature useful and feasible in their environment.
Scope and Application
This article is meant for DBA and Support Engineers. It does not require in-depth technical knowledge in any of these features.
The article contains overview of covered topics and not technical details/steps.
Comparison Between Features : RAC, Dataguard, Streams, Advance Replication and Basic Replication
Features
Real Application Clusters (RAC)
Dataguard
Streams
Advance Replication
Basic Replication
Topic of Comparison
Physical Standby
Logical Standby
Multi-Master Replication
Updatable Materialized
Views
Read-only Materialized
Views
Description
Allows the Oracle database to run
applications across a set of clustered servers.
Provides a physically identical
copy of the primary database, with on disk database structures that are
identical to the primary database on a block-for-block basis.
Contains the same logical
information as the production database, although the physical organization
and structure of the data can be different.
Enables information sharing in
form of stream of messages. Enables the propagation and management of data,
transactions, and events in a data stream.
(Also called peer-to-peer or n-way
replication)Enables multiple sites,
acting as equal peers, to manage groups of replicated database objects.
Provides complete or partial copy
(replica) of a target table from a single point in time. Enable users to work
on a subset of a database while disconnected from the central database
server.
Provides complete or partial
read-only copy (replica) of a target table from a single point in time.
Enable users to view a subset of a database while disconnected from the
central database server.
Purpose
- High availability
- Scalability
- Redundancy during failures
- Data protection
- Disaster recovery
- High availability
- Data protection
- Disaster recovery
- Efficient use of redundant
hardware
- Data distribution
- Data sharing
- Data sharing
- Data distribution
- Sharing subset of data with
update access.
- Data distribution
- Sharing subset of data in
read-only mode.
Hardware
All nodes must have hardware that
runs same OS.
All sites must have hardware that
runs same OS.
All sites must have hardware that
runs same OS.
Servers with different hardware
can be used.
Servers with different hardware
can be used.
Servers with different hardware
can be used.
Servers with different hardware
can be used.
OS
Same OS on all nodes including
Patchset release
Same OS. Patchset release can be
different in different sites.
Same OS. Patchset release can be
different in different sites.
Can be used with different OS
Can be used with different OS
Can be used with different OS
Can be used with different OS
Oracle Software
Same version on all nodes
including Oracle Patchset release
Same version on all nodes
including Oracle Patchset release
Same version on all nodes
including Oracle Patchset release
Can be used with different Oracle
versions
Can be used with different Oracle
versions
Can be used with different Oracle
versions
Can be used with different Oracle
versions
Feature Specific Terms
Servers involved in RAC
configuration are generally known as Nodes. High-speed link between nodes is
called Interconnect.
Primary and Standby database
servers are generally known as Primary Site and Standby Site respectively.
Primary and Standby database
servers are generally known as Primary Site and Standby Site respectively.
Database from where changes are
captured is called source or capture site. Database from where changes are
applied is called destination or apply site.
One database where Replication
Administrative activities can be performed is called Master Definition Site.
All other replicated databases are called Master sites.
Database with Master table is
called Master Site or Target Site. Database where Materialized view is
created is called Materialized view site.
Database with Master table is
called Master Site or Target Site. Database where Materialized view is
created is called Materialized view site.
Database
Multiple instances linked by
interconnect to share access to an Oracle database.
One production database and one or
more physical standby databases.
One production database and one or
more logical standby databases.
Data stream can propagate data
either within a database or from one database to another.
Complete copy of replicated table
is maintained in multiple databases.
Updatable Materialized view is
created in database other than the one containing master table.
Read-only materialized view can be
created either within a database or in another database.
Data Storage
Single database on shared storage.
Primary site and Standby sites
have their own database.
Primary site and Standby sites
have their own database.
Source and Destination can either
be same database or they can be different databases.
All replicated master sites are
having their own databases.
Master table and Materialized
views are part of different databases.
Master table and Materialized
views can either be in same database or they can be in different databases.
Logical Database Structure
As there is one database, there is
one logical structure of the database.
As physical structure of the
database is exactly (block-by-block) same, the logical structure also remains
same in primary and standby databases.
When created, logical standby
database has same structure as primary. Later, additional schema/objects can
be created in logical standby database. However, original objects must remain
same as primary.
Streams provide flexibility to
have different structure of schema/objects at source and destination
databases.This can be accomplished
by using transformation of messages.
Logical structure of replicated
objects must be same. Owner of those objects must be same in all master
sites.
Both row and column subsetting
enable you to create materialized views that contain a partial copy of the
data at a master table. However, they are always based on a single master
table.
Both row and column subsetting
enable you to create materialized views that contain a partial copy of the
data at a master table. Read-only materialized views can be created using
join between multiple master tables.
Architecture Overview
A cluster comprises multiple
interconnected computers or servers that appear as if they are one server to
end users and applications. RAC uses Oracle Clusterware for the
infrastructure to bind multiple servers so that they operate as a single
system. If one clustered server fails, the Oracle database will continue
running on the surviving servers.
Standby database is kept
synchronized with the primary database, though Redo Apply, which recovers the
redo data, received from the primary database and applies the redo to the
standby database. If the primary database becomes unavailable, standby database
can be switched to the primary role.
Standby database is kept
synchronized with the primary database though SQL Apply, which uses logminer,
transforms the data from redo logs into SQL statements and then executing the
SQL statements on the standby database. If the primary database becomes
unavailable, standby database can be switched to the primary role.
Each unit of shared information is
called a message. Streams can capture, stage, and manage messages in Queue.
Messages can be DML, DDL and user-defined messages. Streams can propagate the
messages from one queue to other queue. When messages reach a destination,
Streams can consume them based on your specifications.
Internal triggers capture changes
applied at one site. The trigger stores those captured transactions locally.
The source master site pushes (propagates and applies) those transactions to
destination site.
Updatable Materialized view is a
view that stores view data in it's own storage. A push process that is same
as multi-master replication pushes updated data from MV site. However,
changes from master site are pulled by refresh site using refresh process.
Read-only Materialized view is a
view that stores view data in it's own storage. Data in materialized view are
refreshed by refresh process. Refresh process is initiated at Materialized
view site. Refresh process pulls data from master table using SQL query that
was used to create Materialized view.
Overview of Installation/Setup
RAC installation is a two-phase
installation. In phase one, use Oracle Universal Installer (OUI) to install
Oracle Clusterware. In second phase, use OUI to install the RAC software
(I.e. Oracle RDBMS with RAC option). You must install Oracle Clusterware and Oracle
RDBMS in separate home directories.
Prepare Primary Database by making
required changes in parameters, logging, archiving etc. Create a Backup Copy
of the Primary Database Datafiles. Create a Control File for the Standby
Database. Prepare an Initialization Parameter File for the Standby Database.
Copy Files from the Primary System to the Standby System. Set Up the
Environment to Support the Standby Database. Start the Physical Standby
Database in continuous recovery mode.
Verify prerequisites Logical
Standby Database (e.g. Datatypes, Primary Key etc). Create a Physical Standby
Database. Stop Redo Apply on the Physical Standby Database. Prepare the
Primary Database to Support a Logical Standby Database. Convert to a Logical
Standby Database. Adjust Initialization Parameters for the Logical Standby
Database. Open the Logical Standby Database and then perform certain
Post-Creation Steps.
Create streams administrator user
in all databases. In source database, create Capture Process and Propagation
schedule for propagation to destination database. Create Apply Process in
destination database.Start
Supplemental Logging in source database. . Prepare source database/objects.
Create copy of those objects in destination database using export/import
(datapump or traditional) or using RMAN. Complete the instantiation of
objects. Start Apply, Propagation and Capture processes.
Create replication administrator
user at all the sites with required privileges. Create propagation from each
site to all other sites. At one site create Master replication group. The
group remains in quiesced state when created. This site becomes MDS. Add
objects (to be replicated) in the group from MDS. Add all master sites in
master group from MDS. Start replication by resuming replication group. It
alters replication group from quiesced to normal state.
At master site, create replication
administrator. At materialized view (MV) site, create MV administrator.
Create propagation from MV site to master site. Create master group at master
site and add master objects in master group. Create materialized views at MV
site. Create MV group at MV site and add materialized views in the MV group.
On the MV site, create refresh group and add materialized views in refresh
group.Start replication by resuming
replication group.
If MV is being created in database
other than the one containing master table, then create database link in MV
database to point to master database for accessing master table. Create
materialized view.
Database/Instance status
All or any node of RAC can have
instance with database open for DML/DDL access.
Redo apply requires database in
recovery mode. When Physical standby database is in recovery mode, it cannot
be opened. When not in recovery mode, it can only be opened in read-only
mode.
SQL Apply requires database open
for running SQL statements. Hence, Logical standby database must be open in
normal circumstances.
Apply process requires database
open for running SQL statements. Hence, destination database must be open in
normal circumstances.
Push job requires master sites to
be open when it pushes transaction to other master sites. Hence, in normal
circumstances, all the master databases must be open.
Push as well as refresh job
requires master and MV sites to be open. Hence, in normal circumstances, the
master and MV databases must be open.
Refresh job requires master and MV
sites to be open. Hence, in normal circumstances, the master and MV databases
must be open.
Restriction on Datatypes
As there is one database, it
supports all datatypes.
As physical structure of the
database is exactly (block-by-block) same, it supports all datatypes.
There is restriction on datatypes
allowed in logical standby setup.
There is restriction on datatypes
allowed in streams setup.
There is restriction on datatypes
allowed in replicated tables.
There is restriction on datatypes
allowed in materialized views.
There is restriction on datatypes
allowed in materialized views.
This document is being delivered to you via Oracle Support's
Rapid Visibility (RaV)
process, and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4
Goal
The following note describes step-by-step procedure to create physical
standby by using RMAN duplicate without shutting down the primary
(Production) database.
Database Name :- prim Primary db_unique_name :- prim standby db_unique_name :- stdby
NOTE: Create password file if not present, also check if archiving enabled.
STEP: 2
Configure a Standby Redo Log on primary,
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 '/u01/app/oracle/databases/prim/redo/log3a.log' size 50m;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/databases/prim/redo/log4a.log' size 50m;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/databases/prim/redo/log5a.log' size 50m;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/databases/prim/redo/log6a.log' size 50m;
Database altered.
NOTE: To check the number of SRL,
(maximum number of logfiles for each thread + 1) * maximum number
of threads
For example, if the primary database has 3 log files for each thread and 2 threads, then 8 standbys redo log
file groups are needed on the standby database.
Verify the standby redo log file groups were created.
SQL> SELECT GROUP#,ThREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
STEP :3
Modify the primary initialization parameter for dataguard on primary,
SQL>alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim,stdby)';
System altered.
SQL>
alter system set
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/databases/prim/redo/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim';
System altered.
SQL>alter
system set LOG_ARCHIVE_DEST_2='SERVICE=stdby LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
System altered.
SQL>alter system set FAL_SERVER=stdby;
System altered.
SQL>alter system set FAL_CLIENT=prim;
System altered.
SQL>alter
system set
DB_FILE_NAME_CONVERT='/u01/app/oracle/databases/prim/data/','/u01/app/oracle/databases/stdby/data'
scope=spfile;
System altered.
SQL>alter
system set
LOG_FILE_NAME_CONVERT='/u01/app/oracle/databases/prim/redo/','/u01/app/oracle/databases/stdby/redo'
scope=spfile;
System altered.
STEP:4
Run the backup job at the primary by connecting to target and catalog DB(if any)
run { allocate channel c1 type disk; allocate channel
c2 type disk; backup database format '/u01/app/oracle/databases/stage/%U'; backup archivelog all format '/u01/app/oracle/databases/stage/%U';
backup current controlfile for standby format '/u01/app/oracle/databases/stage/%U'; }
using target database
control file instead of recovery catalog allocated channel: c1 channel c1: sid=22 devtype=DISK
Starting backup at 05-MAR-09 channel c1: starting
full datafile backupset channel c1: specifying datafile(s) in backupset including standby control file in backupset channel
c1: starting piece 1 at 05-MAR-09 channel c1: finished piece 1 at 05-MAR-09 piece handle=/u01/app/oracle/databases/stage/13k95lji_1_1
tag=TAG20090305T143634 comment=NONE channel c1: backup set complete, elapsed time: 00:00:02 Finished backup at 05-MAR-09 released channel: c1 released channel: c2
STEP :5
Create parameter file on primary copy it to standby and make the necessary changes,
SQL>create pfile='/u01/app/oracle/databases/prim/stage/initstdby.ora' from spfile;
File created.
STEP :6
Copy the listener.ora,tnsname.ora and sqlnet.ora files into staging directory.
b.
Alternatively we can copy the backups to standby different directory.
On standby server connect to RMAN target as primary and auxiliary
instance or if your primary is having catalog database then connect to
target as primary, catalog database and auxiliary instance. Catalog
those backup pieces to let the controlfile of primary or catalog
database to know the backup information.
For details on how to catalog backup piece refer,
Note 470463.1 - How To Catalog Backups / Archivelogs / Datafile Copies / Controlfile Copies
STEP: 8
Make the necessary changes on the copied initstdby.ora file on standby.
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 5 12:30:56 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PRIM (DBID=3971986030)
connected to auxiliary database: PRIM (DBID=3971986030, not open)
RMAN> duplicate target database for standby dorecover;
Starting Duplicate Db at 05-MAR-09 using target database control file instead of recovery catalog allocated channel:
ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: sid=36 devtype=DISK
contents of Memory Script: { set until scn 503194;
restore clone standby controlfile; sql clone 'alter database mount standby database'; } executing Memory Script
executing command: SET until clause
Starting restore at 05-MAR-09 using channel ORA_AUX_DISK_1
sql statement: alter database mount standby database released channel: ORA_AUX_DISK_1
contents of Memory Script:
{ set until scn 503194; set newname for datafile 1 to "/u01/app/oracle/databases/stdby/data/system01.dbf"; set newname for datafile 2 to "/u01/app/oracle/databases/stdby/data/undotbs01.dbf"; set newname for datafile 3 to "/u01/app/oracle/databases/stdby/data/sysaux01.dbf"; restore check readonly clone database ; } executing Memory Script
Note 183570.1 - Creating a Data Guard Database with RMAN (Recovery Manager) using Duplicate Command Note 466321.1 - RMAN Duplicate For Standby Fails with Rman-06024: No Backup Or Copy Of The Control File Found To Restore
