All about Database Administration, Tips & Tricks

The Insights of a Geek DBA !!!

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

12c Database : Move datafile online, no offline or rename required going forward

12c Introduced the datafiles movement online, Earlier to 12c we have to

	a) offline the datafile
	b) Move the datafile to new location using os command
	c) Rename the datafile for data dictionary update
	d) Online the datafile

From 12c, its just need a command "alter database move datafile" Lets see how it works

	[oracle@Geek DBA12c admin]$ sqlplus / as sysdba

	SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 2 12:43:54 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> select file_name from dba_data_files;

	FILE_NAME
	--------------------------------------------------------------------------------
	/u01/app/oracle/oradata/DB12c/system01.dbf
	/u01/app/oracle/oradata/DB12c/sysaux01.dbf
	/u01/app/oracle/oradata/DB12c/undotbs01.dbf
	/u01/app/oracle/oradata/DB12c/users01.dbf

Create new directory to hold the datafile mkdir -p /u01/app/oracle/oradata/DB12c/new

Let's mimic some activity on users tablespace to ensure move operation wont affect the DML operation In one session

	SQL> create table t tablespace users as select * from dba_objects;

	Table created.

	SQL> begin
	  2  for i in 1..100 loop
	  3  insert into t select * from dba_objects;
	  4  end loop;
	  5  commit;
	  6  end;
	  7  /
Session insert operation in progress......

Open another session and move the datafile

	SQL> alter database move datafile '/u01/app/oracle/oradata/DB12c/users01.dbf' to '/u01/app/oracle/oradata/DB12c/new/users01.dbf';

	Database altered.

As you observed the insert operation above does not affecting the move operation. Lets check how does the data dictionary looks like.

	SQL> select file_name from dba_data_files;

	FILE_NAME
	--------------------------------------------------------------------------------
	/u01/app/oracle/oradata/DB12c/system01.dbf
	/u01/app/oracle/oradata/DB12c/sysaux01.dbf
	/u01/app/oracle/oradata/DB12c/new/users01.dbf
	/u01/app/oracle/oradata/DB12c/undotbs01.dbf

Another Check in OS level too

	ls -ltr /u01/app/oracle/oradata/DB12c/new/users01.dbf
	-rw-r-----. 1 oracle oinstall 5251072 Sep  2 12:45 /u01/app/oracle/oradata/DB12c/new/users01.dbf

	ls -ltr /u01/app/oracle/oradata/DB12c/
	total 1754712
	drwxr-x---. 2 oracle oinstall      4096 Sep  2 08:33 pdbseed
	-rw-r-----. 1 oracle oinstall  52429312 Sep  2 08:37 redo02.log
	drwxr-x---. 2 oracle oinstall      4096 Sep  2 08:38 pdb12c
	-rw-r-----. 1 oracle oinstall  52429312 Sep  2 10:37 redo03.log
	-rw-r-----. 1 oracle oinstall  62922752 Sep  2 12:37 temp01.dbf
	-rw-r-----. 1 oracle oinstall  89137152 Sep  2 12:42 undotbs01.dbf
	-rw-r-----. 1 oracle oinstall 807411712 Sep  2 12:45 system01.dbf
	-rw-r-----. 1 oracle oinstall 723525632 Sep  2 12:45 sysaux01.dbf
	drwxr-xr-x. 2 oracle oinstall      4096 Sep  2 12:45 new
	-rw-r-----. 1 oracle oinstall  52429312 Sep  2 12:45 redo01.log
	-rw-r-----. 1 oracle oinstall  17973248 Sep  2 12:45 control01.ctl

As you see above, the user01.dbf datafile is not in the oradata directory.

Check the table count just in case to check the table contains rows or not 😉

	SQL> select count(*) from t;

	  COUNT(*)
	----------

9163831

This is very cool nice feature for Level 1 support DBA's where you can reduce these steps (Offlining and copying the files at OS and rename and then online). All in one shot

ORATOP : Another Oracle Performance tool from Oracle

Another nice performance tool from Oracle which process real time sessions and shows in nice output. Here it is

A very simple elegant, light weight tool to monitor the performance of the database,

Just copy the executable from the note 1500864.1 and set your environment and run the command to monitor your database.

-Thanks

Geek DBA

12c Books so far

Few books released so far on 12c features or includes 12c Features, but all are not India (Asia) editions so they might be expensive, Watch out for this books in Flipkart and you may get for lesser prices once they get released here in India.

12c New Features book from Robert Freeman As usual

Rman Recepies from Arup Nanda & Darl Kuhn & Sam R Alapati

On RAC & its Internals, Riyaz's book

Martin Bach's Oracle 12c Consolidation

-Thanks
Geek DBA

12c database Architecture diagram, multitenant database architecture

If you are unable to read, click to download

Diagram Source : Oracle.com (the document is uploaded for education purpose only not for any other commercial purpose)

Click here to download

12c Database : Managing PDB’s

Starting/Stopping CDB and PDBs

The traditional approach of starting and stopping databases is now only valid for CDB. So in order to start and stop CDB you will use the familiar startup and shutdown commands.

PDBs don’t get automatically started with CDB. Even if your CDB is up and running it is possible that PDBs are still inaccessable. To view the current status of PDBs run the following command.

	SQL> select name,open_mode from v$pdbs; 
	NAME 				OPEN_MODE 
	----------------------------- ---------- 
	PDB$SEED 			READ ONLY 
	PDB12c 				MOUNTED 
	PDBTEST 			MOUNTED 

	SQL> alter pluggable database pdbtest open; 

	Pluggable database altered. 

	SQL> select name,open_mode from v$pdbs; 
	NAME 				OPEN_MODE 
	------------------------------ ---------- 
	PDB$SEED 			READ ONLY 
	PDB12c 				MOUNTED 
	PDBTEST 			READ WRITE

