Why CBD_DATA_FILES and V$DATAFILE differs?

Though it is well documented in the Oracle 12c documentation, just to demo I wrote this post.

http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn009.htm#REFRN009

CDB_* Views

For every DBA_* view, a CDB_* view is defined. In the root of a multitenant container database (CDB), CDB_* views can be used to obtain information about tables, tablespaces, users, privileges, parameters, and so on contained in the root and in pluggable databases (PDBs).
CDB_* views are container data objects. When a user connected to the root queries a CDB_* view, the query results will depend on the CONTAINER_DATA attribute for users for the view. The CONTAINER_DATA clause of the SQL ALTER USER statement is used to set and modify users' CONTAINER_DATA attribute.
The CDB_* views are owned by SYS, regardless of who owns the underlying DBA_* view.
By default, a user connected to the root will only see data pertaining to the root.

In a PDB, the CDB_* views only show objects visible through a corresponding DBA_* view.
In addition to all the columns found in a given DBA_* view, the corresponding CDB_* view also contains the CON_ID column, which identifies a container whose data a given CDB_* row represents. In a non-CDB, the value of a CON_ID column will be 0.
Data returned by these views depends on whether a given PDB is open at the time the query is issued.

In particular, in an Oracle RAC environment, data returned by these view may vary according to the instance to which a session is connected.

List of action performed

1. Logged into CDB
2. Queried  both cbd_data_files and v$datafile , CDB_ view has only 7 rows
3. Queried V$PDBS to know status off PDBs
4. One PDB is in Mount state
5. Opened that PDB in read write mode.
6. Then we could see all the files.

SQL> set time on echo on
22:27:54 SQL> select name,cdb from v$database;

NAME      CDB                                                                  
--------- ---                                                                  
ORCL12C   YES                                                                  

22:28:14 SQL> sho con_name

CON_NAME                                                                       
------------------------------                                                 
CDB$ROOT                                                                       
22:28:20 SQL> set pages 20
22:28:54 SQL> select name from v$datafile;

NAME                                                                           
--------------------------------------------------------------------------------
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSTEM01.DBF                                    
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSTEM01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSAUX01.DBF                                    
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSAUX01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\UNDOTBS01.DBF                                   
D:\APP\TWELVEC\ORADATA\ORCL12C\USERS01.DBF                                     
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SYSTEM01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SYSAUX01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF               
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\EXAMPLE01.DBF                           

10 rows selected.

22:29:03 SQL> select file_name from cdb_data_files;

FILE_NAME                                                                      
--------------------------------------------------------------------------------
D:\APP\TWELVEC\ORADATA\ORCL12C\USERS01.DBF                                     
D:\APP\TWELVEC\ORADATA\ORCL12C\UNDOTBS01.DBF                                   
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSAUX01.DBF                                    
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSTEM01.DBF                                    
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSTEM01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSAUX01.DBF                            

6 rows selected.

22:29:24 SQL> select con_id,name,open_mode from v$pdbs order by 1;

    CON_ID NAME                           OPEN_MODE                            
---------- ------------------------------ ----------                           
         2 PDB$SEED                       READ ONLY                            
         3 PDBORCL                        MOUNTED                              


22:31:08 SQL> alter pluggable database PDBORCL open read write;

Pluggable database altered.

22:31:25 SQL> select con_id,name,open_mode from v$pdbs order by 1;

    CON_ID NAME                           OPEN_MODE                            
---------- ------------------------------ ----------                           
         2 PDB$SEED                       READ ONLY                            
         3 PDBORCL                        READ WRITE                           

22:31:32 SQL> select file_name from cdb_data_files;

FILE_NAME                                                                      
--------------------------------------------------------------------------------
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSTEM01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSAUX01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\USERS01.DBF                                     
D:\APP\TWELVEC\ORADATA\ORCL12C\UNDOTBS01.DBF                                   
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSAUX01.DBF                                    
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSTEM01.DBF                                    
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SYSTEM01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SYSAUX01.DBF                            
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF               
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\EXAMPLE01.DBF                           

10 rows selected.

22:31:45 SQL> spool off

Please provide your valuable suggestion/ corrections to improve
Thanks , Happy reading...

