Categotry Archives: oracle 12c

Home » oracle 12c

rman 在oracle 12c的备份还原

Posted on 2014 年 1 月 23 日 by oradbca

由于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监听设置

Posted on 2013 年 8 月 9 日 by oradbca

在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

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

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

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

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

连接到:
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

连接到:
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 to`TRANSACTION` on the service, this automatically sets `COMMIT_OUTCOME` to `TRUE`. JDBC Replay Driver uses the`FAILOVER_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 `TRANSACTION`setting.
`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` and`failover_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 the`commit_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 the`COMMIT_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 to`DYNAMIC` (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的增、删、插的管理

Posted on 2013 年 8 月 9 日 by oradbca

PDB基本的日常管理

—增加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命令配置

Posted on 2013 年 8 月 8 日 by oradbca

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启动与关闭

Posted on 2013 年 8 月 7 日 by oradbca

在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;
/

2022年12月
一	二	三	四	五	六	日
« 九
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31