Tag Archives: 12c

rman 在oracle 12c的备份还原

0

Posted on by

  由于12c有CDB和PDB的概念。rman在备份使用上会有点区别。

 

备份:

1.备份全部的数据库(包括CDB)

[oracle@ora12c ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Jan 23 14:42:27 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADBCA (DBID=261209273)


RMAN> BACKUP DATABASE;

 

2.只备份CDB

[oracle@ora12c ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Jan 23 14:46:10 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADBCA (DBID=261209273)

RMAN> BACKUP DATABASE ROOT;

 

3.只备份PDB(一个或多个)

单个CDB备份:

[oracle@ora12c ~]$ rman target=sys/oracle@pdb1

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Jan 23 14:48:00 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADBCA (DBID=261209273)

RMAN> BACKUP DATABASE;

或者

[oracle@ora12c ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Jan 23 14:48:46 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADBCA (DBID=261209273)

RMAN> BACKUP PLUGGABLE DATABASE pdb1;

 

 

多个CDB备份:

[oracle@ora12c ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Jan 23 14:49:29 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADBCA (DBID=261209273)

RMAN> BACKUP PLUGGABLE DATABASE pdb1,pdb2;

 

 

4.只备份其中的某一个表空间

     这个和原来的没啥区别,只需要rman登入要备份那个数据库就行了。

[oracle@ora12c ~]$ rman target=sys/oracle@pdb1

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Jan 23 14:53:54 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADBCA (DBID=261209273)

RMAN> BACKUP TABLESPACE users;

 

 

还原:

 

1.全部数据库还原

[oracle@ora12c ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Jan 23 15:11:23 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADBCA (DBID=261209273)

RMAN> run
2> {
3> startup mount;
4> restore database;
5> recover database;
6> alter database open;
7> }

 

2.只CDB还原

[oracle@ora12c ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Jan 23 15:12:41 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADBCA (DBID=261209273)

RMAN> run
2> {
3> startup mount;
4> restore database root;
5> recover database root;
6> alter database open;
7> }

 

3.只PDB还原(一个或者多个)

[oracle@ora12c ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Jan 23 15:20:14 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADBCA (DBID=261209273)

RMAN> run
2> {
3> alter pluggable database pdb1, pdb2 close;
4> restore pluggable database pdb1, pdb2;
5> recover pluggable database pdb1, pdb2;
6> alterpluggable database pdb1, pdb2 open;
7> }

 

12c PDB监听设置

1

Posted on by

        在12c database中所有的CDB,PDB都是共用一个监听的,怎么样关闭某一个PDB的监听,而不影响别的数据库的连接。可以用DBMS_SERVICE来关闭相应的SERVICE来阻止客户端的连接。

 

—需要会话切到PDB

alter session set container=pdb1;

 

服务的关闭:

SQL>  exec DBMS_SERVICE.stop_SERVICE('pdb1');

PL/SQL procedure successfully completed.

 

服务的开启:

SQL> exec DBMS_SERVICE.start_SERVICE('pdb1');

PL/SQL procedure successfully completed.

 

 

kill掉全部连接:

SQL> EXEC DBMS_SERVICE.DISCONNECT_SESSION('pdb1');

PL/SQL procedure successfully completed.

 

演示如下:

[oracle@ora12c pdb1]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 9 15:48:33 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> 
SQL> 
SQL> alter session set container=pdb1;

Session altered.

SQL> show con_name

CON_NAME
——————————
PDB1
SQL> 
SQL> !lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 – Production on 09-AUG-2013 15:49:11

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora12c)(PORT=1521)))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 – Production
Start Date                09-AUG-2013 00:31:45
Uptime                    0 days 15 hr. 17 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora12c/listener/alert/log.xml
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora12c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/wh12cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary…
Service "pdb1" has 1 instance(s).
  Instance "wh12cdb1", status READY, has 1 handler(s) for this service…

Service "pdb2" has 1 instance(s).
  Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
Service "wh12cdb1" has 1 instance(s).
  Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
Service "wh12cdb1XDB" has 1 instance(s).
  Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
The command completed successfully

SQL> exec DBMS_SERVICE.STOP_SERVICE('pdb1');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 – Production on 09-AUG-2013 15:50:03

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora12c)(PORT=1521)))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 – Production
Start Date                09-AUG-2013 00:31:45
Uptime                    0 days 15 hr. 18 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora12c/listener/alert/log.xml
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora12c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/wh12cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary…
Service "pdb2" has 1 instance(s).
  Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
Service "wh12cdb1" has 1 instance(s).
  Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
Service "wh12cdb1XDB" has 1 instance(s).
  Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
The command completed successfully

SQL> exec DBMS_SERVICE.start_SERVICE('pdb1');

PL/SQL procedure successfully completed.


SQL> !lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 – Production on 09-AUG-2013 15:50:28

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora12c)(PORT=1521)))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 – Production
Start Date                09-AUG-2013 00:31:45
Uptime                    0 days 15 hr. 18 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora12c/listener/alert/log.xml
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora12c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/wh12cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary…
Service "pdb1" has 1 instance(s).
  Instance "wh12cdb1", status READY, has 1 handler(s) for this service…