Closing the PDB database

SQL> alter pluggable database pdbtest close;
Pluggable database altered.

Closing/open all PDB's at once

	SQL> alter pluggable database all open; 
	Pluggable database altered. 

	SQL> alter pluggable database all close; 
	Pluggable database altered.

To know about which container you are

	SQL> show con_name 
	CON_NAME 
	------------------------------ 
	PDB12C

When you connect in PDB and issue shutdown are startup that will be applicable to that PDB ONLY

	SQL> show con_name 
	CON_NAME 
	----------------------------- 
	PDB12C 
	SQL> shutdown immediate; 
	Pluggable Database closed. 
	SQL> startup 
	Pluggable Database opened.

Renaming the PDB database


	SQL> alter pluggable database pdb_test close; 
	Pluggable database altered. 

	SQL> alter pluggable database pdb_test open restricted; 
	Pluggable database altered. 

	Connect to the PDB Database using ezconnect model

	$ sqlplus sys/oracle@localhost:1521/pdb_test as sysdba 
	SQL*Plus: Release 12.1.0.1.0 Production on Sat Jun 29 01:39:34 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> show con_name 

	CON_NAME 
	------------------------------ 
	PDB_TEST 

	SQL> alter pluggable database pdb_test rename global_name to pdb3; 
	Pluggable database altered. 

	SQL> alter pluggable database pdb_test rename global_name to pdb3; 
	Pluggable database altered. 

	SQL> alter pluggable database close immediate; 
	Pluggable database altered. 


	SQL> alter pluggable database open; 
	Pluggable database altered. 

	SQL> select name,open_mode from v$pdbs; 

	NAME 				OPEN_MODE 
	------------------------------ ---------- 
	PDB3 				READ WRITE

12c Database : Create PDB’s , Plug & Unplug

Create & Plug and Unplug PDB's

To Create PDB's the following methods can be followed

Copying

From PDB Seed

Cloning from a existing PDB using

Locally

Remotely through DB Link

Plugging in

Plugging in an unplugged DB (unplug and plug as database into same container database)

Plugging in a NON-CDB as a PDB (NON-CDB database converting to PDB database into a container database)

Before getting Started the options or clauses using for Create/Alter Pluggable database are

Storage Limits :- You can specify the limit of the size of the PDB you create

MAX_SIZE = The amount of storage that can be used by all tablespaces that belong to the PDB
MAX_SHARED_TEMP_SIZE = The amount of storage in the default temporary tablespace shared by all PDBs that can be used by sessions connected to the PDB

File Locations

    PATH_PREFIX = Restricting the PDB to specified directory so that all files placed in this directory
    Example:- PATH_PREFIX = '/disk1/oracle/dbs/salespdb/'

    FILE_NAME_CONVERT = Convert the location of the files to this location while creating PDB
    Example:- FILE_NAME_CONVERT = ('/oracle/dbs/', '/oracle/pdb5/')
    Note: If this parameter is not set, Oracle looks for pdb_file_name_convert parameter and converts accordingly


    SOURCE_FILE_NAME_CONVERT= When unplugging and plugging databases, Using this parameter will convert the names of the files that different of XML
    SOURCE_FILE_NAME_CONVERT = ('/disk1/oracle/pdb7/', '/disk2/oracle/pdb7/')

Temp File Reuse

TEMPFILE Reuse:- Clause reuse the temp file in the location that PDB going to create.

Snapshot copy

Examples:-

Task	Method	Description	Command
Create PDB	Copying From Seed PDB	creates a PDB (Assuming OMF is enabled or PDB_FILE_NAME_CONVERT is specified)	SQL> CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password;
	Copying From Seed PDB	creates a PDB with a pdb dba role assigned to user	SQL> CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password ROLES=(DBA);
	Copying From Seed PDB	A PDB with name SALESPDB to be create Admin user (local) salesadm to be create Size of tablespaces should not exceed more than 2gb Shared Temporary tables should not exceed by this PDB database session by 100m Default tablespace set as sales and datafile as below with 250M all the files that creates in this PDB automatically relative to this /disk1/oracle/dbs/salespdb/ path file name Convert the seed location to target location	SQL> CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M) DEFAULT TABLESPACE sales DATAFILE '/disk1/oracle/dbs/salespdb/sales01.dbf' SIZE 250M AUTOEXTEND ON PATH_PREFIX = '/disk1/oracle/dbs/salespdb/' FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdbseed/', '/disk1/oracle/dbs/salespdb/');
	Cloning DB Local	Example clones a PDB1 to PDB2	SQL> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1;
		example clones a PDB1 to PDB2 with the path_prefix changes and changing the file name paths	SQL> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 PATH_PREFIX = '/disk2/oracle/pdb2' FILE_NAME_CONVERT = ('/disk1/oracle/pdb1/', '/disk2/oracle/pdb2/');
		Cloning from local PDB with storage limit clauses	SQL> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 FILE_NAME_CONVERT = ('/disk1/oracle/pdb1/', '/disk2/oracle/pdb2/') STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M);
	Clone DB from Remote	Creates a PDB from a remote PDB	SQL CREATE PLUGGABLE DATABASE pdb2 FROM pdb1@pdb1_link;
