19 September 2014

Default value for _sys_logon_delay Oracle 12c parameter

I couldn't find this anywhere else on the Internet, therefore worth posting.

Default value of  _sys_logon_delay 12c parameter is 1.

Value of zero will switch off this feature, so this proves it is enabled by default. One Oracle blog said the higher the values, the bigger the delay is.


NAME      Type  Value Is Def ISSES ISSYS_MOD DESCRIPTION
---------------- ----- ------ ----- ---------------------
_sys_logon_delay  3 1 TRUE   FALSE FALSE failed logon delay for sys

// posted by Tatjana (Tanja) Kane @ 9/19/2014 0 comments

02 September 2014

Using OEM 12c - notes from Oracle course

2 products exist for managing: Cc + Ops Center (host management)

 Oracle HTTP server is started up implicitly when OMS is started. Managed by opmnctl process (if we need explicitly to run something).

 Discovery
=======
Agent-based (self explanatory) or Agent-less (scans all IP n/w addresses. Then you can review and promote what you want)

 Backup
=====
Backup s/w home. That will cover WLS admin server, the whole web logic, OMs and webtier configuration files.
Backup the instance home: emctl exportconfig oms command.

 Monitor
=====
What happens during the upgrade to user-defined metrics? They are called metric extensions now.
Linux and Solaris hosts can be monitored but YaST needs to be installed.
Targets can be grouped in a "group". Notification can be done 'per group'.
Part of service Level Mgmt pack:
  -- Define "SYSTEM" for bunching up all targets that are the infrastructure for the services (like SAP, PS, etc...)
  -- One SYSTEM can be an infrastructure for multiple services.
Use SYSTEMs and beacons for end-to-end monitoring.
  -- Service test is end-to-end test.
  -- Availability can be defined per service

 Some events are statefull and some stateless. Statefull
emcli clear_stateless_elements -older_then=7 -target_type=oracle_database  (older than 7 days)

 Events/Incidents/Problems
====================
For Enterprise Manager 12c, the concept and function of notification rules has been replaced with a two-tier stem consisting of Event Rules and Incident Rule Sets.
Create rules on any of these, to email or do something.
Problem is a type of incident with the same root cause
Incident is something you need to act on
Event Rules: Operate at the lowest level granularity (on discrete events) and performs the same role as notification rules from earlier releases.

 Software Lifecycle Management
=======================
Roles can be more granular: admin, designer (designs workflows), operators
DBA needs to configure the software library, put gold images inside
  -- OMS and all agents need to be able to access the shared file system