Service "pdb2" has 1 instance(s).
  Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
Service "wh12cdb1" has 1 instance(s).
  Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
Service "wh12cdb1XDB" has 1 instance(s).
  Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
The command completed successfully

SQL> 
SQL> EXEC DBMS_SERVICE.DISCONNECT_SESSION('pdb1');

PL/SQL procedure successfully completed.

SQL> 

 

可以看win一台客户端会话被中断了

C:\Users\Administrator>sqlplus system/oracle@pdb1

SQL*Plus: Release 11.2.0.3.0 Production on 星期五 8月 9 15:47:35 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

SQL> select 'oradbca' oradbca from dual;

ORADBCA
——-
oradbca

SQL> select 'oradbca' oradbca from dual;
select 'oradbca' oradbca from dual
*
第 1 行出现错误:
ORA-00028: 您的会话己被终止


SQL> exit
从 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions 断开

C:\Users\Administrator>sqlplus system/oracle@pdb1

SQL*Plus: Release 11.2.0.3.0 Production on 星期五 8月 9 15:51:18 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

SQL>

 

 

DBMS_SERVICE Package Subprograms

Subprogram Description

CREATE_SERVICE Procedure

Creates service

DELETE_SERVICE Procedure

Deletes service

DISCONNECT_SESSION Procedure

Disconnects sessions running under this service

MODIFY_SERVICE Procedure

Modifies service

START_SERVICE Procedure

Activates service

STOP_SERVICE Procedure

Stops service

 

 

创建service具体参数说明:

DBMS_SERVICE.CREATE_SERVICE(
   service_name               IN VARCHAR2, 
   network_name               IN VARCHAR2,
   goal                       IN NUMBER DEFAULT NULL,
   dtp                        IN BOOLEAN DEFAULT NULL,
   aq_ha_notifications        IN BOOLEAN DEFAULT NULL,
   failover_method            IN VARCHAR2 DEFAULT NULL,
   failover_type              IN VARCHAR2 DEFAULT NULL,
   failover_retries           IN NUMBER DEFAULT NULL,
   failover_delay             IN NUMBER DEFAULT NULL,
   clb_goal                   IN NUMBER DEFAULT NULL,
   edition                    IN VARCHAR2 DEFAULT NULL);

Parameters

Table 140-6 CREATE_SERVICE Procedure Parameters

Parameter Description

service_name

Name of the service, limited to 64 characters in the Data Dictionary

network_name

Network name of the service as used in SQLNet connect descriptors for client connections. This is limited to the NET service_names character set (see Oracle Database Net Services Reference).

parameter_array

Associative array with name-value pairs of the service attributes. Supported names:

  • goal

  • dtp

  • aq_ha_notifications

  • failover_method

  • failover_type

  • failover_retries

  • failover_delay

  • clb_goal

  • edition

  • commit_outcome

  • retention_timeout

  • replay_initiation_timeout

  • session_state_consistency

  • sql_translation_profile

goal

Workload management goal directive for the service. Valid values:

  • DBMS_SERVICE.GOAL_SERVICE_TIME

  • DBMS_SERVICE.GOAL_THROUGHPUT

  • DBMS_SERVICE.GOAL_NONE

dtp

Declares the service to be for X/Open Distributed Transaction Processing (DTP) or any distributed transaction (especially XA)

aq_ha_notifications

Determines whether Fast Application Notification (FAN) is enabled for OCI/OCCI/ODP. In Oracle Database12c, FAN uses Oracle Notification Services (ONS). This parameter is still used to enable FAN. FAN is recommended for all High Availability systems, and is on by default for Application Continuity