Plugging IN	Unplug and plug as new	To unplug first check the compatibility and also to flat xml file	set serveroutput on DECLARE compatible BOOLEAN := FALSE; BEGIN compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/u01/app/oracle/oradata/pdb1.xml'); if compatible then DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? YES'); else DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? NO'); end if; END; /
		Plug the database using NOCOPY Means all the datafiles should be in same location no copy required	create pluggable database pdb_plug_nocopy using '/u01/app/oracle/oradata/pdb1.xml' NOCOPY TEMPFILE REUSE; Verify the status select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_COPY'; select open_mode from v$pdbs where name='PDB_PLUG_COPY'; alter pluggable database pdb_plug_nocopy open;
		Plug the database using COPY method Means all the datafiles should be copied from location to target location	mkdir /u01/app/oracle/oradata/cdb2/pdb_plug_copy sqlplus / as sysdba create pluggable database pdb_plug_copy using '/u01/app/oracle/oradata/pdb2.xml' COPY FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2','/u01/app/oracle/oradata/cdb2/pdb_plug_copy'); Verify the status select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_COPY'; select open_mode from v$pdbs where name='PDB_PLUG_COPY'; alter pluggable database pdb_plug_copy open;
		Plug the database using MOVE method when cloning	mkdir /u01/app/oracle/oradata/cdb2/pdb_plug_move sqlplus / as sysdba create pluggable database pdb_plug_move AS CLONE using '/u01/app/oracle/oradata/pdb2.xml' MOVE FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2','/u01/app/oracle/oradata/cdb2/pdb_plug_move'); Verify the status select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_MOVE'; select open_mode from v$pdbs where name='PDB_PLUG_MOVE'; alter pluggable database pdb_plug_move open;
Unplugging	Unplug database from CDB	Unplug and export to Flat xml file	`SQL> alter pluggable database pdb3 close immediate;` `Pluggable database altered.` `SQL> alter pluggable database pdb3 unplug into '/u01/app/oracle/oradata/pdb3_unplug.xml';` `Pluggable database altered.`
Convert NON-CDB database to PDB	Convert NON-CDB database to PDB	1) Upgrade the Non-CDB if the database is pre 12c 2) Open the database in readonly 3) Execute DBMS_PDB 4) Use Create Pluggable database 5) Execute the script noncdb_to_pdb.sql	1) Upgrade and set comptabile = 12.0.0 2) OPen the database in readonly mode 3) Execute the dbms_pdb package describe procedure BEGIN DBMS_PDB.DESCRIBE( pdb_descr_file => '/disk1/oracle/ncdb.xml'); END; / 4) Shutdown the non-cdb database 5) If in same server your CDB database contains CREATE PLUGGABLE DATABASE ncdb USING '/disk1/oracle/ncdb.xml' COPY FILE_NAME_CONVERT = ('/disk1/oracle/dbs/', '/disk2/oracle/ncdb/'); 6) Execute the script @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql 7) Open the read write mode new PDB

12c Database : Truncate with Cascade

From 12c onwards,

Oracle 12c introduced truncate with cascade so when truncate operates on master table the child table rows also get truncated automatically.

	SCOTT@PDB > create table MASTER (
	  2  ID number generated always as identity PRIMARY KEY,
	  3  NAME VARCHAR2(20)
	  4  );
	 Table created.

	 SCOTT@PDB > create table CHILD (
	  2   ID number generated always as identity PRIMARY KEY,
	  3  NAMECHILD VARCHAR2(20)
	  4  );
	 Table created.

	 SCOTT@PDB > alter table CHILD add constraint FK_MASTER FOREIGN KEY (ID) references MASTER(ID) ON DELETE CASCADE;
	 Table altered.

	 SCOTT@PDB > insert into MASTER (NAME) values ('Geek DBA');
	 1 row created.

	 SCOTT@PDB > insert into CHILD (NAMECHILD) values ('Geek DBA');
	 1 row created.

	 SCOTT@PDB > commit;
	 Commit complete.

          SCOTT@PDB > select * from MASTER;
		 ID NAME
	---------- --------------------
		 1 Geek DBA

	 SCOTT@PDB > select * from CHILD;
		 ID NAMECHILD
	---------- --------------------
		 1 Geek DBA

	 SCOTT@PDB > truncate table MASTER cascade;
	 Table truncated.

	 SCOTT@PDB > select * from MASTER;
          no rows selected

	 SCOTT@PDB > select * from CHILD;
	 no rows selected

Note: Truncate cascade only works when you have the referential integrity with on delete cascade on

12c Database : RMAN backups and recovery with PDB

RMAN backup scenarios in 12c,

With the advent of 12c PDB/CDB rman backup has changed a lot while taking backups or restoration.

Backups on PDB or CDB
"BACKUP PLUGGABLE DATABASE" - To take backups for a pluggable database
"BACKUP DATABASE" - "BACKUP DATABASE;" on root container backs up the whole container includes SEED Container and PDBs
"BACKUP DATABASE ROOT;" backs up the ROOT Container. "BACKUP DATABASE 'PDB$SEED';" backs up SEED Database
"BACKUP PLUGGABLE DATABASE ,.....;" To backup multiple databases

Restorations on PDB (When connected as PDB)

"RESTORE PLUGGABLE DATABASE" and "RECOVER PLUGGABLE DATABASE" are used to perform a Complete/Incomplete PDB Recovery 
"RESTORE DATABASE" and "RECOVER DATABASE" are used when connected to PDB
"RESTORE TABLESPACE" and "RECOVER TABLESPACE" or "RESTORE DATAFILE" and "RECOVER DATAFILE"

Backups for transportation tablespace

"BACKUP FOR TRANSPORT PLUGGABLE DATABASE PDB;" or "BACKUP TO PLATFORM "platforn name" DATABASE;" is used to backup the whole database to transport to a different platform of same endian format(Big, little etc...).

Examples

1) Authenticating with "SYSBACKUP":

	Direct SYSBACKUP Authentication
	[oracle@Geek DBA ~]$ rman target '"/ as sysbackup"'
	Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 1 22:52:29 2013
	Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
	connected to target database: TESTDB (DBID=1085647373)

	RMAN> BACKUP DATAFILE 1;
	Starting backup at 01-JUL-13
	using target database control file instead of recovery catalog
	allocated channel: ORA_DISK_1

	Starting Control File and SPFILE Autobackup at 01-JUL-13
	piece handle=/oradata/backups/TESTDBc-1085647373-20130701-0e comment=NONE
	Finished Control File and SPFILE Autobackup at 01-JUL-13

