Steps to Create Oracle Database 18c

Machine : Oracle VirtualBox

OS           : RHEL 6.9

[oracle@nk2b ~]$ lsb_release -a

LSB Version:    :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch

Distributor ID: RedHatEnterpriseServer

Description:    Red Hat Enterprise Linux Server release 6.9 (Santiago)

Release:        6.9

Codename:       Santiago

RAM     : 8gb

Storage  : 100gb

18c Database Software : V978967-01.zip

How to collect statistics in an INTERVAL mode

First Check the old values

SQL> select * from sys.aux_stats$;

Create a new and old Statistics Table

SQL> exec dbms_stats.create_stat_table(ownname=>'SYS',stattab=>'backup_stats');

SQL> exec dbms_stats.create_stat_table(ownname=>'SYS',stattab=>'current_stats');

Save the old Statistics

SQL> exec dbms_stats.export_system_stats(stattab=>'backup_stats');

How to modify Statistics History Retention

Check current retention value

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

---------------------------

                         31

Unable to login as sysdba remotely

While connecting to remote database with sysdba privilege, we might encounter below error:-

[oracle@aqua dbs]$ sqlplus sys/Welcome123@aqua as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 30 18:33:40 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:

This is because, the password file might have a different password.

Duplicate Database Using TARGETLESS RMAN Connection

Source DB: orasbt1

Target DB: orasbp1

Catalog DB: RMANDB

DB Version: 11.2.0.1 for all 3 databases

ASM DiskGroups: (optional)

DATA: For datafile and online redo logs

REDO: For archivelog location

OBACK: For RMAN Backup location

How to create Redo Log Groups and Members on filesystem and ASM Diskgroup

Create Redo Log member with a specific group number

on Filesystem

ALTER DATABASE ADD LOGFILE GROUP 1('/d01/oradata/orasbt1/redo1a.log', '/d02/oradata/orasbt1/redo1b.log') SIZE 20M;

on ASM Diskgroup

ALTER DATABASE ADD LOGFILE GROUP 1('+REDO', '+REDO') SIZE 20M; -- 2 members will be created on REDO disk group

Migrate Oracle Database from Non-ASM to ASM Using RMAN

We need to modify the spfile of the target database as follows:                                

1. Disable block change tracking. (In case you have Block Change Tracking enabled)
2. Modify DB_CREATE_FILE_DEST, DB_CREATE_ONLINE_LOG_DEST_n and CONTROL_FILES parameter to appropreiate ASM Disk Groups

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; --optional           
                                                                        
SQL> ALTER SYSTEM SET db_create_file_dest='+DATA' SCOPE=SPFILE;         
SQL> ALTER SYSTEM SET db_create_online_log_dest_1='+DATA' SCOPE=SPFILE; 
SQL> ALTER SYSTEM SET db_create_online_log_dest_2='+DATA' SCOPE=SPFILE; 
SQL> ALTER SYSTEM SET db_create_online_log_dest_3='+DATA' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET control_files='+DATA' SCOPE=SPFILE;

Planning UNDO RETENTION ...

Find UNDO Size:-

select sum(d.bytes) "undo"
from v$datafile d,
v$tablespace t,
dba_tablespaces s
where s.contents = 'UNDO'
and s.status = 'ONLINE'
and t.name = s.tablespace_name
and d.ts# = t.ts#;

How to Configure Active Dataguard using SQL*Plus

Prerequisites:-

Need to Setup Dataguard  -  CLICK HERE

How to Setup Physical Standby Database

Prerequisites:

Need to install Oracle Database 11.2.0.1

Database Link (DB Link)

How to create DB Link

SQL> create public database link troy connect to troy identified by martin using 'cost1';

SQL> create database link tm connect to troy identified by martin using 'cost1';

A DB Link is a schema object that helps us to fetch objects of a schema from another database

Public DB Link can be accessed by any database user:-
           Using this DB Link any user of this database(cosp1) can access all the objects in troy's schema, as well as the objects on which troy has access to in cost1 database.
Note: We need to have troy's password which is used in cost1 to create this DB Link

How to enable ARCHIVED LOG MODE

Please follow below steps to enable Archived Log Mode

1) Login to database

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 24 06:23:47 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>