This topic describes how to create a PolarDB-O cluster, specify basic configurations, and connect to the cluster. Then, you can familiarize yourself with the entire process about how to purchase and use a PolarDB-O cluster.

Procedure

To purchase and use a PolarDB-O cluster, you must perform the following steps:

  1. Step 1: Create a PolarDB-O cluster
  2. Step 2: Configure the cluster whitelist
  3. Step 3: Create a database account
  4. Step 4: Connect to the database cluster
  5. Step 5: Create a database
  6. Step 6: Query data

Step 1: Create a PolarDB-O cluster

  1. Log on PolarDB console.
  2. On the upper-left corner of the page, click Create Cluster.
  3. Select Subscription or Pay-As-You-Go as Product Type.
    • Subscription: An upfront payment is required for the compute nodes when you create the cluster. You are charged for the consumed storage space by hour. The charges are deducted from your account balance on an hourly basis.
    • Pay-As-You-Go: This method does not require upfront payments. You are charged for the compute nodes and consumed storage space by hour. The charges are deducted from your account balance on an hourly basis.
  4. Specify the following parameters.
    Section Parameter Description
    Basic Configuration Region The region where the cluster is deployed. You cannot change the region after you purchase the instance.
    Note Make sure that you deploy your PolarDB-O cluster in the same region as the Elastic Compute Service (ECS) instance to which you want to connect. Otherwise, the instances cannot communicate through the internal network and optimal performance cannot be achieved.
    Create Type The create type of a Polar-O cluster. Valid values:
    • Create Primary Cluster: create a new Apsara PolarDB cluster.
    • Restore from Recycle: create a new cluster by restoring a backup of a deleted cluster from the recycle bin.
      • Source Version: the version of the cluster that has been deleted.
      • Deleted Clusters: the name of the cluster that has been deleted.
      • Backup History: select the backup that you want to restore.

    You can select other options to create databases of other engines.

    Primary Availability Zone The zone of the cluster.
    • Each zone is an independent geographical location within a region. The zones that are deployed in the same region are similar.
    • You can deploy your Apsara PolarDB cluster and the ECS instance in the same zone or in different zones.
    • You only need to select the primary zone. The system automatically selects a secondary zone.
    Network type

    By default, this parameter is set to VPC. You do not need to specify this parameter.

    VPC

    VSwitch

    Make sure that the Apsara PolarDB cluster and ECS instance are deployed in the same VPC network. Otherwise, the cluster and the ECS instance cannot communicate with each other over the internal network and achieve optimal performance.
    • If you have created a VPC network that meets your network plan, select the VPC. For example, if you have created an ECS instance and the VPC network where it is deployed meets your network plan, select this VPC.
    • Otherwise, use the default VPC and VSwitch.
      • Default VPC:
        • It is a unique VPC in the selected zone.
        • The default VPC uses a 16-bit subnet mask. For example, the CIDR block 172.31.0.0/16 provides up to 65,536 internal IP addresses
        • It is not included in the total number of VPC networks that you can create.
      • Default VSwitch:
        • It is a unique VSwitch in your selected zone.
        • The default VSwitch uses a 20-bit subnet mask. For example, the CIDR block 172.16.0.0/20 provides up to 4,096 internal IP addresses
        • The default VSwitch is not included in the total number of VSwitches that you can create in a VPC network.
    • If the default VPC and VSwitch cannot meet your business requirements, you can create your own VPC and VSwitch.
    Instance Compatibility Select Compatibility with Oracle Syntax.

    PolarDB-O is highly compatible with Oracle syntax. For more information, see Oracle compatibility.

    Node Specification Select the specifications as needed. All nodes in the Apsara PolarDB cluster are dedicated nodes with stable and reliable performance. For more information about specifications, see Specifications and pricing.
    Number of Nodes
    • The number of nodes in the cluster. You do not need to specify this parameter. The system automatically creates a read-only node with the same specification as that of the primary node.
    • If the primary node fails, the system automatically switches the read-only node as the primary node, and creates a new read-only node.
    • For more information about read-only nodes, see Architecture.
    Storage Cost The fee incurred by the storage. You do not need to specify this parameter. The system charges you on an hourly basis based on the actual data usage. For more information, see Specifications and pricing.
    Note You do not need to select a storage capacity when you purchase a cluster. The storage capacity can automatically resize based on your data usage.
    Enable TDE Specify whether to enable Transparent Data Encryption (TDE). After TDE is enabled, Apsara PolarDB encrypts cluster data files. This may reduce the performance by 5% to 10%.
    Note You cannot disable TDE after it is enabled.
  5. Specify Purchase Plan and Number for the cluster and click Buy Now. You can specify the purchase plan for only the subscription clusters.
    Note You can create a maximum of 50 clusters at a time. For example, you can create multiple clusters in scenarios such as enabling multiple game servers at a time.
  6. On the Confirm Order page, confirm your order information. Read the terms of the service agreement, select the check box, and click Buy Now.
  7. After you complete the payment, it takes 10 to 15 minutes to create the cluster. Then, the newly created cluster is displayed on the Clusters page in the console.
    Note
    • If some nodes in the cluster are still in the Creating state, the cluster is still being created and unavailable. The cluster is available only when the cluster is in the Running state.
    • Make sure that you have selected the region where the cluster is deployed. Otherwise, you cannot view your cluster.

