1z0-060 Upgrade to Oracle Database 12c - Use OUI, DBCA for installation and configuration (Manage Templates)

 

Managing Templates with DBCA

DBCA templates are XML files that contain information required to create a database. Templates are used in DBCA to create new databases and duplicate existing databases. The information in templates includes database options, initialization parameters, and storage attributes (for data files, tablespaces, control files, and online redo log files).

Templates can be used just like scripts, but they are more powerful than scripts because you have the option of duplicating a database. Duplication saves time because you copy the files of an existing database, referred to as a seed database, to the correct locations.

Templates are stored in the following directory:

ORACLE_HOME/assistants/dbca/templates

Advantages of Using Templates

Using templates has the following advantages:

• Time saving. If you use a template, then you do not have to define the database.



• Easy duplication. By creating a template containing your database settings, you can easily create a duplicate database without specifying parameters twice.



• Easy editing. You can quickly change database options from the template settings.



• Easy sharing. Templates can be copied from one computer to another.

Types of Templates

Templates are divided into the following types:

• Seed templates



• Nonseed templates

 

Manage Templates:

 

oracle@solaris:~$ dbca -createTemplateFromDB -help

Create a template from an existing database by specifying the following parameters:


        -createTemplateFromDB


                -sourceDB    <Database unique name for RAC Database and SID for Single Instance Database>


                -templateName      <new template name>


                -sysDBAUserName     <user name  with SYSDBA privileges>


                -sysDBAPassword     <password for sysDBAUserName user name>


                [-maintainFileLocations <true | false>]


                [-connectionString   <EZCONNECT String for connecting to remote database for example "host:port/servicename">]

oracle@solaris:~$ dbca -createCloneTemplate -help

Create a clone template from an existing database by specifying the following parameters:


        -createCloneTemplate


                -sourceSID    <Database unique name for RAC Database and SID for Single Instance Database>


                -templateName      <new template name>


                [-sysDBAUserName     <user name  with SYSDBA privileges>


                 -sysDBAPassword     <password for sysDBAUserName user name>]


                [-maxBackupSetSizeInMB     <Maximum backup set size (MB)>]


                [-rmanParallelism  <Parallelism value>]


                [-datafileJarLocation       <directory to place the datafiles in a compressed format>]

1z0-060 Upgrade to Oracle Database 12c - Use OUI, DBCA for installation and configuration (Delete Database)

 

Delete Database:

oracle@solaris:~$ dbca -deleteDatabase -help
Delete a database by specifying the following parameters:
        -deleteDatabase
                -sourceDB    <source database sid>
                [-sysDBAUserName     <user name  with SYSDBA privileges>
                 -sysDBAPassword     <password for sysDBAUserName user name>]
                [-emConfiguration <DBEXPRESS|CENTRAL|BOTH|NONE>]
                        [-omsHost     <Enterprise Manager central agent home>
                        -omsPort     <Enterprise Manager central agent port>
                        -emUser     <Host user name for EM backup job>
                        -emPassword     <Host user password for EM backup job>]]

1z0-060 Upgrade to Oracle Database 12c - Use OUI, DBCA for installation and configuration (Configure Database Options)

 

Configure Database Options:

1z0-060 Upgrade to Oracle Database 12c - Use OUI, DBCA for installation and configuration (Create Database)

Reference URL: http://docs.oracle.com/cd/E16655_01/server.121/e17643/install.htm#ADMQS023

Create Database:

oracle@solaris:~$ dbca -help
dbca  [-silent | -progressOnly] {<command> <options> }  | { [<command> [options] ] -responseFile  <response file > } [-continueOnNonFatalErrors <true | false>]
<command> : -createDatabase | -configureDatabase | -createTemplateFromDB | -createCloneTemplate | -generateScripts | -deleteDatabase | -createPluggableDatabase | -unplugDatabase | -deletePluggableDatabase | -configurePluggableDatabase
Enter "dbca -<command> -help" for more option
oracle@solaris:~$ dbca -createDatabase -help
Create a database by specifying the following parameters:
        -createDatabase
                -templateName <name of an existing template in default location or the complete template path>
                [-cloneTemplate]
                -gdbName <global database name>
                [-ignorePreReqs] <ignore prerequisite checks for current operation>
                [-sid <database system identifier>]
                [-createAsContainerDatabase <true|false>
                        [-numberOfPDBs <Number of Pluggable databases to be created, default is 0>]
                        [-pdbName <New Pluggable Database Name>]
                [-sysPassword <SYS user password>]
                [-systemPassword <SYSTEM user password>]
                [-emConfiguration <DBEXPRESS|CENTRAL|BOTH|NONE>]
                        -dbsnmpPassword     <DBSNMP user password>
                        [-omsHost     <EM management server host name>
                        -omsPort     <EM management server port number>
                        -emUser     <EM Admin username to add or modify targets>
                        -emPassword     <EM Admin user password>]]
                [-dvConfiguration <true | false Specify "true" to configure and enable Database Vault
                        -dvUserName     <Specify Database Vault Owner user name>
                        -dvUserPassword     <Specify Database Vault Owner password>
                        -dvAccountManagerName     <Specify separate Database Vault Account Manager >
                        -dvAccountManagerPassword     <Specify Database Vault Account Manager password>]
                [-olsConfiguration <true | false Specify "true" to configure and enable Oracle Label Security >
                [-datafileDestination <destination directory for all database files> |
-datafileNames <a text file containing database objects such as controlfiles, tablespaces, redo log files and spfile to their corresponding raw device file names mappings in name=value format.>]
                [-redoLogFileSize <size of each redo log file in megabytes>]
                [-recoveryAreaDestination <destination directory for all recovery files>]
                [-datafileJarLocation  <location of the data file jar, used only for clone database creation>]
                [-storageType < FS | ASM >
                        [-asmsnmpPassword     <ASMSNMP password for ASM monitoring>]
                         -diskGroupName   <database area disk group name>
                         -recoveryGroupName       <recovery area disk group name>
                [-characterSet <character set for the database>]
                [-nationalCharacterSet  <national character set for the database>]
                [-registerWithDirService <true | false>
                        -dirServiceUserName    <user name for directory service>
                        -dirServicePassword    <password for directory service >
                        -walletPassword    <password for database wallet >]
                [-listeners  <list of listeners to configure the database with>]
                [-variablesFile   <file name for the variable-value pair for variables in the template>]]
                [-variables  <comma separated list of name=value pairs>]
                [-initParams <comma separated list of name=value pairs>]
                [-sampleSchema  <true | false> ]
                [-memoryPercentage <percentage of physical memory for Oracle>]
                [-automaticMemoryManagement ]
                [-totalMemory <memory allocated for Oracle in MB>]
                [-databaseType <MULTIPURPOSE|DATA_WAREHOUSING|OLTP>]]
oracle@solaris:~$ dbca -configureDatabase -help
Configure a database by specifying the following parameters:
        -configureDatabase
                -sourceDB    <Database unique name for RAC Database and SID for Single Instance Database>
                [-sysDBAUserName     <user name  with SYSDBA privileges>
                 -sysDBAPassword     <password for sysDBAUserName user name>]
                [-registerWithDirService|-unregisterWithDirService|-regenerateDBPassword <true | false>
                        -dirServiceUserName    <user name for directory service>
                        -dirServicePassword    <password for directory service >
                        -walletPassword    <password for database wallet >]
                [-addDBOption     <Specify any of the following DB Options as a comma separated list: JSERVER | ORACLE_TEXT | IMEDIA | CWMLITE | SPATIAL | OMS | APEX | DV>]
                [-dvConfiguration <true | false Specify "true" to configure and enable Database Vault
                        -dvUserName     <Specify Database Vault Owner user name>
                        -dvUserPassword     <Specify Database Vault Owner password>
                        -dvAccountManagerName     <Specify separate Database Vault Account Manager >
                        -dvAccountManagerPassword     <Specify Database Vault Account Manager password>]

1z0-060 Upgrade to Oracle Database 12c - Use EM Express

Oracle Enterprise Manager Database Express (EM Express) is a web-based database management tool that is built inside the Oracle Database. It supports key performance management and basic database administration functions. From an architectural perspective, EM Express has no mid-tier or middleware components, ensuring that its overhead on the database server is negligible.

Using EM Express, you can perform administrative tasks such as managing user security and managing database memory and storage. You can also view performance and information about your database.

Enterprise Manager Database Control is no longer available in Oracle Database 12c. You can use Enterprise Manager Cloud Control 12c or Enterprise Manager Database Express 12c to manage your Oracle Database 12c databases.

SQL> select dbms_xdb_config.gethttpsport() from dual;

DBMS_XDB_CONFIG.GETHTTPSPORT()
------------------------------
                          5500

SQL>  select dbms_xdb_config.gethttpport() from dual;

DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
                         8080

SQL>  select value from v$parameter where name='dispatchers';

