Transcript Don't Shut Down That Database!

Don’t Shut Down That Database!
Use Oracle 9i Online Object Redefinition Instead
Chris Lawson
Performance Solutions
Roger Schrag
Database Specialists, Inc.
IOUG Live! April, 2002
Session #524
1
Session Topics







What is “online object redefinition”?
Why is this feature important?
The dbms_redefinition package
The online redefinition process
Examples
Restrictions
Lessons learned
2
Online Object Redefinition
The ability to change the definition of a
database object without restricting the ability
of users to query and update data
 Oracle 8i provided some online capabilities
for IOTs.
 Oracle 9i enables a wider range of online
maintenance for almost all types of tables.
3
Things You Can Do With Online
Redefinition
 Move a table or index to a new tablespace
 Change a table’s organization (partitioning,
index-organized, etc.)
 Add, remove, or rename columns in a table
 Change the data type of a column in a table
 Add new indexes to a table
 Change constraint definitions on a table
4
Why Do We Need This Feature?
 Databases are getting larger and more
complex.
 DBAs are being given less down time to
perform maintenance.
 Companies often insist on no downtime.
 This feature makes it possible to perform
many important maintenance tasks online.
5
The dbms_redefinition Package
Use the five procedures in this package to
redefine an object online.





CAN_REDEF_TABLE
START_REDEF_TABLE
FINISH_REDEF_TABLE
ABORT_REDEF_TABLE
SYNC_INTERIM_TABLE
6
Permissions Required To Redefine
A Table Online






EXECUTE on the dbms_redefinition package
CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE
Typically, you’ll want to use a DBA account to redefine
objects online.
7
Overview Of The Online
Redefinition Process
Step 1: Verify the table is eligible for online
redefinition.
Step 2: Create an interim table.
Step 3: Associate the interim table with the
table to be redefined.
Step 4: Add constraints, indexes, triggers, and
grants to the interim table.
Step 5: Complete the online redefinition.
8
Step 1: Verify The Table Is
Eligible For Online Redefinition
 Confirms table to be redefined meets
requirements for online object redefinition
 Call the CAN_REDEF_TABLE procedure in the
dbms_redefinition package:
EXECUTE dbms_redefinition.can_redef_table ('FINANCE', 'RATE');
 Table qualifies if no exception is raised
 Exception will be raised if table does not qualify:
ORA-12089: Cannot online redefine table with no primary key
9
Step 2: Create An Interim Table
 Online redefinition uses an interim table to
change the object definition without
restricting user access
 Create interim table using exact column
names, data types, organization, tablespace
assignment, and storage clause that the
redefined table should have
10
Step 2: Create An Interim Table
 Declare primary key on interim table, but do
not create any other indexes, constraints, or
triggers
 Oracle will copy rows from the original table
into the interim table during the redefinition
process
 Oracle will swap the names of the two tables
at the end so the interim table will become
the production table and vice versa
11
Step 3: Associate The Interim Table
With The Table To Be Redefined
 Call the START_REDEF_TABLE procedure:
EXECUTE dbms_redefinition.start_redef_table ('FINANCE', 'RATE', 'STAGING');
 Oracle creates a materialized view and log in
order to populate the interim table from the
production table and track subsequent updates to
the production table. All rows in the production
table are copied to the interim table
12
Step 3: Associate The Interim Table
With The Table To Be Redefined
 A column mapping must be specified when calling
START_REDEF_TABLE if any columns are being
added or removed, if any data types are being
changed, or if any data is being modified
 Could take a long time if production table has
many rows
 Users have full query and update capability
13
Step 4: Add Constraints, Indexes,Triggers,
And Grants To Interim Table
 Create constraints, indexes, database triggers,
and grants on interim table desired on the
production table at the end of the online
redefinition process
 Features on the production table, but not the
interim table, will disappear from the redefined
table in the next step
 Create foreign keys with the DISABLE keyword
14
Step 5: Complete The Online
Redefinition
 Interim table has organization, column definitions,