Deployment:
  -- create a database profile
  -- create a procedure workflow (Like "Clone Database", "Patch database")
  -- publish it and Launch it
  -- Ability to pause and resume upgrade (by setting breakpoints

 Reports
======
Lots of Predefined reports
!! We can replace our daily report with one of the OEM reports
https://zpgrid1:7799/public/reports,

 Oracle Enterprise Manager Connectors Integration
=====================================
Guide number E25163-4

// posted by Tatjana (Tanja) Kane @ 9/02/2014 0 comments

OEM 12c Installation and Upgrade - notes from Oracle course

OMS is a J2EE app deployed via Web Logic. It has 3 apps:
1 - The Console serves up all the /em URLs.
2 - PBS (Platform Background Services) serves up all the /empbs URLs. It is where agents
upload their metrics.
3 - OCMRepeater is the link between EM CC and My Oracle Support for consolidating
configuration data collected from agents.

 12 new:
Each agent has plug-ins. They are automatically discovered.

 Backup/Restore OMS:
/u01/app/oracle/product/middleware/oms/bin/emctl exportconfig oms
emctl importconfig oms –file /.../EMGC_OMS1/sysman/backup/OMS_backups.bka

 Default ports:
2889/2900 or 3872/3870 between agent and OMS
7788/7799  between OMS and CC
JDBC:1521 from OMS to Rep

 -Installation from s/w distribution is possible.
-11g required a pre-existing WLS installation; 12c does not.
-12c metrics are gathered by agent plug-ins
-OMs replication means copy the config files only (OMS needs to be shut down)
-OMS and OMR can run on the same server. No advantage to separating it.

 Enterprise Manager startup sequence:
1. OMR listener and database instance
2. OMS
3. Agent on OMS server
• Shutdown is in the reverse order.

 Installation:
1. First install OREP database, as normal
2. Download software from http://www.oracle.com/technetwork/oem/grid-control/downloads/linuxx8664soft-085949.html
3. Unpack
4. Run prerequsite on OREP
5. cd to it, ./runInstaller
6. Choose medium size db...
7. Backup OMS
8. Check that https://host:7799/em is working. Secure it with certificate from the trusted domain.

 Upgrade:

 1-system upgrade:
- The Upgrade Console is installed into your current OMS by applying Patch 14375077
- Follow the process.
- Grid is down all the time.
- Agents first, then OMS

 2-system upgrade:
- No loss of metric data
- We can have 11g and 12c running in parallel
- The Upgrade Console is installed into your current OMS by applying Patch 14375077
- Execute post-installation scripts
$OH/bin/rcuJDBCEngine sys/Oracle123@host:1521:orep JDBC_SCRIPT post_install_script.sql $PWD $ORACLE_HOME
$OHbin/rcuJDBCEngine  sys/Oracle123@host:1521:em11rep JDBC_SCRIPT post_install_patch.sql $PWD $ORACLE_HOME
Apply latest PSU
- Install the 12c agent software
   -- Put it in the directory where console can see it
   -- Copy the emkey from oms to repository
- Configure Upgrade console
  -- In "Identify Host.." specify new hostname
  -- In Manage Softare, specify the link to the software
  -- In "Provide Backup.." put date of the templates, or date after
- Clone the current repository
  -- Create a template (with data) from your existing 11g repository
  -- Start the 11g OMS and remove the emkey from the repository
        ##The emkey is a random number that is generated during the installation of the Repository and is stored in a table. When the OMS is installed, the emkey is copied from the  Rep to the $OH/sysman/config/emkey.ora file. After the emkey has been copied, you must remove it from the  Repository as it is not considered secure. If it is not removed, data such as database passwords, server passwords and other sensitive information can be easily decrypted.
  -- Scp the template accross
  -- Use dbca to create a new repository from the template
  -- Other option is to clone it any other way, as long as data is the same #What about removing the emkey?
  -- grant execute on dbms_random to sysman, dbsnmp; #additional privileges needed
- Check the new 12c repository database
  -- emprereq command
  -- If changes are required, run the emprereqkit again and let it make changes that can be auto-corrected.
- Install OMS 12c
  -- ./runINstaller on the new host
     # Chose an option "Upgrade an Existing OEM with 2-Server" not the "Create new OEM"
- In "Link to old OMR" part:
  --grep CONNECTDESCRIPTOR /u01/app/oracle/product/middleware/gc_inst/em/EMGC_OMS1/emgc.properties
    and attach that into the first field
- Use the Upgrade console to deploy 12c agents (check space first)
  --Agent on zpgrid1 can be skipped
  -- Add/Select other agents in “Deploy and Configure Agents”, follow the instructions and Submit a Job
- Agent Health check
  -- Click the “Generate Health Report of Deployed Agents” link, add the targets that have 12c agent deployed
  -- Sign off
- Switch to 12c agent
  -- "Switch Agents" and add the target
  -- This taks supposedlyalso runs Accrued data
- Access Post-Upgrade Console
  -- Navigate to Setup > Manage Cloud Control > Post Upgrade Tasks \ Accrued Data and Submit job for all Components
  -- This is just a read-only view (apparently).
  -- Run what you need in the Deferred Data.
  -- Sign off : This task also de-installs the 11g agent
- Wait for all agents to be activated on 12c before decommissioning the current OMS #if that's what you want to do
  -- While waiting, 11g agents will be active
- If all agents can't be activated, revert to 11g

 How to discover targets:
1. Manually discover the host
2. Set up ssh to it from OMS host
3. Push the agent to it
4. Do auto discover

 Maintenance
=========
Web Logic has to be patched separately

 Security
======
- Backup the emkey emctl exportconfig oms -dir  
- Remove emkey from the 11g repository #don't remove till you backup
- Assuming that you use the default secure communications between the agents and the OMS,
disable unsecure communications with the emctl command: emctl secure lock.
- Secure the console with an SSL certificate from a trusted authority # check what has been done
- Check OEM DR whether it's working

// posted by Tatjana (Tanja) Kane @ 9/02/2014 0 comments

Software enhancement request for Oracle 12c +

Idea

Flashback to be enabled per pluggable database, instead of per the whole instance.

Why?


Because I like flashback and use it a lot for patching/upgrades/production releases/security against user errors. Because it makes DBA's life easier.

Can it be done?

As flashback logs contain the whole block copy of the database, I'm sure somewhere in the block Oracle writes down the pluggable database name, simple because Oracle needs to know to which database that block belongs. 

Action


Software enhancement request raised with Oracle.

// posted by Tatjana (Tanja) Kane @ 9/02/2014 0 comments

12c New Features - Notes

Latest patch right now: 12.1.0.2

OEM CC

Chargeback basaed on capacity
App Mgmt: based on APIs: PS, VMware,...
            Plugg-ins are used instead of db2 (life for SAP)
                        agent has to be installed
Default ports:
            4889/4900 (http/https) – agents
            7788/7801 – OMS
            1521 – JDBC – Repository
Agents can monitor PS suite
Homepage based on the role
Express EM is free: Web Servier is running within XMLDB of the instance. It it’s down, the no monitoring, It monitor only through SQLs. https://host:5500/em (browser requires a Flash Plugin)
In 12c, XMLDB is a mandatory option.
It uses dispatcher, so you need shared servers setup

SQL Developer

It allows DBA operations

Multi Tenant

PD$SEED (like a r/o Model db in SQL) + PD$ROOT (root container) + pluggable dbs
They all share memory, redo logs, flashbadck, control files, undo, temp
CDB_ views; SHOW CON_NAME; SHOW CON_ID
Common vs. Local (Common users use C## prefix)
Don’t share: SYSTEM, SYSAUX

Creating

CREATE DB... ENABLE PLUGGABLE DATABASE ...
            SEED FILE_NAME_CONVERT (‘/dir1’,’/dir2’)
or use DB_CREATE_FILE_DEST or PDB_FILE_NAME_CONVERT
Two containers created by default: CBD$ROOT and CBD$SEED
Default service name is DBNAME.DOMAIN
New PDB from the SSID will have PDBA user with PDB_DBA role
CREATE PLUGGABLE DATABASE dbname
ADMIN USER admin1 IDENTIFIED BY pwd ROLES=(CONNECT) FILES_NAME...
There is local and common DBA role

Plugging in

Violations are listed in PDB_PLUG_IN_VIOLATIONS
Non CDB --> PDB gets SYSTEM, SYSAUX, catalog, service
            EXEC DBMS_PDB.DESCRIBE(‘/tmp/ORCL.xml’);
            CREATE PLUGGABLE DB (NAME) USING ‘/tmp/ORCL.xml’;
            ... @/noncdb_to_pdb
You can unplug PDB from one CDB and plug it into another

Dropping

ALTER DB... CLOSE; DROP PLUGGABLE DB ... [INCLUDING DATAFILES];

Migration 11G--> 12C

1) Upg and plug OR 2) Create empty PDB and exp/impdp

