Oracle how to achieve synchronization of the two databases (with materialized views) (Oracle snapshot example)
Oracle how to achieve synchronization of the two databases (with materialized views) (Oracle snapshot example)
One, technical implementation details Unless otherwise noted, the following SQL commands are are run under the SYSETM user of the database ora_db2. Suppose you want to copy (or synchronize) all the tables of user db1 in database ora_db1 on another server.
1. Create a database connection (dblink) to database 1 (ora_db1)
SQL>CREATEPUBLICDATABASELINKtestLKCONNECTTOdb1identifiedbydb1using ‘( DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.1)(PORT=1521))))(CONNECT_DATA=(service_name=ora_db1)))’;
** For security reasons, a private data connection can be used.
2. Create a tablespace named Snapshot_ts to hold the snapshots, and create a user named db2 associated with that tablespace.
SQL>CREATETABLESPACEsnapshot_tsDATAFILE’d:\db\snapshot_ts.dbf’SIZE30MDEFAULTSTORAGE( INITIAL30KNEXT15KMINEXTENTS1MAXEXTENTS100PCTINCREASE0)ONLINEPERMANENT;
SQL> CREATEUSERdb2IDENTIFIEDBYdb2DEFAULTTABLESPACEsnapshot_ts;
SQL> GRANTCONNECT,RESOURCETOdb2;
You can create a new database in the ora_db1 database as the db1 user to roughly estimate the size of the tablespace snapshot_ts.
SQL>SELECTSUM(bytes)FROMUSER_SEGMENTSWHERESEGMENT_NAMEIN(selecttable_namefromuser_tables);
3. Run the following script to generate the code for creating the ora_db1 Run the following script to generate a snapshot of the code table under the db1 user on the database:
Note: The file create_snapshot.sql script obtained by running the following select under db1 is run under db2.
SQL>spoold:\snap\create_snapshot.sql
Note that the above script for generating a snapshot of the required table has some limitations, if the table for which the snapshot is to be generated contains columns of type long, ‘select*’ will not work here. The above SQL script does not automatically create a script that generates the required snapshot, you must create a snapshot of the table by explicitly adding a long column name to the select list. Here’s an example, if we want to create a snapshot of a table table1 that relies on a column note of type long, we need to write a separate script to create the snapshot as follows:
oracle how to create a database instance (oracle how to create a database)
From the Windows desktop to execute the “Start” → “Programs” → “Oracle-” → “Configuration and Removal Tools” → “DatabaseAssistant” command, open the DatabaseAssistant dialog box welcome screen. “Next button
Enable the Create Database option and click the Next button to enter the Step 2: Database Templates screen. Template” screen
Enable the “General Purpose or Transaction” option, click the “Next” button, and go to the “Step 3: Database Identity” screen
Enable the “General Purpose or Transaction” option, click the “Next” button, and go to the “Step 3: Database Identity” screen. “
In the “Step 3: Database Identification” screen, set the global database name of the new database, which is myorcl, and the database instance name (SID), which is the same as the global database name by default, which is also myorcl
Set the password. After setting the password, click the Next button to enter the Step 6: Storage Options screen, where the File System option is enabled
Click the Next button to enter the Step 6: Storage Options screen. “Next” button to enter the “Step 7: Database File Location” interface. After setting the storage location, click the Next button to enter “Step 8
¤Use the default settings, click the Next button to enter “Step 9: Click the Next button to enter the “Step 9: Database Contents” screen, where you can configure the sample scenario and customized scripts. Using the default settings, click the Next button to enter the Step 10: Initialization Parameters screen, where you can configure the memory, resize, character set and connection mode. To use the default settings, click the “Next” button to enter the “Step 11: Security Settings” interface, in which you can use the default settings, i.e., enable the option “Retain Enhanced 11g Default Security Settings”. In this screen, use the default setting, i.e., enable the “Retain enhanced 11g default security settings” option. ¤ Click the Next button to go to the Step 12: Automated Maintenance Tasks screen and select the Enable Automated Maintenance Tasks option.
Click the Next button to enter the Step 13: Database Storage interface, in which you can specify the database storage parameters, click the Next button to enter the Step 13: Database Storage interface, in which you can specify the database storage parameters, click the Next button to enter the Step 13: Database Storage interface, in which you can specify the database storage parameters. button, enter the “Step 14: Create Options” interface
Click the “Finish” button, in the pop-up “Confirm” dialog box, click “OK”. Click the “OK” button in the pop-up “Confirm” dialog box, you can start the creation of the new database
oracle database if I have 2 instances ORCL,MYDB, how to connect to the specified instance. Default is ORCL but I want to connect to MYDB
1, first parameter INSTANCE_TYPE has the value of ASM for ASM instances, for oracle instances the default value is RDBMS.
2, the default value of parameter DB_UNIQUE_NAME is ASM, which is the unique name of the ASM instance.
3. The parameter ASM_DISKGROUPS parameter, which specifies a list containing disk group names.
4. The parameter ASM_DISKSTRING specifies one or more strings, for the disk device that created the disk group.
5. The final parameter ASM_PREFERRED_READ_FAILURE_GROUPS is a list of failure groups, as shown below.
How to Operate (+) Connection in Oracle Database
How to Operate (+) Connection in Oracle Database
This article mainly introduces you to the actual operation of (+) Connection in Oracle Database, this article is a description of the relevant use of the code to lead to the actual operation of its operation, you if you are the ‘(+)’ in Oracle database. connection in the Oracle database, if you are one of the crazy family of words, come and take a look at it!
1. Slave table A.A1(+)=Master table B.B1
Copy code SELECT
1. TO_CHAR(TBA.FBS_IO_DATE,’YYYY/MM/DD’)A1
2.,TBA.TBA_TBA_TBD_NOA2
3.,TBB. TBB_NAME_SA3
4.,TBC.TBC_CHAR1A4
5.,TBD.TBD_NAMEA5
6.,TBA.TBA_SNS_NAME_SPA6
7.,TBA.TBA_TORI_KINA7
8.,,TBA. TO_CHAR(TBA.TBA_RPL_DATE,’YYYY/MM/DD’)A8
9.FROM
10.SNTBATRNTBA
11.,ZMTBCMSTTBC
12.,CMTBBMSTTBB
13. CMTBDMSTTBD
14.WHERE
15.TBA.TBA_KAI_CODE=’XXX’
16.ANDTBA.TBA_KESHI_KBN=’0′
17.ANDTBA.TBA_TAISYO_KBNIN(‘0’, ‘2’)
18.ANDTBB_KAI_CODE(+)=’XXX’
19.ANDTBB_BUN_CODE(+)=’S001′
20.ANDTBB_START_DATE<=SYSDATE
21. END_DATE>=SYSDATE
22.ANDTBB.TBB_CODE(+)=TBA.TBA_STN_CODE
23.ANDTBC.TBC_KAI_CODE(+)=’XXX’
24.ANDTBC.TBC_SUB_ KBN(+)=’SN’
25.ANDTBD.TBD_KAI_CODE(+)=’XXX’
26.ANDTBD.TBD_CODE(+)=TBA.TBA_SNS_CODE
2. Master Table A.A1=Slave Table B.B1(+)
1. Replication Code
2.SELECT
3.TBA.TBA_1,
4.TBB.TBB_1,
5.TBC.TBC_1
6.FROM
7.TBA,
8.(SELECTTBB.TBB_1,TBB. TBB_2
9.FROMTBB
10.WHERE
11.TBB.TBB_3LIKE’%NEWWEAPON%’TBB),
12.TBC
13.WHERE
14.Master Table TBC
15. TBA.TBA_A>2
16.Slave table TBB
17.ANDTBA.TBA_2=TBB.TBB_2
18.Slave table TBC
19.NOTE TRIM
20.ANDTRIM(TBA.TBA_3)=TRIM(TBC. TBC_3(+))
;