2) Authenticating with SYSBACKUP Privileged user

	[oracle@Geek DBA TESTDB]$ sqlplus / as sysdba
	SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 1 22:37:32 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


	Create the C##BACKUPadmin common user (to know more about common users)
	SQL> CREATE USER C##BACKUPADMIN IDENTIFIED BY 123;
	User created.


	SQL> GRANT SYSBACKUP TO C##BACKUPADMIN;
	Grant succeeded.


	SQL> !rman target C##BACKUPADMIN 
	Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 1 22:38:07 2013
	Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
	target database Password: 
	connected to target database: TESTDB (DBID=1085647373)

	RMAN> BACKUP DATAFILE 8;
	Starting backup at 01-JUL-13
	using target database control file instead of recovery catalog
	channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
	Finished backup at 01-JUL-13


	Starting Control File and SPFILE Autobackup at 01-JUL-13
	piece handle=/oradata/backups/TESTDBc-1085647373-20130701-0d comment=NONE
	Finished Control File and SPFILE Autobackup at 01-JUL-13

2) Backup of Pluggable Database (PDB)

	
	On ROOT Container connection, Using "BACKUP PLUGGABLE DATABASE" 

	[oracle@Geek DBA ~]$ rman target /
	Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 1 17:11:07 2013
	Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
	connected to target database: TESTDB (DBID=1085647373)

	 RMAN> BACKUP PLUGGABLE DATABASE PDB PLUS ARCHIVELOG;
	 OR
	 On PDB Connection itself, Using "BACKUP DATABASE" on PDB

	[oracle@Geek DBA ~]$ rman target sys@PDB
	Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 1 17:35:59 2013
	Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
	target database Password: 
	connected to target database: TESTDB (DBID=1085647373)

	RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
	Starting backup at 01-JUL-13
	using target database control file instead of recovery catalog
	allocated channel: ORA_DISK_1
	channel ORA_DISK_1: SID=58 device type=DISK
	specification does not match any archived log in the repository
	backup cancelled because there are no files to backup
	Finished backup at 01-JUL-13
	Starting backup at 01-JUL-13
	using channel ORA_DISK_1
	channel ORA_DISK_1: starting full datafile backup set
	channel ORA_DISK_1: specifying datafile(s) in backup set
	input datafile file number=00009 name=/oradata/TESTDB/PDB/sysaux01.dbf
	input datafile file number=00011 name=/oradata/TESTDB/PDB/example01.dbf
	input datafile file number=00008 name=/oradata/TESTDB/PDB/system01.dbf
	input datafile file number=00019 name=/oradata/TESTDB/PDB/root_cdb_01.dbf
	input datafile file number=00022 name=/oradata/TESTDB/PDB/ts_pdb_01.dbf
	channel ORA_DISK_1: starting piece 1 at 01-JUL-13
	channel ORA_DISK_1: finished piece 1 at 01-JUL-13
	piece handle=/oradata/backups/TESTDBTESTDB_47_819653777_TESTDB tag=TAG20130701T173617 comment=NONE
	channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
	Finished backup at 01-JUL-13
	Starting backup at 01-JUL-13
	using channel ORA_DISK_1
	specification does not match any archived log in the repository
	backup cancelled because there are no files to backup
	Finished backup at 01-JUL-13
	Starting Control File and SPFILE Autobackup at 01-JUL-13
	piece handle=/oradata/backups/TESTDBc-1085647373-20130701-03 comment=NONE
	Finished Control File and SPFILE Autobackup at 01-JUL-13

3) Restore and Recovery of Pluggable Database (PDBs)

