- Reprinted on the project group Zhou Chao Yong notes
Topic: 1. Configure table space and various user objects. 2. Create data image from a real-time database to a backup database.
Environment: Sun Solaris UNIX 9 Oracle 9i
Conditions: 1. There are two empty databases: Live Database, Backup Database. 2. The user has SYSDBA permissions to the above two databases. 2. The server where the above two databases is located can be accessed through the TCP / IP protocol.
1. Configure table space and various user objects.
Oracle is a database that allows users to freely configure various parameters and space. - Users can configure various Oracle initialization parameters in the PFILE. Including the number of Rollback Segment, the specific ROLLBACK Segment, the output settings of the SQL Log file, and more. - The user can freely assign data space for various objects. Including DB File, Segment, Block, Tablespace, Table, and more.
Oracle's storage space is divided into physical storage space and logical storage, similar to disk management of Windows operating systems.
- Carrier of data objects: DB File-the most basic storage unit: block-data object space configuration basic unit: segment-all user objects: TableSpace
The database concept exists in the form of explanation ============================================== =====================================DataFile Physical can use multiple Data file as a storage space of TableSpace Segment Physical must use Data File Continuous space Block Physical generally defaults 8K Byte
TableSpace Logical is built on the Data File, you can use multiple Data FileTable Logical to be built on TableSpace. MV log logical is built on tablespace. MV Logical is built on tablespace. Index Logical is built on tablespace. View Logical is built on System TableSpace to create a user's object
Strored Procedure Logical is an object that creates a user
User has the default tablespace, as well as temporary tablespace, defined in Create User Statement
* MV for materialized view
1) Create a TableSpace
When creating a carrier of a user object, Oracle will create (if there is no existence) and assign Data file. In this article, we use the Segment Space Management Auto feature.
I.E .: Use sysdba to DB, run the following SQL:
DROP TABLESPACE ts_xxx INCLUDING CONTENTS AND DATAFILES; CREATE TABLESPACE ts_xxx LOGGING DATAFILE '/oracle/oradata/dbName/ts_xxx01.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT 5120K MINIMUM EXTENT 512K SEGMENT SPACE MANAGEMENT AUTO DEFAULT STORAGE (INITIAL 512K NEXT 512K PCTINCREASE 0 MINEXTENTS 8 MaxExtents Unlimited; The above script will be in the / Oracle / ORADATA / DBNAME / Directory (when you create a database, you have created.) Ts_xxx01.dbf. If TS_XXX already exists in the database, Oracle will delete the TS_XXX, including all content and data files Ts_xxx01.dbf built on it. The initialized DATA FILE size is 512m, which can be reused after the tableSpace is dropped by DROP. Segment will automatically initialize 512K and automatically grow according to the TableSpace configuration. The next segment will be 5120k 512K.
2) Creating User needs to create a user before establishing table. In Oracle, all user objects belong to a SCHEME, and scheme is a collection of all user objects of a user. I.E .: Use sysdba to DB, run the following SQL:
Drop User OP Cascade; Drop Role Oprole;
Create Role Oprole; Grant DBA, CREATE session to oprole;
Create User Op Identified by Oppwd Default TableSpace Ts_xxx Temporary TableSpace Ts_Temp; Grant Oprole To OP;
Grant Unlimited TableSpace to OP;
The above script will create a user OP (Password OPPWD), as well as the role oprole. If the user already exists before the script runs, all user objects in the corresponding theme are first deleted, and the user is also deleted. Then establish a new user. The user's default table space is TS_XXX, and the temporary table space is TS_TEMP (custom creation when creating tablespace). The permissions of DBA and Create Session will be given to oprole. Unlimited TableSpace in DBA cannot give a role, so you must separately give UNLIMITED TABLESPACE to the user OP after the role is given to the user OP.
3) Creating a Table User OP to DB, run the following SQL:
ALTER TABLE TB_XXX DROP Primary Key Cascade; Drop Table TB_XXX Cascade Constraints;
CREATE TABLE tb_xxx (xxx_NO VARCHAR2 (9 BYTE) NOT NULL, xxx_ID VARCHAR2 (3 BYTE) NOT NULL, xxx NUMBER (13,2), xxx_DT DATE DEFAULT sysdate) TABLESPACE ts_xxxPCTUSED 40PCTFREE 10INITRANS 1MAXTRANS 255STORAGE (INITIAL 10M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 Buffer_pool default) Logging Nocachenoparallel; The above script is used to create Table: TB_XXX. If the table already exists, you will first remove the existing table. If the table has a primary key, first remove the primary key. The above table is created on the tableSpace called TB_xxx.
ALTER TABLE tb_xxx ADD (CONSTRAINT idx_tb_xxx PRIMARY KEY (col_a, col_b) USING INDEX TABLESPACE ts_index PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 3M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)); the primary key for scripts to create table is: col_a, col_b, This primary key is created on idx_tb_xxx, and the index idx_tb_xxx is created on TableSpace TS_Index. INDEX IDX_TB_XXX is automatically generated when running the above script. TableSpace TS_INDEX should be established when generating tablespace.
ALTER TABLE tb_xxx ADD (CONSTRAINT IDX_tb_xxx_UNQ UNIQUE (col_a, col_b) USING INDEX TABLESPACE ts_index PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)); similar to the primary key of the above table scripts to create a unique key : Col_a, col_b, the primary key is created on index idx_tb_xxx_unq, and the index idx_tb_xxx_unq is created on TableSpace TS_Index. Index IDX_TB_XXX_UNQ is automatically generated when running the above script. TableSpace TS_INDEX should be established when generating tablespace.
4) Create index to log in to DB with user op, run the following SQL:
CREATE INDEX IDX_TB_xxx ON TB_xxx (col_a, col_b) LOGGINGTABLESPACE TS_indexPCTFREE 10INITRANS 2MAXTRANS 255STORAGE (INITIAL 10M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT) NOPARALLEL; IDX_TB_xxx above script for indexing on the col_b col_a and, based on the index tablespace ts_index. 5) Create a MV logoracle to record all the operations of the corresponding table. These records will be recorded in the log table. Data mirroring is looking for records recorded in the log table, mirroring the data into the backup database. I.E .: Use the user op to DB, run the following SQL:
Drop Materialized View Log on TB_XXX;
CREATE MATERIALIZED VIEW LOG ON tb_xxx PCTUSED 40 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL 20M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT) TABLESPACE ts_xxx LOGGING NOCACHE WITH PRIMARY KEY; above script builds Log on tb_xxx, if the Log already exists, First delete it first. This LOG is built on TableSpace TS_XXX.
6) Creating a sequence to log in to DB with user op, run the following SQL:
DROP SEQUENCE SQ_XXX;
Create sequence sq_xxxminvalue 1maxvalue 99999999start with 1increment by 1cache20cycle;
The above scripts are used to establish sequence sq_xxx. If the sequence exists, first delete. The initial value of the sequence is 1, the maximum is 99999999, the step size is 1, each Oracle is executing SQ_XXX.NEXTVAL, will generate 20 sequence caches in the database server memory, will return to the maximum value of the sequence reaches the maximum To the initial initialization value.
7) Creating a View to log in to DB with user op, run the following SQL:
Create or replace view vw_xxx asselect * from TBL_XXX;
The script is running will create view vw_xxx.
8) Create a stored procedure: Log in to DB with the user op, run the following SQL:
Create Or Replace Package PK_XXX AS ... /
Create Or Replace Package Body PK_XXX AS ... /
The above script ends and executes a SQL. The script is running will establish a stored procedure. The stored procedures here include Package and Package Body, establishing a definition of an abstract interface in Package, a specific definition of each abstract interface in Package Body, is also an abstract interface in package.
Package Body and Package must correspond to one by one. If there is a corresponding package or package body, you already delete and then build it before establishing package or package body. 9) Cancel some permission to use Sysdba to log in to DB, run the following SQL:
Revoke DBA from oplole; Revoke Unlimited TableSpace from OP;
The above scripts will cancel the DBA permissions of the role BKOP_ROLE, and the Unlimited TableSpace permission of the user BKOP.
2. Create data image from a real-time database to a backup database. There are a variety of ways to establish a data backup, which is a relatively convenient, efficient and secure, using the MaterializeD View.
Principle of data mirroring using the Materialized View: Read the log record of the real time database, back up real-time data to the backup database at custom time.
1) Similar to the establishment of the real-time database, first create a table space with the user sysdba to accommodate the Materialized View.
2) Similar to users in the real-time database, create users: log in to DB with user sysdba, run the following SQL:
Drop User Bkop Cascade; Drop Role Bkop_Role;
Create role bkop_role; Grant DBA, Create session to bkop_role;
Create User Bkop Identified by Bkoppwd Default TableSpace Ts_xxxmv Temporary TableSpace Ts_Temp; Grant Bkop_Role To Bkop;
Grant Unlimited TableSpace to Bkop; The above script will create a user BKOP (password BKOPPWD), as well as the role Bkop_Role. If the user already exists before the script runs, all user objects in the corresponding theme are first deleted, and the user is also deleted. Then establish a new user. The user's default table space is TS_XXXMV, and the temporary table space is TS_TEMP (custom creation when creating tablespace). The permissions of DBA and Create Session will be given to bkop_role. Unlimited TableSpace in DBA cannot give a role, so you must separately give UNLIMITED TABLESPACE to the user BKOP independently after the role is given to the user BKOP.
3) Create a database connection: log in to DB with the user sysdba, run the following SQL:
DROP PUBLIC DATABASE LINK FROMBKTOLIVE;
Create Public Database Link frombktolive Connect to Op Identified by Oppwd Using 'LiveDatabaseName';
The above scripts are used to create a Database Link from the backup database to the real-time database.
4) Creating a Materialized View to log in to DB with user bkop, run the following SQL:
DROP MATERIALIZED View TB_XXX;
CREATE MATERIALIZED VIEW tb_xxx PCTUSED 40 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL 256M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT) TABLESPACE TS_xxxMV LOGGING NOCACHE NOPARALLEL REFRESH FAST WITH PRIMARY KEY USING DEFAULT ROLLBACK SEGMENT AS SELECT * FROM tb_xxx @ FromBKtoLive; Running the above script After, Oracle uses the table structure of the table in the real-time database, and the data directly Copy to the Materialized View. The created table structure includes data definitions, as well as the primary key. The corresponding INDEX constraint will be built on the default TableSpace TS_XXXMV. 5) Create INDEX on the Materialized View: Log in to DB with user bkop, run the following SQL:
DROP INDEX IDX_TB_XXX;
CREATE INDEX IDX_TB_xxx ON TB_xxx (col_a, col_b) LOGGINGTABLESPACE TS_xxxMVPCTFREE 10INITRANS 2MAXTRANS 255STORAGE (INITIAL 10M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT) NOPARALLEL; After running the above script to generate a table index tb_xxx on (col_a, col_b) in Materialized View This index idx_tb_xxx is generated on TableSpace TS_XXXMV.
6) Establish a stored procedure to log in to DB with user BKOP, run the following SQL:
CREATE OR REPLACE PACKAGE PK_MIRRORING AS TYPE BK_MV IS REF CURSOR D; --MATERIALIZED VIEW Table Name Collection Procedure SP_MAKE (RefreshType Varchar2); END PK_MIRRORING; /
create or replace package body PK_Mirroring is - a main storage procedure PROCEDURE SP_Make (refreshType varchar2) is CURSOR BK_MV IS --Materialized View information table select Table_Name from User_Tables order by Table_name; V_Mirror BK_MV% ROWTYPE; begin open BK_MV; Loop FETCH BK_MV into V_Mirror ; exit when BK_MV% notfound; DBMS_MVIEW.REFRESH (V_Mirror.Table_Name, refreshType); End Loop; end; end PK_Mirroring; / script above procedure for establishing a storage package and package body, the package has a procedure SP_Make (refreshType varchar2) , Used to traverse all user objects of user BKOP users of backup databases, and simultaneously refresh, refreshType is 'f' (fast) or 'c' (Complete), etc., generally choose FAST, Just will update updated real-time data mirror into the backup database. Complete is a list of all data from the real-time database to the backup database, which will take up a lot of time and space.
Users can log in to the backup database with BKOP, run EXEC PK_MIRRORING.SP_MAKE ('F')
Or establish a task in UNIX's crontabs table, run the mirror processing at a specific point in time.
7) Cancel the user's permission to log in to DB with Sysdba, run the following SQL:
Revoke DBA from bkop_role; Revoke Unlimited TableSpace from bkop;
The above scripts will cancel the DBA permissions of the role BKOP_ROLE, and the Unlimited TableSpace permission of the user BKOP.
A complement to run the SQL script: You can join the spool, prompt command before and after SQL text. A log file is generated in the current SQL_PATH Run Directory. Easy to analyze the results of SQL operation.
Spool tb_xxx.log
PromptPROMPT CREANG TABLE TB_XXXPROMPT ======================================== PROMPT ... The specific SQL statement about establishing TB_XXX ... Spool off
After running the above script, a TB_XXX.LOG will generate the ASCII file, the content is CREANG TABLE TB_XXX =============================== = ... the result of running the table statement, success or failure
Zhou Chao Yong Contact: YORCK_ZHOU@hotmail.com
俺 Contact: ligang1000@hotmail.com