VALUE
--------------------------------------------------------------------------------
(PROTOCOL=TCP) (SERVICE=orclXDB)

 

SQL> select value from v$parameter where name='local_listener';

VALUE
--------------------------------------------------------------------------------
LISTENER_ORCL

SQL> ! tnsping LISTENER_ORCL

TNS Ping Utility for Solaris: Version 12.1.0.1.0 - Production on 04-JUN-2014 05:31:14

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (ADDRESS = (PROTOCOL = TCP)(HOST = solaris)(PORT = 1521))
OK (80 msec)

SQL> ! lsnrctl status

LSNRCTL for Solaris: Version 12.1.0.1.0 - Production on 04-JUN-2014 05:24:12

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=solaris)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 12.1.0.1.0 - Production
Start Date                04-JUN-2014 04:46:56
Uptime                    0 days 0 hr. 37 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/solaris/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=solaris)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=solaris)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=solaris)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

http://192.168.56.201:8080/em/

https://192.168.56.201:5500/em/

 

Use the PL/SQL procedure DBMS_XDB_CONFIG.SETHTTPSPORT to set the HTTPS port for EM Express. This will update the HTTPS port in thexdbconfig.xml file in the Oracle XML DB Repository. You must connect as SYS / AS SYSDBA to run the procedure. For example:

SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5500);

As a database administrator, you can log in to EM Express with the SYS or SYSTEM user account to perform administrative and other tasks. Nonadministrative users may also want to log in to EM Express.

For nonadministrative users to have access to EM Express, they must be granted the EM_EXPRESS_BASIC or the EM_EXPRESS_ALL role.

The EM_EXPRESS_BASIC role enables users to connect to EM Express and to view the pages in read-only mode. The EM_EXPRESS_BASIC role includes the SELECT_CATALOG_ROLE role.

The EM_EXPRESS_ALL role enables users to connect to EM Express and use all the functionality provided by EM Express (read/write access to all EM Express features). The EM_EXPRESS_ALL role includes the EM_EXPRESS_BASIC role.

SQL> select  'Privilege' type, privilege from dba_sys_privs where grantee='&grantee'
  2  union all
  3  select  'Role' type, granted_role from dba_role_privs where grantee='&&grantee'
  4  union all
  5  select  'Object' type, owner||'.'||table_name||'('||privilege||')' from dba_tab_privs where grantee='&&grantee'
  6  order by 1,2;

TYPE       PRIVILEGE
---------- ----------------------------------------------------------------------
Object     SYS.DBMS_PERF(EXECUTE)
Object     SYS.PRVTEMX_ADMIN(EXECUTE)
Object     SYS.V_$DIAG_INCIDENT(SELECT)
Privilege  CREATE SESSION
Privilege  EM EXPRESS CONNECT
Role       SELECT_CATALOG_ROLE

SQL> select  'Privilege' type, privilege from dba_sys_privs where grantee='&&grantee'
  2  union all
  3  select  'Role' type, granted_role from dba_role_privs where grantee='&&grantee'
  4  union all
  5  select  'Object' type, owner||'.'||table_name||'('||privilege||')' from dba_tab_privs where grantee='&&grantee'
  6  order by 1,2;
Enter value for grantee: EM_EXPRESS_ALL

TYPE       PRIVILEGE
---------- ----------------------------------------------------------------------
Object     SYS.DBMS_AUTO_SQLTUNE(EXECUTE)
Object     SYS.DBMS_AUTO_TASK_ADMIN(EXECUTE)
Privilege  ADMINISTER ANY SQL TUNING SET
Privilege  ADMINISTER SQL MANAGEMENT OBJECT
Privilege  ADMINISTER SQL TUNING SET
Privilege  ADVISOR
Privilege  ALTER ANY ROLE
Privilege  ALTER PROFILE
Privilege  ALTER SYSTEM
Privilege  ALTER TABLESPACE
Privilege  ALTER USER
Privilege  CREATE CREDENTIAL
Privilege  CREATE JOB
Privilege  CREATE PROFILE
Privilege  CREATE ROLE
Privilege  CREATE TABLESPACE
Privilege  CREATE USER
Privilege  DROP ANY ROLE
Privilege  DROP PROFILE
Privilege  DROP TABLESPACE
Privilege  DROP USER
Privilege  GRANT ANY OBJECT PRIVILEGE
Privilege  GRANT ANY PRIVILEGE
Privilege  GRANT ANY ROLE
Role       EM_EXPRESS_BASIC

 

SQL> select grantee,granted_role from dba_role_privs where granted_role like 'EM_EXPRESS%' order by 2;