Recover one of the PDBs System Datafile

	SQL> ALTER SESSION SET CONTAINER=PDB;
	ERROR:
	ORA-00604: error occurred at recursive SQL level 1
	ORA-01116: error in opening database file 8
	ORA-01110: data file 8: '/oradata/TESTDB/PDB/system01.dbf'
	ORA-27041: unable to open file
	Linux-x86_64 Error: 2: No such file or directory
	Additional information: 3

	[oracle@Geek DBA PDB]$ rman target /
	Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 1 20:59:44 2013
	Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
	connected to target database: TESTDB (DBID=1085647373)

	RMAN> SHUTDOWN ABORT;
	using target database control file instead of recovery catalog
	Oracle instance shut down

	RMAN> STARTUP NOMOUNT;
	connected to target database (not started)
	Oracle instance started
	Total System Global Area    1269366784 bytes
	Fixed Size                     2287912 bytes
	Variable Size                452986584 bytes
	Database Buffers             805306368 bytes
	Redo Buffers                   8785920 bytes

	RMAN> ALTER DATABASE MOUNT;
	Statement processed

	RMAN> REPORT SCHEMA;
	Report of database schema for database with db_unique_name TESTDB
	List of Permanent Datafiles
	===========================
	File Size(MB) Tablespace           RB segs Datafile Name
	---- -------- -------------------- ------- ------------------------
	1    790      SYSTEM               ***     /oradata/TESTDB/system01.dbf
	3    740      SYSAUX               ***     /oradata/TESTDB/sysaux01.dbf
	4    760      UNDOTBS1             ***     /oradata/TESTDB/undotbs01.dbf
	5    250      PDB$SEED:SYSTEM      ***     /oradata/TESTDB/pdbseed/system01.dbf
	6    5        USERS                ***     /oradata/TESTDB/users01.dbf
	7    590      PDB$SEED:SYSAUX      ***     /oradata/TESTDB/pdbseed/sysaux01.dbf
	8    0        PDB:SYSTEM           ***     /oradata/TESTDB/PDB/system01.dbf
	9    630      PDB:SYSAUX           ***     /oradata/TESTDB/PDB/sysaux01.dbf
	10   5        PDB:USERS            ***     /oradata/TESTDB/PDB/SAMPLE_SCHEMA_users01.dbf
	11   357      PDB:EXAMPLE          ***     /oradata/TESTDB/PDB/example01.dbf
	15   260      SALESPDB:SYSTEM      ***     /oradata/TESTDB/salespdb/system01.dbf
	16   620      SALESPDB:SYSAUX      ***     /oradata/TESTDB/salespdb/sysaux01.dbf
	17   250      SALESPDB:SALES       ***     /oradata/TESTDB/salespdb/sales01.dbf
	19   50       PDB:ROOT_CDB         ***     /oradata/TESTDB/PDB/root_cdb_01.dbf
	22   10       PDB:TS_PDB           ***     /oradata/TESTDB/PDB/ts_pdb_01.dbf
	23   260      PDB1:SYSTEM          ***     /oradata/TESTDB/PDB1/system01.dbf
	24   620      PDB1:SYSAUX          ***     /oradata/TESTDB/PDB1/sysaux01.dbf
	25   250      PDB1:PDB1            ***     /oradata/TESTDB/PDB1/PDB01.dbf
	38   790      ORA12C:SYSTEM        ***     /oradata/TESTDB/ORA12C/system01.dbf
	39   700      ORA12C:SYSAUX        ***     /oradata/TESTDB/ORA12C/sysaux01.dbf
	40   5        ORA12C:USERS         ***     /oradata/TESTDB/ORA12C/users01.dbf
	41   358      ORA12C:EXAMPLE       ***     /oradata/TESTDB/ORA12C/example01.dbf

	List of Temporary Files
	=======================
	File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
	---- -------- -------------------- ----------- --------------------
	1    60       TEMP                 32767       /oradata/TESTDB/temp01.dbf
	2    20       PDB$SEED:TEMP        32767       /oradata/TESTDB/pdbseed/pdbseed_temp01.dbf
	3    28       PDB:TEMP             32767       /oradata/TESTDB/PDB/PDB_temp01.dbf
	4    20       SALESPDB:TEMP        32767       /oradata/TESTDB/salespdb/pdbseed_temp01.dbf
	5    20       PDB1:TEMP            32767       /oradata/TESTDB/PDB1/pdbseed_temp01.dbf
	6    60       ORA12C:TEMP          32767       /oradata/TESTDB/ORA12C/temp01.dbf

	RMAN> RESTORE DATAFILE 8;
	Starting restore at 01-JUL-13
	allocated channel: ORA_DISK_1
	channel ORA_DISK_1: SID=20 device type=DISK
	channel ORA_DISK_1: starting datafile backup set restore
	channel ORA_DISK_1: specifying datafile(s) to restore from backup set
	channel ORA_DISK_1: restoring datafile 00008 to /oradata/TESTDB/PDB/system01.dbf
	channel ORA_DISK_1: reading from backup piece /oradata/backups/TESTDBTESTDB_88_819662940_TESTDB
	channel ORA_DISK_1: piece handle=/oradata/backups/TESTDBTESTDB_88_819662940_TESTDB tag=TAG20130701T200740
	channel ORA_DISK_1: restored backup piece 1
	channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
	Finished restore at 01-JUL-13

	RMAN> RECOVER DATAFILE 8;
	Starting recover at 01-JUL-13
	using channel ORA_DISK_1
	starting media recovery
	media recovery complete, elapsed time: 00:00:01
	Finished recover at 01-JUL-13

	RMAN> SQL 'ALTER DATABASE OPEN';
	sql statement: ALTER DATABASE OPEN

	RMAN> SQL 'ALTER PLUGGABLE DATABASE PDB OPEN';
	sql statement: ALTER PLUGGABLE DATABASE PDB OPEN

4) Backup for Transporting Pluggable Database


	On PDB Container

	[oracle@Geek DBA ~]$ rman target sys@PDB
	Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 1 23:13:11 2013
	Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
	target database Password: 
	connected to target database: TESTDB (DBID=1085647373)

        RMAN> BACKUP TO PLATFORM "Solaris Operating System (x86-64)";


	On ROOT Container

	[oracle@Geek DBA ~]$ rman target /
	RMAN> BACKUP TO PLATFORM "Solaris Operating System (x86-64)" PLUGGABLE DATABASE;
	Starting backup at 01-JUL-13
	using target database control file instead of recovery catalog
	allocated channel: ORA_DISK_1
	channel ORA_DISK_1: SID=70 device type=DISK

	............................................................................................

	.............................................................................................

	.............................................................................................
	channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
	Finished backup at 01-JUL-13

5) Duplicating a Pluggable Database

TESTDB - SOURCE Database (TARGET)
AUXDB - DESTINATION Database (AUXILIARY)

[oracle@Geek DBA AUXDB]$ rman target sys@TESTDB auxiliary sys@AUXDB
Recovery Manager: Release 12.1.0.1.0 - Production on Tue Jul 2 01:40:28 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: TESTDB (DBID=1085647373)
auxiliary database Password:
connected to auxiliary database: AUXDB (not mounted)
RMAN> RUN
{
SET NEWNAME FOR DATABASE TO '/oradata/AUXDB/%U';
DUPLICATE TARGET DATABASE TO AUXDB PLUGGABLE DATABASE PDB FROM ACTIVE DATABASE NOFILENAMECHECK;
}2> 3> 4> 5>