Managing

SQL> SHOW CON_NAME #Show db you are connected to
STARTUP MOUNT; ALTER PLUGGABLE DB ALL OPEN/CLOSE;
ALTER SESSION SET CONTAINER=name; #to switch to a container
2 new EVENT triggers: Before unplug and after;
You cannot create local user in ROOT PDB.

Backup/Recovery

S/w request for introduction flashback for each PDB, as you can only flashback instance
New RC_PDBS

HEAT MAP or ILM – Advanced Compression option

Enable it to use ADO – Automatic Data Optimization. HEAT_MAP=ON
As data gets less used or tblsp fuller, it compresses more and more
6 levels: BASIC, ADVANCED, QUERY (LOW or HIGH), ARHIVE (l or h)
Can be set on tblspc level, segment, partition, row
Lowest level policy will first take effect
ALTER TABLE tab1 ILM ENABLE_ALL; - enable all policies
Disable on instance level: EXEC DBMS_ILM_ADMIN.DISABLE_ILA:

Move DF online

ALTER DATABASE MOVE DATAFILE ‘...’ TO ‘...’ [REUSE/KEEP];
Monitor progress in v$SESSION_LONGOPS

IN-DB ARCHIVING

ALTER TABLE... ROW ARCHIVAL;
Column ORA_ARCHIVE_STATE added to each table (0 for new rows, 1 for archived)
ALTER SESSION SET ROW ARCHIVE VISIBILITY=ALL;
This is not automatic. It has to be done manually

Temporal Validity

CREATE TABLE... ( ..., PERIOD FOR user_time);
It creates 2 new columns: user_time_start/end
DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME(‘ASOF’,date);
SELECT ... AS OF ....

Unified Auditing – an option

GEN0 process flushes audit records from the memory, or you can flush them manully from the SGA, to the unifiied trail. SYS.UNIFIED_AUDIT_TRAIL view;
            To flush: EXEC DBMS_AUDIT+AUDIT_MGMT.FLUSH_UNIFIED...
You can configure them Immediate-Write mode of Queued_Write (from memory)
CREATE POLICY for the “component” and enable it.
            Components: DP, RMAN, FGA, OLS, DVault, RA Security