indexes, constraints, triggers, and grants desired on
redefined table. Interim table also has all of the rows
of the production table except for updates occurring
since Step 3 began
 Call the FINISH_REDEF_TABLE procedure:
EXECUTE dbms_redefinition.finish_redef_table ('FINANCE', 'RATE', 'STAGING');
15
Step 5: Complete The Online
Redefinition
 Oracle uses the materialized view log to propagate
pending updates to interim table
 Oracle locks production and interim tables briefly
and swaps their names in data dictionary
 Oracle drops the materialized view and log and
enables disabled foreign keys
 Redefinition is complete. Interim table (which used
to be the production table) may be dropped
16
Step 5: Complete The Online
Redefinition
 Step usually runs quickly (unless users have
updated a lot of rows in the production table
between the time Step 3 began and the time this
step began)
 Tables are locked only briefly. At all other times
during the entire process, users have complete
query and update ability on the table undergoing
online redefinition
17
Aborting An Online Redefinition
 You may cancel an online redefinition
process at any time before calling the
FINISH_REDEF_TABLE procedure.
 Abort the online redefinition by calling the
ABORT_TABLE_REDEF procedure. Oracle
drops the materialized view and log
definitions, if they had been created.
 You may drop the interim table if you wish.
18
Examples Of Online Object
Redefinition
1. Move a table to a new tablespace and add
two new indexes.
2. Massage values in one column of a table
and change the data type of another
column.
19
Example #1: Move A Table To A New
Tablespace And Add Two New Indexes
Move the accts_payable table in the finance schema
to a new tablespace. At the same time, add indexes on
the vendor_name and vendor_po columns.
 ALTER TABLE...MOVE locks the table.
 Export / Import makes the table read-only during
export and inaccessible during import.
 CREATE INDEX locks the table.
 Online redefinition allows full query and update
access.
20
Step 1:Verify The Table Is Eligible
For Online Redefinition
SQL> BEGIN
2
dbms_redefinition.can_redef_table
3
('FINANCE', 'ACCTS_PAYABLE');
4 END;
5 /
PL/SQL procedure successfully completed
SQL>
21
Step 2: Create An Interim Table
SQL>
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE accts_payable_stage
(
account_id
VARCHAR2(20),
vendor_name
VARCHAR2(50) NOT NULL,
vendor_address VARCHAR2(50),
vendor_po
VARCHAR2(20),
invoice_date
DATE
NOT NULL,
invoice_amount NUMBER
NOT NULL,
CONSTRAINT accts_payable_pk PRIMARY KEY (account_id)
USING INDEX TABLESPACE accts_pay_ind
)
TABLESPACE accts_pay_tab;
Table created.
SQL>
22
Step 2: Create An Interim Table
 Same column names and data types as
production table
 Primary key declared, but no other indexes
or constraints
 Desired tablespaces, storage clauses, and
organization specified
23
Step 3: Associate The Interim Table
With The Table To Be Redefined
SQL> BEGIN
2
dbms_redefinition.start_redef_table
3
('FINANCE', 'ACCTS_PAYABLE',
4
'ACCTS_PAYABLE_STAGE');
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
24
Step 3: Associate The Interim Table
With The Table To Be Redefined
 No column mapping provided because no
column information changed in this example
 Step could take a long time if accts_payable
table has many rows
 Users will have full query and update ability
25
Step 4: Add Constraints, Indexes,
Triggers, And Grants To Interim Table
SQL> CREATE INDEX accts_payable_n1 ON accts_payable_stage (vendor_name)
2 TABLESPACE accts_pay_ind;
Index created.
SQL> CREATE INDEX accts_payable_n2 ON accts_payable_stage (vendor_po)
2 TABLESPACE accts_pay_ind;
Index created.
SQL> CREATE INDEX accts_payable_n3 ON accts_payable_stage (invoice_date)
2 TABLESPACE accts_pay_ind;
Index created.
SQL>
26
Step 4: Add Constraints, Indexes,
Triggers, And Grants To Interim Table
 Add to interim table whatever features are
desired on redefined table
 Could be quite different from what exists on