executing command: SET NEWNAME
Starting Duplicate Db at 02-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
current log archived
contents of Memory Script:
{
sql clone "alter system set db_name =
''TESTDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''AUXDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone from service 'TESTDB' primary controlfile;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ''TESTDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''AUXDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started

Total System Global Area 417546240 bytes
Fixed Size 2289064 bytes
Variable Size 255853144 bytes
Database Buffers 155189248 bytes
Redo Buffers 4214784 bytes

Starting restore at 02-JUL-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TESTDB
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/AUXDB/control01.ctl
output file name=/fra/AUXDB/control02.ctl
Finished restore at 02-JUL-13
database mounted
Skipping pluggable database SALESPDB
Skipping pluggable database PDB1
Skipping pluggable database ORA12C
Automatically adding tablespace SYSTEM
Automatically adding tablespace SYSAUX
Automatically adding tablespace PDB$SEED:SYSTEM
Automatically adding tablespace PDB$SEED:SYSAUX
Automatically adding tablespace UNDOTBS1
Skipping tablespace USERS
contents of Memory Script:
{
set newname for datafile 1 to
"/oradata/AUXDB/data_D-AUXDB_TS-SYSTEM_FNO-1";
set newname for datafile 3 to
"/oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-3";
set newname for datafile 4 to
"/oradata/AUXDB/data_D-AUXDB_TS-UNDOTBS1_FNO-4";
set newname for datafile 5 to
"/oradata/AUXDB/data_D-AUXDB_TS-SYSTEM_FNO-5";
set newname for datafile 7 to
"/oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-7";
set newname for datafile 8 to
"/oradata/AUXDB/data_D-AUXDB_TS-SYSTEM_FNO-8";
set newname for datafile 9 to
"/oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-9";
set newname for datafile 10 to
"/oradata/AUXDB/data_D-AUXDB_TS-USERS_FNO-10";
set newname for datafile 11 to
"/oradata/AUXDB/data_D-AUXDB_TS-EXAMPLE_FNO-11";
set newname for datafile 19 to
"/oradata/AUXDB/data_D-AUXDB_TS-ROOT_CDB_FNO-19";
set newname for datafile 22 to
"/oradata/AUXDB/data_D-AUXDB_TS-TS_PDB_FNO-22";
restore
from service 'TESTDB' clone database
skip forever tablespace "USERS",
"SALESPDB":"SYSTEM",
"SALESPDB":"SYSAUX",
"SALESPDB":"SALES",
"PDB1":"SYSTEM",
"PDB1":"SYSAUX",
"PDB1":"PDB1",
"ORA12C":"USERS",
"ORA12C":"SYSTEM",
"ORA12C":"SYSAUX",
"ORA12C":"EXAMPLE" ;
sql 'alter system archive log current';
}

executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 02-JUL-13
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TESTDB
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /oradata/AUXDB/data_D-AUXDB_TS-SYSTEM_FNO-1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TESTDB
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-3
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TESTDB
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oradata/AUXDB/data_D-AUXDB_TS-UNDOTBS1_FNO-4
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TESTDB
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /oradata/AUXDB/data_D-AUXDB_TS-SYSTEM_FNO-5
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TESTDB
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-7
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TESTDB
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /oradata/AUXDB/data_D-AUXDB_TS-SYSTEM_FNO-8
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TESTDB
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-9
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TESTDB
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /oradata/AUXDB/data_D-AUXDB_TS-USERS_FNO-10
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TESTDB
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to /oradata/AUXDB/data_D-AUXDB_TS-EXAMPLE_FNO-11
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TESTDB
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00019 to /oradata/AUXDB/data_D-AUXDB_TS-ROOT_CDB_FNO-19
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service TESTDB
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00022 to /oradata/AUXDB/data_D-AUXDB_TS-TS_PDB_FNO-22
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 02-JUL-13
sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
restore clone force from service 'TESTDB'
archivelog from scn 2616072;
switch clone datafile all;
}
executing Memory Script

