Shulou(Shulou.com)06/01 Report--

This is an article written by an oracle rookie who is suffering and struggling because of the oppression of his leader.

Detailed installation documentation in the previous blog, errors that may be encountered during installation and available solutions for testing have been given.

Differences between Oracle12C and 11g version

I encountered a problem when creating a user (my findings are based on the problems encountered):

Problem description:

Prompt ORA-65096 when creating a user required for the service: the public user name or role is not valid.

The cause of the problem:

According to the official Oracle documentation, the creation of a public user name (CDB user) for version 12C must start with clocked # or clocked #. After testing, you must also log in with the user name of Category #.

CDB and PDB explain:

Oracle 12C introduces new features of CDB and PDB, allowing a database container (CDB) to host multiple pluggable databases (PDB) in the multi-rent user environment (Multitenant Environment) introduced by ORACLE 12C databases.

The full name of CDB is Container Database, the Chinese translation is database container, and the full name of PDB is Pluggable Database. You can plug and unplug the database.

Before ORACLE 12C, the instance and database had an one-to-one or many-to-one relationship (RAC): that is, an instance could only be associated with one database, and the database could be loaded by multiple instances. It is impossible for an instance to have an one-to-many relationship with a database. When entering ORACLE 12C, the instance can have an one-to-many relationship with the database.

The following is a concept map given by the official documentation:

My understanding: this structure is somewhat similar to the relationship of docker. A management library (CDB) to manage all the pluggable databases (PDB), if you want to create a user in the PDB library, you do not need to add the user #, but also this user can only be used in this PDB library, can not be applied to other libraries.

Create a sample sql for a public user:

CREATE USER c##test IDENTIFIED BY test DEFAULT TABLESPACE test ACCOUNT UNLOCK

Similarly, when doing operations such as authorization or login, you must also add cantilever:

GRANT RESOURCE TO c##test

Comes with some common PDB statements related to CDB:

A sample statement to create a PDB space (you need to create a tablespace file first):

Create tablespace TSCREDITLOG datafile'/ home/oracle/app/oracle/creditdb/CREDITLOG/TSCREDITLOG01.dbf' size 20m

Query the Name of all PDB and CDB:

SELECT name, pdb FROM v$services ORDER BY name

View only the PDB library: show pdbs

(PDB I see two states: read-write and read-only)

Open all PDB libraries: alter pluggable database all open

Switch to a PDB (Container switch): ALTER SESSION SET container=NAMEPDB

Switch to CDB:alter session set container=cdb$root

Check the name of the container now: show conname

Some problems encountered in the operation and their solutions:

Only an error is reported as a question mark or garbled: an inconsistent character set results in:

Run: alter session set nls_language=american

If the database prompt is garbled with question marks:

You need to add the environment variable NLS_LANG to .bash _ profile under oracle users.

Failed to create tablespace:

1. There is a problem with tablespace folder authorization

two。 Tablespace folder does not exist

When you create a table, you are prompted to create a failure (insufficient permissions, no users, etc.):

Check the script that creates the user, and the first time it is executed, the authorization is successful or the creation is successful but without permission, so it is executed again. Problem solving (problem rule-big trick: many problems can be solved by restarting the instance or re-executing).

Create tablespace error ORA-65048:

Error processing current DDL statement in pluggable database PDBORCL ORA-00959: tablespace 'TEST' does not exist

Just restart the database to solve the problem!

The method of starting and shutting down Oracle Database

Sql > shutdown abort;-one by one abnormal shutdown, but the fastest way to shut down, because of the newly installed database, there are not so many scruples.

Sql > startup

You can happily create users normally ~ ~

Navicat cannot connect to the oracle12c problem, indicating that there is no matching authentication protocol:

The cause of the problem: Navicat has a low version of the OCI.dll file. No more in-depth study, the following directly put the solution found.

Two files that require Oracle:

Baidu Cloud Link: http://pan.baidu.com/s/1jIfQ95G password: wm9l

If you don't have Navicat, go to Baidu next (Nacicat for oracle).

Here, extract the instantclient-basic-nt-12.1.0.2.0.zip directly to the instantclient_10_2 directory of the extracted directory of Navicat for Oracle.

Then add instantclient_12_1 to this directory, and then extract the instantclient-sqlplus-nt-12.1.0.2.0.zip to instantclient_12_1.

Complete

Finally, open Navicat for Oracle and click tools-> options-> OCI.

Here are two options. Just refer to my path.

OCI your path\ instantclient_12_1.oci.dll

SQL your path\ instantclient_12_1.sqlplus.exe

Then create a new connection and connect to the database:

Got it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.