Primary Database Creation¶

Login with iDrac/iLO console

1. Create DB¶

1.1 Start the DB creation program

Execute dbca

dbca

1.2 Select Create a database, and click Next

1.3 Select Advanced configuration, and click Next

1.4 Select the type of database (make sure database type is Oracle Single Instance database), and select Custom Database ,then click Next

1.5 Setup Global database name and same as SID, and unchecked Create as Container database, then click Next

1.6 Click Use following for the database storage attributes and modify Database files location, then click Next

Example:

Database files location → /oradata/{DB_UNIQUE_NAME}

Database files location → /oradata/ZSUTF8Q reference your DB path

1.7 Click Next

1.8 Click Next

1.9 Uncheck below items, and click Next

1.10 Check Memory tab (or you can reference your original DB)

Share pool size must over or equal 604 MB

If shared pool size to small, you will get this error

Check Sizing tab (or you can reference your original DB)

Check Character sets tab (or you can reference your original DB)

Check Connection mode tab (or you can reference your original DB), and click Next

1.11 Unchecked Configure Enterprise Manager (EM) database express, and click Next

1.12 Setup sys/system password, and click Next

1.13 Config Parameters and storage locations

Click All initialization Parameters...

Checked Show advanced parameters, and click Name to sort

Modify below parameter, and click Include in spfile

Set audit_trail to db, extended

Set pga_aggregate_limit to 0

Set sga_max_size to reference your DB setting (MB)

Set sga_target to 0

Set sql92_security to FALSE

Set streams_pool_size to 128

Then click Close

Click Customize Storage Locations

Click Control Files, and edit control files and remember to click Apply

after finishing, we recommend check control files setting again

If you want to modify control file parameter, please see Options and remember click Apply

Edit redo log

Click Next

1.14 Click Finish to start DB creation progress

1.15 Click Close to finish DB create

2. DB config¶

2.1 Check DB version

su - oracle
export ORACLE_SID=ZSUTF8Q
sqlplus / as sysdba

select patch_id,patch_uid,patch_type,to_char(action_time,'yyyy/mm/dd hh24:mi:ss')action_time,source_version,target_version,description,logfile from dba_registry_sqlpatch;

exit

2.2 Create listener

Create listener file

vim /oracle/ora19c/network/admin/listener.ora

add below content:

LISTENER_SFCSDQ =
    (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.21.90)(PORT = 1521))
    )
SID_LIST_LISTENER_SFCSDQ =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ZSUTF8Q)
      (ORACLE_HOME = /oracle/ora19c)
      (SID_NAME = ZSUTF8Q)
    )
  )
SECURE_REGISTER_LISTENER_SFCSDQ = (TCP)

2.3 Start listener

lsnrctl start LISTENER_SFCSDQ

2.4 Disable password expiration and fail log-on in DEFAULT profile

export ORACLE_SID=ZSUTF8Q
sqlplus / as sysdba

ALTER PROFILE "DEFAULT" LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL;

Check DEFAULT profile

COLUMN PROFILE FORMAT A8;
COLUMN RESOURCE_NMAE FORMAT A16;
COLUMN RESOURCE_TYPE FORMAT A15;
COLUMN LIMIT FORMAT A10;
select profile,resource_name,resource_type,limit from dba_profiles where profile='DEFAULT';

2.5 Disable parameter

Disable SQL tuning advisor

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

Check again

col client_name for a40
col status for a10
select client_name, status from dba_autotask_client order by client_name;
exit

[Only for 19.7]Disable ASTS (Automatic SQL Tuning Set)

Begin
DBMS_Auto_Task_Admin.Disable(
Client_Name => 'Auto STS Capture Task',
Operation => NULL,
Window_name => NULL);
End;
/

2.6 Add parameter in sqlnet.ora

cd $ORACLE_HOME/network/admin/
vim sqlnet.ora
cat sqlnet.ora

add below content:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

2.7 Enable archive log

We will enable archive log mode in PRD DB, QAS/DEV can disable archive log mode

mkdir /oradata/{SID}/archive

alter system set log_archive_dest_1='location=/oradata/{SID}/archive/' scope=both;
alter system set log_archive_format='arch_{SID}_%t_%s_%r.log' scope=spfile;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list
alter system switch logfile;

2.8 Disable control file auto backup

Disable control file auto backup in PRD/STB (If you have many DBs, you should setup on every DBs)

export ORACLE_SID=ZSUTF8Q
rman target / nocatalog

CONFIGURE CONTROLFILE AUTOBACKUP OFF;
exit

2.9 Access control list (ACL) and mail testing

Create ACL

sqlplus / as sysdba

begin
dbms_network_acl_admin.create_acl (
acl             => 'db_call_smtp.xml',
description     => 'For DB to call smtp service',
principal       => 'CONNECT',
is_grant        => TRUE,
privilege       => 'connect',
start_date      => null,
end_date        => null);
end;
/

Grant ACL permission to PUBLIC

begin
dbms_network_acl_admin.add_privilege (
acl  => 'db_call_smtp.xml',
principal => 'PUBLIC',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null);
end;
/

Setting allow rule for ACL and save change

begin
dbms_network_acl_admin.assign_acl (
acl => 'db_call_smtp.xml',
host => '*',
lower_port => null,
upper_port => null);
end;
/

commit;

Confirm the ACL setup is OK

select host, acl from dba_network_acls where ACL='/sys/acls/db_call_smtp.xml';

ACL testing

SET SERVEROUTPUT ON SIZE 40000
DECLARE
  req   UTL_HTTP.REQ;
  resp  UTL_HTTP.RESP;
  value VARCHAR2(1024);
  i number:=10;
BEGIN
  req := UTL_HTTP.BEGIN_REQUEST('http://seweb.wistron.com:8080/admweb_dad/SEWEB');
  UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
  resp := UTL_HTTP.GET_RESPONSE(req);
  FOR i IN 1..10
  LOOP
    UTL_HTTP.READ_LINE(resp, value, TRUE);
    DBMS_OUTPUT.PUT_LINE(value);
  END LOOP;
  UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(resp);
END;
/

Mail test (You must apply mail relay before this test)

Execute the SQL script to testing (Remember to change recipient and smtp server)

DECLARE
  v_From      VARCHAR2(80) := 'test_account@wistron.com';
  v_Recipient VARCHAR2(80) := 'seven_qin@wistron.com';
  v_Subject   VARCHAR2(80) := 'send mail test';
  v_Mail_Host VARCHAR2(30) := 'sendmailserver.wistron.com';
  v_Mail_Conn utl_smtp.Connection;
  crlf        VARCHAR2(2)  := chr(13)||chr(10);
BEGIN
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
utl_smtp.Data(v_Mail_Conn,
   'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
   'From: '   || v_From || crlf ||
   'Subject: '|| v_Subject || crlf ||
   'To: '     || v_Recipient || crlf ||
   crlf ||
   'some message text aa'|| crlf ||     -- Message body
   'more message text bb'|| crlf
);
utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
   raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;
/

2.10 Huge-page configuration

Refer to: [Oracle Hugepage Configuration](https://wzs-yum.wistron.com:8000/Database/2.Oracle/2.2.maintain/Hugepages-Config/)