彦祖的小号-CSDN博客

原创 Administering PDBs with SQL*Plus

使用alter pluggable database管理pdb详见SQL语法Using the ALTER SYSTEM Statement to Modify a PDB除下面alter system可在pdb中执行，其它alter system需要在root使用comm user执行，它将更改所有pdbs:ALTER SYSTEM FLUSH SHARED_POOLALTER SYSTEM FLUSH BUFFER_CACHEALTER SYSTEM FLUSH FLASH_CAC..

2021-08-09 13:57:23 947

原创 Administering a CDB with SQL*Plus

（一）Administrative Tasks Common to CDBs and Non-CDBs因为以下资源在CDB中是共享的，所以在CDB配置会影响所有pdbs，管理方法同non-cdb，详见alter database语法Managing processes Managing memory Monitoring errors and alerts：A CDB has one alert log for the entire CDB Managing diagnostic data..

2021-08-09 13:56:55 970

原创 Administering Application Containers with SQL*Plus

Note： application root的comm user只存在于application container中（包括application root, application pdbs, application seed）（一）Managing Applications in an Application ContainerApplication表示通过用户连接数据库使用应用相关数据，而CDB中的application核心在于把它使用的连接用户(common user)与数据(comm..

2021-08-09 13:56:14 1066

原创 Creating PDBs with SQL*Plus

（一）Techniques for Creating a PDB创建新pdb会复制源库所有临时表空间，包括非默认表空间Users in the PDB who used the default temporary tablespace of the source non-CDB or PDB use the default temporary tablespace of the cloned PDB. Users who used non-default temporary tablespa..

2021-08-09 13:55:28 1030

原创 Creating and Configuring a CDB

Oracle使用create database或dbca来创建数据库实例，只是现在分为CDB实例与non-CDB实例，单台服务器可安装多个实例，它们间互不影响Note：A CDB can contain up to 4,096 PDBs(including PDBs, application roots, application PDBs, application seeds, and proxy PDBs. This number does not include the CDB root a..

2021-08-09 13:54:27 760

原创 Creating and Removing Application Containers

The application PDBs and application root can share application common objects.There are three types of application common objects:Metadata-linked application common objects store the metadata for specific objects, such as tables, so that the contain..

2021-08-09 13:53:55 819

原创 CREATE PLUGGABLE DATABASE Syntax

主要分为三个子句：Create_pdb_from_seedCreate_pdb_cloneCreate_pdb_from_xmlAS APPLICATION CONTAINER Clause用于创建application container, 而不是application container下的pdb(即application pdb)执行时当前container必须为CDB$root而不能为application root.创建application pdb方法与一般pd...

2021-08-09 13:52:49 1494

原创 ALTER PLUGGABLE DATABASE Syntax

Alter pluggable database只能对traditional PDB, an application container, or an application PDB使用，可以在cdb$root或是pdb中使用，后者只对此pdb产生影响基于向后兼容，也可以在pdb下使用alter database来设置pdb.Alter pluggable database privilege：To change the open mode of the PDB from mounted..

2021-08-09 13:52:31 1851

原创 Managing Services Associated with PDBs

创建PDB时会自动创建它的默认service，名字与pdb名相同，此servcie是用于DBA管理的，尽量不要用于应用连接，因为没有SERIVE属性，用也无所谓PDB数据库是以动态监听连接的，PDB无法使用静态服务( 因为PDB不是一个实例，无法使用SID作实例名）When a PDB is created, a new default service for the PDB is created automatically, and this service has the same name..

2021-08-06 09:45:50 747

原创 Database Character Set for a Multitenant Container Database

Starting with Oracle Database 12cRelease 2 (12.2), pluggable databases (PDBs) in a multitenant container database (CDB) can have differentdatabasecharacter sets and differentnationalcharactersets.The databases orPDB candidatesthat can be plugged...

2021-08-06 09:45:18 745

原创 Concepts of the Multitenant Architecture

（一）Challenges for a Non-CDB ArchitectureLarge enterprises may use hundreds or thousands of databases. Often these databases run on different platforms on multiple physical servers.Because of improvements in hardware technology, especially the increas..

2021-08-06 09:44:49 767

原创 Viewing Information About CDBs & PDBs

（一）原理The data dictionary that stores the metadata for the CDBas a whole is stored only in the system tablespaces.The data dictionary that stores the metadata for a specific PDB is stored in the self-contained tablespaces dedicated to this PDB.The PD...

2021-08-06 09:44:17 856

原创 CDB&PDB USERS AND PRIVILLEGE

容器中只有以下DDL操作可以使用container=all|current，其它DDL只能对当前的容器有效：create user, alter user, create role, grant, revokeuser/role：comon user/role：只能在cdb$root或application root中创建，即创建时不用指定container=alllocal user/role：只能在pdb或application pdb中创建，即创建时也不用指定container=cu..

2021-08-06 09:43:10 1173

原创 Catcon.pl使用

在Oracle 12c中，Oracle 建议使用catcon.pl 脚本来执行SQL 脚本和SQL 语句Catcon.pl脚本是一个perl 脚本，需要在操作系统中执行。脚本在$ORACLE_HOME/rdbms/admin目录下In a CDB, the catcon.pl script is the best way to run SQL scripts and SQL statements.An Oracle Database installation includes severa..

2021-08-06 09:40:44 1042

原创 Configuring Operating System Users for a PDB

Instead theoracleoperating system user, you can set a specific user account to be the operating system user for that PDB.If you do not set a specific user to be the operating system user for the PDB, then by default the PDB uses theoracleoperating ...

2021-08-06 09:40:03 784

原创临时表空间及REDO LOG重建

（一）TEMPORARY TABLESPACE AND TEMPORARYFILE REBUILD无论是手动还是RMAN备份均不会备份临时表空间，恢复数据文件、控制文件以及重建控制文件均不会恢复临时数据文件，RECOVER DATABASE不会对临时空间操作如果临时数据文件未丢失则恢复后会继续使用；如果临时数据文件丢失，则需要OPEN下重建因此恢复后一定要检查临时表空间是否可用，如果启动OPEN发现临时表空间不存在或临时表空间的数据文件不存在重建即可：1. 重建临时数据文件：select tab

2021-08-06 09:39:14 832

原创 Be Careful of these Commands when you recover database

（一）ALTER SYSTEM FLUSHFLUSH命令会清空相应缓存，它应该只是清空clean block，会对缓存命中率产生影响，但对于脏块并不会触发写入磁盘，仍保留在buffer cache，所以在数据文件丢失执行不会导致宕机，但执行此命令也没有意义FLUSH SHARED_POOL TheFLUSHSHARED_POOLclause lets you clear data from the shared pool in the system global area (SGA)...

2021-08-06 09:38:43 724

原创 Using RMAN in CDB or PDB

Connecting as Target to a CDB/PDB连接CDB$ROOT可以备份root、其下单个或多个PDB以及整个CDB, 如BACKUP PLUGGABLE DATABASE sales, hr;There are three most comon ways to connect as target to the root:Connecting locally as a common user:$ rman target sysConnecting with o...

2021-08-06 09:36:30 986

原创 Tuning RMAN Performance

An RMAN backup or restore job can be divided into separate phases or components. The slowest of these phases in any RMAN job is called thebottleneck. The purpose of RMAN tuning is to identify the bottlenecks for a given job and use RMAN commands, initia...

2021-08-05 11:49:42 1036

原创 RMAN相关视图

The following table lists the techniques used to access metadata from the RMAN repository.1)RMANLISTandREPORTcommands：用于RMAN备份恢复，即使用LIST与REPORT命令2)V$views：用于手动备份恢复，详细见手动备份恢复部分When the database is open, severalV$views provide direct access to RMAN...

2021-08-05 11:48:56 789

原创 RMAN Restore & Recover Concepts

RMAN provides multiple methods of data repair.Depending on the situations you anticipate, consider incorporating each of the options described in the following table into your strategy for responding to data loss, and then set up your database to make ..

2021-08-05 11:48:24 89

原创 RMAN PARALLELISM

（一）RMAN Channel and RMAN SessionRMAN CHANNEL与数据库的server session对应An RMAN channel represents one stream of data to a device, and corresponds to one database server session.During a backup or restore operation, the channel reads data from the input de...

2021-08-05 11:47:37 227

原创 RMAN Compatibility

This appendix describes the requirements for compatibility among the different components of the Recovery Manager (RMAN) environment.（一）About RMAN CompatibilityTable C-1describes the components of an RMAN environment. Each component has a release nu...

2021-08-05 11:47:04 56

原创 HOW TO USE RMAN

RMAN的详细语法查《Database Backup and Recovery Reference》,此文档未看有时间可以看一遍如果忘记RMAN语法可以慢慢试出来，如忘记LIST语法则输入LIST后显示RMAN-01009: syntax error: found ";": expecting one of: "all, archivelog, backed, backuppiece, backupset, backup, completed, controlfilecopy, copy, da..

2021-08-05 11:46:39 728

原创 RMAN(Recovery Manager)

（一）About RMANRMAN Architecturetarget databaseThe work of backup and recovery is performed by server sessions running on the target database.recovery catalog databaseA database containing arecovery catalog, which contains metadata that RMAN uses...

2021-08-05 11:45:01 375

原创 Report RMAN JOB Process

（一）查看备份进度相关视图V$RMAN_BACKUP_JOB_DETAILS与V$RMAN_BACKUP_SUBJOB_DETAILSSESSION_KEY: Session identifierTIME_TAKEN_DISPLAY: Time taken查看任务起始结束及耗时：COL STATUS FORMAT a9COL hrs FORMAT 999.99SELECT SESSION_KEY, INPUT_TYPE, STATUS,TO_CH...

2021-08-05 11:44:19 76

原创 Preparing for Complete Database Recovery

（一）Determining the DBID of the Database在使用autobackup恢复SPFILE及controlfile时需要指定DBIDIn situations requiring the recovery of your server parameter file or control file from autobackup, you must know the DBID. DBID记录在控制文件中，一定要把数据库的DBID记在小本本上，否则数据库无法moun..

2021-08-05 11:43:45 67

原创 ORACLE表空间与数据文件OFFLINE区别

（一）OFFLINE TABLESPACEALTER TABLESAPCE OFFLINE可用选项包括NORMAL,TEMPORARY,IMMEDIATE,FOR RECOVERYOFFLINE NORMAL时该表空间的所有数据文件均要ONLINE状态，ONLINE不用RECOVERSpecifyNORMAL to flush all blocks in all data files in the tablespace out of the system global area (SG...

2021-08-05 11:43:16 657

原创 Database Incarnations

INCARNATION可以翻译为转世在使用数据库的时间点恢复或使用备份控制文件恢复后必须使用OPEN RESETLOGSAfter complete recovery, you can resume normal operations without anOPEN RESETLOGS. After a DBPITR or recovery with a backup control file, however, you must open the database with theRESE...

2021-08-04 09:48:51 305

原创 Configuring the RMAN Environment

配置信息存于target database控制文件中，即不同target database可以使用不同配置To simplify ongoing use of RMAN, you can set several persistent configuration settings for each target database. These settings control many aspects of RMAN behavior. RMAN provides sensible defaults ..

2021-08-04 09:48:21 147

原创 Configuring RMAN for Use with a Shared Server

RMAN cannot connect to a target database through a shared server dispatcher. RMAN requires a dedicated server process.If your target database is configured for a shared server, then you must modify your Oracle Net configuration to provide dedicated ser..

2021-08-04 09:47:49 59

原创 CONFIGURE CONTROFILE&FAST RECOVERY AREA

（一）Maintaining the Control File Repository控制文件记录包括两类：可覆盖以及不可覆盖记录The control file contains two types of records:Circular reuse records contain noncritical information that is eligible to be overwritten if needed. TheCONTROL_FILE_RECORD_KEEP_TIMEinit..

2021-08-04 09:47:20 119

原创 Complete Database Recovery

本文为数据文件缺失而进行的全库或表空间级完全恢复，未涉及控制文件及SPFILE恢复（一）Performing Complete Recovery of the Whole Database在整库恢复后会依据控制文件记录重建原来配置的临时表空间，但可能会无法创建成功After restore and recovery of a whole database, when the database is open, missing temporary tablespaces that were r..

2021-08-04 09:46:47 195

原创 Block Corrupt

Oracle有不同方法来监测及修复data block corruption，常用的修复方法重建对象对于表或索引它们自身包含数据，重建不能恢复数据，可以使用DBMS_REPAIR修复DBMS_REPAIR只是把table及index的corruption blocks标识后忽略，在下次查询或操作时不会对corruption blocks操作，不会对corruption block进行数据恢复，所以一般用于对块间的逻辑坏块进行恢复RMAN的RECOVER BLOCK来恢复Oracle Da..

2021-08-04 09:46:16 490

原创 Backing Up Archived Redo Logs with RMAN

（一）About Archived Redo Log Failover归档日志如果出现缺失或坏块RMAN会自动到其它归档日志路径找对应归档日志Thearchived redo log failoverfeature enables RMAN to complete a backup even when some archiving destinations are missing logs or contain logs with corrupt blocks. If at least on...

2021-08-04 09:45:24 61

原创使用dbms_logmnr挖掘误操作

SQL> create table t1(id int) tablespace test;SQL> insert into t1 values(1);SQL> commit;SQL> drop table t1 purge;SQL> alter system switch logfile;SQL> /SQL> /SQL> select name from v$archived_log;NAME---------------...

2021-08-04 09:44:53 182

原创使用_ALLOW_RESETLOGS_CORRUPTION 强行启动数据库

TECH: Summary For Forcing The Database Open With `_ALLOW_RESETLOGS_CORRUPTION` with Automatic Undo Management [ID 283945.1]Warning~~~~~~~The following instructions should only be used under the explicit directionof Oracle Support. These steps s..

2021-08-04 09:44:21 411

原创恢复表空间或数据文件与使用控制文件测试

不论是RMAN还是手动恢复均是使用数据文件的备份来应用归档前滚，即你无法恢复到数据文件备份以前的状态；相对数据文件，控制文件是可以恢复到之前状态的，如使用当前控制文件对数据库进行时间点恢复。即你要看数据文件备份的时间点来评估最早恢复时间在恢复表空间或数据文件时与使用的控制文件有以下关系：恢复创建的表空间或数据文件数据文件备份 --> 控制文件 --> 执行了CREATE TABELSPACE或ADD DATAFILE语句数据文件备份 --> 执行了CREATE TABEL..

2021-08-04 09:43:51 101

原创 Verifying User-Managed Data File Backups

You must periodically verify your backups to ensure that they are usable for recovery.（一）Testing the Restoration of Data File BackupsThe best way to test the usability of data file backups is to restore them to a separate host and attempt to open the..

2021-08-02 10:01:10 128

原创 User-Managed Backups&Recovery of Tablespaces and Data Files

（一）Making User-Managed Backups of Tablespaces and Data Files1. Making User-Managed Backups of Offline Tablespaces and Data FilesNote the following guidelines when backing up offline tablespaces:You cannot take offline theSYSTEMtablespace or a tab...

2021-08-02 10:00:37 130

空空如也

空空如也