failover_method

Failover TYPE for the service for Application Continuity and TAF. If the failover_type is set toTRANSACTION on the service, this automatically sets COMMIT_OUTCOME to TRUE. JDBC Replay Driver uses theFAILOVER_TYPE service attribute setting of TRANSACTION for TRANSACTION failover. OCI uses the older settings of SELECT and SESSION. The server only accepts FAILOVER_METHOD = BASIC with the TRANSACTIONsetting.

failover_type

Failover TYPE for the service for Application Continuity and TAF.

failover_retries

Number of connection retries for Application Continuity and TAF. Using the failover_retries andfailover_delay parameters, the failover can be delayed until the service is next available. This parameter is for connecting. It does not control the number of failovers, which is 3 for each incident for Application Continuity.

failover_delay

Delay in seconds between connection retries for Application Continuity and TAF. The default is 10 seconds for Application Continuity. Do not use a 0-second delay if the service needs time to failover and register. Long delays are good for planned outages and to failover to Data Guard. Short delays work well with RAC when the service is already available.

edition

If this argument has a non-NULL value, this provides the initial session edition for subsequent database connections using this service that do not specify an edition. If no value is specified, this argument has no effect.

During service creation or modification, no validation is performed on this parameter.

At connection time, if the connecting user does not have USE privilege on the edition, or the edition does not exist, this raises the error ORA-38802 (edition does not exist).

commit_outcome

Determines whether transaction COMMIT outcome is accessible after the COMMIT has executed. While the database guarantees that COMMIT is durable, this ensures that the outcome of the COMMIT is durable. Applications use the feature to probe the status of the commit last executed after an outage, and is available to applications to determine an outcome. Note:

  • Invoking the GET_LTXID_OUTCOME Procedure of the DBMS_APP_CONT package requires that thecommit_outcome attribute be set.

  • commit_outcome has no effect on active Data Guard and read-only databases.

  • commit_outcome is only allowed on the database service and on user-defined database services

retention_timeout

Used in conjunction with commit_outcome, it determines the amount of time (in seconds) that theCOMMIT_OUTCOME is retained. Default is 24 hours (86400). Maximum value is 30 days (2592000).

replay_initiation_timeout

For Application Continuity, replay_initiation_timeout is the difference between the time of original execution of first operation of a request, and the time that the replay is ready to start after a successful reconnect. Replay initiation time is measured from the time that the request was originally submitted until the time that replay has connected and is ready to replay. When replay is expected, keep this value high. Default is 900 seconds.

session_state_consistency

Describes how nontransactional is changed during a request (values are DYNAMIC or STATIC). This parameter is considered only if failover_type is set to TRANSACTION for Application Continuity. Examples of session state are NLS settings, optimizer preferences, event settings, PL/SQL global variables, temporary tables, advanced queues, LOBs, and result cache. If these values change after the request starts, set toDYNAMIC (default). Almost all applications should use DYNAMIC mode. If you are unsure, use DYNAMIC mode.

sql_translation_name

Name of SQL translation unit

clb_goal

Method used for Connection Load Balancing (see Table 140-2, "Constants Used in Connection Balancing Goal Arguments")

Examples

DBMS_SERVICE.CREATE_SERVICE('ernie.us.oracle.com','ernie.us.oracle.com');

DECLARE
   params dbms_service.svc_parameter_array;
   BEGIN
      params('FAILOVER_TYPE')            :='TRANSACTION';
      params('REPLAY_INITIATION_TIMEOUT'):=1800;
      params('RETENTION_TIMEOUT')        :=86400;
      params('FAILOVER_DELAY')           :=10;
      params('FAILOVER_RETRIES')         :=30;
      params('commit_outcome')           :='true';
      params('aq_ha_notifications')      :='true';
      DBMS_SERVICE.MODIFY_SERVICE('GOLD',params);
   END;

 

 

PDB的增、删、插的管理

0

Posted on by

PDB基本的日常管理

create_pluggable_database

—增加PDB

SQL>CREATE PLUGGABLE DATABASE pdb1
ADMIN USER pdbadmin IDENTIFIED BY oracle
ROLES=(DBA)
STORAGE unlimited
PATH_PREFIX = '/u01/app/oracle/oradata/wh12cdb1/pdb1/'
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/wh12cdb1/pdbseed/', '/u01/app/oracle/oradata/wh12cdb1/pdb1/');

 

