############################################################################
### 12c How to plugin a non-CDB database to a Container Database (CDB) ###
############################################################################
$ sqlplus sys as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
SQL> alter database open read only;
BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => '/u01/scripts/rac.xml');
END;
/
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/u01/scripts/rac.xml',
pdb_name => 'RAC')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
CREATE PLUGGABLE DATABASE RAC using '/u01/scripts/rac.xml' nocopy tempfile reuse;
select CON_ID, NAME, OPEN_MODE from V$PDBS;
select PDB_NAME, DBID , CON_ID, STATUS from CDB_PDBS;
ALTER SESSION SET CONTAINER =rac;
show con_name
alter session SET CONTAINER=CDB$ROOT;
show con_name
select PDB_NAME, DBID , CON_ID, STATUS from CDB_PDBS;
@/u01/products/rdbms_12102/rdbms/admin/noncdb_to_pdb.sql
#####################################
### Add and Start New PDB service ###
#####################################
./srvctl add service -db cdb12c -service pdb_srv -preferred cdb12c1 -pdb RAC
./srvctl start service -db cdb12c -s pdb_srv
########################################################################################################################
### Save state of PDB. By defualt the PDB's starup in mounted state. Use the following cmd to persere the open state.###
########################################################################################################################
SQL> alter pluggable database UCPDB save state;
alter pluggable database JOHNPDB save state;
##############################
### Connect to PDB Service ###
##############################
sqlplus system/racattack@collabn1:1521/ucpdb
Schema/Password@HOST:Port/Service_Name
################################################
### Open all PDB's that are on mounted mode ###
################################################
SQL> alter pluggable database all open;
##################
### Switch CDB ###
##################
alter session set container=CD$ROOT;
###############################
### Display active services ###
###############################
select name, con_id from v$active_services order by 1;
NAME CON_ID
---------------------------------------------------------------- ----------
SYS$BACKGROUND 1
SYS$USERS 1
cdb12c 1
cdb12cXDB 1
pdb_srv 3
rac 3
ucpdb 4
##########################
### Display Containers ###
##########################
SQL> select con_id, name, open_mode from v$containers;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
1 CDB$ROOT READ WRITE
2 PDB$SEED READ ONLY
3 RAC READ WRITE
4 UCPDB READ WRITE
###########################
### Display PDB Status ###
###########################
set linesize 500
column pdb_name format a50
select pdb_name, status from cdb_pdbs;
PDB_NAME STATUS
-------------------------------------------------- ---------
PDB$SEED NORMAL
RAC NORMAL
UCPDB NORMAL
###############################
### Display Open_Mode of PDB###
###############################
select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
RAC READ WRITE
UCPDB READ WRITE
#########################################################
### Display all datafile at the root container level ###
#########################################################
set linesize 500
column FILE_NAME format a100
select file_name,con_id from cdb_data_files order by con_id;
FILE_NAME CON_ID
---------------------------------------------------------------------------------------------------- ----------
+DATA/CDB12C/DATAFILE/system.295.908285565 1
+DATA/CDB12C/DATAFILE/users.303.908285931 1
+DATA/CDB12C/DATAFILE/sysaux.297.908285699 1
+DATA/CDB12C/DATAFILE/undotbs1.299.908285801 1
+DATA/CDB12C/DATAFILE/undotbs2.302.908285907 1
+DATA/RAC/DATAFILE/users.260.839965263 3
+DATA/RAC/DATAFILE/system.259.839965187 3
+DATA/RAC/DATAFILE/sysaux.258.839965083 3
+DATA/CDB12C/2FFB70F5EDFF486AE053334EA8C0FF4E/DATAFILE/system.308.908621995 4
+DATA/CDB12C/2FFB70F5EDFF486AE053334EA8C0FF4E/DATAFILE/users.311.908622247 4
+DATA/CDB12C/2FFB70F5EDFF486AE053334EA8C0FF4E/DATAFILE/sysaux.309.908621995 4
###########################################################
### Display all tablespaces at the root container level ###
###########################################################
set linesize 500
set pagesize 500
column tablespace_name format a50
select tablespace_name, con_id from cdb_tablespaces order by con_id;
TABLESPACE_NAME CON_ID
-------------------------------------------------- ----------
SYSTEM 1
USERS 1
SYSAUX 1
TEMP 1
UNDOTBS1 1
UNDOTBS2 1
SYSTEM 3
SYSAUX 3
UNDOTBS2 3
TEMP 3
USERS 3
UNDOTBS1 3
SYSTEM 4
USERS 4
TEMP 4
SYSAUX 4
#########################################################
### Display all tempfiles at the root container level ###
#########################################################
set linesize 500
column FILE_NAME format a100
select file_name,con_id from cdb_temp_files order by con_id;
FILE_NAME CON_ID
---------------------------------------------------------------------------------------------------- ----------
+DATA/CDB12C/TEMPFILE/temp.300.908285829 1
+DATA/CDB12C/F2CCC568CEF64318E043334EA8C05910/TEMPFILE/temp.307.908366901 3
+DATA/CDB12C/2FFB70F5EDFF486AE053334EA8C0FF4E/TEMPFILE/temp.310.908622149 4
##################################################
### Create common user while connected to root ###
##################################################
create user c##1 identified by racattack container=all;
set linesize 500
set pagesize 500
column username format a50
select username, common, con_id from cdb_users where username like 'C##%';
USERNAME COM CON_ID
-------------------------------------------------- --- ----------
C##1 YES 3
C##1 YES 1
C##1 YES 4
##################
### Drop PDB ###
##################
--Connect to container DB
$ export ORACLE_SID=cdb1
$ . oraenv
$ sqlplus / as sydba
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
--Close all or just the PDB to drop
SQL> alter pluggable database all close immediate;
Pluggable database altered.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
JOHNPDB MOUNTED
TIGGER MOUNTED
--DROP PDB
SQL> drop pluggable database TIGGER including datafiles;
Pluggable database dropped.
--Verify PDB is gone.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
JOHNPDB MOUNTED
#############
Overview
#############
Purpose
This tutorial covers the steps for using Oracle Recovery Manager (Oracle RMAN) to perform a point-in-time recovery for a pluggable database (PDB).
Time to Complete
Approximately 30 minutes
Introduction
A database point-in-time recovery (DBPITR) is sometimes referred to as an incomplete recovery. This recovery capability is suitable for the following situations:
A user error or corruption removes needed data or introduces corrupted data. For example, a user or DBA erroneously deletes or updates the contents of one or more tables, drops database objects that are still needed during an update to an application, or runs a large batch update that fails midway.
A database upgrade fails or an upgrade script goes awry.
A complete database recovery after a media failure cannot succeed because you do not have all of the needed redo logs or incremental backups.
Here are the DBPITR requirements:
Your database must be running in ARCHIVELOG mode.
You must have backups of all data files before the target SCN for DBPITR.
You must have archived redo logs between the SCN of the backups and the target SCN.
Scenario
In this tutorial, you use Oracle RMAN to perform a point-in-time recovery on a PDB. You make some erroneous data updates and then recover the database to the state it was just before you performed the data updates.
Prerequisites
Before starting this tutorial, you should:
Ensure that you have enough disk space to hold a complete backup copy of the CDB, including all of the PDBs.
Install Oracle Database 12c.
Create one CDB with two PDBs in it.
The environment used in the development of this tutorial is as follows:
ORACLE_HOME: /u01/app/oracle/product/12.1.0
TNS Listener port: 1521
Container databases:
SID: cdb1
SID: cdb2
Pluggable databases (in cdb1):
pdb1
pdb2
##################################
Preparing to Back up the CDB
##################################
Before backing up the database, you must create the backup directory and then put the database in ARCHIVELOG mode.
Creating a Backup Directory
Navigate to /stage or wherever you can find enough available space to store the backup files.
cd /stage
Create a subdirectory called db_backup_files under /stage.
mkdir db_backup_files
Create a subdirectory called cdb1 under /stage/db_backup_files.
cd db_backup_files
mkdir cdb1
Placing the CDB in ARCHIVELOG Mode
In SQL*Plus, connect as sysdba to the cdb1 container database.
. oraenv
[enter cdb1 at the prompt]
sqlplus / as sysdba
Shut down the database.
shutdown immediate
Start the database in mount mode.
startup mount
Turn on database archiving.
alter database archivelog;
Open the CDB.
alter database open;
Open all of the PDBs.
alter pluggable database all open;
Set the Flash Recovery Area (FRA) size.
alter system set db_recovery_file_dest_size = 2G scope=both;
exit
############################
Backing Up the CDB
############################
Use Oracle RMAN to back up the database to the directory that was specified in the previous section.
Perform the following steps in a new terminal window:
Set the NLS_DATE_FORMAT environment variable so that the date and time values displayed by RMAN can be more easily read.
Make sure that ORACLE_HOME is set correctly.
Set ORACLE_SID to cdb1.
export NLS_DATE_FORMAT='DD-MM-YYYY HH:MI:SS'
Connect to Oracle RMAN.
rman target /
Set the Oracle RMAN backup device type and backup file location.
configure channel device type disk format '/stage/db_backup_files/cdb1/%U';
Turn on automatic backup of control files.
configure controlfile autobackup on;
Back up the database and archive logs.
backup database plus archivelog;
exit
###########################################
Performing Erroneous updates in the PDB
###########################################
In this section, you set up a tablespace, schema, and table in each PDBs. Next, you insert some records in the tables. One of the batch inserts will be done "in error."
Creating a Tablespace, Schema, and Table in PDB1
In SQL*Plus, connect to pdb1 as sysdba.
sqlplus sys/oracle@localhost:1521/pdb1 as sysdba
Create a small tablespace where a small table will be stored.
create tablespace dj_pdb1 datafile '/u01/app/oracle/oradata/cdb1/pdb1/dj_pdb1.dbf' size 10m;
Create a schema that will own the table.
create user dj identified by dj temporary tablespace temp default tablespace dj_pdb1;
Grant the schema the necessary privileges.
grant create session, create table, unlimited tablespace to dj;
Create the table in the schema.
create table dj.t1(c varchar2(100)) tablespace dj_pdb1;
###########################################
Performing Data Updates in PDB1
###########################################
Make a note of the current SCN.
select timestamp_to_scn(sysdate) from v$database;
Insert data in the table.
begin
for i in 1.. 10000 loop
insert into dj.t1 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
end loop;
commit;
end;
/
Make a note of the SCN and remain in SQL*Plus.
select timestamp_to_scn(sysdate) from v$database;
#################################################
Creating a Tablespace, Schema, and Table in PDB2
#################################################
In SQL*Plus, connect to pdb2 as sysdba.
connect sys/oracle@localhost:1521/pdb2 as sysdba
Create a small tablespace where a small table will be stored.
create tablespace jfv_pdb2 datafile '+DATA' size 10m;
Create a schema that will own the table.
create user jfv identified by jfv temporary tablespace temp default tablespace jfv_pdb2;
Grant the schema the necessary privileges.
grant create session, create table, unlimited tablespace to jfv;
Create the table in the schema.
create table jfv.t2(c varchar2(100)) tablespace jfv_pdb2;
#################################################
Performing Data Updates in PDB2
#################################################
Insert a row in the table and commit the transaction.
insert into jfv.t2 values ('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
commit;
Make a note of the current SCN, which is the point to which you will recover the database.
select timestamp_to_scn(sysdate) from v$database;
Insert 10,000 more rows into the table.
begin
for i in 1.. 10000 loop
insert into jfv.t2 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
end loop;
commit;
end;
/
Make a note of the SCN.
select timestamp_to_scn(sysdate) from v$database;
################################################
Recovering the PDB to a Certain Point in Time
################################################
To perform point-in-time recovery, you must first close the PDB. Use Oracle RMAN to recover until the SCN before the erroneous data updates to the table in PDB2.
Close the pdb2 pluggable database and exit SQL*Plus.
alter pluggable database pdb2 close;
exit
Connect to Oracle RMAN.
rman target /
Perform point-in-time recovery in Oracle RMAN.
Make sure that you change the SET UNTIL SCN value to the SCN that you recorded in step #1 in the "Performing Data Updates in PDB2" section. Also, set the AUXILIARY DESTINATION value to a location with enough free space to hold a copy of the database files.
run {
set until SCN = 2263440 ;
restore pluggable database pdb2;
recover pluggable database pdb2 auxiliary destination='/stage/db_backup_files/cdb1';
alter pluggable database pdb2 open resetlogs;
}
Exit Oracle RMAN.
exit
################################################
Verifying Sucess of the Point-in-Time Recovery
################################################
If the point-in-time recovery was successful, you should see only one row in the jfv.t2 table.
In SQL*Plus, connect to PDB2.
sqlplus jfv/jfv@localhost:1521/pdb2
Check to see that the table contains only the one row inserted before the 10,000 record batch insert.
select * from t2;
exit
################################################
Resetting your environment
################################################
Perform the following steps to reset your environment prior to repeating the activities covered in this OBE or starting another OBE.
Remove the jfv user and jfv_pdb2 tablespace from pdb2 .
. oraenv
[enter cdb2 at the prompt]
sqlplus sys/oracle@localhost:1521/pdb2 as sysdba
drop user jfv cascade;
drop tablespace jfv_pdb2 including contents;
Remove the dj user and dj_pdb1 tablespace from pdb1.
connect sys/oracle@localhost:1521/pdb1 as sysdba
drop user dj cascade;
drop tablespace dj_pdb1 including contents;
Take the database out of ARCHIVELOG mode .
connect / as sysdba
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;
alter pluggable database all open;
exit
Remove the backup files.
rm -rf /stage/db_backup_files
sqlplus system/racattack@dgprm:1521/TIGGER
###########################################
Performing Erroneous updates in the PDB
###########################################
In this section, you set up a tablespace, schema, and table in each PDBs. Next, you insert some records in the tables. One of the batch inserts will be done "in error."
Creating a Tablespace, Schema, and Table in PDB1
In SQL*Plus, connect to pdb1 as sysdba.
sqlplus sys/oracle@localhost:1521/pdb1 as sysdba
Create a small tablespace where a small table will be stored.
create tablespace dj_pdb1 datafile '+DATA' size 10m;
Create a schema that will own the table.
create user dj identified by dj temporary tablespace temp default tablespace dj_pdb1;
Grant the schema the necessary privileges.
grant create session, create table, unlimited tablespace to dj;
Create the table in the schema.
create table dj.t1(c varchar2(100)) tablespace dj_pdb1;
###########################################
Performing Data Updates in PDB1
###########################################
Make a note of the current SCN.
select timestamp_to_scn(sysdate) from v$database;
Insert data in the table.
begin
for i in 1.. 10000 loop
insert into dj.t1 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
end loop;
commit;
end;
/
Make a note of the SCN and remain in SQL*Plus.
select timestamp_to_scn(sysdate) from v$database;
No comments:
Post a Comment