GRANTEE              GRANTED_ROLE
-------------------- ------------------------------
DBA                  EM_EXPRESS_ALL
SYS                  EM_EXPRESS_ALL
EM_EXPRESS_ALL       EM_EXPRESS_BASIC
SYS                  EM_EXPRESS_BASIC

How to manage the SQL Server error log

Reinitializing SQL Server error logs

 

You can use the sp_cycle_errorlog stored procedure to reinitialize the error logs periodically

Increasing the number of SQL Server error logs

You can increase the number of error logs that are maintained for a specific instance of SQL Server. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

(http://support.microsoft.com/kb/196909/ )

 

 

Limiting the size of SQL Server error logs

In SQL Server 2012 and later versions, you can use the following code to set the maximum size of individual error logs:

USE [master];

GO


EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'ErrorLogSizeInKb', REG_DWORD, 5120;

GO

[INS-41112] Specified network interface doesnt maintain connectivity across cluster nodes

INFO: Validating for no private interface provided
INFO: Flag oracle.install.crs.allowSingleNIC=false
SEVERE: [FATAL] [INS-41112] Specified network interface doesnt maintain connectivity across cluster nodes.
   CAUSE: Installer has detected that network interface eth0 does not maintain connectivity on all cluster nodes.
   ACTION: Ensure that the chosen interface has been configured across all cluster nodes.
SEVERE: [FATAL] [INS-41112] Specified network interface doesnt maintain connectivity across cluster nodes.
   CAUSE: Installer has detected that network interface eth1 does not maintain connectivity on all cluster nodes.
   ACTION: Ensure that the chosen interface has been configured across all cluster nodes.
INFO: Advice is ABORT



[oracle@rac1 grid]$ ./runcluvfy.sh comp nodecon -n rac1,rac2 -verbose

Verifying node connectivity

Checking node connectivity...

Checking hosts config file...
  Node Name                             Status
  ------------------------------------  ------------------------
  rac2                                  passed
  rac1                                  passed

Verification of the hosts config file successful


Interface information for node "rac2"
 Name   IP Address      Subnet          Gateway         Def. Gateway    HW Address        MTU
 ------ --------------- --------------- --------------- --------------- ----------------- ------
 eth0   192.168.6.12    192.168.6.0     0.0.0.0         192.168.6.2     00:0C:29:03:F2:84 1500
 eth1   192.168.9.12    192.168.9.0     0.0.0.0         192.168.6.2     00:0C:29:03:F2:8E 1500


Interface information for node "rac1"
 Name   IP Address      Subnet          Gateway         Def. Gateway    HW Address        MTU
 ------ --------------- --------------- --------------- --------------- ----------------- ------
 eth0   192.168.6.11    192.168.6.0     0.0.0.0         192.168.6.2     00:0C:29:8A:36:71 1500
 eth1   192.168.9.11    192.168.9.0     0.0.0.0         192.168.6.2     00:0C:29:8A:36:7B 1500


Check: Node connectivity of subnet "192.168.6.0"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  rac2[192.168.6.12]              rac1[192.168.6.11]              yes
Result: Node connectivity passed for subnet "192.168.6.0" with node(s) rac2,rac1


Check: TCP connectivity of subnet "192.168.6.0"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  rac1:192.168.6.11               rac2:192.168.6.12               passed
Result: TCP connectivity check passed for subnet "192.168.6.0"


Check: Node connectivity of subnet "192.168.9.0"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  rac2[192.168.9.12]              rac1[192.168.9.11]              yes
Result: Node connectivity passed for subnet "192.168.9.0" with node(s) rac2,rac1


Check: TCP connectivity of subnet "192.168.9.0"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  rac1:192.168.9.11               rac2:192.168.9.12               passed
Result: TCP connectivity check passed for subnet "192.168.9.0"


Interfaces found on subnet "192.168.6.0" that are likely candidates for VIP are:
rac2 eth0:192.168.6.12
rac1 eth0:192.168.6.11

Interfaces found on subnet "192.168.9.0" that are likely candidates for a private interconnect are:
rac2 eth1:192.168.9.12
rac1 eth1:192.168.9.11
Checking subnet mask consistency...
Subnet mask consistency check passed for subnet "192.168.6.0".
Subnet mask consistency check passed for subnet "192.168.9.0".
Subnet mask consistency check passed.

Result: Node connectivity check passed


Verification of node connectivity was successful.
[oracle@rac1 grid]$

There is no error message for the connectivity check.
Issue disappeared after rebooting both RAC nodes.