Starting restore at 02-JUL-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service TESTDB
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=21
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service TESTDB
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=22
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 02-JUL-13
datafile 1 switched to datafile copy
input datafile copy RECID=18 STAMP=819682983 file name=/oradata/AUXDB/data_D-AUXDB_TS-SYSTEM_FNO-1
datafile 3 switched to datafile copy
input datafile copy RECID=19 STAMP=819682983 file name=/oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-3
datafile 4 switched to datafile copy
input datafile copy RECID=20 STAMP=819682983 file name=/oradata/AUXDB/data_D-AUXDB_TS-UNDOTBS1_FNO-4
datafile 5 switched to datafile copy
input datafile copy RECID=21 STAMP=819682983 file name=/oradata/AUXDB/data_D-AUXDB_TS-SYSTEM_FNO-5
datafile 7 switched to datafile copy
input datafile copy RECID=22 STAMP=819682983 file name=/oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-7
datafile 8 switched to datafile copy
input datafile copy RECID=23 STAMP=819682983 file name=/oradata/AUXDB/data_D-AUXDB_TS-SYSTEM_FNO-8
datafile 9 switched to datafile copy
input datafile copy RECID=24 STAMP=819682983 file name=/oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-9
datafile 10 switched to datafile copy
input datafile copy RECID=25 STAMP=819682983 file name=/oradata/AUXDB/data_D-AUXDB_TS-USERS_FNO-10
datafile 11 switched to datafile copy
input datafile copy RECID=26 STAMP=819682983 file name=/oradata/AUXDB/data_D-AUXDB_TS-EXAMPLE_FNO-11
datafile 19 switched to datafile copy
input datafile copy RECID=27 STAMP=819682983 file name=/oradata/AUXDB/data_D-AUXDB_TS-ROOT_CDB_FNO-19
datafile 22 switched to datafile copy
input datafile copy RECID=28 STAMP=819682983 file name=/oradata/AUXDB/data_D-AUXDB_TS-TS_PDB_FNO-22
contents of Memory Script:
{
set until scn 2616267;
recover
clone database
skip forever tablespace "USERS",
"SALESPDB":"SYSTEM",
"SALESPDB":"SYSAUX",
"SALESPDB":"SALES",
"PDB1":"SYSTEM",
"PDB1":"SYSAUX",
"PDB1":"PDB1",
"ORA12C":"USERS",
"ORA12C":"SYSTEM",
"ORA12C":"SYSAUX",
"ORA12C":"EXAMPLE" delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 02-JUL-13
using channel ORA_AUX_DISK_1
Executing: alter database datafile 6 offline drop
Executing: alter database datafile 15 offline drop
Executing: alter database datafile 16 offline drop
Executing: alter database datafile 17 offline drop
Executing: alter database datafile 23 offline drop
Executing: alter database datafile 24 offline drop
Executing: alter database datafile 25 offline drop
Executing: alter database datafile 40 offline drop
Executing: alter database datafile 38 offline drop
Executing: alter database datafile 39 offline drop
Executing: alter database datafile 41 offline drop
starting media recovery
archived log for thread 1 with sequence 21 is already on disk as file /fra/AUXDB/archivelog/2013_07_02/o1_mf_1_21_8x3rpf5j_.arc
archived log for thread 1 with sequence 22 is already on disk as file /fra/AUXDB/archivelog/2013_07_02/o1_mf_1_22_8x3rpg7h_.arc
archived log file name=/fra/AUXDB/archivelog/2013_07_02/o1_mf_1_21_8x3rpf5j_.arc thread=1 sequence=21
archived log file name=/fra/AUXDB/archivelog/2013_07_02/o1_mf_1_22_8x3rpg7h_.arc thread=1 sequence=22
media recovery complete, elapsed time: 00:00:00
Finished recover at 02-JUL-13
Oracle instance started
Total System Global Area 417546240 bytes
Fixed Size 2289064 bytes
Variable Size 255853144 bytes
Database Buffers 155189248 bytes
Redo Buffers 4214784 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''AUXDB'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
}
executing Memory Script
sql statement: alter system set db_name = ''AUXDB'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance started
Total System Global Area 417546240 bytes
Fixed Size 2289064 bytes
Variable Size 255853144 bytes
Database Buffers 155189248 bytes
Redo Buffers 4214784 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUXDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'/oradata/AUXDB/data_D-AUXDB_TS-SYSTEM_FNO-1',
'/oradata/AUXDB/data_D-AUXDB_TS-SYSTEM_FNO-5',
'/oradata/AUXDB/data_D-AUXDB_TS-SYSTEM_FNO-8'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"/oradata/AUXDB/data_D-AUXDB_TS-TEMP_FNO-1";
set newname for tempfile 2 to
"/oradata/AUXDB/data_D-AUXDB_TS-TEMP_FNO-2";
set newname for tempfile 3 to
"/oradata/AUXDB/data_D-AUXDB_TS-TEMP_FNO-3";
switch clone tempfile all;
catalog clone datafilecopy "/oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-3",
"/oradata/AUXDB/data_D-AUXDB_TS-UNDOTBS1_FNO-4",
"/oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-7",
"/oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-9",
"/oradata/AUXDB/data_D-AUXDB_TS-USERS_FNO-10",
"/oradata/AUXDB/data_D-AUXDB_TS-EXAMPLE_FNO-11",
"/oradata/AUXDB/data_D-AUXDB_TS-ROOT_CDB_FNO-19",
"/oradata/AUXDB/data_D-AUXDB_TS-TS_PDB_FNO-22";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /oradata/AUXDB/data_D-AUXDB_TS-TEMP_FNO-1 in control file
renamed tempfile 2 to /oradata/AUXDB/data_D-AUXDB_TS-TEMP_FNO-2 in control file
renamed tempfile 3 to /oradata/AUXDB/data_D-AUXDB_TS-TEMP_FNO-3 in control file
cataloged datafile copy
datafile copy file name=/oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-3 RECID=1 STAMP=819682999
cataloged datafile copy
datafile copy file name=/oradata/AUXDB/data_D-AUXDB_TS-UNDOTBS1_FNO-4 RECID=2 STAMP=819682999
cataloged datafile copy
datafile copy file name=/oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-7 RECID=3 STAMP=819682999
cataloged datafile copy
datafile copy file name=/oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-9 RECID=4 STAMP=819682999
cataloged datafile copy
datafile copy file name=/oradata/AUXDB/data_D-AUXDB_TS-USERS_FNO-10 RECID=5 STAMP=819682999
cataloged datafile copy
datafile copy file name=/oradata/AUXDB/data_D-AUXDB_TS-EXAMPLE_FNO-11 RECID=6 STAMP=819682999
cataloged datafile copy
datafile copy file name=/oradata/AUXDB/data_D-AUXDB_TS-ROOT_CDB_FNO-19 RECID=7 STAMP=819682999
cataloged datafile copy
datafile copy file name=/oradata/AUXDB/data_D-AUXDB_TS-TS_PDB_FNO-22 RECID=8 STAMP=819682999
datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=819682999 file name=/oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-3
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=819682999 file name=/oradata/AUXDB/data_D-AUXDB_TS-UNDOTBS1_FNO-4
datafile 7 switched to datafile copy
input datafile copy RECID=3 STAMP=819682999 file name=/oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-7
datafile 9 switched to datafile copy
input datafile copy RECID=4 STAMP=819682999 file name=/oradata/AUXDB/data_D-AUXDB_TS-SYSAUX_FNO-9
datafile 10 switched to datafile copy
input datafile copy RECID=5 STAMP=819682999 file name=/oradata/AUXDB/data_D-AUXDB_TS-USERS_FNO-10
datafile 11 switched to datafile copy
input datafile copy RECID=6 STAMP=819682999 file name=/oradata/AUXDB/data_D-AUXDB_TS-EXAMPLE_FNO-11
datafile 19 switched to datafile copy
input datafile copy RECID=7 STAMP=819682999 file name=/oradata/AUXDB/data_D-AUXDB_TS-ROOT_CDB_FNO-19
datafile 22 switched to datafile copy
input datafile copy RECID=8 STAMP=819682999 file name=/oradata/AUXDB/data_D-AUXDB_TS-TS_PDB_FNO-22
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Executing: drop pluggable database "SALESPDB"
Executing: drop pluggable database "PDB1"
Executing: drop pluggable database "ORA12C"
contents of Memory Script:
{
sql clone "alter pluggable database all open";
}
executing Memory Script
sql statement: alter pluggable database all open
Dropping offline and skipped tablespaces
Executing: alter database default tablespace system
Executing: drop tablespace "USERS" including contents cascade constraints
Finished Duplicate Db at 02-JUL-13

