Steps to register in oracle.com for metalink , forum and software downloads. Open oracle.com in your browser on the landing page click on Sign in / Register. On the Sign in page click on is create account . Fill up the details. Verify the account by clicking on the link sent on the email. This is a simple video demonstration. Enjoy downloading Oracle Sotwares for free , Use oracle forums to get help from other members , also download patches and using metalink if you have Oracle software licence .
Views: 6524 OracleDBA
IMPORTANT RAC COMMANDS for Oracle 12c: IMPORTANT RAC COMMANDS $crsctl check cluster check the status for a local node $crsctl check cluster -all check the status for all nodes #crsctl stop cluster stops the cluster on local nodes #crsctl start cluster starts the cluster on local nodes #crsctl stop cluster -all stops the cluster on all the nodes #crsctl start cluster -all starts the cluster on all nodes #crsctl stop crs stops the cluster including OHAS #crsctl start crs start the cluster stack #crsctl query css votedisk check the status of voting disk #crsctl query crs activeversion check the status of crs version #crsctl status resource -t list of resources running on the cluster #crsctl status resource -t -init list of background process for the resources #crs_stat-t-v list of resources running on the cluster
Views: 4948 OracleDBA
How to use Metalink in case of an issue. Open support.oracle.com in your browser on the landing page click on Sign in On the Sign in page give your creadentials. In the next page click on create SR Fill up the details. And its done. This is a simple video demonstration. Enjoy downloading patches for Oracle Sotwares , Use oracle support to get help from Oracle , also download documents and notes from metalink. You must have Oracle software licence to use metalink.
Views: 2272 OracleDBA
How to Install SQL Server Management Studio? How to get the SSMS in SQL Server 2016 and Above? Why I am not able to see SSMS in my Server? How to download the Latest Version of SSMS ? For the answer watch this Video Tutorial. This video is the Continuity to Sql Server 2016 Installation. https://youtu.be/BHk4WqJessM You can download the SSMS (Sql Server Management Studio) from the below page: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms We needs this to be separately Installed because in SQL Server 2016 it is not bundled. SSMS 17.1 is first update to the 17.X generation of SQL Server Management Studio. The 17.X generation provides support for almost all feature areas on SQL Server 2008 through SQL Server 2017. Version 17.X is also the generation of SSMS that supports SQL Analysis Service PaaS.
Views: 28235 OracleDBA
How to create an Oracle 12c Database in Oracle Linux
Views: 2403 OracleDBA
How to implement VPD (Virtual Private Database) in Oracle Database(9i,10g,11g,12c) Enterprise Edition. PART 2 Video. links: Part 1 https://youtu.be/JVvtvh3cN1I Part 2 https://youtu.be/xLcin_Wgbvs Part 3 https://youtu.be/mE1cSbmEwnQ Now we will conn to sec_adm user : ------------------ CONNECT sec_adm/sec_adm; This table will contain the user access info. CREATE TABLE users (id VARCHAR2(30) NOT NULL, ouser VARCHAR2(30) NOT NULL, first_name VARCHAR2(50) NOT NULL, last_name VARCHAR2(50) NOT NULL); INSERT INTO users VALUES ('HR','USER1','User','One'); INSERT INTO users VALUES ('FINANCE','USER2','User','Two'); COMMIT; conn schemaowner/schemaowner GRANT SELECT, INSERT ON user_data TO user1, user2; Now we will create the context package: ---------------------------------------- CONNECT sec_adm/sec_adm; CREATE CONTEXT SCHEMAOWNER USING context_package; CREATE OR REPLACE PACKAGE context_package AS PROCEDURE set_context; END; / Next we create the context_package body which will actually set the user context. CREATE OR REPLACE PACKAGE BODY context_package IS PROCEDURE set_context IS v_ouser VARCHAR2(30); v_id VARCHAR2(30); BEGIN DBMS_SESSION.set_context('SCHEMAOWNER','SETUP','TRUE'); v_ouser := SYS_CONTEXT('USERENV','SESSION_USER'); BEGIN SELECT id INTO v_id FROM users WHERE ouser = v_ouser; DBMS_SESSION.set_context('SCHEMAOWNER','USER_ID', v_id); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_SESSION.set_context('SCHEMAOWNER','USER_ID', 0); END; DBMS_SESSION.set_context('SCHEMAOWNER','SETUP','FALSE'); END set_context; END context_package; / SHOW ERRORS Next we make sure that all users have access to the Context_Package. GRANT EXECUTE ON context_package TO schemaowner,user1,user2; Create Login Trigger Next we must create a trigger to fire after the user logs onto the database. CONNECT sec_adm/sec_adm; CREATE OR REPLACE TRIGGER set_security_context AFTER LOGON ON DATABASE BEGIN context_package.set_context; END; / SHOW ERRORS
Views: 329 OracleDBA
How to implement VPD (Virtual Private Database) in Oracle Database(9i,10g,11g,12c) Enterprise Edition. PART 1 Video links: Part 1 https://youtu.be/JVvtvh3cN1I Part 2 https://youtu.be/xLcin_Wgbvs Part 3 https://youtu.be/mE1cSbmEwnQ This is Part of Oracle Advance Security. Please view my earlier videos on how to create a database , if you are not having a database. 1)First we will create the users needed for our environment a)Owner of the schema which will have the objects or tables b)security admin user c)non owner user1 & user2 which will have limited access as per data in the rows (here user1 will have access to data with HR and user2 with FINANCE) Object owner creation: -------------- CREATE USER schemaowner IDENTIFIED BY schemaowner DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT connect, resource TO schemaowner; alter user schemaowner quota unlimited on users; security admin user creation: -------------- CREATE USER SEC_ADM IDENTIFIED BY sec_adm default tablespace users temporary tablespace TEMP; GRANT CONNECT, RESOURCE TO sec_adm; alter user sec_adm quota unlimited on users; garnts for the sec_adm user: ------------- GRANT CREATE SESSION,CREATE ANY CONTEXT,CREATE PROCEDURE,CREATE TRIGGER, ADMINISTER DATABASE TRIGGER TO SEC_ADM; GRANT EXECUTE ON DBMS_SESSION TO SEC_ADM; GRANT EXECUTE ON DBMS_RLS TO SEC_ADM; create user with restricted access on table: ----------------------- CREATE USER user1 IDENTIFIED BY user1 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT connect, resource TO user1; CREATE USER user2 IDENTIFIED BY user2 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT connect, resource TO user2; Now we will log in as schemaowner and create one tables : ------------------- CONN schemaowner/schemaowner This table is the table which contains the secure as well as un-restricted data CREATE TABLE user_data (column1 VARCHAR2(50) NOT NULL, user_id VARCHAR2(30) NOT NULL); Now to access this table from other schema we need to give the select and insert privs to: GRANT SELECT, INSERT ON user_data TO sec_adm,user1, user2;
Views: 1113 OracleDBA
Here we will see How to download Oracle Software from Edelivery.Oracle.Com. The steps are easy as given in the video.
Views: 1291 OracleDBA
How to create an Oracle12c Multitenant database. HOw to create a database in Multitenant architecture: 1)create a contaner db 2)create a pdb Just follow my steps to do this The global DB is the CDB or container DB, its like a normal DB with its own SGA and background processes. PDB is the Pluggable database attached to it
Views: 620 OracleDBA
Here we are going to see how SAP Power Designer is installed. SAP PowerDesigner (PowerDesigner) is a collaborative enterprise modelling tool produced by Sybase, currently owned by SAP. PowerDesigner supports model-driven architecture software design. PowerDesigner stores models using a variety of file extensions, such as .bpm, .cdm and .pdm. The internal file structure can be either XML or a compressed binary file format. PowerDesigner can also store models in a database repository.
Views: 1312 OracleDBA
How to Install SQL Developer on Windows 1) Download for the below link http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html 2) Make sure you download Windows 64-bit with JDK 8 included version. 3) Install SQL developer as given in video. 4) Create a new connection to connect to user , just need to fill up the form. We are not creating any real DB connection at present as I am keeping it for DB creation tutorial. For any query write in the comment section , We will surely reply to it. Please subscribe to my channel for more videos. I created this video with the YouTube Video Editor (http://www.youtube.com/editor)
Views: 1554 OracleDBA
How to Restart all RAC Database instances at same time in oracle 12c version 220.127.116.11.0 Just follow the steps and read the details give, if needed pause the video.
Views: 334 OracleDBA
Detection of Soft Corruption in 12c: To use the scripts below replace : , ",# and $ by proper bracket Lets Create a tablespace and small table. SQL: create tablespace DEMO1 datafile '/u02/tstdb1/TSTDB1/datafile/demo01.dbf' size 50M SQL: create table objects tablespace DEMO1 as select * from dba_objects; SQL: alter table objects add constraint pk_obj primary key #object_id$; SQL: create index idx_obj_name on objects#object_name$ tablespace demo1; Backup tablespace. RMAN: backup tablespace DEMO1; We need to put the DB in archivelog first Rebuild index with NOLOGGING option to simulate soft corruption later. RMAN: alter index idx_obj_name rebuild nologging; Confirm that we have datafiles that require backup because they have been affected with NOLOGGING operation. RMAN: report unrecoverable; Simulate corruption. RMAN: alter database datafile 5 offline; RMAN: restore datafile 5; RMAN: recover datafile 5; RMAN: alter database datafile 5 online; Query table with corrupted index and notice error. SQL: select count#*$ from objects where object_name like 'A%'; Let’s perform validation of datafile to check block corruption. RMAN: backup validate check logical datafile 5; Notice that we have 457 blocks marked corrupt but v$database_block_corruption view is empty. SQL: select count#*$ from v$database_block_corruption; Let’s query v$nonlogged_block view. set lines 200 set pages 999 select file#, block#, blocks,object#,reason from v$nonlogged_block; Will RMAN detect that we have corrupted blocks? RMAN: backup datafile 5; RMAN backup won’t fail due to NOLOGGING corrupt blocks and our backup will contain soft corrupted blocks. Let’s Identify corrupt segments using v$nonlogged_block view. set lines 2000 set pages 9999 col owner for a20 col partition_name for a10 col segment_name for a20 SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file# , greatest#e.block_id, c.block#$ corr_start_block# , least#e.block_id+e.blocks-1, c.block#+c.blocks-1$ corr_end_block# , least#e.block_id+e.blocks-1, c.block#+c.blocks-1$ - greatest#e.block_id, c.block#$ + 1 blocks_corrupted FROM dba_extents e, V$NONLOGGED_BLOCK c WHERE e.file_id = c.file# AND e.block_id "= c.block# + c.blocks - 1 AND e.block_id + e.blocks - 1 := c.block# UNION SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file# , header_block corr_start_block# , header_block corr_end_block# , 1 blocks_corrupted FROM dba_segments s, V$NONLOGGED_BLOCK c WHERE s.header_file = c.file# AND s.header_block between c.block# and c.block# + c.blocks - 1 UNION SELECT null owner, null segment_type, null segment_name, null partition_name, c.file# , greatest#f.block_id, c.block#$ corr_start_block# , least#f.block_id+f.blocks-1, c.block#+c.blocks-1$ corr_end_block# , least#f.block_id+f.blocks-1, c.block#+c.blocks-1$ - greatest#f.block_id, c.block#$ + 1 blocks_corrupted FROM dba_free_space f, V$NONLOGGED_BLOCK c WHERE f.file_id = c.file# AND f.block_id "= c.block# + c.blocks - 1 AND f.block_id + f.blocks - 1 := c.block# order by file#, corr_start_block# / This is the best outcome to get if you notice corruption errors. All errors are related to index corruption so we could fix this problem rebuilding index. alter index idx_obj_name rebuild; Simply issuing "alter index rebuild" command won't work. We should mark index unusable to drop segment before rebuilding it or just rebuild index with online option. It is better choice to mark index unusable because you don't need additional space then, but I will simply rebuild index with online option and see what will happen. SQL: alter index idx_obj_name rebuild online; Index altered. SQL: select count#*$ from objects where object_name like 'A%'; No errors... but, let's validate datafile for corruption. RMAN: backup validate check logical datafile 5; Notice "Marked Corrupt" column. Hm... 457 like before. Don't worry, this is not new corruption. These are FREE blocks which will be reused and Oracle will automatically re-format those blocks. Query the v$nonlogged_block view again as given above. We could force re-formatting creating dummy table and inserting data to dummy table. Check Doc ID 336133.1. create table s # n number, c varchar2#4000$ $ nologging tablespace DEMO1; SQL: BEGIN FOR i IN 1..1000000 LOOP INSERT /*+ APPEND */ INTO sys.s select i, lpad#'REFORMAT',3092, 'R'$ from dual; commit ; END LOOP; END; / SQL: drop table sys.s purge; Notice that we don't have corrupted blocks any more. RMAN: backup validate check logical datafile 5;
Views: 222 OracleDBA
How Update PDM from Database in SAP Powerdesigner. Today we will see how to update Powerdesigner PDM with the latest information from database using direct connection. This is better method of updation instead of using DDL from database. 1)I have a blank PDM (Physical Data Model) 2)Here give your user name and password for the schema: 3)We are using Oracle 12 c Database with scott schema 4)We can see the list of objects to be imported into Power Designer. It takes time and you need a good CPU machine. I am using Virtual machine so it is real slow. As you can see here, my PDM is ready. Thats all , Its real easy. Data modelling , DBA, Oracle
Views: 487 OracleDBA
How to create Virtual Columns in Oracle Database 12c When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below. column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL] If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only. The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form. CREATE TABLE employees ( id NUMBER, first_name VARCHAR2(10), last_name VARCHAR2(10), salary NUMBER(9,2), comm1 NUMBER(3), comm2 NUMBER(3), salary1 AS (ROUND(salary*(1+comm1/100),2)), salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL, CONSTRAINT employees_pk PRIMARY KEY (id) ); INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2) VALUES (1, 'JOHN', 'DOE', 100, 5, 10); INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2) VALUES (2, 'JAYNE', 'DOE', 200, 10, 20); COMMIT; Querying the table shows the inserted data plus the derived commission-based salaries. SELECT * FROM employees; ID FIRST_NAME LAST_NAME SALARY COMM1 COMM2 SALARY1 SALARY2 ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1 JOHN DOE 100 5 10 105 110 2 JAYNE DOE 200 10 20 220 240 2 rows selected. SQL The expression used to generate the virtual column is listed in the DATA_DEFAULT column of the [DBA|ALL|USER]_TAB_COLUMNS views. COLUMN data_default FORMAT A50 SELECT column_name, data_default FROM user_tab_columns WHERE table_name = 'EMPLOYEES'; COLUMN_NAME DATA_DEFAULT ------------------------------ -------------------------------------------------- ID FIRST_NAME LAST_NAME SALARY COMM1 COMM2 SALARY1 ROUND("SALARY"*(1+"COMM1"/100),2) SALARY2 ROUND("SALARY"*(1+"COMM2"/100),2) 8 rows selected. SQL Notes and restrictions on virtual columns include: 1)Indexes defined against virtual columns are equivalent to function-based indexes. 2)Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML. 3)Tables containing virtual columns can still be eligible for result caching. 4)Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled: a)Constraint on the virtual column must be disabled and re-enabled. b)Indexes on the virtual column must be rebuilt. c)Materialized views that access the virtual column must be fully refreshed. d)The result cache must be flushed if cached queries have accessed the virtual column. e)Table statistics must be regathered. 5)Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables. 6)The expression used in the virtual column definition has the following restrictions: a.It cannot refer to another virtual column by name. b.It can only refer to columns defined in the same table. c.If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column. e.The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.
Views: 566 OracleDBA
How to Create an user in Oracle with minimum grants/privileges we need to connect as SYSDBA lets ceate an user test you can see the user is created : But can it connect? lets open a new session we can see it lacks create session priv. so, we grant the create session privilege to user test lets test Its connecting. But is it able to create a table ,lets check. it lacks the ability to create table. SO lets give the privilege. lets test again the create table command. its throwing error regarding no Privilege on users tablespace . But we have given USERS as the default tablespace to test(user). So whats the problem? The problem is there is not quota defined for user test. lets give some quota like "10MB" or UNLIMITED , we will giev 10MB NOw let us test. So we can see that we now have sufficient / minimum privileges for the user test. Hope this will help in understanding the concept behind user creation
Views: 113 OracleDBA
How to Install Oracle 12c Database Software on Oracle Linux 7 I created this video with the YouTube Video Editor (http://www.youtube.com/editor)
Views: 1705 OracleDBA
File Encryption : How To Encrypt And decrypt Files in Linux and UNIX-like operating systems such as FreeBSD, Solaris, MacOS and others With A Password. To encrypt and decrypt files with a password, use gpg command. It is an encryption and signing tool for Linux and UNIX-like operating systems such as FreeBSD, Solaris, MacOS and others. Gnupg is a complete and free implementation of the OpenPGP standard.
Views: 98 OracleDBA
How to Install Microsoft SQL Server 2016 for Beginner You just need to follow the steps I am showing . You can go through my earlier video in case you need to download the software: https://youtu.be/ZUObR38LHNs And For SQL Server Management Studio there is a separate Installer . For this watch My next video https://youtu.be/BHk4WqJessM Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network (including the Internet). Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users. Link to MS SQL website: https://www.microsoft.com/en-in/sql-server/sql-server-2016
Views: 653 OracleDBA
How to implement VPD (Virtual Private Database) in Oracle Database(9i,10g,11g,12c) Enterprise Edition. PART 3 Video. Links: Part 1 https://youtu.be/JVvtvh3cN1I Part 2 https://youtu.be/xLcin_Wgbvs Part 3 https://youtu.be/mE1cSbmEwnQ Create Security Policies In order for the context package to have any effect on the users interaction with the database, we need to define a security_package for use with the security policy. This package will tell the database how to treat any interactions with the specified table. CREATE OR REPLACE PACKAGE security_package AS FUNCTION user_data_insert_security(owner VARCHAR2, objname VARCHAR2) RETURN VARCHAR2; FUNCTION user_data_select_security(owner VARCHAR2, objname VARCHAR2) RETURN VARCHAR2; END security_package; / Next we create the security_package body. CREATE OR REPLACE PACKAGE BODY Security_Package IS FUNCTION user_data_select_security(owner VARCHAR2, objname VARCHAR2) RETURN VARCHAR2 IS predicate VARCHAR2(2000); BEGIN predicate := '1=2'; IF (SYS_CONTEXT('USERENV','SESSION_USER') = 'SCHEMAOWNER') THEN predicate := NULL; ELSE predicate := 'USER_ID = SYS_CONTEXT(''SCHEMAOWNER'',''USER_ID'')'; END IF; RETURN predicate; END user_data_select_security; FUNCTION user_data_insert_security(owner VARCHAR2, objname VARCHAR2) RETURN VARCHAR2 IS predicate VARCHAR2(2000); BEGIN predicate := '1=2'; IF (SYS_CONTEXT('USERENV','SESSION_USER') = 'SCHEMAOWNER') THEN predicate := NULL; ELSE predicate := 'USER_ID = SYS_CONTEXT(''SCHEMAOWNER'',''USER_ID'')'; END IF; RETURN Predicate; END user_data_insert_security; END security_package; / SHOW ERRORS Next we make sure that all users have access to the Security_Package. GRANT EXECUTE ON security_package TO schemaowner,user1,user2; Apply Security Policies to Tables The DBMS_RlS package is used to apply the security policay, implemented by security_package, to the the relevant tables. BEGIN DBMS_RLS.add_policy('SCHEMAOWNER', 'USER_DATA', 'USER_DATA_INSERT_POLICY', 'SEC_ADM', 'SECURITY_PACKAGE.USER_DATA_INSERT_SECURITY', 'INSERT', TRUE); DBMS_RLS.add_policy('SCHEMAOWNER', 'USER_DATA', 'USER_DATA_SELECT_POLICY', 'SEC_ADM', 'SECURITY_PACKAGE.USER_DATA_SELECT_SECURITY', 'SELECT'); END; / Test VPD Finally, test that the VPD is working correctly. Now we will do the test CONNECT user1/user1 INSERT INTO schemaowner.user_data (column1, user_id) VALUES ('User 1', 'HR'); INSERT INTO schemaowner.user_data (column1, user_id) VALUES ('User 2', 'FINANCE'); COMMIT; CONNECT user2/user2 INSERT INTO schemaowner.user_data (column1, user_id) VALUES ('User 1', 'HR'); INSERT INTO schemaowner.user_data (column1, user_id) VALUES ('User 2', 'FINANCE'); COMMIT; CONNECT schemaowner/schemaowner SELECT * FROM schemaowner.user_data; Checking the select option: ------------------------ CONNECT user1/user1 SELECT * FROM schemaowner.user_data; CONNECT user2/user2 SELECT * FROM schemaowner.user_data; Thats all for now
Views: 221 OracleDBA