SQL> show user
USER is "SCOTT"
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION
*****
as sys or system
grant below to scott !!
GRANT SELECT ON v_$session TO scott ;
GRANT SELECT ON v_$sql_plan_statistics_all TO scott ;
GRANT SELECT ON v_$sql_plan TO scott ;
GRANT SELECT ON v_$sql TO scott ;
===
select /*+ domtest */ count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 45sygvgu8ccnz, child number 0
-------------------------------------
select /*+ domtest */ count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 273 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49999 | 1464K| 273 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
19 rows selected.
Sunday, 17 December 2023
PLAN_TABLE_OUTPUT User has no SELECT privilege on V$SESSION
Tuesday, 12 December 2023
How to Enable Oracle Change Tracking (BCT)?
How to Enable Oracle Change Tracking (BCT)?
===
SHOW PARAMETER DB_CREATE_FILE_DEST File will create here
--To Enable :
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING ;
--To check
col filename for a70
select filename,status from v$block_change_tracking;
====
--to disable:
alter database disable block change tracking;
--To check
col filename for a70
select filename,status from v$block_change_tracking;
or
set linesize 400
col FILENAME for a70
col NAME for a15
col BCT_STATUS for a15
col MB for 99999.99
select inst_id, dbid, name, db_unique_name, open_mode, log_mode, flashback_on, switchover_status, database_role,CONTROLFILE_TYPE,filename, BCT_STATUS, bytes/1024/1024 MB
from (select inst_id, dbid, name, db_unique_name, open_mode, log_mode, flashback_on, switchover_status, database_role,CONTROLFILE_TYPE from gv$database),
(select filename, status BCT_STATUS, bytes from v$block_change_tracking)
;
=============================
To enable !!!!
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> Select filename,status from V$block_change_tracking;
FILENAME
--------------------------------------------------------------------------------
STATUS
----------
DISABLED
SQL> SHOW PARAMETER DB_CREATE_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DATA01
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING ;
Database altered.
SQL> col filename for a70
select filename,status from v$block_change_tracking;SQL>
FILENAME
----------------------------------------------------------------------
STATUS
----------
+DATA01/T24TRMPC_LON3A/CHANGETRACKING/ctf.634.1212479513
ENABLED
Friday, 1 December 2023
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
[oracle@wcp12cr2 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Fri Dec 1 05:41:46 2023 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01139: RESETLOGS option only valid after an incomplete database recovery <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< https://anuj-singh.blogspot.com/2021/10/rman-restore-database.html Check the health of datafile !!! :) set linesize 300 pagesize 100 col inst_id for 9999999 heading 'Instance #' col file_nr for 9999999 heading 'File #' col file_name for A70 heading 'File name' col checkpoint_change_nr for 99999999999999 heading 'Checkpoint #' col checkpoint_change_time for A20 heading 'Checkpoint time' col last_change_nr for 99999999999999 heading 'Last change #' col SCNStatus for a15 SELECT fe.inst_id, fe.CON_ID, ---- for >12c fe.fenum file_nr, fn.fnnam file_name, TO_NUMBER (fe.fecps) checkpoint_change_nr, fe.fecpt checkpoint_change_time, fe.fests last_change_nr, NVL2(fe.fecps, '<-good ----------------------------------------------------------------------="" --------------------="" ----------------="" ---------------="" ----------="" --------="" -------="" ----="" -="" 03:27:07="" 05:41:46="" 0="" 10="" 11="" 12.1.0.1.0="" 12="" 12c="" 13="" 18="" 1982="" 1="" 2013="" 2023="" 2270166="" 2="" 3="" 4="" 5="" 64bit="" 65535="" 6="" 7="" 8="" 9="" advanced="" all="" analytics="" and="" application="" as="" below="" bitand="" by="" c="" change="" checkpoint="" con_id="" connected="" copyright="" database="" db="" dec="" decode="" ecover="" edition="" enterprise="" fe.fedup="" fe.fefnh="fn.fnnum" fe.fenum="" fe.fepax="0)" fe.festa="" fe.fetsn="" fe="" file="" fn.fnflg="" fn.fnfno="fe.fenum" fn.fnnam="" fn.fntyp="4" fn="" fri="" from="" ias_opss.dbf="" iasactivities.dbf="" iasjive.dbf="" iaswebcenter.dbf="" iau.dbf="" instance="" is="" kccfe="" kccfn="" last="" lus:="" max="" mds.dbf="" name="" not="" null="" ocs.dbf="" olap="" on="" online="" ood="" option="" options="" or="" oracle.="" oracle="" oradata="" orcl="" order="" partitioning="" production="" real="" recover="" release="" reserved.="" rights="" rows="" scn="" scnstatus="" selected.="" sql="" sqlplus="" status="" svctbl.dbf="" sysaux01.dbf="" sysdba="" system01.dbf="" system="" testing="" the="" this="" time="" to:="" to="" try="" undotbs01.dbf="" users01.dbf="" wcp12cr2="" webcenter_portlet.dbf="" where="" with="" x=""> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01139: RESETLOGS option only valid after an incomplete database recovery SQL> recover database until cancel; Media recovery complete. SQL> alter database open resetlogs; Database altered.
Thursday, 23 November 2023
Oracle Schema Size
Oracle schema size ...
set linesize 200 pagesize 200 timing on time on col Owner for a28 col "HUMAN_READABLE" for a14 col "size" for 9999999999999 col "size GB" for 999999.99 select obj.owner "Owner" --, obj_cnt "Objects",decode(seg_size, NULL, 0, seg_size) "size" ,decode(seg_size, NULL, 0, seg_size)/1024/1024/1024 "size GB" ,dbms_xplan.FORMAT_SIZE(decode(seg_size, NULL, 0, seg_size)) "HUMAN_READABLE" from ( select /*+ parallel(8) */ owner, count(*) obj_cnt from dba_objects group by owner) obj, ( select owner, ceil(sum(bytes)) seg_size from dba_segments group by owner) segment where obj.owner = segment.owner(+) order by 2 desc --order by 3 desc, 2 desc, 1 ;
DEFINE schema_name = 'XXXX'
set linesize 300
col schema_size_gb for 9999999.99
SELECT sum(sizegb) schema_size_gb FROM (
-- tablesize !!!
--SELECT * FROM (
SELECT
owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024/1024 AS sizegb,tablespace_name, extents, initial_extent,
ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_MB
FROM (
-- Tables
SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,segment_name AS table_name, bytes,tablespace_name, extents, initial_extent
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
-- Indexes
SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type,i.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
--LOB Segments
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type,l.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
-- LOB Indexes
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type,l.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX'
)
WHERE owner in UPPER('&schema_name')
)
--WHERE total_table_MB > 10
--ORDER BY total_table_MB DESC, MB DESC
/
====
with count
set linesize 100 pagesize 200
col Owner for a28
select obj.owner "Owner", obj_cnt "Objects",decode(seg_size, NULL, 0, seg_size) "size GB"
from ( select owner, count(*) obj_cnt from dba_objects group by owner) obj,
( select owner, ceil(sum(bytes)/1024/1024/1024) seg_size from dba_segments group by owner) segment
where obj.owner = segment.owner(+)
order by 3 desc, 2 desc, 1;
col TABLE_NAME for a30
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF meg ON REPORT
select * from (
SELECT owner,
table_name,
trunc(SUM(bytes) / 1024 / 1024) meg
FROM (SELECT segment_name table_name,
owner,
bytes
FROM dba_segments
WHERE segment_type = 'TABLE'
UNION ALL
SELECT i.table_name,
i.owner,
s.bytes
FROM dba_indexes i,
dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name,
l.owner,
s.bytes
FROM dba_lobs l,
dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name,
l.owner,
s.bytes
FROM dba_lobs l,
dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
WHERE 1=1
-- owner NOT IN ('SYS', 'SYSTEM')
and owner IN ('xx')
GROUP BY table_name, owner
HAVING SUM(bytes) / 1024 / 1024 > 0 /* Ignore tables lower than 30 MB */
)
where 1=1
and meg!=0
ORDER BY meg DESC
/
Monday, 13 November 2023
Licensing Data Recovery Environments ( Oracle Licensing DataGaurd )
Oracle Licensing DataGaurd From Web Licensing Data Recovery Environments In today's data and information intensive economy, businesses need continuous access to mission-critical information. IT departments must not only manage the rapid growth of business information but they must also keep this information available and protected. That's why every business has data recovery and business continuance plans. This document will help you in understanding how to license Oracle programs in such environments. Data Recovery environments are usually deployed using the following two methods: a) Deploying a clustered environment such as Failover or b) Copying, Synchronizing or Mirroring of the data and/or program files (such as Physical DB files, Binaries, Executables). Data Recovery using Clustered Environments (Failover) The failover data recovery method is an example of a clustered deployment, where multiple nodes/servers have access to one Single Storage/SAN. In such cases your license for the programs listed on the US Oracle Technology Price List (http://www.oracle.com/corporate/pricing/pricelists.html) is eligible for the 10-day rule, which includes the right to run the licensed program(s) on an unlicensed spare computer in a failover environment for up to a total of ten separate 24-hour periods in any given calendar year (for example, if a failover node is down for two hours on Tuesday and three hours on Friday, it counts as two 24-hour periods). The above right only applies when a number of physical or logical machines as defined in Oracle's Partitioning Policy (detailed in https://www.oracle.com/assets/partitioning-070609.pdf) are arranged in a cluster and share one logical disk array located in a single data center. When the primary node fails, the failover node acts as the primary node. Once the primary node is repaired, you must either switch back or designate that repaired server as the failover node. Once the failover period has exceeded ten 24-hour periods, the failover node must be licensed. In addition, only one failover node per clustered environment is at no charge even if multiple nodes are configured as failover. Downtime for maintenance purposes counts towards the ten separate 24-hour periods limitation. When licensing options on a failover environment, the options must match the number of licenses of the associated database. Additionally,when licensing by Named User Plus, the user minimums are waived on one failover node only. Any use beyond the right granted in this section must be licensed separately. In a failover environment, the same license metric must be used for the production and failover nodes when licensing a given clustered configuration. Data Recovery Environments using Copying, Synchronizing or Mirroring Standby and Remote Mirroring are commonly used terms to describe these methods of deploying Data Recovery environments. In these Data Recovery deployments, the data, and optionally the Oracle binaries, are copied to another storage device. In these Data Recovery deployments all Oracle programs that are installed and/or running must be licensed per standard policies documented in the Oracle Agreement. This includes installing Oracle programs on the DR server(s) to test the DR scenario. Licensing metrics and program options on Production and Data Recovery servers must match with two exceptions: 1) Real Application Clusters (RAC) - Oracle RAC does not need to be licensed on the Data Recovery server unless used on the Data Recovery server; 2) For Production servers licensed using one of the Oracle Data Management Cloud Services listed in this document (http://www.oracle.com/us/corporate/contracts/paas-iaas-universal-credits-3940775.pdf), only program options in use on the Production server must be licensed on the Data Recovery server. This document is for educational purposes only and provides guidelines regarding Oracle's Data Recovery policies in effect as of July 28th, 2020. It may not be incorporated into any contract and does not constitute a contract or a commitment to any specific terms. Policies and this document are subject to change without notice. This document may not be reproduced in any manner without the express written permission of Oracle Corporation. Testing For the purpose of testing physical copies of backups, your license for the Oracle Database includes the right to run the database on an unlicensed computer for up to four times, not exceeding 2 days per testing, in any given calendar year. The aforementioned right does not cover any other data recovery method - such as remote mirroring - where the Oracle program binary files are copied or synchronized.
Saturday, 11 November 2023
Oracle 26ai Oracle 23c SET ERRORDETAILS on / off
SET ERRORDETAILS on SQL> SET ERRORDETAILS on SQL> conn /@FREEPDB1 as sysdba ERROR: ORA-01017: invalid credential or not authorized; logon denied Help: https://docs.oracle.com/error-help/db/ora-01017/ <<<< to trun off this SQL> SET ERRORDETAILS off SQL> conn /@FREEPDB1 as sysdba ERROR: ORA-01017: invalid credential or not authorized; logon denied Warning: You are no longer connected to ORACLE. SQL> ****** https://docs.oracle.com/en/database/oracle/oracle-database/23/sqpug/sqlplus-users-guide-and-reference.pdf pageno 266 !!!! In the following example, the variable ERRORDETAILS is set to ON: SET ERRORDETAILS ON SELECT * FROM EMP; SP2-0640: Not connected Help: https://docs.oracle.com/error-help/db/sp2-0640/
[oracle@localhost admin]$ export TWO_TASK='' [oracle@localhost admin]$ echo $TWO_TASK [oracle@localhost admin]$ sqlplus / as sysdba SQL*Plus: Release 23.0.0.0.0 - Production on Wed Sep 18 09:39:00 2024 Version 23.3.0.23.09 Copyright (c) 1982, 2023, Oracle. All rights reserved. Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.3.0.23.09 SQL>
Wednesday, 11 October 2023
Dbca Delete database
Dbca Delete database ..
Delete database using DBCA silent mode
first!!!!
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 11 05:25:51 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> shutdown immediate ; <<<<< shutdown !
Database closed.
Database dismounted.
ORACLE instance shut down.
./dbca -silent -deleteDatabase -sourceDB ODB19C -sysDBAUserName sys -sysDBAPassword 'sys'
=======
pwd
/u01/app/oracle/product/19.0.0/dbhome_1/bin
./dbca -silent -deleteDatabase -sourceDB ODB19C -sysDBAUserName sys -sysDBAPassword 'sys'
[WARNING] [DBT-11503] The instance (ODB19C) is not running on the local node. This may result in partial delete of Oracle database.
CAUSE: A locally running instance is required for complete deletion of Oracle database instance and database files.
ACTION: Specify a locally running database, or execute DBCA on a node where the database instance is running.
[WARNING] [DBT-19202] The Database Configuration Assistant will delete the Oracle instances and datafiles for your database. All information in the database will be destroyed.
Prepare for db operation
32% complete
Connecting to database
35% complete
39% complete
42% complete
45% complete
48% complete
52% complete
65% complete
Updating network configuration files
68% complete
Deleting instance and datafiles
84% complete
100% complete
Database deletion completed.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/ODB19C/ODB19C0.log" for further details.
=====
Another database is running on same home !!
ps -ef|grep -i smon
oracle 3843427 1 0 Oct10 ? 00:00:01 ora_smon_test
Tuesday, 10 October 2023
duplicate Database Using Dbca Command In Oracle 19c
Duplicate Database Using Dbca Command In Oracle 19c
syntax
./dbca -silent -createDuplicateDB -gdbName {CLONE_DB_NAME} -primaryDBConnectionString -sid {CLONE_DB_SID} -databaseConfigType SINGLE -initParams db_unique_name={CLONE_DB_NAME} -sysPassword {PRIMARY_DB_SYS_PWD} -datafileDestination {CLONE_DATAFILE_LOC}
ww hostname=oralal {PRIMARY_DB_SYS_PWD}
./dbca -silent -createDuplicateDB -gdbName ODB19C -primaryDBConnectionString oralal:1521/test -sid ODB19C -databaseConfigType SINGLE -initParams db_unique_name=ODB19C -sysPassword 'sys' -datafileDestination /u02/app/oracle/DB19C
Prepare for db operation
22% complete
Listener config step
44% complete
Auxiliary instance creation
67% complete
RMAN duplicate
89% complete
Post duplicate database operations
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/ODB19C/ODB19C.log" for further details.
cat /u01/app/oracle/cfgtoollogs/dbca/ODB19C/ODB19C.log|more
[ 2023-10-10 11:49:26.253 EDT ] Prepare for db operation
DBCA_PROGRESS : 22%
[ 2023-10-10 11:49:26.389 EDT ] Listener config step
DBCA_PROGRESS : 44%
[ 2023-10-10 11:49:26.556 EDT ] Auxiliary instance creation
DBCA_PROGRESS : 67%
[ 2023-10-10 11:49:46.987 EDT ] RMAN duplicate
DBCA_PROGRESS : 89%
[ 2023-10-10 11:52:27.750 EDT ] Post duplicate database operations
DBCA_PROGRESS : 100%
[ 2023-10-10 11:55:10.425 EDT ]
====
test:/u01/app/oracle/product/19.0.0/dbhome_1:Y >>>>>>> from
ODB19C:/u01/app/oracle/product/19.0.0/dbhome_1:N <<<<<< to
ps -ef|grep -i smon
oracle 3843427 1 0 11:35 ? 00:00:00 ora_smon_test
oracle 3850518 1 0 11:55 ? 00:00:00 ora_smon_ODB19C <<<< database created
Monday, 2 October 2023
Oracle Database 19c Installation Software Only
curl -o oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
0 0 0 0 0 0 0 0 --:--:-- 0:01:06 --:--:-- 0
100 18204 100 18204 0 0 85 0 0:03:34 0:03:33 0:00:01 4954
[root@oracent19c ~]# ls -ltr
total 2988024
-rw-------. 1 root root 1686 Aug 16 13:34 anaconda-ks.cfg
-rw-r--r--. 1 root root 1734 Aug 16 13:35 initial-setup-ks.cfg
drwxr-xr-x. 2 root root 6 Aug 16 13:36 Videos
drwxr-xr-x. 2 root root 6 Aug 16 13:36 Templates
drwxr-xr-x. 2 root root 6 Aug 16 13:36 Public
drwxr-xr-x. 2 root root 6 Aug 16 13:36 Pictures
drwxr-xr-x. 2 root root 6 Aug 16 13:36 Music
drwxr-xr-x. 2 root root 6 Aug 16 13:36 Downloads
drwxr-xr-x. 2 root root 6 Aug 16 13:36 Documents
drwxr-xr-x. 2 root root 40 Aug 16 13:41 Desktop
-rw-r--r--. 1 root root 3059705302 Aug 16 16:02 V982063-01.zip
-rw-r--r--. 1 root root 18204 Aug 16 16:13 oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
Install rpm [root@oracent19c ~]# yum -y localinstall oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
Loaded plugins: fastestmirror, langpacks Examining oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm: oracle-database-preinstall-19c-1.0-1.el7.x86_64 Marking oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package oracle-database-preinstall-19c.x86_64 0:1.0-1.el7 will be installed --> Processing Dependency: compat-libcap1 for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64 Loading mirror speeds from cached hostfile * base: mirrors.coreix.net * extras: mirrors.coreix.net * updates: mirrors.coreix.net --> Processing Dependency: compat-libstdc++-33 for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64 --> Processing Dependency: glibc-devel for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64 --> Processing Dependency: ksh for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64 --> Processing Dependency: libaio-devel for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64 --> Processing Dependency: libstdc++-devel for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64 --> Running transaction check ---> Package compat-libcap1.x86_64 0:1.10-7.el7 will be installed ---> Package compat-libstdc++-33.x86_64 0:3.2.3-72.el7 will be installed ---> Package glibc-devel.x86_64 0:2.17-326.el7_9 will be installed --> Processing Dependency: glibc-headers = 2.17-326.el7_9 for package: glibc-devel-2.17-326.el7_9.x86_64 --> Processing Dependency: glibc-headers for package: glibc-devel-2.17-326.el7_9.x86_64 ---> Package ksh.x86_64 0:20120801-144.el7_9 will be installed ---> Package libaio-devel.x86_64 0:0.3.109-13.el7 will be installed ---> Package libstdc++-devel.x86_64 0:4.8.5-44.el7 will be installed --> Running transaction check ---> Package glibc-headers.x86_64 0:2.17-326.el7_9 will be installed --> Processing Dependency: kernel-headers >= 2.2.1 for package: glibc-headers-2.17-326.el7_9.x86_64 --> Processing Dependency: kernel-headers for package: glibc-headers-2.17-326.el7_9.x86_64 --> Running transaction check ---> Package kernel-headers.x86_64 0:3.10.0-1160.95.1.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================================================================================================================================================== Package Arch Version Repository Size ============================================================================================================================================================================================================== Installing: oracle-database-preinstall-19c x86_64 1.0-1.el7 /oracle-database-preinstall-19c-1.0-1.el7.x86_64 55 k Installing for dependencies: compat-libcap1 x86_64 1.10-7.el7 base 19 k compat-libstdc++-33 x86_64 3.2.3-72.el7 base 191 k glibc-devel x86_64 2.17-326.el7_9 updates 1.1 M glibc-headers x86_64 2.17-326.el7_9 updates 691 k kernel-headers x86_64 3.10.0-1160.95.1.el7 updates 9.1 M ksh x86_64 20120801-144.el7_9 updates 885 k libaio-devel x86_64 0.3.109-13.el7 base 13 k libstdc++-devel x86_64 4.8.5-44.el7 base 1.5 M Transaction Summary ============================================================================================================================================================================================================== Install 1 Package (+8 Dependent packages) Total size: 13 M Total download size: 13 M Installed size: 19 M Downloading packages: (1/8): compat-libcap1-1.10-7.el7.x86_64.rpm | 19 kB 00:00:00 (2/8): compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm | 191 kB 00:00:00 (3/8): libaio-devel-0.3.109-13.el7.x86_64.rpm | 13 kB 00:00:00 (4/8): glibc-headers-2.17-326.el7_9.x86_64.rpm | 691 kB 00:00:00 (5/8): libstdc++-devel-4.8.5-44.el7.x86_64.rpm | 1.5 MB 00:00:00 (6/8): kernel-headers-3.10.0-1160.95.1.el7.x86_64.rpm | 9.1 MB 00:00:02 (7/8): glibc-devel-2.17-326.el7_9.x86_64.rpm | 1.1 MB 00:00:15 (8/8): ksh-20120801-144.el7_9.x86_64.rpm | 885 kB 00:00:15 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 867 kB/s | 13 MB 00:00:15 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : libaio-devel-0.3.109-13.el7.x86_64 1/9 Installing : compat-libcap1-1.10-7.el7.x86_64 2/9 Installing : compat-libstdc++-33-3.2.3-72.el7.x86_64 3/9 Installing : ksh-20120801-144.el7_9.x86_64 4/9 Installing : libstdc++-devel-4.8.5-44.el7.x86_64 5/9 Installing : kernel-headers-3.10.0-1160.95.1.el7.x86_64 6/9 Installing : glibc-headers-2.17-326.el7_9.x86_64 7/9 Installing : glibc-devel-2.17-326.el7_9.x86_64 8/9 Installing : oracle-database-preinstall-19c-1.0-1.el7.x86_64 9/9 Verifying : kernel-headers-3.10.0-1160.95.1.el7.x86_64 1/9 Verifying : libstdc++-devel-4.8.5-44.el7.x86_64 2/9 Verifying : ksh-20120801-144.el7_9.x86_64 3/9 Verifying : glibc-devel-2.17-326.el7_9.x86_64 4/9 Verifying : compat-libstdc++-33-3.2.3-72.el7.x86_64 5/9 Verifying : glibc-headers-2.17-326.el7_9.x86_64 6/9 Verifying : compat-libcap1-1.10-7.el7.x86_64 7/9 Verifying : libaio-devel-0.3.109-13.el7.x86_64 8/9 Verifying : oracle-database-preinstall-19c-1.0-1.el7.x86_64 9/9 Installed: oracle-database-preinstall-19c.x86_64 0:1.0-1.el7 Dependency Installed: compat-libcap1.x86_64 0:1.10-7.el7 compat-libstdc++-33.x86_64 0:3.2.3-72.el7 glibc-devel.x86_64 0:2.17-326.el7_9 glibc-headers.x86_64 0:2.17-326.el7_9 kernel-headers.x86_64 0:3.10.0-1160.95.1.el7 ksh.x86_64 0:20120801-144.el7_9 libaio-devel.x86_64 0:0.3.109-13.el7 libstdc++-devel.x86_64 0:4.8.5-44.el7 Complete! [root@oracent19c ~]# yum install gcc-c++ yum install glibc-devel.i686
==
mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1 chown -R oracle:oinstall /u01 chmod -R 775 /u01
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOSTNAME=ora19 export ORA_INVENTORY=/u01/app/oraInventory export TMP=/tmp export TMPDIR=\$TMP first
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOSTNAME=ora19 export ORA_INVENTORY=/u01/app/oraInventory export TMP=/tmp export TMPDIR=\$TMPcd $ORACLE_HOME <<<<< unzip -oq /path_to_software/LINUX.X64_193000_db_home.zip ./runInstaller -ignorePrereq -waitforcompletion -silent \ -responseFile ${ORACLE_HOME}/install/response/db_install.rsp \ oracle.install.option=INSTALL_DB_SWONLY \ ORACLE_HOSTNAME=${ORACLE_HOSTNAME} \ UNIX_GROUP_NAME=oinstall \ INVENTORY_LOCATION=${ORA_INVENTORY} \ SELECTED_LANGUAGES=en,en_GB \ ORACLE_HOME=${ORACLE_HOME} \ ORACLE_BASE=${ORACLE_BASE} \ oracle.install.db.InstallEdition=EE \ oracle.install.db.OSDBA_GROUP=dba \ oracle.install.db.OSBACKUPDBA_GROUP=dba \ oracle.install.db.OSDGDBA_GROUP=dba \ oracle.install.db.OSKMDBA_GROUP=dba \ oracle.install.db.OSRACDBA_GROUP=dba \ SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \ DECLINE_SECURITY_UPDATES=true Launching Oracle Database Setup Wizard... [WARNING] [INS-13014] Target environment does not meet some optional requirements. CAUSE: Some of the optional prerequisites are not met. See logs for details. installActions2023-10-02_08-18-18AM.log ACTION: Identify the list of failed prerequisite checks from the log: installActions2023-10-02_08-18-18AM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually. The response file for this session can be found at: /u01/app/oracle/product/19.1/db_1/install/response/db_2023-10-02_08-18-18AM.rsp You can find the log of this install session at: /tmp/InstallActions2023-10-02_08-18-18AM/installActions2023-10-02_08-18-18AM.log As a root user, execute the following script(s): 1. /u01/app/oraInventory/orainstRoot.sh 2. /u01/app/oracle/product/19.1/db_1/root.sh Execute /u01/app/oraInventory/orainstRoot.sh on the following nodes: [oem2] Execute /u01/app/oracle/product/19.1/db_1/root.sh on the following nodes: [oem2] [root@oem2 oracle]# /u01/app/oraInventory/orainstRoot.sh Changing permissions of /u01/app/oraInventory. Adding read,write permissions for group. Removing read,write,execute permissions for world. Changing groupname of /u01/app/oraInventory to oinstall. The execution of the script is complete. [root@oem2 oracle]# /u01/app/oracle/product/19.1/db_1/root.sh Check /u01/app/oracle/product/19.1/db_1/install/root_oem2_2023-10-02_08-19-29-677094342.log for the output of root script [root@oem2 oracle]#
Friday, 29 September 2023
Oracle patch Info
Oracle patch Info
from web
list-ohpatches.sh
#!/bin/bash
# Fred Denis -- fred.denis3@gmail.com -- May 21st 2021
#
# Show nice tables of the installed and/or missing patches for some GI/DB Oracle Homes
#
set -o pipefail
#
# Variables
#
TS="date "+%Y-%m-%d_%H%M%S"" # A timestamp for a nice outut in a logfile
GREP="." # What we grep -- default is everything
UNGREP="nothing_to_ungrep_unless_v_option_is_used$$" # What we don't grep (grep -v) -- default is nothing
COLS=$(tput cols) # Size of the screen
ORACLE="oracle" # User to run opatch lspatches if script ran as root
#
# Cleanup on exit -- this will be executed on normal exit as well as if the script is killed
# The place to cleanup things / send emails whatever happens to the script
#
cleanup() {
err=$?
if [[ -s "${TEMP2}" ]]; then
if [[ "${err}" == "0" ]]; then # If already an error, no need to check for nb missing patches
# Check for errors
NB_ERR=$(cat "${TEMP2}" | awk '{cpt+=$1} END {print cpt}')
exit "${NB_ERR}"
fi
fi
# Delete tempfiles
rm -f "${TEMP}" "${TEMP2}"
exit ${err}
}
sig_cleanup() {
printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] I have been killed !" >&2
exit 666
}
trap cleanup EXIT
trap sig_cleanup INT TERM QUIT
#
# Usage function
#
usage() {
printf "\n\033[1;37m%-8s\033[m\n" "NAME" ;
cat << END
$(basename $0) - Show nice tables of the installed and/or missing patches for some GI/DB Oracle Homes
END
printf "\n\033[1;37m%-8s\033[m\n" "SYNOPSIS" ;
cat << END
$0 [-g] [-c] [-G] [-v] [-s] [-u] [-h]
END
printf "\n\033[1;37m%-8s\033[m\n" "DESCRIPTION" ;
cat << END
$(basename $0) Based on oratab, show nice tables of the installed and/or missing patches for some GI/DB Oracle Homes
You can then quickly find a missing patch across a RAC system
$(basename $0) will by default check all the nodes of a cluster (based on olsnodes) which requires ASM to be running
and oraenv to be working with the ASM aslias defined in oratab; If you have no ASM alias in oratab,
you may suffer from https://unknowndba.blogspot.com/2019/01/lost-entries-in-oratab-after-gi-122.html
You can specify a comma separated list of host or a file containing one host per line
$(basename $0) by default checks all the homes defined in oratab, you can use --grep/--home and --ungrep/--ignore to limit your home selection (see examples below)
$(basename $0) relies on opatch lspatches which must run as oracle user (and not root); if the script is started as root,
the opatch lspatches commands will be run after su - ${ORACLE} (see -u | --oracleuser for more on this)
END
printf "\n\033[1;37m%-8s\033[m\n" "OPTIONS" ;
cat << END
-g | --groupfile ) A group file containing a list of hosts
-c | --commalist | --hosts ) A comma separated list of hosts
-G | --grep | --oh | --home ) Pattern to grep from /etc/oratab
-v | --ungrep | --ignore ) Pattern to grep -v (ignore) from /etc/oratab
-s | --showhomes | --show ) Just show the homes from oratab resolving the grep/ungrep combinations
-u | --oracleuser ) User to use to run opatch lspatches if the script is started as root, default is ${ORACLE}
-h | --help ) Shows this help
END
printf "\n\033[1;37m%-8s\033[m\n" "EXAMPLES" ;
cat << END
$0 # Analyze and show all the homes of nodes of a cluster
$0 --show # Show the homes from oratab (only show, dont do anything else)
$0 --grep grid # Analyze the grid home
$0 --grep db --ungrep 12 # Only the DB homes but not the 12 ones
$0 --grep db --ungrep 12 --groupfile ~/dbs_group # Same as above on the hosts contained in the ~/dbs_group file
$0 --home db --ignore 12 --hosts exa01,exa06 # Same as above but only on hosts exa02 and exa06
$0 --home db --ignore 12 --hosts exa01,exa06 -u oracle2 # Same as above but started as root; will then su - oracle2 automatically
END
exit 999
}
#
# Options -- Long and Short, options needs to be separa
# Options are comma separated list, options requiring a parameter need to be followed by a ":"
#
SHORT="g:,c:,g:,v:,u:,sh"
LONG="groupfile:,commalist:,hosts:,grep:,oh:,home:,ungrep:,ignore:,oracleuser:,showhomes,help"
# Check if the specified options are good
options=$(getopt -a --longoptions "${LONG}" --options "${SHORT}" -n "$0" -- "$@")
# If not, show the usage and exit
if [[ $? -ne 0 ]]; then
printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] Invalid options provided: $*; use -h for help; cannot continue." >&2
exit 864
fi
#
eval set -- "${options}"
# Option management, not the "shift 2" when an option requires a parameter and "shift" when no parameter needed
while true; do
case "$1" in
-g | --groupfile ) GROUP="$2" ; shift 2 ;;
-c | --commalist | --hosts ) HOSTS="$2" ; shift 2 ;;
-G | --grep | --oh | --home) GREP="$2" ; shift 2 ;;
-v | --ungrep | --ignore ) UNGREP="$2" ; shift 2 ;;
-u | --oracleuser ) ORACLE="$2" ; shift 2 ;;
-s | --showhomes ) SHOW_HOMES="True" ; shift ;;
-h | --help ) usage ; shift ;;
-- ) shift ; break ;;
esac
done
#
# Different OS support
#
OS=$(uname)
case ${OS} in
SunOS)
ORATAB=/var/opt/oracle/oratab
AWK=/usr/bin/gawk ;;
Linux)
ORATAB=/etc/oratab
AWK=`which awk` ;;
HP-UX)
ORATAB=/etc/oratab
AWK=`which awk` ;;
AIX)
ORATAB=/etc/oratab
AWK=`which awk` ;;
*) echo "Unsupported OS, cannot continue."
exit 666 ;;
esac
#
# Options verifications
#
if [[ $(id -u) -eq 0 ]]; then # We are root
su - "${ORACLE}" -c id > /dev/null 2>&1 # Check if we can su as ${ORACLE}
if [ $? -ne 0 ]; then
printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] Script has been executed as root and the user to use to run opatch lspatches is ${ORACLE}; unfortunately we were unable to connect to this user; cannot continue." >&2
exit 122
fi
fi
if [[ ! -f "${ORATAB}" ]]; then
printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] Cannot find ${ORATAB}; cannot continue." >&2
exit 123
fi
if [[ ! -f "${AWK}" ]]; then
printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] Cannot find a modern versin of awk; cannot continue." >&2
exit 124
fi
#
# Show Homes only if -s option specified
#
if [[ "${SHOW_HOMES}" == "True" ]]; then
printf "\n\033[1;37m%-8s\033[m\n\n" "ORACLE_HOMEs in ${ORATAB}:" ;
cat ${ORATAB} | grep -v "^#" | grep -v "^$" | grep -v agent | ${AWK} 'BEGIN {FS=":"} { printf("\t%s\n", $2)}' | grep ${GREP} | grep -v ${UNGREP} | sort | uniq
printf "\n"
exit 0
fi
if [[ -z "${HOSTS}" ]]; then # HOSTS is empty, -c option not provided
if [[ -f "${GROUP}" ]]; then # Group file exists, we make it a comma separated list
HOSTS=$(cat "${GROUP}" | grep -v "^$" | grep -v "^#" | ${AWK} '{printf("%s,", $1)}' | sed s'/,$//')
else # No group file nor hosts lists, lets get the node list from olsnodes
. oraenv <<< $(ps -ef | grep pmon | grep asm | awk '{print $NF}' | sed s'/.*+/+/') > /dev/null 2>&1
if [ $? -ne 0 ]; then
printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] ASM does not seem to be running and/or oraenv not working so we cannot guess the node list; please use -g or -c to specify a node list then and restart." >&2
exit 125
else # ASM env all set, lets get the nodes list automatically
HOSTS=$($(which olsnodes) | ${AWK} '{printf ("%s,",$1)}' | sed s'/,$//')
fi
fi
fi
#
#
#
TEMP2=$(mktemp)
printf "\033[1;36m%s\033[m\n" "$($TS) [INFO] Starting collecting GI/OH patch information"
printf "\033[1;33m%s\033[m\n" "$($TS) [WARNING] It may be a bit slow if you have many nodes and patches as opatch lspatches is slow"
for OH in $(cat ${ORATAB} | grep -v "^#" | grep -v "^$" | grep -v agent | grep ${GREP} | grep -v ${UNGREP} | awk 'BEGIN {FS=":"} {print $2}'| sort | uniq); do
if [[ -f "${OH}/OPatch/opatch" ]] && [[ -x "${OH}/OPatch/opatch" ]]; then
TEMP=$(mktemp)
[[ $(id -u) -eq 0 ]] && chmod 777 "${TEMP}"
printf "\033[1;36m%s\033[m\n" "$($TS) [INFO] Proceeding with ${OH} . . ."
for HOST in $(echo ${HOSTS} | sed 's/,/ /g'); do
if [[ $(id -u) -eq 0 ]]; then # Script started as root, need to sudo as oracle for opatch lspatches
su - "${ORACLE}" << END
ssh -q "${HOST}" "${OH}/OPatch/opatch lspatches" | grep "^[1-9]" | sort | awk -v H="${HOST}" -F ";" '{print H";"\$1";"\$2}' | sed 's/(.*)//g' >> "${TEMP}"
END
else
ssh -q "${HOST}" "${OH}/OPatch/opatch lspatches" | grep "^[1-9]" | sort | awk -v H="${HOST}" -F ";" '{print H";"$1";"$2}' | sed 's/(.*)//g' >> "${TEMP}"
fi
done
"${AWK}" -v hosts="${HOSTS}" -v cols="${COLS}" -v tempfile="${TEMP2}" \
'BEGIN { FS = ";" ;
# some colors
COLOR_BEGIN = "\033[1;" ;
COLOR_END = "\033[m" ;
RED = "31m" ;
GREEN = "32m" ;
YELLOW = "33m" ;
BLUE = "34m" ;
TEAL = "36m" ;
WHITE = "37m" ;
MISSING = "Missing" ; # Patch is missing
HERE = "x" ; # Patch is installed
# Default columns size
COL_NODE = 8 ;
COL_PATCH = 6 ;
COL_DESCR = 10 ;
cols = cols -5 ; # Screen size, dont want to be too short
nb_missing = 0 ; # Number of missing patches
split(hosts, tab_hosts, ",") ; # An array with the hosts; n is number of hosts
n = asort(tab_hosts) ; # Sort by hostname
for (x in tab_hosts){
if (length(tab_hosts[x]) > COL_NODE) {COL_NODE = length(tab_hosts[x]) + 2}
}
}
#
# A function to center the outputs with colors
#
function center( str, col_size, color, sep) { right = int((col_size - length(str)) / 2) ;
left = col_size - length(str) - right ;
return sprintf(COLOR_BEGIN color "%" left "s%s%" right "s" COLOR_END sep, "", str, "" ) ;
}
#
# A function that just print a "---" white line
#
function print_a_line(size){
printf("%s", COLOR_BEGIN WHITE) ;
for (k=1; k<=size; k++) {printf("%s", "-");} ; # n = number of nodes
printf("%s", COLOR_END"\n") ;
}
{ # Save all the patches list
if ($2 in all_patches){ cpt++ ;
} else {
all_patches[$2] = $3 ;
if (length($2) > COL_PATCH) {COL_PATCH = length($2) + 2}
if (length($3) > COL_DESCR) {COL_DESCR = length($3) + 1}
}
# Save all the patches per node
tab_patches[$1][$2] = $2 ;
}
END {
# To make it fit and nice depending on the screen size
out_size=(COL_PATCH+n*COL_NODE+COL_DESCR+n+2) ;
if (out_size > cols) {
COL_DESCR = COL_DESCR - (out_size - cols) ;
out_size = cols ;
}
# Header
print_a_line(out_size) ;
printf("%-"COL_PATCH"s|", " Patch id") ;
for (i=1; i<=n; i++){ # Each node
printf("%s", center(tab_hosts[i], COL_NODE, WHITE, "|")) ;
}
printf(" %-"COL_DESCR"s", "Patch description") ;
printf("\n") ;
print_a_line(out_size) ;
y = asorti(all_patches, all_patches_sorted)
for (j=1; j<=y; j++){
patch_id = all_patches_sorted[j] ;
printf("%s", center(patch_id, COL_PATCH, WHITE, "|")) ;
for (i=1; i<=n; i++){ # Each node
if (length(tab_patches[tab_hosts[i]][patch_id]) > 0){
printf("%s", center(HERE , COL_NODE, GREEN, "|")) ;
} else {
printf("%s", center(MISSING, COL_NODE, RED , "|")) ;
nb_missing++ ;
}
}
printf(" %-"COL_DESCR"s", substr(all_patches[patch_id], 1, COL_DESCR)) ;
printf("\n") ;
}
# Footer
print_a_line(out_size) ;
print nb_missing >> tempfile ;
}' "${TEMP}"
rm -f "${TEMP}"
else
printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] Cannot find ${OH}/OPatch/opatch; will skip ${OH}" >&2
fi
done
./list-ohpatches.sh
2023-09-29_125128 [INFO] Starting collecting GI/OH patch information
2023-09-29_125128 [WARNING] It may be a bit slow if you have many nodes and patches as opatch lspatches is slow
2023-09-29_125128 [INFO] Proceeding with /u01/app/19.0.0/grid . . .
Thursday, 14 September 2023
Oracle spfile backup and restore
to find spfile !!! asmcmd find --type PARAMETERFILE +DATA1 "*" asmcmd find --type PARAMETERFILE '*' '*' +DATA1/ORCL/PARAMETERFILE/spfile.263.1147405219 +DATA1/ORCL/PARAMETERFILE/spfile.271.1147416877
===
or !!
spfile info !!!
SET PAGESIZE 9999 linesize 500
SET VERIFY off
COLUMN full_alias_path FORMAT a63 HEAD 'File Name'
COLUMN system_created FORMAT a8 HEAD 'System|Created?'
COLUMN bytes FORMAT 9,999,999,999,999 HEAD 'Bytes'
COLUMN space FORMAT 9,999,999,999,999 HEAD 'Space'
COLUMN type FORMAT a18 HEAD 'File Type'
COLUMN redundancy FORMAT a12 HEAD 'Redundancy'
COLUMN striped FORMAT a8 HEAD 'Striped'
COLUMN creation_date FORMAT a20 HEAD 'Creation Date'
COLUMN disk_group_name noprint
BREAK ON report ON disk_group_name SKIP 1
compute sum label "" of bytes space on disk_group_name
compute sum label "Grand Total: " of bytes space on report
SELECT
CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path
, bytes
, space
, NVL(LPAD(type, 18), '<DIRECTORY>') type
, creation_date
, disk_group_name
, LPAD(system_created, 4) system_created
FROM
( SELECT
g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, a.system_created system_created
, f.bytes bytes
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_alias a USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
)
WHERE type IS NOT NULL
and type like '%PARAMETERFILE%'
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
/
System File Name Bytes Space File Type Creation Date Created? --------------------------------------------------------------- ------------------ ------------------ ------------------ -------------------- -------- +DATA/GARBAGE/PARAMETERFILE/spfile.269.966963741 4,608 1,048,576 PARAMETERFILE 01-FEB-2018 17:02:20 Y
===
asmcmd ls -ltr +DATA1/ORCL/PARAMETERFILE/spfile.271.1147416877
WARNING: option 'r' is deprecated for 'ls'
please use 'reverse'
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE SEP 13 07:00:00 Y spfile.271.1147416877
asmcmd spbackup +DATA1/ORCL/PARAMETERFILE/spfile.271.1147416877 /home/grid/spfile_ORCL.bak
error !!!!!!!!!!!!!!!!!!
asmcmd spbackup +DATA1/ORCL/PARAMETERFILE/spfile.271.1147416877 /home/grid/spfile_ORCL.bak
ORA-15056: additional error message
ORA-06512: at line 7
ORA-17503: ksfdopn:2 Failed to open file +DATA1/ORCL/PARAMETERFILE/spfile.271.1147416877
ORA-15309: could not access database SPFILE in ASM instance
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 635
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)
asmcmd -p
srvctl start database -d ORCL
srvctl config database -d ORCL
Database unique name: ORCL
Database name:
Oracle home: /u01/app/oracle/product/19.0.0/dbhome_3
Oracle user: oracle
Spfile: +DATA1/ORCL/PARAMETERFILE/spfile.271.1147416877
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA1
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: ORCL1,ORCL2
Configured nodes: 533853-oralab4,533854-oralab5
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
run {
allocate channel ch1 device type disk;
backup spfile;
}
backup spfile format '/home/oracle/spfile_%t_s%s_s%p_spfile' ;
RMAN>
Starting backup at 14-SEP-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 14-SEP-23
channel ORA_DISK_1: finished piece 1 at 14-SEP-23
piece handle=/home/oracle/spfile_1147492136_s2_s1_spfile tag=TAG20230914T034856 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-SEP-23
Starting Control File and SPFILE Autobackup at 14-SEP-23
piece handle=+DATA1/ORCL/AUTOBACKUP/2023_09_14/s_1147492137.264.1147492139 comment=NONE
Finished Control File and SPFILE Autobackup at 14-SEP-23
RMAN>
run {
backup spfile format '/home/grid/spfile_%t_s%s_s%p_spfile' ;
backup spfile;
}
RMAN> SELECT DBID FROM V$DATABASE;
DBID
----------
1674589424
RUN{
set dbid=1674589424;
RESTORE SPFILE TO PFILE '/home/oracle/spfile_1147492136_s2_s1_spfile.txt' FROM '/home/oracle/spfile_1147492136_s2_s1_spfile';
}
RMAN> RESTORE SPFILE TO PFILE '/home/oracle/spfile_1147492136_s2_s1_spfile.txt' FROM '/home/oracle/spfile_1147492136_s2_s1_spfile';
Starting restore at 14-SEP-23
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/spfile_1147492136_s2_s1_spfile
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 14-SEP-23
RMAN>
ls -ltr /home/oracle/spfile_1147492136_s2_s1_spfile.txt
-rw-r--r--. 1 oracle asmadmin 1707 Sep 14 03:54 /home/oracle/spfile_1147492136_s2_s1_spfile.txt
Tuesday, 5 September 2023
DBCA Silent Mode 19c
create 19c database
for file system
-storageType FS
-datafileDestination /u01/db_files
select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
export Oracle home and sid
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ${ORACLE_SID} -sid ${ORACLE_SID} -characterSet AL32UTF8 -sysPassword xxxxx -systemPassword xxxxx -createAsContainerDatabase false -totalMemory 6000 -storageType ASM -datafileDestination '+DATA' -emConfiguration NONE -ignorePreReqs -sampleSchema true
for RAC
./dbca -silent -nodelist ora1,ora2 -createDatabase -templateName General_Purpose.dbc -gdbName ORCL -sid ORCL -SysPassword test123 -SystemPassword test123 -emConfiguration none -redoLogFileSize 1024 -recoveryAreaDestination '+DATA1' -storageType ASM -asmsnmpPassword welcome1 -diskGroupName '+DATA1' -recoveryGroupName '+DATA1' -listeners LISTENER -registerWithDirService false -characterSet WE8ISO8859P9 -nationalCharacterSet AL16UTF16 -databaseType MULTIPURPOSE -automaticMemoryManagement true -totalMemory 60480 -sampleSchema true & ==== another db creation https://anuj-singh.blogspot.com/search?q=General_Purpose.dbc which dbca /u01/app/oracle/product/19.0.0/dbhome1/bin/dbca [oracle@oragrid ~]$ ls -ltr /u01/app/oracle/product/19.0.0/dbhome1/assistants/dbca/templates/General_Purpose.dbc -rw-r----- 1 oracle oinstall 4768 Apr 17 2019 /u01/app/oracle/product/19.0.0/dbhome1/assistants/dbca/templates/General_Purpose.dbc [oracle@oragrid ~]$ /u01/app/oracle/product/19.0.0/dbhome1/bin/dbca -CreateDatabase -silent \ -gdbName v14mpc \ -sid v14mpc \ -sysPassword sys \ -systemPassword sys \ -createAsContainerDatabase true \ -numberofPDBs 1 \ -pdbname v14mpcpdb \ -pdbAdminUserName pdba \ -pdbAdminPassword password \ -emConfiguration LOCAL \ -dbsnmpPassword dbsnmp123 \ -storageType ASM \ -datafiledestination +DATA \ -recoveryAreaDestination +DATA \ -databaseType MULTIPURPOSE \ -responseFile NO_VALUE \ -totalmemory 4096 \ -characterset AL32UTF8 \ -nationalcharacterset AL16UTF16 \ -automaticmemorymanagement FALSE \ -templateName /u01/app/oracle/product/19.0.0/dbhome1/assistants/dbca/templates/General_Purpose.dbc [oracle@oragrid ~]$ /u01/app/oracle/product/19.0.0/dbhome1/bin/dbca -CreateDatabase -silent \ > -gdbName v14mpc \ > -sid v14mpc \ > -sysPassword sys \ > -systemPassword sys \ > -createAsContainerDatabase true \ > -numberofPDBs 1 \ > -pdbname v14mpcpdb \ > -pdbAdminUserName pdba \ > -pdbAdminPassword password \ > -emConfiguration LOCAL \ > -dbsnmpPassword dbsnmp123 \ > -storageType ASM \ > -datafiledestination +DATA \ > -recoveryAreaDestination +DATA \ > -databaseType MULTIPURPOSE \ > -responseFile NO_VALUE \ > -totalmemory 4096 \ > -characterset AL32UTF8 \ > -nationalcharacterset AL16UTF16 \ > -automaticmemorymanagement FALSE \ > -templateName /u01/app/oracle/product/19.0.0/dbhome1/assistants/dbca/templates/General_Purpose.dbc [WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards. CAUSE: a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. b.The password entered is a keyword that Oracle does not recommend to be used as password ACTION: Specify a strong password. If required refer Oracle documentation for guidelines. [WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards. CAUSE: a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. b.The password entered is a keyword that Oracle does not recommend to be used as password ACTION: Specify a strong password. If required refer Oracle documentation for guidelines. [WARNING] [DBT-06208] The 'PDBADMIN' password entered does not conform to the Oracle recommended standards. CAUSE: a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. b.The password entered is a keyword that Oracle does not recommend to be used as password ACTION: Specify a strong password. If required refer Oracle documentation for guidelines. Prepare for db operation 7% complete Registering database with Oracle Restart 11% complete Copying database files 33% complete Creating and starting Oracle instance 35% complete 38% complete 42% complete 45% complete 48% complete Completing Database Creation 53% complete 55% complete 56% complete Creating Pluggable Databases 60% complete 78% complete Executing Post Configuration Actions 100% complete Database creation complete. For details check the logfiles at: /u01/app/oracle/cfgtoollogs/dbca/v14mpc. Database Information: Global Database Name:v14mpc System Identifier(SID):v14mpc Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/v14mpc/v14mpc0.log" for further details.
Monday, 4 September 2023
How to change retention of securefile Lob segment
How to change retention of securefile Lob segment How to change retention of securefile Lob segment (Doc ID 2175438.1) we can only specify RETENTION parameter For SECUREFILE LOBs. Also note that you can specify either PCTVERSION or RETENTION for BASICFILE LOBs, but not both. show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> CREATE test.TABLE cust_int (id number,c_lob CLOB) LOB(c_LOB) STORE AS SECUREFILE ; Table created. create table test.cust_int (id number,c_lob CLOB) LOB(c_LOB) STORE AS SECUREFILE ; Table created. set linesize 300 col owner for a20 col TABLE_NAME for a20 col COLUMN_NAME for a20 col RETENTION_TYPE for a15 select owner,TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='CUST_INT'; OWNER TABLE_NAME COLUMN_NAME PCTVERSION RETENTION SEC RETENTI RETENTION_VALUE -------------------- -------------------- -------------------- ---------- ---------- --- ------- --------------- TEST CUST_INT C_LOB YES DEFAULT alter system set undo_retention=1500; [-PRIMARY-]sys@ORCLD> alter system set undo_retention=1500; System altered. [-PRIMARY-]sys@ORCLD> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 1500 <<< UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention undo_tablespace string UNDOTBS1 [-PRIMARY-]sys@ORCLD> alter table