–创建默认表空间

SQL>  create tablespace users datafile '/u01/app/oracle/oradata/wh12cdb1/pdb1/user01.dbf' size 500m autoextend on next 100m;

Tablespace created.

SQL> alter database default tablespace users;

Database altered.

 


–克隆

SQL>alter pluggable database pdb1 close; 

  
SQL> alter pluggable database pdb1 open read only; 
  
Pluggable database altered. 
  

SQL>create pluggable database pdb2 from pdb1 file_name_convert =
('/u01/app/oracle/oradata/wh12cdb1/pdb1','/u01/app/oracle/oradata/wh12cdb1/pdb2'); 

 

—卸载,插入

SQL> 
SQL> alter pluggable database  PDB2 close;

Pluggable database altered.


SQL> alter  pluggable database PDB2 UNPLUG into '/u01/PDB2.xml';

Pluggable database altered.


SQL> DROP PLUGGABLE DATABASE pdb2 ;

 

–自动copy的异目录克隆

SQL>create pluggable database pdb3 using '/u01/PDB2.xml' copy file_name_convert=
('/u01/app/oracle/oradata/wh12cdb1/pdb2','/u01/app/oracle/oradata/wh12cdb1/pdb3') TEMPFILE REUSE;

 

–手动copy的同目录克隆

SQL>CREATE PLUGGABLE DATABASE pdb3  USING '/u01/PDB2.xml' nocopy 


–手动copy的异目录克隆

SQL>CREATE PLUGGABLE DATABASE pdb3
  USING '/u01/PDB2.xml' nocopy 
  SOURCE_FILE_NAME_CONVERT =
('/u01/app/oracle/oradata/wh12cdb1/pdb2','/u01/app/oracle/oradata/wh12cdb1/pdb3')

DBMS_XDB_CONFIG命令配置

1

Posted on by

 

DBMS_XDB_CONFIG用来配置Enterprise Manager Database Express(EM)通过自定义用HTTPS,HTTP的什么端口来连接,还可以配置FTP来连接数据库。。

DBMS_XDB_CONFIG的配置:

查看端口:

               select dbms_xdb_config.gethttpport() from dual;

              select dbms_xdb_config.gethttpsport () from dual;

             select dbms_xdb_config.getftpport () from dual;

开启并设置一个端口:

              exec DBMS_XDB_CONFIG.setHTTPPort(8888);

              exec DBMS_XDB_CONFIG.setHTTPsPort(8888);

              exec DBMS_XDB_CONFIG.setFTPPort(8888);

关闭:

              exec DBMS_XDB_CONFIG.setHTTPPort(0);

              exec DBMS_XDB_CONFIG.setHTTPsPort(0);

              exec DBMS_XDB_CONFIG.setFTPPort(0);

 

看全部的信息:

SELECT DBMS_XDB.cfg_get() FROM DUAL;

 

DBMS_XDB: Configuration Management Functions and Procedures

Function/Procedure Description

cfg_get

Returns the configuration information for the current session.

cfg_refresh

Refreshes the session configuration information using the current Oracle XML DB configuration file, xdbconfig.xml.

cfg_update

Updates the Oracle XML DB configuration information. This writes the configuration file, xdbconfig.xml.

getFTPPort

Returns the current FTP port number.

getHTTPPort

Returns the current HTTP port number.

setFTPPort

Sets the Oracle XML DB FTP port to the specified port number.

setHTTPPort

Sets the Oracle XML DB HTTP port to the specified port number.

 

oracle 12c pdb启动与关闭

0

Posted on by

在oracle 12c pdb数据库默认是不起来的,下面介绍一下pdb起关的一些常用命令。

默认状态:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED

 

全部开启pdb:

SQL>  alter pluggable database all  open;

Pluggable database altered.

 全部关闭pdb:

SQL> alter pluggable database all close immediate;

Pluggable database altered.

 

当然也可以对个别pdb库关闭或开启:

SQL> alter pluggable database PDB1 close immediate;

Pluggable database altered.

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

 

怎么样让pdb随着instance起来而起来呢?需要写一个TRIGGER来拉动pdb

conn / as sysdba

CREATE TRIGGER open_all_pdb
   AFTER STARTUP
   ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdb;
/