Step 2: Configure the cluster whitelist

  1. In the left-side navigation pane, choose Settings and Management > Whitelists.
  2. On the Whitelists page, find the whitelist that you want to manage, and click Modify in the Actions column for the whitelist to modify the whitelist. You can also click Add IP Whitelist to add a whitelist.
    Whitelists
    • To modify the existing IP whitelist, click Modify in the Actions column.
    • To add an IP whitelist, click Add IP Whitelist.
  3. In the Add IP Whitelist panel, configure the information of the IP whitelist and click OK.
    • If you need to connect your ECS instance to the PolarDB cluster, you can view the IP addresses of the ECS instance in the Configuration Information section on the Instance Details page. Then, add these IP addresses to the whitelist.
      Note If the ECS instance and the PolarDB cluster are in the same region, such as the China (Hangzhou) region, use the private IP address of the ECS instance. If the ECS instance and the PolarDB cluster are in different regions, use the public IP address of the ECS instance. You can also migrate the ECS instance to the region where the PolarDB cluster resides. Then, you can use the private IP address of the ECS instance.
    • If you need to connect your on-premises servers, computers, or other cloud servers to the PolarDB cluster, add the related IP addresses to the whitelist.

Step 3: Create a database account

  1. Log on PolarDB console.
  2. On the top of the page, select the region where the target cluster is located.
  3. Find the target cluster and click the cluster ID to go to the Overview page.
  4. In the left-side navigation pane, choose Settings and Management > Accounts.
  5. On the page that appears, click Create Account.
  6. In the Create Account pane, configure the following parameters.
    Parameter Description
    Account Name

    Enter an account name. The account name must meet the following requirements:

    • It must start with a lowercase letter and end with a letter or a digit.
    • It can contain lowercase letters, digits, and underscores (_).
    • It must be 2 to 16 characters in length.
    • It cannot be a system reserved username, such as root or admin.
    Account Type
    • To create a privileged account, select Privileged Account.
    • To create a standard account, select Standard Account.
    Password Enter an account password. The password must meet the following requirements:
    • It must contain at least three of the following character types: uppercase letters, lowercase letters, digits, and special characters.
    • It must be 8 to 32 characters in length.
    • It can contain the following special characters:

      ! @#$%^&*()_+

    Confirm Password Enter the password again.
    Description Enter the information about the account to facilitate subsequent account management. The description must meet the following requirements:
    • It cannot start with http:// or https://.
    • It must start with a letter.
    • It can contain letters, digits, underscores (_), and hyphens (-).
    • It must be 2 to 256 characters in length.
  7. Click OK.

