Twelve new DBA commands for Oracle 12c

-
With more than 500 new features, Oracle Database 12c is designed to give us exactly what we need for easy database administration, consolidation, security and availability. And I cannot deny the complexity it brings into our pluggable world.

Pluggable Database
Online Data file Movement
Online Table Partition Movement
DDL Logging
Temporary UNDO for Temporary Tablespace
RMAN Table level Recovery
RMAN SQL Command Execution
SQL Enhancement
PGA Sizing Limitation
Pre Upgrade fixes enhancement
Disable Archive Log in Data Pump
Concurrent Database Statistics Generation
Flex Cluster Supported for Oracle RAC Architecture
Flex architecture and Disk Scrubbing Support to Oracle ASM
12_commands
But straight to the point. Here are 12 new commands for Oracle DBAs:
1. impdp … transform=disable_archive_logging:Y
The new TRANSFORM option DISABLE_ARCHIVE_LOGGING appended to the impdp command line will cause Oracle Data Pump to disable (almost completely) redo logging when loading data into tables and when creating indexes. Check this example.
2. select xmltransform(dbms_qopatch.get_opatch_lsinventory, dbms_qopatch.get_opatch_xslt) from dual;
With the new DBMS_QOPATCH package we can view the installed database patches:
GET_OPATCH_BUGS: Provides a bugs list for a patch
GET_OPATCH_COUNT: Provides the total number of installed patches
GET_OPATCH_LSINVENTORY: Returns whole opatch inventory
GET_OPATCH_PREQS: Provides prerequisite patches for a given patch
Check this example.
3. grant sysdba, sysoper, sysbackup, sysdg, syskm to c##julian identified by password container=all;
If we want to create a common user in the CDB, we must prefix it with c##. It is because the default common user prefix in 12.1.0 is c##. However, the _common_user_prefix parameter controls that prefix. One option is to set it to NULL.
SYSBACKUP, SYSDG and SYSKM are also database users. SYSDG is used for Oracle Data Guard operations: startup, shutdown and flashback database while SYSKM is used for encryption key management operations: connect to the database even if the database is not open.
4. create table porter(owner varchar2(128), object_name varchar2(128), created date invisble);
porter
This “trick” does not work with temporary tables, external tables and cluster tables. Check this example.
5. alter system set max_string_size=extended;
extended_size
The new init.ora parameter MAX_STRING_SIZE (default is standard) controls the maximum size of the VARCHAR2, NVARCHAR2, and RAW data types. It can be increased from 4,000 to 32767 bytes. Check this example.
6. alter database move datafile ‘old_location’ to ‘new_location’;
move_online
In 12.1.0, a data file can now be moved online while it is open and being accessed, all these are possible now:
– Renaming an Online Datafile
– Relocating an Online Datafile
– Copying an Online Datafile
– Relocating an Online Datafile and Overwriting an Existing File
– Relocating an Online Datafile to Oracle ASM
– Moving a File from One ASM Location to Another ASM Location
7. recover table emp until time ‘sysdate-1’;
table_recovery
RMAN enables us to recover one or more tables or table partitions to a specified point in time without affecting the remaining database objects. We can use previously-created RMAN backups to recover tables and table partitions to a specified point in time. Check this example.
8. alter session set row archival visibility = active;
alter session set row archival visibility = all;
In-Database Archiving enables us to archive rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression, but are not visible to an application. To manage In-Database Archiving for a table, we must enable ROW ARCHIVAL for the table and manipulate the ORA_ARCHIVE_STATE hidden column of the table. Check my old blog post.
9. alter session set temp_undo_enabled = true;
Check this example.
10. truncate table dept cascade;
TRUNCATE TABLE is enhanced in the following way: a new clause CASCADE allows you to recursively truncate child tables. Check this example.
11. create sequence temp_order_seq start with 100 increment by 10 session;
The new clause SESSION allows you to create a session sequence. Check this example.
12. $ORACLE_HOME/perl/bin/perl catctl.pl -n 8 -l $ORACLE_HOME/diagnostics catupgrd.sql
Oracle Database 12c introduces the Parallel Upgrade Utility (catctl.pl), which enables components that do not have to be upgraded in a specific order to be upgraded at the same time, taking full advantage of CPU capacity. The Parallel Upgrade Utility reduces the total amount of time it takes to perform an upgrade by loading the database dictionary in parallel using multiple SQL processes to upgrade the database.
-n specifies the number of processes to use for parallel operations (default = 4). The maximum is 8. Set this parameter to 0 to run catctl.pl in serial mode.

Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the ...
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify th...
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12      ...
Read more