To verify:

SQL> SELECT NAME,CDB,OPEN_MODE FROM V$DATABASE;
NAME CDB OPEN_MODE
--------- --- --------------------
AUXDB YES READ WRITE

SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB READ WRITE

12c Database: New groups for OS Authentication and New Sys roles and users

From 12c onwards we have seperate roles to be created for OS Authentication

Prior to 12c we have OSDBA, OSOPER, OSASM, OSDBA, OSOPER groups

From 12c onwards,

	OSBACKUPDBA maps to backupdba by default and is used to allow users to backup the database. Users in this group can connect as SYSBACKUP and have their privileges limited to what is needed for backup and recovery operations.

	OSDGDBA typically maps to dgdba and is used to administer and monitor Data Guard configurations. Members of the group can connect as SYSDBG)

	OSKMDBA typically maps to kmdba and allows the user connecting with the SYSKM role to perform key management and encryption tasks.

So first of all you have to create the required OS groups along with new 11g groups

	for i in asmdba asmadmin oinstall dba backupdba kmdba dgdba; do
	  groupadd $i
	done

Add the oinstall user as secondary group for all the groups you have created

	# useradd -g oinstall -G asmdba,dba,backupdba,dgdba,kmdba oracle
	# passwd oracle

Create the asmdba, asmadmin, grid users

	# useradd -g oinstall -G asmdba,asmadmin grid

# passwd grid

After you install 12c and select the above groups appropriately, the following database users will be created for you

	SQL> select username,account_status
	  2  from dba_users
	  3  where username like 'SYS%';

	USERNAME             ACCOUNT_STATUS
	-------------------- --------------------------------
	SYS                  OPEN
	SYSTEM               OPEN
	SYSBACKUP            EXPIRED
	SYSKM                EXPIRED & LOCKED
	SYSDG                EXPIRED & LOCKED

Upon checking the password file users you find the respective roles assigned to the users created.

	SQL> select * from v$pwfile_users;

	USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
	------------------------------ ----- ----- ----- ----- ----- ----- ----------
	SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
	SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          1
	SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          1
	SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           1

For example if you want to take a backup of rman (suppose you belongs to a team who has to do backups )

	rman / as sysbackup

-Thanks
Geek DBA

How to determine to collect histograms or not (Even oracle determines by same)

From Oracle 10g, the Oracle SMON (System Monitor) process regularly gathers information on columns that are used in query predicates and updates them automatically into a table named COL_USAGE$ in the SYS schema. Unlike the MON_MOD$ table, also in the SYS schema, which records table-level DML activity such as the number of rows changed by INSERT, DELETE, and UPDATE operations, and exposes them via the DBA_TAB_MODIFICATIONS view, the COL_USAGE$ view is neither documented nor wrapped by a legible view. The column names, however, are very revealing; these are detailed in

Col_usage$ description
Column Name	Description
OBJ#	         Object number. Corresponds directly to the OBJ# column in the SYS.OBJ$ table. You can use this to determine the name of the table containing that column.
INTCOL#	         Column number. Corresponds directly to the COL# column in the SYS.COL$ table. You can use this to determine the name of the column involved.
EQUALITY_PREDS	Number of times this column uses an equality predicate of the form table.column = constant.
EQUIJOIN_PREDS	Number of times this column was used in an equijoin using a predicate of the form table1.column1 = table2.column2.
NONEQUIJOIN_PREDS	Number of times this column was used in an nonequijoin using a predicate of the form table1.column1 != table2.column2.
RANGE_PREDS	Number of times this column was used as a predicate of the form table1.column1 BETWEEN constant1 AND constant2.
LIKE_PREDS	Number of times this column was used as a predicate of the form table1.column1 LIKE like_constant.
NULL_PREDS	Number of times this column was used as a predicate of the form table1.column1 IS NULL.
TIMESTAMP	         The timestamp when the column was last recorded as having been used as a predicate in any query. This is updated by SMON once every 15 minutes.

Using the values in this table, you can determine whether additional indexes and histograms can be created on these columns based on how the columns are referred and by what type of predicates.

For example if that column is involved more in range predicates a histograms creation may helpful and also for a equijoin predicates.

As usual, there are some caveats. Be aware that SMON obtains this information by scanning the library cache in the shared pool and updating this table once every 15 minutes or so. In an active database with memory pressure on the shared pool, this information may be lost. This information may also be lost when the database is shut down unless you invoke the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure beforehand.

-Thanks
Geek DBA

All about Database Administration, Tips & Tricks

New Features for DBA’s

Subscribe to Posts by Email

Subscriber Count

Disclaimer

Recent Posts

Categories

Archives

Pages

12c Database : Move datafile online, no offline or rename required going forward

ORATOP : Another Oracle Performance tool from Oracle

12c Books so far

12c database Architecture diagram, multitenant database architecture

12c Database : Managing PDB’s

12c Database : Create PDB’s , Plug & Unplug

12c Database : Truncate with Cascade

12c Database : RMAN backups and recovery with PDB

12c Database: New groups for OS Authentication and New Sys roles and users

How to determine to collect histograms or not (Even oracle determines by same)

New Features for DBA’s

Follow Me!!!

Subscribe to Posts by Email

Subscriber Count

Disclaimer

Recent Posts

Categories

Archives

Tags

Pages