To switch it on: relink the libraries
Roles: AUDIT_ADMIN and AUDIT_VIEWER
Policy can be on: actions, priv, roles with WHEN stmt; and can be “BY user1, user2”

Privileges

New: SYSBACKUP, SYSDG, SYSKM (they are in PW file)
Privilege Analysis work with db Vault option
DBMS_PRIVILEGE_CAPTURE.ENABLE/DISABLE/CREATE_CAPTURE
DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT
View DBA_[UN]USED_PRIVS
Grant INHERIT PRIVILEGE user tanja to simon; -- this means Tanja trusts that Simon’s code is honest enough to run stuff over Tanja’s objects
Recreate pw file after the 12c upgrade to get the new PW format
Revoke INHERIT PRIV fromPUBLIC, as every new user gets them

Redaction – option (hiding data) !

DBMS_REDACT pkg ADD/DROP/ALTER/ENABLE/DISABLE_POLICY
Full redaction, partial (xxxx-xxxx-xxxx-3424), randmon

RMAN

We can run SQL statms from RMAN prompt
In 11g, use MULTI SECTON BACKUP with SECTION SIZE option.
DUPLICATE uses backup sets in 12c by default. You can leave dest db mounted.
Transport dbs across platform by using backup sets. In 11g, we had to copy files and convert. DB source neds to be r/o
Table Recovery – uses AUX instance (1Gb SGA), called Bpsw.
            Space used: SYSTEM +  recovered tablepsaced + undo  + SYSAUX

Monitoring

11.1v+ Real-Time SQL Monitoring – see where inexec plan SQL stmt is
DB Ops – Tuning Pack ! – group statemts by users or app

Schema Comparison DBLM Pack !

It propages schema changes to diff envs

Adaptive SQL Plan Mgmt – Tuning pack !

optimizer_user_sql_plan_baseline = TRUE
It takes first plan P1 as baseline B1. When P2 comes along, it will compare. If it is at least 3 times better create B2, otherwise, wait for DBA to approve.
In 11g, P2 was compared to P1 just once. In 12c, it reconsiders them every time “comparison job” runs.
            11g: DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE
            12C: sys_audot_smp_evolve_task

Adaptive Executions Plan

V$SQL.IS_RESOLVED_ADAPATIVE_PLAN
Set OPTIMIZER_ADAPTIVE_REPORTING_ONLY=FALSE + compat>=12.1
Two techniques: Dynamic plans or re-optimization
            It doesn’t touch SQLs with profile or baseline
1) Just 2 sub-plans are considered, with the lowest costs
2) Checks whether stats have changed by some threshold
Cardinality feedback : _optimizer_user_feedback=TRUE
It saves STATS on predicate conditions, like ‘p>100 and t=’femail)=200 rows in real life, not 400,000 optimizer thinks it is

Stats

CREATE TABLE.. AS SELECT and INSET INTO empty table will do stats by auto
Gathering stats can be concurrent (parallelised)
Each session has its own stats for shared GLOBAL TEMP table
Extended stats on column group or expressions
EXEC DBMS_STATS.SEED_COL_USAGE – O does the rest automatically
Dynamic sampling=11 will consider any of the previous numbers

Histograms

Height balance are not used any more
2 new: Top-frequency and Hybrid
1) When most data is in few buckets
2) It picks only top n popular values and puts them into n buckets
abbbbbcccdd
Hybird: b5, c3,d2         Freq: a1,b6,c8,d10       Top-freq:b6,c8,d10

Emergency Monitoring – Diag pack

You don’t have to wait for ADDM. Just run it in real-time:
select dbms_addm.real_time_addm_report() from dual;
The report does cause and effect analysis.

ASH

MMON writes to a memory buffer. When full or periodically, MMNL empties it to AWR snapshots.

Multi-threaded architecture

It uses 1 main process that spawns threads instead of lots of processes.
ALTER SYSTEM SET threaded_execution=true
SCMN – spawns client processes instead of listener

Flash Cache

Add flash drives to SGA memory. In 11g=> only 1 possible

Paralel In-memory on RAC

parallel_degree_policy=AUTO
It divides segments into chunks and sends them to separate nodes

Temporary UNDO

In 11g, undo was logged to redo
In 12c, no redo at all. ALTER SYSTEM/SESS SET TEMPO_UNDO_ENABLED

Multiple Indexes

You can create multiple indexes on the same columns, but only B Tree or bitmap. Either one or the other must be visible at a time.

Invisible Columns