original table (new indexes, different
constraints, etc.)
27
Step 5: Complete The Online
Redefinition
SQL> BEGIN
2
dbms_redefinition.finish_redef_table
3
('FINANCE', 'ACCTS_PAYABLE',
4
'ACCTS_PAYABLE_STAGE');
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
28
Step 5: Complete The Online
Redefinition
 Could take a long time if many updates
occurred after Step 3 began
 Production table locked briefly near end of
step
 Production and interim table names are
swapped
 Drop interim table (old production table)
manually after online redefinition is complete
29
Example #2: Massage Values In One
Column Of A Table And Change The
Data Type Of Another Column
Strip trailing blanks from all values in the description
column of the invoices table. Also, change the data type of
the vendor_id column.
SQL> DESCRIBE invoices
Name
---------------INVOICE_ID
LINE_ITEM
DESCRIPTION
VENDOR_ID
INVOICE_AMT
Null?
-------NOT NULL
NOT NULL
Type
---VARCHAR2(20)
NUMBER
VARCHAR2(200)
NOT NULL NUMBER
NOT NULL NUMBER
30
Step 1:Verify The Table Is Eligible
For Online Redefinition
SQL> BEGIN
2
dbms_redefinition.can_redef_table
3
('FINANCE', 'INVOICES');
4 END;
5 /
PL/SQL procedure successfully completed
SQL>
31
Step 2: Create An Interim Table
SQL>
2
3
4
5
6
7
8
9
10
11
CREATE TABLE invoices_stage
(
invoice_id
VARCHAR2(20),
line_item
NUMBER
NOT NULL,
description
VARCHAR2(200),
vendor_id
VARCHAR2(20) NOT NULL,
invoice_amt
NUMBER
NOT NULL,
CONSTRAINT invoices_pk PRIMARY KEY (invoice_id)
USING INDEX TABLESPACE ind
)
TABLESPACE tab;
Table created.
SQL>
32
Step 2: Create An Interim Table
 Data type of vendor_id column has changed
from NUMBER to VARCHAR2(20)
 Tablespaces specified even though we are
not moving table or index to new tablespace
33
Step 3: Associate The Interim Table
With The Table To Be Redefined
SQL> BEGIN
2
dbms_redefinition.start_redef_table
3
('FINANCE', 'INVOICES', 'INVOICES_STAGE',
4
'INVOICE_ID INVOICE_ID, LINE_ITEM LINE_ITEM, ' ||
5
'RTRIM (DESCRIPTION) DESCRIPTION, ' ||
6
'TO_CHAR (VENDOR_ID) VENDOR_ID, ' ||
7
'INVOICE_AMT INVOICE_AMT');
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
34
Step 3: Associate The Interim Table
With The Table To Be Redefined
 Column mapping provided due to data massaging
and type conversion
 RTRIM function to strip trailing blanks from
description column
 TO_CHAR function to convert numeric vendor_id
to VARCHAR2
 Invoice descriptions and vendor IDs are
transformed as rows are copied to interim table
35
Step 4: Add Constraints, Indexes,
Triggers, And Grants To The Interim
Table
 For simplicity, the invoices table in this
example does not have any constraints,
indexes, triggers, or grants other than the
primary key declared when the interim table
was created.
36
Step 5: Complete The Online
Redefinition
SQL> BEGIN
2
dbms_redefinition.finish_redef_table
3
('FINANCE', 'INVOICES', 'INVOICES_STAGE');
4 END;
5 /
PL/SQL procedure successfully completed.
SQL>
37
Step 5: Complete The Online
Redefinition
 At the end of the online redefinition, the description column
is free of trailing blanks and the data type of the vendor_id
column has been changed:
SQL> DESCRIBE invoices
Name
---------------INVOICE_ID
LINE_ITEM
DESCRIPTION
VENDOR_ID
INVOICE_AMT
Null?
-------NOT NULL
NOT NULL
Type
---VARCHAR2(20)
NUMBER
VARCHAR2(200)
NOT NULL VARCHAR2(20)
NOT NULL NUMBER
SQL>
38
Limitations Of The Online Object
Redefinition Feature
 Table eligibility requirements
 Limitations of column mapping
 Row selectivity