Step 4: Connect to the database cluster

In this step, Data Management (DMS) is used to show how to connect to a database cluster. To connect to a PolarDB-O cluster through a client, perform the following steps:

  1. Log on PolarDB console.
  2. On the top of the page, select the region where the target cluster is located.
  3. Find the target cluster and click the cluster ID to go to the Overview page.
  4. In the upper right corner of the Overview page, click Log On to Database to go to the RDS Database Logon page.
    Use DMS to connect to a PolarDB-O cluster
  5. On the RDS Database Logon page, enter the primary endpoint and the port number, and separate them with a colon (:). Then, enter the username and password of the privileged or standard account, and click Log On.
    When you connect to a cluster by using DMS, you can only use a primary endpoint. DMS does not support cluster endpoints.

For more information about how to connect to a PolarDB-O cluster, see Connect to a database cluster.

Step 5: Create a database

  • The following examples show how to create the dept, emp, and jobhist tables:
    CREATE TABLE dept (
        deptno          NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
        dname           VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
        loc             VARCHAR2(13)
    );
    CREATE TABLE emp (
        empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
        ename           VARCHAR2(10),
        job             VARCHAR2(9),
        mgr             NUMBER(4),
        hiredate        DATE,
        sal             NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
        comm            NUMBER(7,2),
        deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                            REFERENCES dept(deptno)
    );
    CREATE TABLE jobhist (
        empno           NUMBER(4) NOT NULL,
        startdate       DATE NOT NULL,
        enddate         DATE,
        job             VARCHAR2(9),
        sal             NUMBER(7,2),
        comm            NUMBER(7,2),
        deptno          NUMBER(2),
        chgdesc         VARCHAR2(80),
        CONSTRAINT jobhist_pk PRIMARY KEY (empno, startdate),
        CONSTRAINT jobhist_ref_emp_fk FOREIGN KEY (empno)
            REFERENCES emp(empno) ON DELETE CASCADE,
        CONSTRAINT jobhist_ref_dept_fk FOREIGN KEY (deptno)
            REFERENCES dept (deptno) ON DELETE SET NULL,
        CONSTRAINT jobhist_date_chk CHECK (startdate <= enddate)
    );
  • The following examples show how to insert data into the tables:
    INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
    INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
    INSERT INTO dept VALUES (30,'SALES','CHICAGO');
    INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
    INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
    INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
    INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
    INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
    INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
    INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30);
    INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);
    INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20);
    INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
    INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30);
    INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20);
    INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30);
    INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20);
    INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
    INSERT INTO jobhist VALUES (7369,'17-DEC-80',NULL,'CLERK',800,NULL,20,'New Hire');
    INSERT INTO jobhist VALUES (7499,'20-FEB-81',NULL,'SALESMAN',1600,300,30,'New Hire');
    INSERT INTO jobhist VALUES (7521,'22-FEB-81',NULL,'SALESMAN',1250,500,30,'New Hire');
    INSERT INTO jobhist VALUES (7566,'02-APR-81',NULL,'MANAGER',2975,NULL,20,'New Hire');
    INSERT INTO jobhist VALUES (7654,'28-SEP-81',NULL,'SALESMAN',1250,1400,30,'New Hire');
    INSERT INTO jobhist VALUES (7698,'01-MAY-81',NULL,'MANAGER',2850,NULL,30,'New Hire');
    INSERT INTO jobhist VALUES (7782,'09-JUN-81',NULL,'MANAGER',2450,NULL,10,'New Hire');
    INSERT INTO jobhist VALUES (7788,'19-APR-87','12-APR-88','CLERK',1000,NULL,20,'New Hire');
    INSERT INTO jobhist VALUES (7788,'13-APR-88','04-MAY-89','CLERK',1040,NULL,20,'Raise');
    INSERT INTO jobhist VALUES (7788,'05-MAY-90',NULL,'ANALYST',3000,NULL,20,'Promoted to Analyst');
    INSERT INTO jobhist VALUES (7839,'17-NOV-81',NULL,'PRESIDENT',5000,NULL,10,'New Hire');
    INSERT INTO jobhist VALUES (7844,'08-SEP-81',NULL,'SALESMAN',1500,0,30,'New Hire');
    INSERT INTO jobhist VALUES (7876,'23-MAY-87',NULL,'CLERK',1100,NULL,20,'New Hire');
    INSERT INTO jobhist VALUES (7900,'03-DEC-81','14-JAN-83','CLERK',950,NULL,10,'New Hire');
    INSERT INTO jobhist VALUES (7900,'15-JAN-83',NULL,'CLERK',950,NULL,30,'Changed to Dept 30');
    INSERT INTO jobhist VALUES (7902,'03-DEC-81',NULL,'ANALYST',3000,NULL,20,'New Hire');
    INSERT INTO jobhist VALUES (7934,'23-JAN-82',NULL,'CLERK',1300,NULL,10,'New Hire');