CREATE TABLE... (... col2 NUMBER INVISIBLE);
It can be accessed only by explicitly naming the column in DML commands or SET COLINVISIBLE ON in SQL session.

Online Redefinition

You can add dml_lock_timeout for REDEF procedure to waits on other DMLs
It works better with VPD

Compression

Renamed: Compress in 11g is Row Store Compress in 12c
Compress for OLTP is called row store compress advanced.
Compression Adviser is slightly more clever. – option!

ONLINE DMLs

DROP INDEX/CONSTRAINT; ALTER INDEX UNUSABLE; SET COLUMN UNUSED

ADR

New directories: Under SID/log/ddl and debug
ALTER SYSTEM SET enable_ddl_logging=TRUE- for DDL logging  FALSE for debug
adrci: show log

Network performance: Compression – option!

SDU is 8K by default for dedicated sessions, 64K for shared, maximum allowed is 2Mb
SQLNET.COMPRESSION=ON

Data Pump, Loader & External Tables

11.2.0.3+ whole db can be transported into another. It works like an enhanced version of transportable tblscp.  Expdp FULL=y TRANSPORTABLE=always ...
Take tblespaces into r/o mode; expdp; copy (use rman convert or dbms_file_transfer for endians); then impdp TRANSPORT_DATAFILES=....
Or you can use db link from the destitation: impdp FULL=Y NETWORK_LINK=sourcedb TRANSPORTABLE=always....
Redo logging during import can be disabled
            impdp ... TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y|N[:TABLE|IND]
Views can be exported as tables; Constraints preserved. expdp VIEWS_AS_TABLES=...
Encryption password ENCRYPTION_PWD_PROMPT=Y – provided silently at run time
Compression can be defined at import time: TRANSFORM=TABLE_COMPRESSION_CLAUSE:’ROW STORE COMPRESS|...”
LOBs can be converted to secure files during import: LOB+STORAGE:SECUREFILE
SQL*Loader supports insert into IDENTITY columns, even via direct path roads.
CREATE TABLE (col1 NUMBER GENERATED
[ALWAYS/BY DEFAULT on NULL] AS IDENTITY
SQL*Loader allows wildcards INFILE (‘emp?.sh*’)
SQL*Loader supports CSV files with newlines:  FIELDS CSV WITH EMBEDDED
SQL*Loader- FIELDS ... , NULLIF = “value”
            FIELD NAMES FIRST FILE – skip the first field
            ALL FIELDS OVERRIDE – skip the columns with default values
            Express mode: $sqlldr user TABLE=tablename - must have tablename.dat file

Partitioning

ALTER TABEL ... MOVE PARTITION p1 [COMPRESS .../TABLESPACE t1]
UPDATE INDEXES ONLINE; - Basic compression is free.
Reference partitioning introduced in11g. Child is partitioned in the same way as the parent (inherited through FK key), even if the child doesn’t have the partition column.
            In 12c: INTERVALs on reference ;
In 12c: TRUNCATE/EXCHANGE PARTITION CASCADE
11g –only 2 partitions at a time 12c-> all at a time; for Add/truncate/drop/merge/split
            Indexes are also affected in the same way
Partitioned Indexes: each partition is a completely diff index.
Local index can be created on a group of selected partitions. ...
...PARTITION p1 ....) INDEXING ON/OFF,
CREATE INDEX ... INDEXING PARTIAL.. – creates global index only on partitions which have “INDEXING ON’.
DROP/TRUNCATE partition with UPDATE INDEXES will mark the change in meta-data. Later, maint. job SYS.PMO_DEFERRED_GIDX_MAINT_JOB kicks in to clean up those ORPHANED_ENTRIES (column in DBA views). You can manually kick this with DBMS_PART.CLEANUP_GID;

Various

TRUNCATE TABLE name CASCADE; as long as there is 1 constr with ON DELETE CASCADE clause
select * from tale(dbms_xplan.display_curosr(format=>’basice +note’);
[N]VARCHAR2 and RAW is now 32767 bytes – alter system set max_string_size=extended;, run utl32k.sql script, bounce the instance, rebuild indexes.
DB Migration Assistant – migrates dbs from standard to Unicode set.
SecureFiles are now default storage mechanism for LOBs
SELECT ... FETCH FIRST 5 ROWS ONLY; or FETCH FIRST 10 PERCENT ONLY;
SELECT ... OFFSET 100 FETCH NEXT 10 rows only: --fetches 101-110 rows

// posted by Tatjana (Tanja) Kane @ 9/02/2014 0 comments

This page is powered by Blogger. Isn't yours?