Oracle 12c Database install on Windows 7

Today I have installed 12c database on my laptop to test one issue, just thought to share the steps. Will post all my experiments in this blog soon. Happy learning. :)

1. Configure Security updates.

2. Mention whether software updates needed

3. Install options, select 

4. Install class Desktop or server class

5. Grid installation option

6. Install type

7. Specify Oracle Home user

8. Select home path, provide OS admin user password, whether container/ pluggable DB need to be created

9 & 10 Pre requisite check and Summary Screen ( Forgot to take Snap) sorry

11. Installing database, will invoke DBCA and will create CDB and PDB

12. Finish

Test login

Windows x64 Hardware Requirements

Requirement	Value
System Architecture	Processor: AMD64 and Intel EM64T
Physical memory (RAM)	2 GB minimum
Virtual memory (swap)	If physical memory is between 2 GB and 16 GB, then set virtual memory to 1 times the size of the RAM If physical memory is more than 16 GB, then set virtual memory to 16 GB
Disk space	Typical Install Type total: 10 GB Advanced Install Types total: 10 GB See Table 2-2 for details.
Video adapter	256 colors
Screen Resolution	1024 X 768 minimum

Reference:

Oracle® Database Installation Guide 12c Release 1 (12.1) for Microsoft Windows
Oracle 12c Download

DBCA error Java.lang.OutOfMemoryError: Java Heap Space" Error

We can increase the default 128m memory value in dbca file and start dbca again

Windows :

1. Navigate to ORACLE_HOME/BIN (Take a backup dbca.bat)

2. Find and edit file dbca.bat with the below 

"D:\Oracle_DB\product\11.2.0\dbhome_1\jdk\jre\BIN\JAVA" -DORACLE_HOME="%OH%" -Doracle.installer.not_bootstrap=true -DJDBC_PROTOCOL=thin -mx128m oracle.sysman.assistants.dbca.Dbca %*

3. Modify "-mx128m" value to a higher value like -mx512m

Linux:

1. cd $ORACLE_HOME/bin

2. ls -ltr dbca

3. vi dbca and edit the value -mx128m

JRE_OPTIONS="${JRE_OPTIONS} -DSET_LAF=${SET_LAF} -Dsun.java2d.font.DisableAlgorithmicStyles=true -Dice.pilots.html4.ignoreNonGenericFonts=true -DDISPLAY=${DISPLAY} -DJDBC_PROTOCOL=thin -mx128m"

How to find a column has space:

There may be several other ways to find space in a column, this is another simple way

SQL> sho user
USER is "SYS"
SQL> conn a/a
Connected.
SQL> create table space (space varchar2(10));

Table created.

SQL> insert into space values ('test1');

1 row created.

SQL> insert into space values ('test2 ');

1 row created.

SQL> insert into space values ('test3  ');

1 row created.

SQL> insert into space values ('test4   ');

1 row created.

SQL> insert into space values ('test5    ');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from space;

SPACE
----------
test1
test2
test3
test4
test5

SQL> select length(space) from space;

LENGTH(SPACE)
-------------
            5
            6
            7
            8
            9

SQL> select space|| ';' from space;

SPACE||';'
-----------
test1;
test2 ;
test3  ;
test4   ;
test5    ;

SQL> select length(space) "length",space|| ';' value from space;

    length VALUE
---------- -----------
         5 test1;
         6 test2 ;
         7 test3  ;
         8 test4   ;
         9 test5    ;

Compare AWR reports easily.

This is my new post exactly after '5 months, 3 weeks, 6 days' long break. I have joined most prestigious company for any DBA. New office, city, house... day by day new learning and unique by its nature. I will try to post my new learning here.

I have seen feature in OEM to compare two awr reports and today found a sql and functions for it AWR_DIFF_REPORT_HTML/ AWR_DIFF_REPORT_TEXT.

I ran in a windows 7, oracle 11.2.0.2 database.

The script is @?/rdbms/admin/awrddrpt. It will confirm two dbids and 4 snaps. easier to compare two reports.

Snap of a sample report.

Reference:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_wkrpos.htm#BACEHJJI