Oracle Database 10 GB high availability implementation solution source code

Chapter 1

Create tablespace ws_app_data datafile 'u01/product/Oracle/oradata/orcl/ws_app_data01.dbf' size 100 m;

Create tablespace ws_app_idx datafile 'u01/product/Oracle/oradata/orcl/ws_app_idx01.dbf' size 100 m;

Create user ws_app identified by ws_app default tablespace ws_app_data temporary tablespace temp;

Grant connect, resource to ws_app;

Connect ws_app/wsapp;

Create Table woodscrew (
Scr_id number not null,
Manufactr_id varchar2 (20) not null,
Scr_type varchar2 (20 ),
Thread_cnt number,
Length number,
Head_config varchar2 (20 ),
Constraint pa_woodscrew primary key (scr_id, manufactr_id)
Using index tablespace ws_app_idx );

Create index woodscrew_identity on
Woodscrew (scr_type, thread_cnt, length, head_config) tablespace ws_app_idx;

Create Table woodscrew_inventory (
Scr_id number not null,
Manufactr_id varchar2 (20) not null,
Warehouse_id number not null,
Region varchar2 (20 ),
Count number,
Log_price number );

Create Table woodscrew_orders (
Ord_id number not null,
Ord_date date,
Cust_id number not null,
Scr_id number not null,
Ord_cnt number,
Warehouse_id number not null,
Region varchar2 (20 ),
Constraint pk_wdscr_orders primary key (ord_id, ord_date)
Using index tablespace ws_app_idx );

-- Now Add rows to the tables
-- Now add woodscrew tables
Insert into woodscrew values (
1000, 'Tommy hardware ', 'finish', 30, 1.5, 'phillips ');
Insert into woodscrew values (
1000, 'balaji parts, Inc. ', 'finish', 30, 1.5, 'phillips ');
Insert into woodscrew values (
1001, 'Tommy hardware ', 'finish', 'phillips ');
Insert into woodscrew values (
1001, 'balaji parts, Inc. ', 'finish', 'phillips ');
Insert into woodscrew values (
1002, 'Tommy hardware ', 'finish', 30, 1.5, 'phillips ');
Insert into woodscrew values (
1002, 'balaji parts, Inc. ', 'finish', 30, 1.5, 'phillips ');
Insert into woodscrew values (
1003, 'Tommy hardware ', 'finish', 'phillips ');
Insert into woodscrew values (
1003, 'balaji parts, Inc. ', 'finish', 'phillips ');
Insert into woodscrew values (
1004, 'Tommy hardware ', 'finish', 'phillips ');
Insert into woodscrew values (
1004, 'balaji parts, Inc. ', 'finish', 'phillips ');
Insert into woodscrew values (
1005, 'Tommy hardware ', 'finish', 'phillips ');
Insert into woodscrew values (
1005, 'balaji parts, Inc. ', 'finish', 'phillips ');
Insert into woodscrew values (
1006, 'Tommy hardware ', 'finish', 'phillips ');
Insert into woodscrew values (
1006, 'balaji parts, Inc. ', 'finish', 'phillips ');

-- Now add woodscrew_inventory tables
Insert into woodscrew_inventory values (
1000, 'Tommy hardware ', 200, 'northeast', 3000000,. 01 );
Insert into woodscrew_inventory values (
1000, 'Tommy hardware ', 350, 'southwest', 1000000,. 01 );
Insert into woodscrew_inventory values (
1000, 'balaji parts, Inc. ', 450, 'northeast', 1500000,. 015 );
Insert into woodscrew_inventory values (
1005, 'balaji parts, Inc. ', 450, 'northeast', 1700000,. 017 );

-- Now add woodscrew_orders table
Insert into woodscrew_orders values (
20202, '2017-09-22 00:02:02 ', 2003, 64114, 'northeast ');
Insert into woodscrew_orders values (
20203, '2017-09-22 00:02:04 ', 2003, 'northeast ');
Insert into woodscrew_orders values (
20204, '2014-09-22 00:02:06 ', 2003, 'northeast ');
Insert into woodscrew_orders values (
20205, '2017-09-22 00:02:08 ', 2003, 'northeast ');
Insert into woodscrew_orders values (
20206, '2017-10-04 00:02:02 ', 2003, Hangzhou, 'southwest ');
Insert into woodscrew_orders values (
20207, '2017-10-04 00:02:14 ', 2003, 80903, 'southwest ');
Insert into woodscrew_orders values (
20208, '2017-10-04 00:02:16 ', 2003, 'southwest ');
Insert into woodscrew_orders values (
20209, '2017-10-04 00:02:08 ', 2003, 'northeast ');
Insert into woodscrew_orders values (
20210, '2017-11-04 00:02:16 ', 2003, 'southwest ');
Insert into woodscrew_orders values (
20211, '2017-11-04 00:02:16 ', 2003, Hangzhou, 'southwest ');
Insert into woodscrew_orders values (
20212, '2017-11-04 00:02:08 ', 2003, 'northeast ');
Insert into woodscrew_orders values (
20213, '2017-11-04 00:02:08 ', 2003, 'northeast ');
Insert into woodscrew_orders values (
20214, '2017-12-04 00:02:16 ', 2003, 'southwest ');
Insert into woodscrew_orders values (
20215, '2017-12-04 00:02:08 ', 2003, 'northeast ');
Insert into woodscrew_orders values (
20216, '2017-12-04 00:02:16 ', 2003, 'southwest ');
Insert into woodscrew_orders values (
20217, '2017-12-04 00:02:15 ', 2003, 'southwest ');