39
Not Eligible For Online
Redefinition
 Tables without a declared primary key
 Tables that are the basis of a materialized
view
 Tables belonging to a cluster
 Temporary tables
 Tables in the SYS or SYSTEM schema
 Tables with FILE, LONG, or user-defined
data types
40
Column Mapping Limitations
 Simple functions and expressions only
– Allowed: Changing the case of a string of text or
multiplying a numeric value by a constant
– Not allowed: Subqueries or non-deterministic
functions
 Mandatory columns must get values derived
from existing columns
41
Row Selectivity Limitation
 Cannot eliminate rows from a table during
online redefinition
 Redefined table will have all of the rows in
the original table
42
Lessons Learned





Testing with the interim table
Constraint validation
Handling foreign keys
Column mapping
Speeding up the FINISH_REDEF_TABLE
call
43
Testing With The Interim Table
You can validate your object redefinition against the
interim table before calling the FINISH_REDEF_TABLE
procedure.




Verify column mapping
Validate data transformation
Test new indexes
Abort the redefinition if problems are
discovered—without impacting production
44
Constraint Validation
 Foreign keys declared on the redefined
table are enabled but not validated at end of
redefinition
 Existing rows not tested for compliance
 Validating existing rows must be done
manually if validation desired
The Oracle 9i documentation fails to mention this point!
45
Handling Foreign Keys
Redefining a table that is a parent to foreign keys
declared on other tables presents a special problem.
 Recall that at the end of an online object
redefinition, the names of the original production
table and the interim table are swapped.
 This means that after an online redefinition,
foreign keys on other tables that used to reference
the production table will now reference the interim
table.
46
Redefining A Table That Is A Parent To
Foreign Keys Declared On Other Tables
 Declare new foreign keys in disabled state
that reference the interim table.
 When redefinition is complete, drop foreign
keys that reference the interim table and
enable foreign keys that reference the
redefined table.
 Plan for locks and resources required to
enable the new constraints.
47
Column Mapping
 Specify column map in call to
START_REDEF_TABLE if any column
names, data types, or values are changed
 Single string consisting of comma-delimited
expression / name pairs
– An expression is a formula to derive column
values from the existing production table.
– A name is the name of a column in the interim
table to be loaded with the value.
48
SQL Syntax Refresher
 Strings are delimited by single quotes.
 Include a single quote in a string by
specifying two single quotes in a row.
 Concatenate two strings with the || operator.
49
Column Mapping Example
Append an “A” to each invoice number while redefining
the invoices table.
SQL> BEGIN
2
dbms_redefinition.start_redef_table
3
('FINANCE', 'INVOICES', 'INVOICES_INTERIM',
4
'INVOICE_ID INVOICE_ID, VENDOR_ID VENDOR_ID, ' ||
5
'INVOICE_DATE INVOICE_DATE, AMOUNT AMOUNT, ' ||
6
'INVOICE_NUMBER || ''A'' INVOICE_NUMBER');
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
50
Speeding Up The
FINISH_REDEF_TABLE Call
 Materialized view log catches all updates to
production table after START_REDEF_TABLE call
 All pending changes are propagated to interim
table during FINISH_REDEF_TABLE call
 Can call SYNC_INTERIM_TABLE before
FINISH_REDEF_TABLE to propagate all pending
changes
 Does not speed up the overall redefinition process
51
Wrapping Up
 Oracle Corporation recognizes the need for high
availability.
 By and large, the online redefinition feature of
Oracle 9i is pretty easy to use.
 Tricky issues, such as foreign key handling, stem
from the complexities of changing a table while
users are querying and updating it.
 As with any new Oracle feature, test thoroughly
before using!
52
Contact Information
Chris Lawson
Performance Solutions
Tel: 925/829-7496
Email: [email protected]
Web: www.oraclemagician.com
Roger Schrag
Database Specialists, Inc.
Tel: 415/344-0500
Email: [email protected]
Web: www.dbspecialists.com
53