Step 6: Query data

Execute SELECT statements to query data from the tables. The following example shows how to query data of all columns in the emp table:

SELECT * FROM emp;

 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno
-------+--------+-----------+------+--------------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
(14 rows)

Execute the following SQL statement to query data of specified columns:

SELECT ename, sal, sal * 24 AS yearly_salary, deptno FROM emp;

 ename  |   sal   | yearly_salary | deptno
--------+---------+---------------+--------
 SMITH  |  800.00 |      19200.00 |     20
 ALLEN  | 1600.00 |      38400.00 |     30
 WARD   | 1250.00 |      30000.00 |     30
 JONES  | 2975.00 |      71400.00 |     20
 MARTIN | 1250.00 |      30000.00 |     30
 BLAKE  | 2850.00 |      68400.00 |     30
 CLARK  | 2450.00 |      58800.00 |     10
 SCOTT  | 3000.00 |      72000.00 |     20
 KING   | 5000.00 |     120000.00 |     10
 TURNER | 1500.00 |      36000.00 |     30
 ADAMS  | 1100.00 |      26400.00 |     20
 JAMES  |  950.00 |      22800.00 |     30
 FORD   | 3000.00 |      72000.00 |     20
 MILLER | 1300.00 |      31200.00 |     10
(14 rows)

Execute the following SQL statement to query employees whose salary exceeds 1,000 in department 20:

SELECT ename, sal, deptno FROM emp WHERE deptno = 20 AND sal > 1000;

 ename |   sal   | deptno
-------+---------+--------
 JONES | 2975.00 |     20
 SCOTT | 3000.00 |     20
 ADAMS | 1100.00 |     20
 FORD  | 3000.00 |     20
(4 rows)

Execute SQL statements to return data in a specific order by using ORDER BY. The following example shows how to return data in alphabetical order of names:

SELECT ename, sal, deptno FROM emp ORDER BY ename;

 ename  |   sal   | deptno
--------+---------+--------
 ADAMS  | 1100.00 |     20
 ALLEN  | 1600.00 |     30
 BLAKE  | 2850.00 |     30
 CLARK  | 2450.00 |     10
 FORD   | 3000.00 |     20
 JAMES  |  950.00 |     30
 JONES  | 2975.00 |     20
 KING   | 5000.00 |     10
 MARTIN | 1250.00 |     30
 MILLER | 1300.00 |     10
 SCOTT  | 3000.00 |     20
 SMITH  |  800.00 |     20
 TURNER | 1500.00 |     30
 WARD   | 1250.00 |     30
(14 rows)

Execute SQL statements to query distinct values in a column. The following example shows how to query distinct job titles in the job column:

SELECT DISTINCT job FROM emp;

    job
-----------
 ANALYST
 CLERK
 MANAGER
 PRESIDENT
 SALESMAN
(5 rows)