Commit;

Chapter 2
2.3.2.2 partitioned table
Create a range Partition
Create Table woodscrew_orders (
Ord_id number not null,
Ord_date date,
Cust_id number not null,
Scr_id number not null,
Ord_cnt number,
Warehouse_id number not null,
Region varchar2 (20 ),
Constraint pk_woodscrew_orders primary key (ord_id, ord_date)
Using index tablespace ws_app_idx)
Partition by range (ord_date)
(Partition values less than (to_date ('1-OCT-2003 ', 'dd-MON-YYYY '))
Tablespace wdscrord_sep_2003,
Partition values less than (to_date ('1-NOV-2003 ', 'dd-MON-YYYY '))
Tablespace wdscrord_oct_2003,
Partition values less than (to_date ('1-DEC-2003 ', 'dd-MON-YYYY '))
Tablespace wdscrord_nov_2003,
Partition values less than (to-date ('1-Jan-100', 'dd-MON-YYYY '))
Tablespace wdscrord_dec_2003)
Enable row movement;

Create a hash Partition
Create Table woodscrew (
Scr_id number not null,
Manufactr_id varchar2 (20) not null,
Scr_type varchar2 (20 ),
Thread_cnt number,
Length number,
Head_config varchar2 (20 ),
Constraint pk_woodscrew primary key (scr_id, manufactr_id)
Using index tablespace ws_app_idx)
Partition by hash (scr_id)
Partitions 4
Store in (wdscr_part1, wdscr_part2 );

Create list partitions
Create Table woodscrew_inventory (
Scr_id number not null,
Manufactr_id varchar2 (20) not null,
Warehouse_id number not null,
Region varchar2 (20 ),
Count number,
Lot_price number)
Partition by list (manufactr_id)
(Partition east_suppliers values9 'Tommy hardware ', '2most parts ')
Tablespace wdscr_inv_part1,
Partition west_suppliers values ('balaji parts ')
Tablespace wdscr_inv_part2,
Partition other values (default)
Tablespace ws_app_data)
Enable row movement;

Partition created (range-Hash partition)
Partition the woodscrew_orders table by ord_date to achieve better performance and further distribute the load to each tablespace.
To facilitate restoration. Use hash partitions to subpartition the table.
Create Table woodscrew_orders (
Ord_id number not null,
Ord_date date,
Cust_id number not null,
Scr_id number not null,
Ord_cnt number,
Warehouse_id number not null,
Region varchar2 (20 ),
Constraint pk_woodscrew_orders primary key (ord_id, ord_date)
Using index tablespace ws_app_idx)
Partition by range (ord_date) subspartition by hash (ord_id)
Subspartitions 2
(Partition values less than (to_date ('1-OCT-2003 ', 'dd-MON-YYYY '))
Store in (wdscrord_sep_2003_part1, wdscrord_sep_2003_part2 ),
Partition values less than (to_date ('1-NOV-2003 ', 'dd-MON-YYYY '))
Store in (wdscrord_oct_2003_part1, wdscrord_oct_2003_part2 ),
Partition values less than (to_date ('1-DEC-2003 ', 'dd-MON-YYYY '))
Store in (wdscrord_nov_2003_part1, wdscrord_nov_2003_part2 ),
Partition values less than (to-date ('1-Jan-100', 'dd-MON-YYYY '))
Store in (wdscrord_dec_2003_part1, wdscrord_dec_2003_part2)
Enable row movement;

 
Range-list partitions
This first item is introduced in Oracle9i version 2, which partitions by range and then subpartitions by list. This method can be used to clearly control the partition shape,
In this way, the granularity level of maintenance and mining operations can reach the optimal. P30.
Create Table woodscrew_orders (
Ord_id number not null,
Ord_date date,
Cust_id number not null,
Scr_id number not null,
Ord_cnt number,
Warehouse_id number not null,
Region varchar2 (20 ),
Constraint pk_woodscrew_orders primary key (ord_id, ord_date)
Using index tablespace ws_app_idx0
Partition by range (ord_date)
Subpartition by list (region)
(Partition wdscrord_sep_2003
Values less than (to_date ('1-OCT-2003 ', 'dd-MON-YYYY '))
(Subpartition wdscrord_sep_2003_west values ('southwest', 'northwest ')
Tablespace wdscrord_sep_2003_part1,
Subpartition wdscrord_sep_2003_east values ('southeast ', 'northeast ')
Tablespace wdscrord_sep_2003_part2 ),
Partition wdscrord_oct_2003
Values less than (to_date ('1-NOV-2003 ', 'dd-MOV-YYYY '))
(
Subpartition wdscrord_oct_2003_west values ('southwest', 'northwest ')
Tablespace wdscrord_oct_2003_part1,
Subpartition wdscrord_oct_2003_east values ('southwest', 'northwest ')
Tablespace wdscrord_oct_2003_part2
),
Partition wdscrord_nov_2003
Values less than (to_date ('1-DEC-2003 ', 'dd-MON-YYYY '))
(
Subpartition wdscrord_nov_2003_west values ('southwest', 'northwest ')
Tablespace wdscrord_nov_2003_part1,
Subpartition wdscrord_nov_2003_east values ('southeast ', 'northwest ')
Tablespace wdscrord_nov_2003_part2
),
Partition wdscrord_dec_2003
Values less than (to_date ('1-Jan-2004 ', 'dd-MON-YYYY '))
(
Subpartition wdscrord_dec_2003_west values ('southwest', 'northwest ')
Tablespace wdscrord_dec_2003_part1,
Subpartition wdscrord_dec_2003_east values ('southwest', 'northeast ')
Tablespace wdscrord_dec_2003_part2
))
Enable row movement;

Create materialized view P.35
Create materialized view log on ws_app.woodscrew
Tablespace ws_app_data;
Create meterialized view log on ws_app.woodscrew_orders
Tablespace ws_app_data;

Create materialized view ws_app.cust_ws_order_mv
Pctfree 0 tablespace ws_app_data
Storage (initial 16 K next 16 K pctincrease 0)
Parallel
Build immediate
Refresh on demand
Enable query rewrite
Select W. scr_type, W. head_config, Wo. cust_id, Wo. ord_cnt, Wo. scr_id
From ws_app.woodscrew_orders Wo, ws_app.woodscrew
Where W. scr_id = Wo. scr_id
And wo. cust_id = 2002;

Query Rewriting
This function can be enabled for a session or the entire system.
Alter session set query_rewrite_enabled = true;
Alter system set query_rewrite_enabled = true;
You can also use the alter or create command to enable the query rewrite function for a specific materialized view.

Online reorganization p 36

If partitions are implemented on the generated tables and indexes, which of the following is the index organization table or materialized view? Online Reorganization technology: dbms_redefinition
The online table management fee can be used in the following situations:
-Make the table an indexed Organizational table (and vice versa)
-Change a table to a partitioned table (and vice versa)
-Add or delete a column
-Move the table to a different tablespace or change the storage parameters.
Tables with materialized visual acuity cannot be reorganized online.
Use dbms_redefinition to change the table to a partitioned IOT (index organization table)
1. Obtain the number of rows in the table to help determine the completion time of the first phase of reorganization.
Select count (*) from woodscrew_orders;
2. confirm that the table can be reorganized.
Begin
Dbms_redefinition.can_redef_table ('sales', 'woodscrew _ orders ',
Dbms_redefinition.cons_use_pk );
End;
/
3. Create a temporary table. In our case, we need to create a partition index structure for the woodscrew_orders table.
Create Table woodscrew_orders_new (
Ord_id number not null,
Ord_date date,
Cust_id number not null,
Scr_id number not null,
Ord_cnt number,
Warehouse_id number not null,
Region varchar2 (20 ),
Constraint pk_woodscrew_orders primary key (ord_id, ord_date ))
Organization Index
Including ord_date pctthreshold 20
Overflow tablespace wd_scr_overflow
Partition by range (ord_date)
(
Partition values less than (to_date ('1_OCT-2003 ', 'dd-MON-YYYY '))
Tablespace wdscrord_sep_2003,
Partition values less than (to_date ('1-NOV-2003 ', 'dd-MON-YYYY '))
Tablespace wdscrord_oct_2003,
Partition values less than (to_date ('1-DEC-2003 ', 'dd-MON-YYYY '))
Tablespace wdscrord_nov_2003,
Partition values less than (to_date ('1-Jan-2004 ', 'dd-MON-YYYY '))
Tablespace wdscrord_dec_2003
);
4. Start the reorganization process. Because no columns are remapped, you only need to specify the mode, original table and temporary table.
Begin
Dbms_redefinition.start_redef_table ('ws _ app', 'woodscrew _ orders ',
'Woodscrew _ orders_new ', dbms_redefinition.cons_use_pk );
End;
/
5. automatically re-build the dependencies of all tables (indexes, triggers, etc)
Begin
Dbms_redefinition.copy_table_dependents ('ws _ app', 'woodscrew _ orders ',
'Woodscrew _ orders_new ', true, false );
End;
/
6. When a dependency object is created, the materialized view generated from the background may be required based on the accumulated data volume.
Refresh the log data once.
Begin
Dbms_redefinition.sync_interim_table ('ws _ app', 'woodscrew _ orders ',
'Woodscrew _ orders_new ');
End;
/
7. Lock the original table, end the last synchronization operation on the temporary table, and then switch the table names of the two tables to complete the reorganization process.
Begin
Dbms_redefinition.finish_redef_table ('ws _ app', 'woodscrew _ orders ',
'Woodscrew _ orders_new ');
End;
/
8. After completing the last step, you can delete the temporary table-that is, the woodscrew_orders_new table in the example
This is actually the renamed original table.

2.4 Data Manager and scheduler p39
Establish a simple resource planning and allocate CPU resources
Begin
Dbms_resource_manager.create_simple_plan (simple_plan => 'woodscrew _ plan ',
Consumer_group1 => 'order _ placement ', groupworker CPU => 70,
Consumer_group2 => 'order _ Review', group2_cpu => 30 );
End;
/
Create a Scheduling
Begin
Dbms_scheduler.create_schedule (
Schedule_name => 'maid _ review_schedule ',
Start_date => '24-DEC-2003 01:00:00 am ',
End_date => '01-Jan-2005 01:00:00 am ',
Repeat_interval => 'freq = daily; interval = 1 ',
Comments => 'schedule for nightly review ');
End;
/
Repeat_interval uses a new calendar syntax.

Program
Begin
Dbms_scheduler.create_program (
Program_name => 'alert _ review_script ',
Program_action => '/Oracle/ora10/admin/Prod/scripts/alter_rev.sh ',
Program_type => 'executable ',
Comments => 'executes and alert review ');
End;
/
The program defines how the job runs.

Job
Begin
Dbms_scheduler.create_job (
Job_name => 'daily _ alert_review ',
Program_name => 'alert _ review_script ',
Schedule_name => 'maid _ review_schedule ');
End;
/
Job description describes the task to be completed

Window
Begin
Dbms_scheduler.create_window (
Window_name => 'year _ end_reporting_window ',
Resource_plan => 'woodscrew _ plan ',
Start_date => '01-Jan-2004 01:00:00 am ',
Repeat_interval => 'freq = yearly ',
End_date => '31-Jan-2006 01:00:00 am ',
Duration => interval '30' day,
Window_priority => 'high ',
Comments = 'end of year sales reporting for CEOs ');
End;
/

Logminer: Transaction Extraction
It mines data in online redo logs and archives redo logs to obtain information about changes in the database.
. Example
View deleted transactions in an archive log. Note: before deleting a transaction, you must modify a system parameter.
Obtain complete transactions
1. Open the append log of the database
Alter database add Supplemental log data;
Select supplemental_log_data_min from V $ database;
2. Switch the log file and delete the transaction.
Connect/As sysdba
Alter system switch logfile;
Connect ws_app/ws_app
Delete from woodscrew;
Commit;

Connect/As sysdba
Alter system switch logfile;
Select name from V $ archived_log;
3. Add the newly generated log file to the logminer list.
"If you see a mistake in running this statement, remove ["-"]". Here I write it in the book.
Yes, only for reference
Execute dbms_logmnr.add_logfile (-
Logfilename =>
'/U02/oradata/flash_recovery_area/orcl/01_mf_201716213032xckmg _. arc ',-
Options => dbms_logmnr.new );
4. Specify the online directory to be used for logminer. If the source database is open and available,
It is also available
Execute dbms_logmnr.start_logmnr (-
Options => dbms_logmnr.dict_from_online_catalog );
5. query the information of a header deletion transaction in V $ logmnr_contents.
Select username, SQL _redo, SQL _undo
From v $ logmnr_contents where username = 'ws _ app'
And Operation = 'delete ';

2.6 transportable tablespace (TTS) p43
You can
Copy the primary data file and insert the tablespace into another database.
Transfer Time = over FTP + copy

Confirm the transfer set. The transfer set refers to the set to move all tablespaces.
You can use the dbms_tts package to ensure that the tablespace set meets the transmission conditions.
Execute dbms_tts.transport_set_check ('wdscrord _ sep_2003,
Wdscrord_oct_2003, wdscrord_nov_2003, wdscrord_dec_2003 ', true );
Select * From transport_set_violations;

TTS limit P44
The 8 I table space that can be transferred is available, and the 10g table space has been upgraded. It can be transferred across platforms.
Query any 10 Gb database to view its supported Operating Systems
Column platform_name format A30
Select * from V $ transportable_platform;
Note that the endian_format column determines the number of steps required for transmission to the new platform.
In addition to the endial format, there is a new restriction that only 10 Gb of data files can be transmitted between platforms.
Other restrictions are the same as those of TTS on the same platform.
Ha studio: Transfers tablespaces from Solaris to Linux
Note:
In this example, if the Partition Table space of a woodscrew_orders table is
After porting the Oracle 10 Gb to Linux, we noticed that the table could not be transmitted without transmitting the entire tablespace.
For this reason, you need to temporarily swap the partition with an independent table so that the partition becomes its own table,
Then, use the TTS process for Transmission
1.
Generate a temporary table for partition exchange
Create tablespace ws_sep_trans datafile
'/U01/product/Oracle/oradata/orcl/ws_sep_trans01.dbf'
Size 50 m;
Create Table woodscrew_orders_sep (
Ord_id number not null,
Ord_date date,
Cust_id number not null,
Scr_id number not null,
Ord_cnt number,
Warehouse_id number not null,
Region varchar2 (20 ),
Constraint pk_woodscrew_orders_sep primary key (ord_id, ord_date)
Using index tablespace ws_app_idx)
Tablespace ws_sep_trans;
2.
Swap partitions with tables
Alter table woodscrew_orders
Exchange partition wdscrew_sep_2003 with table woodscrew_orders_sep;
3.
Make sure that the independent tables in the wdscrord_sep_2003 tablespace meet the transmission conditions.
But it simply exchanges information in the data dictionary. Therefore,
The partition is now in our new tablespace ws_sep_trans, but it is not what we want to transmit,
The tablespace we really want to transmit is the original tablespace of wdscrord_sep_2003.
The name of the original partition), because the tablespace now contains our independent table
Connect/As sysdba
Execute dbms_tts.transport_set_check ('wdscrord _ sep_2003 ', true );
Select * From transport_set_violations;
Note: When selecting in transport_set_violations, because of our index
The tablespace in which it is located is not in the transport set. Therefore,
Constraint conflict. You can select not to export the constraint to eliminate it (
For example, step 1)
4.
Set tablespace to read-only
Alter tablespace wdscrord_sep_2003 read only;
5.
Export metadata. Note that the constraint is not specified and the primary key must be rebuilt in the new database table.
Exp file =/u01/product/Oracle/oradata/orcl/ws_sep_dat.dmp
Transport_tablespace = y constraints = n tablespaces = wdscrord_sep_2003
6.
Use RMAN to convert the data file to the little endian required for Linux transplantation.
Convert tablespace wdscrord_sep_2003
To platform 'linux Ia (32-bit )'
Format = '/u01/product/Oracle/oradata/orcl/wscrord_sep_2003_for_lnx.dbf ';
7.
By porting data files to Linux, you can use any method that meets your needs for speed and ease of use.
For row file transfer, we use binary ftp. After the transfer is complete, rename the data file (from wscrord_sep_2003_for_lnx
Change to wscrord_sep_2003.dbf ).
8.
Of course, to set the tablespace to read/write mode, and then switch the partition back to the original location, you need to make the table index take effect again
Connect/As sysdba
Alter tablespace wdscrord_sep_2003 read write;
Connect ws_app/ws_app
Alter table woodscrew_orders
Exchange partition wdscrord_sep_2003 with table woodscrew_orders_sep;
9.
Import the metadata of the data file on the target Linux
IMP file =/u01/product/Oracle/oradata/orcl/ws_sep_dat.dmp
Transport_tablespace = y tablespaces = wdscrord_sep_2003
Datafiles = 'u01/product/Oracle/oradata/orcl/wscrord_sep_2003.dbf'
Tts_owners = (ws_app)
10.
Set the new tablespace to read/write mode
Connect/As sysdba
Alter tablespace wedscrord_sep_2003 read write;